Mysql_num_rows(): supplied argument is not a valid MySQL result resource

Calus · 10-08-2010, 04:09 PM

Hey guys, some weekend fun for us all.

I'm trying to setup a simple script to display a MySQL database tables contents, in a table. My intention is for the table to dynamically add new rows as more information is added to the database.

This is the file that should display the database info. It shows the table headers, and the Mysql_num_rows(): error message.

According to my research that shouldn't happen as long as database and username connection checks out.

Anyone have an idea?

Code:

<body>

<?php 

// Kobler til databasen 

mysql_connect("localhost", "username", "password") or die(mysql_error());
mysql_select_db("database") or die(mysql_error());

// Bygger foresp?rsel
$query = mysql_query("SELECT * FROM `database table`") or die(mysql_error());

$result = mysql_query($query);
$num = mysql_num_rows($result);
?>
	
<table border="0" cellspacing="2" cellpadding="2">
<tr>
<th><font face="Arial, Helvetica, sans-serif">ID</font></th>
<th><font face="Arial, Helvetica, sans-serif">Name</font></th>
<th><font face="Arial, Helvetica, sans-serif">Level</font></th>
<th><font face="Arial, Helvetica, sans-serif">Class</font></th>
<th><font face="Arial, Helvetica, sans-serif">Reference</font></th>
<th><font face="Arial, Helvetica, sans-serif">Why join?</font></th>
<th><font face="Arial, Helvetica, sans-serif">Previous guilds</font></th>
<th><font face="Arial, Helvetica, sans-serif">Experience</font></th>
<th><font face="Arial, Helvetica, sans-serif">Contact ingame</font></th>
<th><font face="Arial, Helvetica, sans-serif">Contact SWTOR</font></th>
<th><font face="Arial, Helvetica, sans-serif">Contact e-mail</font></th>
<th><font face="Arial, Helvetica, sans-serif">Accepted policy</font></th>
<th><font face="Arial, Helvetica, sans-serif">E-mail address</font></th>
</tr>

<?php
$i=0;
while ($i < $num) {
	
$ID=mysql_result($result,$i,"ID");
$appname=mysql_result($result,$i,"appname");
$applvl=mysql_result($result,$i,"applvl");
$appcla=mysql_result($result,$i,"appcla");
$appref=mysql_result($result,$i,"appref");
$appwhy=mysql_result($result,$i,"appwhy");
$apprev=mysql_result($result,$i,"apprev");
$appexp=mysql_result($result,$i,"appexp");
$coning=mysql_result($result,$i,"coning");
$conswm=mysql_result($result,$i,"conswm");
$conemail=mysql_result($result,$i,"conemail");
$policy=mysql_result($result,$i,"policy");
$email=mysql_result($result,$i,"email");

?>

<tr>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $ID; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $appname; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $applvl; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $appcla; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $appref; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $appwhy; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $apprev; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $appexp; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $coning; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $conswm; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $conemail; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $policy; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $email; ?></font></td>
</tr>

<?php
$i++;
}
?>
</table>
</body>
</html>

Can someone please help me find out what is wrong here? I just don't see it yet.

Village Idiot · 10-08-2010, 04:35 PM

That error 99.9% of the time means the query you ran didnt work. Check your SQL for error by using "or die mysql_error();" after the mysql_query command

GSmith · 10-10-2010, 06:40 PM

Actually, 100% of the time it means the function was not passed a valid MySQL result resource. This error should narrow it down to whatever is being passed to mysql_num_rows; in this case, $result.

The problem is found in the second call to mysql_query.

PHP Code:


			
$query = mysql_query("SELECT * FROM `database table`") or die(mysql_error());



$result = mysql_query($query);

$num = mysql_num_rows($result);

$query holds a result set.
$result ultimately will always be FALSE, since the second call to mysql_query is being passed a resource $query where it expects a string argument. As Village Idiot mentioned, checking for errors after the second call to mysql_query would confirm this.

$query is an inappropriate variable name for a result set. Rename $query to $result and get rid of the second call to mysql_query.

Here's what I end up with after the changes:

PHP Code:


			
mysql_connect("localhost", "username", "password") or die(mysql_error());

mysql_select_db("database") or die(mysql_error());



$result = mysql_query("SELECT * FROM `database table`") or die(mysql_error());

$num = mysql_num_rows($result);



...

I hope my explanation is elaborate enough.

Cheers.

Calus · 10-11-2010, 12:10 AM

I can't believe I didn't see that. But I guess most will feel that way after getting their newbie mistakes pointed out to them.

Thank you both for your help, the script is now working as intended and the mistake I made has been noted down so I'll learn from it for the future.