Call a PHP function multiple times creates MySQL error

Question

I'm trying to retrieve multiple data from a database with a PHP function, but somehow when I do this multiple times it gives a MySQL connection error.

$heat=getStat("heat", $userid);
$cash=getStat("cash", $userid);
echo mysql_error();

I use the code above to assign variables by calling a function which retrieves the stats from a database.

When I use the above codes separately, they work. But when I put them together they fail.

Is this a simple you-are-a-beginner-noob-programming-mistake?

I forgot to post the function so here it is:

function getStat($statName,$userID) {
    require_once 'config.php';
    $conn = mysql_connect($dbhost,$dbuser,$dbpass)
       or die('Error connecting to MySQL' . mysql_error());
    mysql_select_db($dbname);
    $query = sprintf("SELECT value FROM user_stats WHERE stat_id = (SELECT id FROM stats WHERE display_name = '%s' OR short_name = '%s') AND user_id = '%s'",
    mysql_real_escape_string($statName),
    mysql_real_escape_string($statName),
    mysql_real_escape_string($userID));
    $result = mysql_query($query);
    list($value) = mysql_fetch_row($result);
    return $value;        
}

We would need to see the definition of the `getStat()` function to have any idea what it does, as well as any related database connection and querying code. — Michael Berkowski, Jan 10 '15 at 23:19
A total shot in the dark - the `getStat()` function _closes_ a global MySQL database connection which it did not open itself, so the same connection is not present for subsequent calls. — Michael Berkowski, Jan 10 '15 at 23:20
What error do you get specifically? This doesn't look like it would error _unless_ you have a limited number of MySQL connections and open too many. It would be best to call `mysql_connect()` only _once_, outside this function rather than open a new connection each time the function is calle.d — Michael Berkowski, Jan 10 '15 at 23:27
or die ('Error connecting to mysql'); I get this error on my page, which means it failed to connect to the database in the function itself — Mark, Jan 10 '15 at 23:29
Check MySQL's _actual_ error with `die('Error connecting to MySQL ' . mysql_error());` --- you must call `mysql_error()` to get any useful information. — Michael Berkowski, Jan 10 '15 at 23:30
It still just says "Error connecting to MySQL" ... Am i doing something wrong again? — Mark, Jan 10 '15 at 23:41

score 5 · Answer 1 · answered Jan 10 '15 at 23:37

5

The problem is most likely caused by the require_once. As this is where you are pulling in your config for the database connection. The second time the require is executed it will not pull in the code required to define your database connection vars.

As @MichaelBerkowski has stated, it would be much better to have one global connection when the script loads, and make use of this connection for each request to the database. However if you wish to stick with the way you have currently, this should solve the problem:

function getStat($statName,$userID) {
    require 'config.php'; /// <-- note this has changed to just require
    $conn = mysql_connect($dbhost,$dbuser,$dbpass)
        or die ('Error connecting to mysql');
    mysql_select_db($dbname);
    $query = sprintf("SELECT value FROM user_stats WHERE stat_id = (SELECT id FROM stats WHERE     display_name = '%s' OR short_name = '%s') AND user_id = '%s'",
        mysql_real_escape_string($statName),
        mysql_real_escape_string($statName),
        mysql_real_escape_string($userID));
    $result = mysql_query($query);
    list($value) = mysql_fetch_row($result);
    return $value;      
}

answered Jan 10 '15 at 23:37

Pebbl

34,937
6
62
64

Oh, well spotted. It wouldn't be an issue except for variable scope in the function. – Michael Berkowski Jan 10 '15 at 23:42
oke, thank you both for your help guys! I will try to move the connection outside of the function (i am really new to SQL and php) but first I will try the require change! – Mark Jan 10 '15 at 23:44
I moved the connection into the config.php file and now it runs like a charm! Thank you so much Michael, your help is really appreciated – Mark Jan 10 '15 at 23:52
@Mark ~ No problem, glad it helped! As @MichaelBerkowski stated, your `require_once` would have worked, had it been included outside of a function scope (and your `getStat()` function had then globalised the config values using `global $dbhost, $dbuser, $dbpass`). But if that had been the case, you would only have needed to include the config once anyway. Rather than having a separate `config.php` and then processing the db connection, you may prefer having a `database.php` that you require at the start of your script, which has the config in it and a `connect()` function that returns `$conn`. – Pebbl Jan 11 '15 at 00:18
@MichaelBerkowski ~ totally agree! Keep the connection code simple and in one location :) – Pebbl Jan 11 '15 at 00:19
2

Agree with all of the above, and I would add that @Mark you need to migrate your database extension from `mysql_` to either `mysqli_` or `PDO`. The `mysql_` functions are deprecated and will be removed in a future version of PHP. Read [this answer](http://stackoverflow.com/a/26476208/3899908) for a quick overview of what you need to change. – worldofjr Jan 11 '15 at 00:59

Call a PHP function multiple times creates MySQL error

1 Answers1