SQL call IF EXISTS

Question

SOS! I am trying to build a simple search feature that either creates a new row with the search content or increments the amount of times that particular content has been searched if the row already exists. I tried doing a similar SQL call straight inside of phpMyAdmin but it gave me a #1064 error? ($_POST['search'] is the search content)

<?php
  $con=mysqli_connect(...);
        $result = mysqli_query($con,"IF EXISTS (SELECT * FROM search WHERE text='" . $_POST['search'] . "') 
        UPDATE search SET searches=searches+1 WHERE text='" . $_POST['search'] . "' 
        ELSE 
        INSERT INTO search (text, searches) VALUES ('" . $_POST['search'] . "', '1')");
  echo $result;
?>

possible duplicate of [Usage of MySQL's "IF EXISTS"](http://stackoverflow.com/questions/5528854/usage-of-mysqls-if-exists) — , Jul 02 '14 at 04:58
**B17** --- there. As did my bomber. Wait a minute, that was a Bingo button. — Funk Forty Niner, Jul 02 '14 at 05:01

Sultan · Accepted Answer · 2014-07-02T05:31:29.940

1

Make the "text" column of the "search" table a "unique key". Then you can use this query:

INSERT INTO search (text,searches) VALUES ({$searched_text},1) ON DUPLICATE KEY UPDATE searches=searches+1;

edited Jul 02 '14 at 05:31

answered Jul 02 '14 at 05:14

Sultan

637
6
14

score 0 · Answer 2 · answered Jul 02 '14 at 05:09

At first you are doing very wrong thing in your sql. Please for your sake and sake of application, never never let unfiltered input into your sql query. This can lead to SQL Injection and someone can easily highjack your db.

So, lets fix this first:

$search = mysql_real_escape_string($_POST['search']);

Now when we have fixed it, lets try to do something with it. I dont know if you have set a key for that table, but i suppose you do, so to escape your conditional try to simply do next thing:

insert into search (searches) values($search) on duplicate key update searches=searches+1

Please let me know how its going.

Kind regards Vlad

SQL call IF EXISTS

2 Answers2