mysql_num_rows(): returns invalid result resource

[Pawige]

Here's the snipped out part of the code I'm using:

[code]
$dbh = mysql_connect ("localhost", "user", "password") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("database");

$movequery = "SELECT * FROM Gallery WHERE category=$category";
$moveresult = mysql_query($movequery);
$query = "SELECT * FROM Gallery WHERE id=$id AND category=$category";
$result = mysql_query($query);
mysql_close();

$numrows = mysql_num_rows($moveresult);

if ( $id == $numrows ) { $nextid=1; } else { $nextid=$id + 1; }
if ( $id == 1 ) { $previd=$numrows; } else { $previd=$id - 1; }

$row = mysql_fetch_row($result);
[/code]

Running it at:

[u]http://[my url]/gallery.php?id=3&category=0[/u]

It gives me:

[b]Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /[my url]/gallery.php on line 18[/b]

[b]Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /[mu url]/gallery.php on line 23[/b]

Everything works fine if I change it to something like this:

[code]$movequery = "SELECT * FROM Gallery WHERE category=1";
$moveresult = mysql_query($movequery);
$query = "SELECT * FROM Gallery WHERE id=1 AND category=1";
$result = mysql_query($query);
mysql_close();[/code]

So I'm guessing it has something to do with how I'm referencing those variables, but I'm totally stumped, because it used to work, and then suddenly stopped, and I hadn't touched any of the code in the interim.

I'm pretty new at this, primarily an artist, just trying to learn some programming skills on the side, so if I've made an idiotic error, you can just chalk it up to that...

Thanks in advance for any help!

[btherl]

After each query, check for errors like this:

[code]$query = "SELECT * FROM Gallery WHERE id=$id AND category=$category";
$result = mysql_query($query);
if (!$result) die("Error in $query: " . mysql_error());[/code]

I'm also not sure if you can call mysql_close() so soon.  Maybe you should move it until after you have fetched the results.

That added line will tell you two things
- It will show you the query you are trying to execute.
- It will show you the error message that caused the query to fail (if it did fail)

[Pawige]

Now it says:

Error in SELECT * FROM Gallery WHERE id= AND category=: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND category=' at line 1

Here's what I could find about the server version, not sure if it's the right information, but it seems likely:

MySQL - 4.1.21-standard-log
Protocol version: 10
Server: Localhost via UNIX socket

I'm digging around to see if I can find the answer, but if somebody knows it or where to find it, that'd be nice, as I'm having some trouble finding anything helpful so far.

[akitchin]

it's because $id and $category are undefined, thus they appear empty in your query.  MySQL won't tell you much more than "i'm expecting you to actually provide a value to compare against."  the problem is in your PHP.

since you're getting 'id' and 'category' from the URL, you'll need to access them using the $_GET superglobal unless register_globals is set to on, which for your sake i hope it isn't:

[code]$movequery = "SELECT * FROM Gallery WHERE category={$_GET['category']}";
$query = "SELECT * FROM Gallery WHERE id={$_GET['$id']} AND category={$_GET['category']}";[/code]

give those a whirl.  it's also good practice to treat those values passed in the URL with a little caution (encasing them in single quotes in the query is a good starter).

[Pawige]

Thanks a lot for the quick replies! Works like a charm now.

Edit: Actually, I've got another quick question, how can I use this $_GET to grab the values from the URL and stick them into a local value? Something along the lines of:

$querylocal = $_GET['urlquery']

Which doesn't seem to work, but I imagine there must be some way to do it. I only need to because I've got a couple variables that I get from the URL and use in numerous places and in a couple of equations.

$querylocalblah = $querylocal - 1;

Something like that.

[fenway]

Definitely quote this literal values, or you're just asking for SQL injection troubles.  And I can't see why you wouldn't be able to do a single assignment, but I'm not no PHP expert.