CODEWITHSUNDEEP
phpsqlif-statementinsertsql-update
sgrutman978
sgrutman978

Reputation: 112

SQL call IF EXISTS

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;
?>

Upvotes: 0

Views: 106

Answers (2)

Sultan
Sultan

Reputation: 647

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;

Upvotes: 1

Vladd
Vladd

Reputation: 124

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

Upvotes: 0

Related Questions