Handling MySQL connections

[Shalazar]

If you have a page set-up where you will be making several different MySQL queries in distinct parts of the page, do you have to make fresh connections to the database each time you're going to drop into PHP mode?

Or is it better to use a persistent connection on pages like that?

And is there an advantage/disadvantage to each approach in terms of load time, and overall efficiency in displaying results?

[Justin]

Oops - I almost overlooked this one...

A MySQL connection is carried over throughout the entire script until you either close the connection, or the script terminates.

When you consider &quot;drop into PHP mode&quot;, think of it as being the opposite - because you are always in PHP - eg, the file is being parsed no matter what - but when you are outside the <? ?> tags, you are simply printing text to the browser. You're still in PHP though.

So there is no need to re-connect at all. Also note that Persistant connections are not supported, and calling a mysql_pconnect() will simply do an ordinary connection - eg, there would be no difference either way.

Hope this helps.

------------------
Justin Nelson
FutureQuest (http://www.FutureQuest.net/index.php) Support

[Shalazar]

That goes a long way to help Justin, thanks. Let me summarize as I understand it:

So on any given page, you only need one instance of openning a connection to the database, and then if you need queries or such down further in the page, oen can simply make the explicit query and nothing else - because the connection persists throughout the page unless otherwise closed.

But, if for some reason the connection is closed somewhere in the page, you would have to reopen it further down should you need to utilize it again.

[Justin]

You are correct. What I usually do is place all of my connection functions in a file by itself, and include() it when needed. This way your variables (eg, your plain text password) is only in one file. Makes updates/changes much easier too, as well as troubleshooting.

For example - the majority of PHP scripts I use (on various sites) would look like this if they needed to use MySQL:
</font><font face="Courier" size="3">
include &quot;connect.php&quot;;
$result = mysql_query (&quot;select foo from bar&quot;);
</font><font face="Verdana, Arial" size="2">
For any site that will use MySQL in every page, I just put the connection stuff in the header file, which also prints the initial HTML etc.

As for the connection closing, the only time that will happen is if you close the connection, or (very very rare) if the MySQL server is overloaded (in which case, 99% of the time any open connections will remain open; only new connections would not be allowed, which you would have handled when making the connection - hopefully :))

Hope this helps.

------------------
Justin Nelson
FutureQuest (http://www.FutureQuest.net/index.php) Support

[Justin]

Error handling is something that should be considered in any programming language, no matter what the program is suppsoed to do. The most common misconception is that your program will always get what it is expecting - be it user input, environment variables, or successful operation even in its own functions. Everything needs to be double checked before continuing on.

Have you ever visited a site using PHP and MySQL, and seen a slew of error messages? For example:

Warning: mysql_connect() failed in foo.php line 3
Warning: mysql_query() failed in foo.php line 5
Warning: 0 is not a mysql_result ID in foo.php line 6
Warning: 0 is not a mysql_result ID in foo.php line 6
Warning: 0 is not a mysql_result ID in foo.php line 7
Warning: 0 is not a mysql_result ID in foo.php line 7
The above was made up off the top of my head, but you get the idea - since the connection was not successful but the programmer had assumed it would be, the program continues. And without a connection ID, the query fails. And without a successful query, there is no results to check...

This is the most common problem in any program - assuming. Have you ever pasted a URL that is extremely large into Netscape? Immediate crash. Reproducable every time. Why? They assume you will not paste that large a URL in the address bar - but if you do, the program causes an internal overflow of some kind, and crashes.

Each step needs to be checked for success - you can suppress the PHP error messages by preceding each function call with an '@':

@mysql_connect (&quot;foo&quot;, &quot;bar&quot;, &quot;blah&quot;);

But this does not ensure success - it simply shuts PHP up. This is still not good enough...

Here is an example of what I use:
</font><font face="Courier" size="3">
$SQL = @mysql_connect (&quot;foo&quot;, &quot;bar&quot;, &quot;blah&quot;);
if (!$SQL) error (&quot;We're sorry, but our database is currently down. Please try again later.&quot;);
mysql_select_db (&quot;xdomain&quot;);

$result = mysql_query (&quot;select foo from bar&quot;);
if ($result) $total = mysql_numrows ($result);
if ($total) {
[nbsp][nbsp] # do stuff here

} else {
[nbsp][nbsp] print &quot;No results found.&quot;;

}
</font><font face="Verdana, Arial" size="2">
In the above example, I call error() - a function I always keep in my header file for handling errors, rather than using die().

In reality my programs usually check a lot more than what I have above - I want to know exactly what is going on and when and why/why not - and handle execution accordingly. It might mean retrying a time or two. It might mean checking some things out to try and figure out the problem, then sending an email to myself explaining what went wrong and how to fix it. It might mean exiting nice and clean, logging the problem. But one thing you will never see in any Windows program I've written is &quot;Illegal Operation&quot; or a GPF ;)

Reasons for error handling:

1) The above errors might scare a user away, or make them think they did something wrong

2) Which error looks more professional and well handled?

3) You certainly do not want your program continuing, especially if it might make decisions based on the results from the query - for example, say you are pulling in data and sending out emails or writing to a text file - for any number of reasons it is just good programming practice to always know what is going on in your program and why, and to keep as many details away from the user as possible.

At any rate, I didn't mean to get into a rant - and I am not trying to offend anyone or put down anyone's programming abilities - just some friendly advice, and you seem to be on the right track already :)

Hope this psychotic rambling made some sense to someone... :)

------------------
Justin Nelson
FutureQuest (http://www.FutureQuest.net/index.php) Support

[Shalazar]

Hey Justin, thanks for that post.[nbsp][nbsp]For those of us just getting into PHP and MySQL, it's nice to have a veteran user around to provide that kind of insight.

You're an excellent resource to have around, and will most certainly be a model from which I can start developingmy own dynamic content.

[jbroder]

Actually, I was hoping to provoke a good rant.

I knew I didn't go far enough, but I didn't know how far to go or where to start.

Thanks Justin.

[jbroder]

&quot;only new connections would not be allowed, which you would have handled when making the connection - hopefully&quot;

Is there a good description posted somewhere on how to handle mySQL errors? my handling is not what it needs to be....

[Brian]

You might want to check out the excellent MYSQL user manual
- http://www.mysql.com/Manual_chapter/manual_toc.html

-Brian