where not exist mysql

Question

When someone visits my site, i want to log the users ip, but when you arrive to the site the table might not exist, so the UPDATE query gives me an error. And I also need to check against existing tables. I've tried: reading, but none seems to cover this problem, if the table dont exist. It's probably a typo.

$db->query("INSERT INTO track (tm, user_agent, host, ip, port, lang) VALUES ('$tm','$user_agent','$host','$ip','$port','$lang') WHERE NOT EXISTS (
    SELECT ip FROM track WHERE ip='$ip'");

what is error message? use ``die`` function to see what is wrong — doniyor, Feb 21 '13 at 16:42
[INSERT Syntax](http://dev.mysql.com/doc/refman/5.5/en/insert.html) — Hamlet Hakobyan, Feb 21 '13 at 16:44
I believe your second query is completely wrong. [Insert Query](http://www.w3schools.com/php/php_mysql_insert.asp) | [Insert Ignore](http://stackoverflow.com/questions/1361340/how-to-insert-if-not-exists-in-mysql) | [Insert Update](http://stackoverflow.com/questions/4205181/insert-to-table-or-update-if-exists-mysql) — UnholyRanger, Feb 21 '13 at 16:44

score 5 · Answer 1 · answered Feb 21 '13 at 16:44

5

You probably want to look at the INSERT ... ON DUPLICATE KEY UPDATE Syntax which allows you to specify an action if the key is already present

answered Feb 21 '13 at 16:44

Rowland Shaw

37,700
14
97
166

Kamil · Answer 2 · 2013-02-21T17:01:51.300

I found your typo.

Your (bad) code with better formatting:

$db->query("
    INSERT INTO track (
        tm, 
        user_agent, 
        host, 
        ip, 
        port, 
        lang
    ) 
    VALUES (
        '$tm',
        '$user_agent',
        '$host',
        '$ip',
        '$port',
        '$lang'
    ) 
    WHERE NOT EXISTS 
    (
        SELECT ip FROM track WHERE ip='$ip'
");

after fix:

$db->query("
    INSERT INTO track (
        tm, 
        user_agent, 
        host, 
        ip, 
        port, 
        lang
    ) 
    VALUES (
        '$tm',
        '$user_agent',
        '$host',
        '$ip',
        '$port',
        '$lang'
    ) 
    WHERE NOT EXISTS 
    (
        SELECT ip FROM track WHERE ip='$ip'
    ) -- that was missing
");

Anyway, you may need something like this:

    // check if ip exists
    $query = $db->$query("
        SELECT count(ip) FROM track WHERE ip='$ip'
    ");

    $result = $query->fetchFirstRowSomehow(); // i dont know methods you have in $db object

    $rows = $result['0']; // getting first (only) column that should contain count()

    if ($rows == 0)
    {
        // your insert code here
    }
    else
    {
        // your update code here
    }

where not exist mysql

2 Answers2