mysql_fetch_array(): error

adavis

When you query a database, will you get this error message if the database is empty? This works fine if it finds a match, but when I dump the table, I get this error message again:

Warning: mysql_fetch_array():
supplied argument is not a valid MySQL result resource in
/web/apache/htdocs/x/x/input.php on line 30

Where lines 28-32 are:

28 $sql = "SELECT * FROM speaker WHERE id='".$_POST['id']."' LIMIT 1";
29 $result = mysql_query($sql,$dblink) || die("QUERY FAILED: $sql");
30 while ($row = mysql_fetch_array($result)) {
31   $num++;
32 }

What I'm wanting to do is to check for a match and if one is not there, then I want to create a record. Since all I'm doing is checking for a match, then maybe I need to use a different mysql argument. Would that fix the problem?

ir4z0r

you're right, when empty, mysql_query() returns nothing

do it like this:

$result = mysql_query(...);
if ($result)
{
while (...
}
else echo "Query didn't return anything";

adavis

ahhhhhhh...............

so you can use an else on a while statement. I didn't know that. Thanks a bunch.

ir4z0r

uhh, no, the "else" belongs to the "if"!!

if (condition)
{
// so sth, i.e. a while-llop
}
else
{
// do sth else
}

adavis

okay, so how 'bout this:

$locksql = "LOCK TABLES speaker READ";
$lockreq = mysql_query($locksql) || die("QUERY FAILED:$locksql");
$sql = "SELECT * FROM speaker WHERE id='".$_POST['id']."' LIMIT 1";
$result = mysql_query($sql,$dblink) || die("QUERY FAILED:$sql");
$locksql = "UNLOCK TABLES";
$lockreq = mysql_query($locksql) || die("QUERY FAILED:$locksql");
if ($result) {
  $formcase = "select";
} else {
  $formcase = "add";
  $locksql = "LOCK TABLES speaker WRITE";
  $lockreq = mysql_query($locksql) || die("QUERY FAILED: $locksql");
  $sql = "INSERT INTO speaker 
           SET id='".$_POST['id']."', 
             last='".$_POST['last']."', 
            first='".$_POST['first']."'";
  $result = mysql_query($sql) || die("QUERY FAILED:$sql");
  $locksql = "UNLOCK TABLES";
  $lockreq = mysql_query($locksql) || die("QUERY FAILED: $locksql\n");
}

wrapped text in the $sql statement is to make it fit on the page better and not on multiple lines in my code.

adavis

This is not working the way I want it to. $result comes back with the value of 1, which I am assuming means that the $query worked. I think I need to do something with count() and maybe mysql_num_rows.

ir4z0r

hmm, what do you want to do?

maybe mysql_affected_rows() helps you, giving you the number
of inserted/deleted/updated or selected rows

btw. I don't think, locking is necessary for SELECT

adavis

Not mysql_num_rows: I get this error:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in 
/web/apache/htdocs/xx/xx/input.php on line 24

21 $sql = "SELECT COUNT(*) FROM speaker WHERE id = '".$_POST['id']."' LIMIT 1";
22 $result = mysql_query($sql,$dblink) || die("QUERY FAILED: $sql\n");
23 $numrows = 0;
24 $numrows = mysql_num_rows($result);
... (unlocking db)
27  echo "numrows:".$numrows."<br>sql: ".$sql."<br>";

echo from line 27:

numrows:
sql: SELECT COUNT(*) FROM speaker WHERE id='123456' LIMIT 1

ir4z0r

that's obviously cause the SELECT query does not return anything
so mysql_num_rows() can't work with it

so what about mysql_affected rows()?