Let's play Find the error! (again, sigh)

[Jax2]

Hi all, happy april fools day Have something here that, unfortunately, isn't so funny. Yet another broken piece of code. I'm hoping someone with more knowledge than me can help me through this bit.

 

I created a table:  ID | search_term | count

 

When someone uses the search bar on my site, it adds the term they searched for to the database with a count of 1. If that exact term already exists in the database, it should not add a new row, but change the count for that term to count+1.

 

While testing the script, inserting a never before seen query works as it should. It is inserted into the database.

 

However, if I repeat the search, for some reason, the count is not being updated as it should. I tried echoing any mysql_errors and it came back without an error.

 

Here is the code:

$query=sanitize(strtolower($_POST['query']));
$sql = "select * from ".$prefix."searches where search_term = '$query'";

   $result = mysql_query($sql ,$db);

   if (mysql_num_rows($result) > 0) {

      $row = mysql_fetch_row($result);
      $count = $row[1];
      $newcount = $count + 1;
      $sql = "update ".$prefix."searches set count = $newcount where search_term = '$query'" or die(mysql_error());
  $result=mysql_query($sql, $db);
   } else {

      $sql = "insert into ".$prefix."searches (search_term, count) values ('$query', 1)";
  $result=mysql_query($sql, $db);

   }

 

Any ideas would be greatly appreciated.

[premiso]

$query=sanitize(strtolower($_POST['query']));
   // selecting the column you need will tend to yield more efficient queries.
   $sql = "select `count` from ".$prefix."searches where search_term = '$query'";
   $result = mysql_query($sql ,$db) or trigger_error("MySQL Select Failed: " . mysql_error());

   if (mysql_num_rows($result) > 0) {
      $sql = "update ".$prefix."searches set `count` = `count`+1 where search_term = '$query'" or die(mysql_error());
  $result=mysql_query($sql, $db) or trigger_error("MySQL Update Failed: " . mysql_error());
else {
      $sql = "insert into ".$prefix."searches (search_term, `count`) values ('$query', 1)";
      $result=mysql_query($sql, $db) or trigger_error("MySQL Insert Failed: " . mysql_error());
   }

 

Try that, you do not need to pull the data out of the database and add it with php, mysql is perfectly capable of doing such mundane tasks.

 

As I am not sure if "count" is a reserved word in MySQL (I know it is a function) I encased it in backticks so it will definitely be passed / treated as a column. See if that fixes your issue.

[Jax2]

Beautiful, thanks! I tend to overcomplicate stuff and it bit me this time :/

 

Works perfect. Thanks again.