PHP MySQL connections (how often?)

PHP
1

Hey guys,

I grab a lot of stuff from my MySQL database on most pages of my site. Is there a better way to go about grabbing this data and making connections to the MySQL database than to include the connection string / code and disconnection strings each time I pull data?

E.g.


$con = mysql_connect("localhost","toop","poop");
	if (!$con)
		die('Could not connect: ' . mysql_error());
	mysql_select_db($db, $con);
	
	$result = mysql_query('SELECT *...

..etc etc..
mysql_close($con);

Or is it better to have a global .php file that always keeps the connection to the database open?

2

If you use OOP then you could have a database class which gets passed around as needed. Are you closing the connections after each query or are you only closing the connection at the end of the script?

3

usually just at the end of the script, or else it seems pointless to keep closing the connection

4

anybody else have any input?

5

Unless you use persistent connections PHP itself closes all open connections when the script terminates even when (especially when) the script crashes. So it isn’t something you need to worry too much about.

Using persistent connections isn’t advised.

6

Just open a single connection at the start of the script, normally php will automatically close any open connections when the script has finished running. If your not using OOP then you should create an include file in which you keep the database connection lines that have other scripts that need the db access include the file, unless your opening a another link php will make use of the already open link.

btw, you may want to consider migrating from the mysql_* functions over to the mysqli_* functions as I believe the mysql_* functions are being deprecated and so may well be removed at some point.

7

Okay thanks guys

8

Would you recommend moving to MySQLi? My current site uses MySQL_ functions, not the “improved” class. Also, would you suggest using object oriented or procedural programming with MySQL_ class

9

I would suggest moving to [fphp]PDO[/fphp] actually.

10

May I ask why you recommend doing so?

11

Well for one, switching databases with PDO is simply a matter of changing the driver setting. For another, PDO features parameter binding which helps prevent SQL injection. For yet another reason, there has been talk of moving the other, older db libraries out of PHP core and leaving PDO in their place so they won’t be loaded by default.

12

And furthermore, PDOs exception handling allows you to drastically the amount of error checking you need to do while at the same time helping to ensure that any errors that are encountered are handled. Much cleaner code.

13

thx guys