Question 1 - Connecting to db multiple times
Currently I am connecting (and disconnecting) to the database multiple times to build my page, once each for building the nav, page content, and processing a form if it's been submitted. Would I be better off just connecting to the database once at the top of the page, and then closing the connection at the bottom of the page?
e.g. My page currently looks like this:

1. If a form has been submitted connect to the database
2. Post data
3. Close connection
4. PHP stuff
5. Connect to the database
6. Get nav data
7. Close connection
8. PHP stuff
9. Connect to the database
10. Get content data
11. Close the connection
12. PHP stuff

would it be better to do:

1. Connect to the database
2. If a form has been submitted Post data
3. PHP stuff
4. Get nav data
5. PHP stuff
6. Get content data
7. Close the connection
8. PHP stuff

Question 2 - adding values that already exist to a unique column
On my site users can add/remove tags for pages. The tags are just listed in a textbox seperated by a comma or semicolon. What I'm doing at the moment is to explode (in PHP) the tags textbox data, then add all the seperate tags posted to the tags table. Because the tags column is unique tags that are already in the table won't be added again. Is it okay to work like this or I would I be better checking whether a tag exists and then only adding it to the table if it's not already in there?

Thanks

Dave

In my opinion, for your first question I think your single connection / disconnection is the better option. And for your second question, I think that's the correct way to go, otherwise you're increasing your PHP running time for no real reason if the database can already handle it quite quickly.

Hey Dave,

Answering your first question:

It would be better to open a database connection once at the beginning and close it at the end of your php executions. Opening and closing repeatedly slows things down and adds load to your server. If you aren't working on a monster site, the easiest way to achieve this is to store your database details in an include file, have that file create a connection and store the resource in a variable. You can then use this variable in future SQL statements. It's then just a matter of closing the connection at the end of your PHP dealings.

Your second question:

If you have a column that is marked as unique then you would definitely want to check to ensure you're not doubling up on values when adding tags to the database.

Something like:




Using INSERT IGNORE is a handy shortcut for not adding duplicate rows.

Ah, that's much better explaination of question 2's answer... thanks, Rakuli!

Thanks for your help Rakuli, with regards to the duplicate values question, the reason I exploded the tags text input was I was inserting the tags one by one, like this:


For some reason I didn't even think of inserting multiple values in the one statement. Anyway, you can see from the above code that since it inserts each tag one by one if a tag already exists it will just abort that insertion and carry on with the next one. Since I didn't put any error handling in this code I wasn't even aware that an error was being generated when trying to insert a tag that already exists.

Anyway, I presume it's much more efficient to add the tags all in one go using your method? It would also mean I don't have to explode the tags input, just preg_replace semi-colons with commas.

Dave

This post has been edited by djeyewater: Nov 5 2008, 08:08 AM

You will probably still need to explode the tags because they are string input they need to be wrapped in quotes. You could use preg_replace to do that as well but it's probably better to explode so you can strip whitespace and not end up with empty values.

It is better to do more at once with MySQL rather than run multiple queries with PHP.

Thanks

Dave