Urgent mysql_connect issue

[bfinke]

Right now I have a file called db.php that connects to my DB using a function like this:

$dbcnx = @mysql_pconnect($dbhost, $dbtable, $dbpass)
or die("The site database appears to be down.");

if ($dbselection!="" and !@mysql_select_db($dbselection))
die("The site database is unavailable.");


Now I am rather new to programming but we just setup on a new dedicated server and the site is running very slowly. I talked with the server manager and he says the MySql is eating up the resources and making it go very slow.

I THINK the problem is that before each SQL query, I have the code run the function above to connect to the DB first. Am I creating too many connections to the DB per user? What is the best way around this?

Should I be using mysql_connect instead? Would that require me to close the sql connection? (I am not closing any sql connection now).

Or should I stop calling the function to donnect to the DB before every query?

The reason I am not sure is that I have a file called accesscontrol.php that includes db.php and accesscontrol.php is called on every single page after a user logs in. So if I should be using mysql_pconnect still, how do I make it so it is called only once? If I go to mysql_connect, is this a good idea and do I need to close the connection thereafter?

THANKS THANKS THANKS!!!

[Fumigator]

Persistant connections are risky. Here's why:

http://us.php.net/manual/en/features.persistent-connections.php

I'd stick with normal connections unless you have a compelling reason (and the tuning to back it up) to use persistant connections.

[bfinke]

Thanks fumigator, I will change it to mysql_connect then.

Do you have to "close" connections to the DB using mysql_connect?

If so, where does this go? After $result?

$sql = "SELECT * FROM table";
$result = mysql_query($sql);


Also, if on the page there are multiple sql queries being made, do I need to connect each time, or is it enough to just call the function at the top of the page once (and if so, do I close the connection at the end of the page...?)

[Fou-Lu]

You *should* free and close any resource in PHP. So, after you're done with $result in the above, you're run it through mysql_free_result($result).
mysql_close($dbcnx) can be done at the end of the script; however, if you're opening this in an included file, I would not close this in a handling file. The reason for this is in case another file needs to make use of the connection it will be unavailable.

This all being said, if PHP terminates its script properly it will automatically close any open resource and free any allocated memory (although there are several ways atm to create memory leaks in PHP, I believe these are all based on OO code with its __autoload handling). If it terminates improperly for whatever reason, it risks not closing the connections.

[bfinke]

Thanks guys for your help, this is a great community and when I finally become knowledgeable enough I will contribute back.

My issue I think is related to my sql statements. Thanks for getting me on that track: http://codingforums.com/showthread.php?t=181504