0

I am getting this error:

Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource

Here's my Connection.php :

$userDB_server = "";
$userDB_user = "";
$userDB_password = "";
$userDB_database = "";
$connection = mysql_connect("$userDB_server","$userDB_user","$userDB_password") or die ("Unable to establish a DB connection");
$userDB = mysql_select_db("$userDB_database", $connection) or die ("Unable to establish a DB connection");


$gameDB_server = "";
$gameDB_user = "";
$gameDB_password = "";
$gameDB_database = "";
$gameDB_connection = mysql_connect("$gameDB_server","$gameDB_user","$gameDB_password", true) or die ("Unable to establish a DB connection");
$gameDB = mysql_select_db("$gameDB_database", $gameDB_connection) or die ("Unable to establish a DB connection");

Here's my function :

require_once('Connection.php');
$findQuery = sprintf("SELECT * FROM `Keys` WHERE `ID` = '$gID'");
$findResult = mysql_query($findQuery, $connection) or die(mysql_error());
$resultRow = mysql_fetch_assoc($findResult) or die(mysql_error());

The error is on "$findResult = mysql_query($findQuery, $connection) or die(mysql_error());" But I don't see a problem anywhere.

What I've tried :

  • I've tried with and without the "true" on the second connection, didn't seem to make a difference anywhere.
  • Echoing the $connection and $gameDB_connection shows nothing,
  • Using var_dump on $connection shows "resource(9) of type (mysql link)"
  • Removing the $connection from the mysql_query has it connect to the other DB (gameDB_connection) and I get an error that the table doesn't exist (its not on that DB).
  • Adding / changing / removing the backquote ( ` ) from the query seems to have no effect on the error
  • The variable $gID echo's correctly, so it's not null (its 1001 in this case)
  • If I run the SELECT part in the actual sql form (instead of via php), it lists them all correctly
  • The Connection.php is used in other places (one page reads from both databases at the same time) successfully. No errors anywhere else

Anyone have any idea what's wrong?

halfer
  • 19,824
  • 17
  • 99
  • 186
CrackedPixel
  • 3
  • 1
  • 4
  • **[Please, don't use mysql_* functions for new code.](http://bit.ly/phpmsql)** They are no longer maintained and are officially deprecated. See the **[red box](http://j.mp/Te9zIL)**? You can use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) instead - [this article](http://j.mp/QEx8IB) can help you decide which. – jcsanyi Jun 30 '13 at 00:46
  • Is that really the entirety of your `Connection.php` and test files? Can you reproduce the problem with **just** the lines that you're showing here? – jcsanyi Jun 30 '13 at 00:50
  • Also, this is unrelated to your problem - but there's no reason to use values like `"$gameDB_server"` - you can just use the variable itself without enclosing it in quotes: `$gameDB_server` – jcsanyi Jun 30 '13 at 00:52
  • That is 100% of the Connection.php (besides the before and after it), and that is 100% of the function. It gets called and passed the variable $gID (which seems to pass successfully, since it echos correctly). Oh, and I'm using PHP version 5.2.17 incase that matters – CrackedPixel Jun 30 '13 at 00:53
  • When you say function.. do you mean a literal function that you're calling? `$connection` is a global variable, and won't be available in a function unless you use `global $connection` in the function. – jcsanyi Jun 30 '13 at 00:54
  • When you did a `var_dump($connection)`, did you do it **immediately before** the mysql_query call? or somewhere else, like in Connection.php? – jcsanyi Jun 30 '13 at 00:56
  • ..Now I feel like i'm getting somewhere lol. When I place the var_dump right before the query call (it was in Connection.php before) it returns NULL. But since I require_once inside the function, shouldn't it use the $connection variable? – CrackedPixel Jun 30 '13 at 01:00
  • I agree with KyleK that you do not need to use `sprintf`. You can just assign the string directly to the variable: ``$findQuery = "SELECT * FROM `Keys` WHERE `ID` = '$gID'";`` – jcsanyi Jun 30 '13 at 01:17

1 Answers1

1

Based on the comments, it sounds like the problem is caused by using require_once() inside a function.

One of two thing is happening. Either:

  1. You've already included Connection.php somewhere else, so when you get to the function, it's not actually included.. because of the once part of require_once.

    or...

  2. It is working the first time you call the function, but the second time you call it, the file has already been included and does not get included again.

The problem is that when the file is first included (assuming that's from within this function), the $connection variable is created in the function scope, and like any other function variable, is gone at the end of the function. When you call the function a second time, the include doesn't happen because you're using require_once.

You could probably fix this by calling require() instead of require_once(), but that will end up reconnecting to the database every time you call the function - which is a lot of unnecessary overhead. It's much cleaner to just move the include outside of the function, and either pass the connection into the function, or use it as a global variable.

That would look like this:

require_once('Connection.php');

function getResult() {
    global $connection;

    $findQuery = "SELECT * FROM `Keys` WHERE `ID` = '$gID'";
    $findResult = mysql_query($findQuery, $connection) or die(mysql_error());
    $resultRow = mysql_fetch_assoc($findResult) or die(mysql_error());
}

All that being said, there's 2 major problems with this code.

  1. You're using the mysql_* functions which are deprecated and will soon be removed from new versions of PHP. See this question for more details: Why shouldn't I use mysql_* functions in PHP?

    It's not actually that hard to switch to something like the mysqli_* functions instead - there's a non-object set of functions that are almost identical to what you're using now.

  2. You're including a variable in your query without properly escaping it. At the very least you should be calling mysql_real_escape_string() (or mysqli_real_escape_string()), but a better solution is to look into prepared statements. You can find more information on prepared statements here: How can I prevent SQL injection in PHP?

Community
  • 1
  • 1
jcsanyi
  • 8,133
  • 2
  • 29
  • 52
  • Alright, so using the new layout (require_once at the top, global $connection IN the function) it doesn't have any errors, but the function stops at : `$resultRow = mysql_fetch_assoc($findResult) or die(mysql_error());` Anything below that never happens, but there's no error. Ahh! (even when I put `echo mysql_error()` right above it) EDIT : Changing `require_once` to `require` has no effect – CrackedPixel Jun 30 '13 at 01:24
  • Fixed it! I was using gID 1001 still (its selected from a list) but the table only had values for 1014. Guess I need to add a counter in there, eh? Haha. Thanks man! – CrackedPixel Jun 30 '13 at 01:27
  • @CrackedPixel You can use `mysql_num_rows()` to see whether any results were actually returned - so it doesn't just die if you use the wrong ID. – jcsanyi Jun 30 '13 at 01:36