Has Mysql Query Executed - Confirm query has executed correctly

Question

I have recently written a script that writes to a database every ten minutes. The aim of the script is to update the record if a record for that user exists else it inserts a record for that user.

The database has three important fields, id username ip.

As the IP changes the script will update the IP field for that user or add a record for the user with the new IP and on the next run update the IP rather than add a record.

I am trying to find out the best way to know if the record exists so that I can update rather than insert.

I was previously using $f->rowCount() after the update statement ie

if($f->rowCount() > 0){ continue; }else{ the insert statement}

So if the affected rows were greater than 0 I know the record exists. However, if the IP had not changed, effectively no update ever took place and the $f->rowCount() would be equal to 0.

Now I am simply select all records and store them in an array, check if the username is in_array if so do an update, else do an insert. Is there a better or quicker way to ensure whether a record exists, without having to do a select?

My question is really to see if something similar to $f->rowCount() exists as I only discovered this the other day. Many thanks

for me it's very unclear what you are doing. specially "As the IP changes the script will update the IP field for that user or add a record for the user with the new IP and on the next run update the IP rather than add a record." - when will it update the record, when will it insert a new one? please provide more of your code since i don't understand the logic you wanna use. — low_rents
– low_rents, Commented Nov 26, 2014 at 9:28
@northkildonan What I think OP is implying, is that if the combo of username and IP exists then nothing should happen (an update here is thus redundant). Unclear though why he's going on user name instead of user id and IP. — silkfire
– silkfire, Commented Nov 26, 2014 at 9:30

silkfire · Accepted Answer · 2014-11-26 09:28:20Z

2

You could possibly make those three columns into a unique key. This would allow you to use the
INSERT ... ON DUPLICATE KEY query expression:

INSERT INTO `users`
([other fields], `username`, `ip`)
VALUES ([values for other fields], [user name], [IP address]) 
ON DUPLICATE KEY
UPDATE [other fields]=[other values], `username`=[user name], `ip`=[IP address]

answered Nov 26, 2014 at 9:28

silkfire

26.3k17 gold badges92 silver badges116 bronze badges

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

1 Comment

low_rents Over a year ago

this is looking good. I did not know about ON DUPLICATE KEY when an INSERT fails due to a duplicate key.

low_rents · Accepted Answer · 2014-11-26 09:42:50Z

1

silkfire's answer is looking very promising. but if you don't wanna change the unique keys in your table, why not change the query for your rowCount()?
for example:

SELECT COUNT(*) FROM `users` 
WHERE `ip` = [IP address]
AND 'username' = [user name]

looks like you are using PDO; so with $f->fetchColumn() on this statement you'll know if this exact combination already exists. you will also know if it exists more than once.

answered Nov 26, 2014 at 9:42

low_rents

4,4813 gold badges30 silver badges55 bronze badges

Collectives™ on Stack Overflow

Has Mysql Query Executed - Confirm query has executed correctly

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related