PHP/SQL never updates only inserts

Question

For some reason my insert/update check only ever inserts. the value userID does have a value so i dont know what is up with this. Any ideas?

$result = mysql_query("SELECT * FROM users where userID = $userID ");
if (mysql_num_rows($result) > 0) {
   mysql_query("UPDATE users SET firstName='$firstName', lastName='$lastName', 
             birthday='$birthday', update='$today', accessToken='$accessToken', emailOne='$emailOne' WHERE userID='$userId'");
} else {
   mysql_query("INSERT INTO users (userID, firstName, lastName, birthday, updated, accessToken, emailOne ) 
               VALUES ('$userId', '$firstName', '$lastName','$birthday', '$today', '$accessToken', '$emailOne')");
}

Have you thought about putting this logic into a routine instead? May cause a good bit less of a headache, although I do not see the error here. — MoarCodePlz
– MoarCodePlz, Commented Jun 19, 2011 at 16:59
This needs basic debugging first. What does $userID contain? What happens if you run the query in phpMyAdmin or a similar tool? — Pekka
– Pekka, Commented Jun 19, 2011 at 17:00
What DBMS? You're better off combining the two statements into a single MERGE statement. — eaolson
– eaolson, Commented Jun 19, 2011 at 17:13

Marc B · Accepted Answer · 2011-06-19 17:01:47Z

4

You'd be far better off doing INSERT ... ON DUPLICATE KEY UPDATE. Your version is subject to race conditions. It's entirely possible that between the time you do the SELECT * and then attempt the update/insert queries, ANOTHER script has already inserted the same ID number and then your script breaks. This also reduces the database load by one query.

As well, unless you've passed all those variables in the query through mysql_real_escape_string(), you'll probably be getting a visit from Little Bobby Tables.

answered Jun 19, 2011 at 17:01

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

itsols · Accepted Answer · 2011-06-19 17:08:52Z

1

From the way you're inserting the records, it seems that your userId field is a varchar (or alphanumeric) field. So your query NEVER reads the data that matches it since it is searching for it as a numeric. You've got to re-write the first line as:

$result = mysql_query("SELECT * FROM users where userID = '$userID' ");

Hope it helps.

answered Jun 19, 2011 at 17:08

itsols

5,5927 gold badges56 silver badges98 bronze badges

Collectives™ on Stack Overflow

PHP/SQL never updates only inserts

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related