MySQL - Check if exists, if yes update - else insert - without unique ID?

Question

Basically, I have a table to log when users login and what's their latest IP.

To prevent duplicates, i use the following method: screenshot

This works fine, to a certain degree. Sometimes, or actually plenty of times you can spam request it to log twice, or even more than twice. The requests are sent really rapidly, so this does not work very well.

I assume one way to speed it up, would be to have a simple SQL Query to search to perform this:

Search for the username... If it exists, update IP. If it does not exist, insert new column.

But, how I would do this? I have no idea. Most of the methods are using "ON DUPLICATE KEY" which does not work with text/blob.

Any help is greatly appreciated.

Code: http://pastebin.com/xuFmgFp0

$result = $conpdo->prepare("SELECT * FROM `logs` WHERE `username` LIKE :username");
$result->execute(array(':username' => $_GET['username']));         
$info = $result->fetch(PDO::FETCH_ASSOC);

if(empty($info))
{
    $result = $conpdo->prepare("INSERT INTO `logs` (`id`, `ip`, `username`, `last_update`) VALUES (NULL, :ip, :username, '0000-00-00 00:00:00');");
    $result->execute(array(':username' => $_GET['username'], ':ip' => $_GET['ip']));  
}
else
{
    $result = $conpdo->prepare("UPDATE `logs` SET `ip` = :ip WHERE `logs`.`id` = :id");
    $result->execute(array(':ip' => $_GET['ip'], ':id' => $info['id']));       
}

possible duplicate of [check if row exists with mysql](http://stackoverflow.com/questions/22252904/check-if-row-exists-with-mysql) — Funk Forty Niner, Jun 02 '16 at 15:10
and you made it to expire in 29 days; *why?*. don't make links expire, they will serve no one once they do expire. another thing you have learned on how Stack rolls. — Funk Forty Niner, Jun 02 '16 at 15:12
That one looks like what I am already doing. Search, then perform query based on what the search returns, this ends up duplicates somehow (there are multiply requests a second some times) — OxideNET, Jun 02 '16 at 15:12
updated again, sorry - used ShareX which automatically adds expire. — OxideNET, Jun 02 '16 at 15:13
I don't see a reason for a text/blob here. Your problem would be a textbook example for `on duplicate key update`. Create a unique index on ip and let mysql handle the rest. — Solarflare, Jun 02 '16 at 16:13
unique index is there, but how would i do that on IP? so that index is locked to the specificed ip or username — OxideNET, Jun 03 '16 at 17:22

MySQL - Check if exists, if yes update - else insert - without unique ID?

0 Answers0