mysql_connect/mysql_close

Hello,

I have three SELECT in a page.php, which solution is the best ?

1.$link=mysql_connect(…)
SELECT….x
SELECT…y
SELECT…z
mysql_close($link) ;

or
2. $link=mysql_connect(…)
SELECT …x.
mysql_close($link) ;
$link=mysql_connect(…)
SELECT…y.
mysql_close($link) ;
$link=mysql_connect(…)
SELECT…z.
mysql_close($link) ;

thank you

Don't use mysql_close() before you are finished. There's a lot of overhead in opening a connection to mysql. In fact, I NEVER use mysql_close(). If I'm using a module version I use mysql_pconnect() and mysql_close() has no effect. With mysql_connect() the connection is closed when the script finishes executing so there's no reason to specifically close it.

Rod, if the administrator does not have access to the my.cnf file and the host will not alter the my.cnf file - what else can I do about a plague of "too many connection" errors.

I've tried mysql_free_result($resultofquery) and even mysql_close($db) - no dice.

A lot of work and learning has gone into the project I'm working on, and I'd hate to throw it all away because of a seemingly uncorrectable error. It seems the 'too many connections' error is not that uncommon, either.

- Mike

A unique connection is only opened for unique host/user pairs. i.e. if you use mysql_[p]connect('localhost','user','pass') only 1 connection will be used by all scripts that call localhost with the user of 'user'. If you are on a dedicated server that only ever uses one mysql user and one mysql host for all web pages, there will only ever be one connection to mysql.

The problem lies in virtual hosting. It's not uncommon to have 250 different sites on one server, each with it's own mysql user obviously. AIUI, each connection uses about 1 MB of memory so the number of connections are limited (less than 100 with 128 MB of memory) so if more than one site has a script requesting a connection, the server will run out of connections. (not to say that the loss of RAM will cause this but if max_connections are set too high for the amount of RAM the machine will crash anyway.)

Your solution - move to a different host... or demand that your current host place you on a machine that has enough RAM to support the number of virtual hosts.

Thank you for the explanation.

I'll call my hosting company and see what they are willing to do.

- Mike