Good way to open and close connections

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
amir
Forum Contributor
Posts: 287
Joined: Sat Oct 07, 2006 4:28 pm

Good way to open and close connections

Post by amir »

Hi,

We've currently had a problem with our VPS server. We used too many resources.
I guess that the connection we have to read/write our database is the reason of this problem.

This is how we work:

Code: Select all

//Connection to localhost:
@ $handledb1 = mysql_connect("localhost","user","password");
mysql_select_db("namedb1", $handledb1);

//Connection to database on other server:
@ $handledb2 = mysql_connect("IP","user","password");
mysql_select_db("namedb2", $handledb2);

$query1 = "SELECT * FROM table WHERE field = '$value'";
$sql1 = mysql_query($query1, $handledb1);

$query2 = "SELECT * FROM table WHERE field = '$value'";
$sql2 = mysql_query($query2, $handledb2);
sometimes up to 6-7 queries in one php-file

This all works great. But is it holding resources or not?
I was thinking that after the script is done, all connections will be closed automatically and the used resources are free again? Or am I thinking wrong. I'm not sure anymore


Is there another way to read the database? SQL is very time consuming on large database, even with a good index?


Thanks,
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

Assuming you aren't opening new connections for each individual query I doubt that's where resources are being used. What sort of things are you processing/computing?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Each query generally be their own resources (if the query is unique among the other queries.) What is often suggested is to use mysql_free_result() immediately following the finished use of a query. Calling mysql_close() immediately after you're finished with the database can also help some. Looping a query can often be a bad code smell, so take great care in their usage.
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

You may also want to look at mysql_unbuffered_query() although that has some implications if you lose connectivity.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

If you can (like feyd suggested) call mysql_free_result() as soon as you are done with the result set. mysql_close() is an automatic call at the end of the script execution as long as you are not using persistent connections to the db server (mysql_pconnect() instead of mysql_connect()).
amir
Forum Contributor
Posts: 287
Joined: Sat Oct 07, 2006 4:28 pm

Post by amir »

Thanks a lot!
Post Reply