select insert if not exist add or update

Question

im trying to log searches that happen on my website. when someone searches for something i want to first check the database if it has been searched before. if it has add +1 count to the number of searches if it hasnt submit it to the database.

the problem im getting at the moment is anytime you search it updates all logged search terms. i presume becuse i havent set an id. how can i get the id of the search term in the first select query and pass the id as a variable in the second query so it only updates the searchterm associated with that id?

$result = mysql_query("SELECT * FROM wss_search WHERE searchterm ='$trimmed' ");

if( mysql_num_rows($result) > 0) {
mysql_query("UPDATE wss_search SET  count = count+1 WHERE searchterm = '$trimmed' ");
}
else
{
mysql_query("INSERT INTO wss_search (id, searchterm, count) VALUES ('NULL', '$trimmed', '1') ");
}

any help very much appreciated.

im also aware that i should be using mysqli but the rest of the software hasnt been update to mysqli yet.

As you go through your code and update it, you might want to bump those parts to `mysqli`. You can run them in parallel. This looks terrifyingly insecure. Are you **sure** your user parameters are [properly escaped](http://bobby-tables.com/php)? — tadman, Sep 12 '14 at 17:28
aside from this being really insecure... why not just change the first select to `SELECT id FROM wss_search`.. then the $result is equal to that id.. then pass the result in the WHERE in your second query `WHERE id = '$result[0]' and searchterm = '$trimmed'` — John Ruddell, Sep 12 '14 at 17:36
Please read: http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php and http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php — Pang, Sep 13 '14 at 09:42

score 0 · Answer 1 · answered Sep 12 '14 at 17:55

Ive got it working using the below code. im new to php could you please tell me why its not secure and what i need to do to make it secure?

$var = $_GET['q'] ;
$trimmed = mysql_secure($var);

$result = mysql_query("SELECT id FROM wss_search WHERE searchterm ='$trimmed' ");

while($row = mysql_fetch_array($result)) {

    $id = $row['id'];
}

if( mysql_num_rows($result) > 0) {
mysql_query("UPDATE wss_search SET  count = count+1 WHERE id = '$id' and searchterm = '$trimmed' ");
}
else
{
mysql_query("INSERT INTO wss_search (id, searchterm, count) VALUES ('NULL', '$trimmed', '1') ");
}

select insert if not exist add or update

1 Answers1