CODEWITHSUNDEEP
phpmysqlmysql-num-rows
Chris
Chris

Reputation: 374

MySQL number rows error

$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?

Upvotes: 0

Views: 670

Answers (2)

mikabytes
mikabytes

Reputation: 2169

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

Upvotes: 0

CristiC
CristiC

Reputation: 22698

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.

Upvotes: 5

Related Questions