2 queries - 2nd gets "mysql num rows(): supplied blabla

please help I'm stuck

[22kenny22]

<?
$sql = "SELECT distinct Domain FROM ModelFaces order by Domain";
if ($result = mysql_query($sql)) {
if (mysql_num_rows($result)) {
while ($row = mysql_fetch_assoc($result)) {
echo "<option value=\"{$row['Domain']}\">{$row['Domain']}</option>";
}
}
}
mysql_close();
?>
</select>
<select name="rate">
<option value=1>1</option>
<option value=2>2</option>
<option value=3>3</option>
</select>
<input type="submit" value="Go!">
</form>
<?php
$chooser = $_POST['chooser'];
$ratelo = $_POST['rate'];
$ratehi = $ratelo+.99;
$host="localhost"; // Host name
$username="******"; // Mysql username
$password="******"; // Mysql password
$db_name="kenny22_Models"; // Database name
$tbl_name="ModelFaces"; // Table name
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");
$sql3="SELECT ID, Rating, Image, Link, Domain FROM $tbl_name where Domain='$chooser' and Rating between $ratelo and $ratehi order by Rating DESC, Image";
$result3=mysql_query($sql3);
$count=mysql_num_rows($result3);

$sql3 query works, so $result3 has rows but the next line creates errors

[jlindsey]

What errors are you getting?

[22kenny22]

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

[jlindsey]

Instead of using $result3 as input for mysql_num_rows, try using $sql3.

Edit: Nevermind. Misread the code.

Edit 2: Try using

$sql4 = "SELECT COUNT(*) FROM $tbl_name WHERE Domain='$chooser' AND Rating BETWEEN $ratelo AND $ratehi"
$result = mysql_query($sql4);
$countrows = mysql_fetch_row($result);
print $countrows[0]

[edited by: jlindsey at 1:16 am (utc) on May 25, 2009]

[22kenny22]

Thanks, but same result

this used to work before I added the query above to choose variables. The select of $sql3 works, but I get the error.

But when I update the rating value from the select results,
the following code does not work:

if($Submit){
for($i=0;$i<$count;$i++){
$sql6="UPDATE $tbl_name SET Rating=$Rating[$i] WHERE ID=$id[$i]";
$result6=mysql_query($sql6);

[jlindsey]

Try using

$sql4 = "SELECT COUNT(*) FROM $tbl_name WHERE Domain='$chooser' AND Rating BETWEEN $ratelo AND $ratehi"
$result = mysql_query($sql4);
$countrows = mysql_fetch_row($result);
// number of rows is $countrows[0]

[22kenny22]

Here is the new code:

<?
$sql = "SELECT distinct Domain FROM ModelFaces order by Domain";
if ($result = mysql_query($sql)) {
if (mysql_num_rows($result)) {
while ($row = mysql_fetch_assoc($result)) {
echo "<option value=\"{$row['Domain']}\">{$row['Domain']}</option>";
}
}
}
mysql_close();
?>
</select>
<select name="rate">
<option value=1>1</option>
<option value=2>2</option>
<option value=3>3</option>
</select>
<input type="submit" value="Go!">
</form>
<?php
$chooser = $_POST['chooser'];
$ratelo = $_POST['rate'];
$ratehi = $ratelo+.99;
$host="localhost"; // Host name
$username="**"; // Mysql username
$password="**"; // Mysql password
$db_name="kenny22_Models"; // Database name
$tbl_name="ModelFaces"; // Table name
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");
$sql3="SELECT ID, Rating, Image, Link, Domain FROM $tbl_name where Domain='$chooser' and Rating between $ratelo and $ratehi order by Rating DESC, Image";
$sql4 = "SELECT COUNT(*) FROM $tbl_name WHERE Domain='$chooser' AND Rating BETWEEN $ratelo AND $ratehi";
$result3=mysql_query($sql3);
$result4 = mysql_query($sql4);
$countrows = mysql_fetch_row($result4);
?>
<table width="640" border="0" cellspacing="1" cellpadding="0">
<form name="form1" method="post" action="">
<tr>
<td>
<table width="640" border="0" cellspacing="1" cellpadding="0">
<?php
while($rows3=mysql_fetch_array($result3)){
?>
<tr>
<td align="left"><? $id[]=$rows3['ID']; ?><? echo $rows3['ID']; ?></td>
<td align="left"><? echo $rows3['Link']; ?></td>
<td align="left"><? echo $rows3['Image']; ?></td>
<td align="left"><input name="Rating[]" type="text" id="Rating" value="<? echo $rows3['Rating']; ?>"></td>
<td align="left"><img src="<? echo $rows3["Image"]; ?>" height=106 width=80></td>
</tr>
<?php
}
?>
<tr>
<td colspan="4" align="center">
<input type="submit" name="Submit" value="Submit">
</td>
</tr>
</table>
</td>
</tr>
</form>
</table>
<?php
if($Submit){
for($i=0;$i<$count;$i++){
$sql6="UPDATE $tbl_name SET Rating=$Rating[$i] WHERE ID=$id[$i]";
$result6=mysql_query($sql6);
}
}
mysql_close();

I get these errors right away

Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /home/kenny22/public_html/Models/models100.php on line 60

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/kenny22/public_html/Models/models100.php on line 68

I can select a domain, submit, get good results, update, submit again, and I get the errors again and the update query doesnt work

[22kenny22]

Thanks for trying, hopefully I will figure it out. I could split the queries into two pages, I suppose.

[enigma1]

first of all I do not see in the code where the mysql connection is established (upper section). I see you start doing queries right away. So I assume the first query fails but there is no error message?

You should establish the db connection at the top of the script so try that.

Also get rid of the short tags
not
<?
but
<?php

[idfer]

Generally, the error "supplied argument is not a valid MySQL result resource" means that your SQL statement couldn't be executed, either because of a syntax error or typo in a table or field name. In your code, i don't see any checks to see if the query was executed properly, so try adding this code after every call mysql_query():

$result = mysql_query($sql); //existing call
if($result === false) {
 echo 'Unable to execute SQL query '.htmlentities($sql);
 echo '<br>'.htmlentities(mysql_error());
 exit;
}

Try that (renaming $result and $sql as needed), it might help you find the problem. BTW, if you want to be clever, write a function called db_query (or whatever you like) that implements the code above and returns $result if ok, then call that function instead of mysql_query() everywhere: instant hassle-free error reporting. And on the production server, you can enhance the function with better error handling. ;-)

[22kenny22]

I know my error handling is weak here, but that is not the problem.