First off, you do risk creating several db connections per user.
$cr = db_connect();
# some code ...
# you run the risk of misspelling your connection result variable name
db_query('SELECT ...', $vr); # = new connection...
# referencing local instead of global variable
function stuff() {
db_query('SELECT ...', $cr); # $cr is local variable = new connection
}
$cr = false; # could happen by "accident"...
db_query('SELECT ...', $cr); # after the above line, every subsequent query will create a new connection
If you instead create a class to handle this and only use this class to perform queries, there will be no risk of multiple connections per user. One simple example
class Db {
private static $conn = false;
public static query($qry) {
# There is no connection, try to create one. This can only happen once, so no risk of multiple
# db connections per user. Unless you call mysql_close() somewhere, which you shouldn't.
if (!self::$conn) {
self::$conn = self::connect()
}
# this failed, which means a connection to the DB can't be established.
if (!self::$conn) {
# can't connect
error_log(mysql_errno() . ': ' . mysql_error());
# probably a good idea to throw an exception here, but at the very least...
return false;
}
$r = mysql_query($qry, $conn);
if (!$r) {
# centralize error handling here. e.g.
error_log('Query failed: ' . $qry . PHP_EOL . mysql_errno() . ': ' . $mysql_error());
# possibly throw an exception here as well...
return false;
}
return $r;
}
private static function connect() {
$result = mysql_connect('localhost', 'xxxx', 'xxxx');
if (!$result) {
# can't call mysql_close(false) - and $result is false here
return false;
}
if (!mysql_select_db('xxxx')) {
# this should also be logged. another place that might be good for an exception
mysql_close($result);
return false;
}
return $result;
}
# if you really need to be able to close db connections, i.e. you run a query early in the script
# and then don't need to do any more queries while the rest of the script takes a long time to run
publis static function close() {
if (self::$conn) {
mysql_close(self::$conn);
self::$conn = false;
}
}
}
Usage
$result = Db::query('SELECT ...');
$result2 = Db::query('SELECT ...');
# and if you want to close the connection
Db::close();
The other thing that can easily become a problem is inefficient queries. If your queries take too long to perform, then each user will be taking up his/her connection a lot longer, which means that you are more likely to run out of connections.
Use explain to see the query execution plans for your queries, and take use of the slow query log if needs be.
You may also have a badly configured db server.
As for the chron job, they can keep running forever since only php scripts run through a web server are affected by max execution time. So if you for some reason end up with
chron script
$conn = db_connect();
$r = db_query('SELECT ...', $conn);
while (true) {
}
... then your script will never end, so even if you start a new just once per day, you will add one more indefinitly running script per day. It's easy to add logging of script start and end times though, to make sure that they do end, and don't take too long to run.
