MySQL number rows error

Question

$result = mysql_query("SELECT FROM table WHERE job_id = '$key'") or die("Error: ". mysql_error(). " with query ". $query);
    $num_rows = mysql_num_rows($result);
    if($num_rows > 0) {
        mysql_query("UPDATE table SET row = '$value' WHERE job_id = '$key'");
        }else{
        mysql_query("INSERT INTO table SET row = '$value', job_id = '$key'");
        }

I need to check if the database has an existing record, if it doesn't I want to create one. Every time I run this though I get:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in my/web/directory/index.php on line 147

I've looked through existing posts on this website, but most of them involve the user having something wrong with his syntax. Such as forgetting mysql_query(); as far as I can tell I think I have the syntax right, but I'm still getting an error. Any help?

score 5 · Accepted Answer · answered Jul 11 '11 at 13:59

The first query is not correct.
Also your insert is not correct.
Try this:

$result = mysql_query("SELECT * FROM table WHERE job_id = '$key'") or die("Error: ". mysql_error(). " with query ". $query);
    $num_rows = mysql_num_rows($result);
    if($num_rows > 0) {
        mysql_query("UPDATE table SET row = '$value' WHERE job_id = '$key'");
        }else{
        mysql_query("INSERT INTO table VALUES ('$value', '$key')");
        }

You can also use INSERT...ON DUPLICATE KEY UPDATE.

I spent close to 45 min trying to figure out what was wrong and you solved it in a minute. Thanks! — Chris, Jul 11 '11 at 14:03

score 0 · Answer 2 · answered Jul 11 '11 at 14:08

With MySql there is a syntax to create a row if it doesn't already exist, or update if it does. In your case you could do something like this:

$result = mysql_query("INSERT INTO table (job_id, row) 
   VALUES ('$key', '$value')  ON DUPLICATE KEY UPDATE row='$value';

This would usually be preferable because you do half the amount of queries to the database and there's no risk of several threads accidentaly adding the row more than once in a race condition.

You can read more at:

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

MySQL number rows error

2 Answers2