php num_rows not working

Question

I have a simple function below to check if the user is already registered on my site. If they are then it doesn't add them and if they aren't it adds them. However In my database for some weird reason a certain user is getting added multiple times with the exact same id. Is there something wrong with mysqli_num_rows?

  $check = mysqli_query($con, "SELECT id FROM users WHERE id='$id'");
    $row_cnt = mysqli_num_rows($check);
       if ($row_cnt == 0) { 
    $query = "INSERT INTO users (id,username) VALUES ('$id','$username')";
    mysqli_query($con, $query);
       } else {   // If Returned user . update the user record      
    $query = "UPDATE users SET username='$username' where id='$id'";
    mysqli_query($con, $query);
    }

What is the value of $check? Show a print_r or var_dump of $check — AlexL
– AlexL, Commented Nov 14, 2014 at 22:12
have you tried running this before the num_rows check? php.net/manual/en/mysqli.store-result.php — Kai Qing
– Kai Qing, Commented Nov 14, 2014 at 22:14
@KaiQing: I've never used mysqli_store_result, what does it do? The docs for mysqli_num_rows() don't show it being used? — gen_Eric
– gen_Eric, Commented Nov 14, 2014 at 22:18
@Rocket - I never had either until I had this exact same issue. I'm using mysqli in OOP, not procedural. Not sure if it matters. Taken from docs it "Transfers a result set from the last query" but some other answer on SO suggested it in my case and indeed it worked. I have no idea why it was necessary and why it didn't allow me to access num_rows without it. Maybe it's just one reason why people are always suggesting PDO over mysqli... I'll try to find the link — Kai Qing
– Kai Qing, Commented Nov 14, 2014 at 22:21
WARNING: When using mysqli you should be using parameterized queries and bind_param to add user data to your query. DO NOT use string interpolation to accomplish this because you will create severe SQL injection bugs. — tadman
– tadman, Commented Nov 14, 2014 at 22:23

O. Jones · Accepted Answer · 2014-11-15 02:44:33Z

A few things here.

First of all, num_rows() notoriously doesn't always return the right value. If you want to know how many rows match some criterion, use this query then read the rowcount in the one-row resultset.

SELECT COUNT(*) AS rowcount FROM users WHERE id='$id'

Second, it looks like you're trying to do a conditional insert / update operation. In this case you will be best off using Use the INSERT ... ON DUPLICATE KEY UPDATE mySQL command. This gets around the race condition that is in the code in your question.

You want a query something like this:

INSERT INTO users (id,username) VALUES ('$id','$username')
ON DUPLICATE KEY UPDATE username='$username';

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

This will either add the row you want or change the username in the existing row, all at once. If some other user tries to do the same thing at the same time, this will do it safely.

Collectives™ on Stack Overflow

php num_rows not working

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related