PHP form won't update my MySQL database.

Question

Can someone please help me fix this code I f***ed up it wont update my database anymore.

     DEFINE ('DB_USER', 'root');
     DEFINE ('DB_PASSWORD', '');
     DEFINE ('DB_HOST', 'localhost');
     DEFINE ('DB_NAME', 'tags');

     if ($dbc = mysql_connect (DB_HOST, DB_USER, DB_PASSWORD)) { 

      if (!mysql_select_db (DB_NAME)) {    

       trigger_error("Could not select the database!\n
MySQL Error: " . mysql_error());

       include ('./includes/footer.htm');
       exit();    
      } 

     } else { // If it couldn't connect to MySQL.


      trigger_error("Could not connect to MySQL!\n
MySQL Error: " . mysql_error());
      include ('./includes/footer.htm');
      exit();

     } // End of $dbc IF.

        $tag = mysql_real_escape_string($_POST['tag']);
      $query = 'UPDATE tags SET count = count+1 WHERE tag = '.$tag;
      mysql_query($query,$dbc);
      if( !mysql_affected_rows() ) {
        $query = 'INSERT INTO tags (tag,count) VALUES('.$tag.',1)';
        if (!mysql_query($query,$dbc))
        {
        die('Error: ' . mysql_error());
        }
      echo "1 record added";
      }

      mysql_close($dbc);

Answer 1

You should read the answers to your previous question, you are still missing the quotes around $tag in the sql queries.

$query = "UPDATE tags SET count = count+1 WHERE tag = '".$tag."'";

$query = "INSERT INTO tags (tag,count) VALUES('".$tag."',1)';

Answer 2

Yeah mate this line:

$query = 'UPDATE tags SET count = count+1 WHERE tag = '.$tag;

should read like this:

$query = "UPDATE tags SET count = count+1 WHERE tag = '".$tag . "'";

Cool.

Answer 3

Assuming you have a unique or primary key constraint on tags.tag, it would be simpler to do this in one query using the ON DUPLICATE KEY feature of MySQL:

$tag = mysql_real_escape_string($_POST['tag']);
$query = "INSERT INTO tags (tag, count) VALUES ('$tag', 1)
          ON DUPLICATE KEY UPDATE count = count+1";
mysql_query($query, $dbc);

Also notice what I did with the quotes. I use double-quotes around the SQL query string, which permits me to interpolate PHP variables directly in the string. No need to end the string and concatenate with "." or anything.

Then I use single-quotes in that SQL around string literals. Doing it this way makes everything more readable, and helps you avoid forgetting to quote strings in the SQL.

PS: You should be aware when using mysql_affected_rows() after an UPDATE that the affected rows can be zero even if a row was matched, because if the update makes no net change to any values, it doesn't count as an affected row. I.e. try "UPDATE tags SET count=count;" even though it applies to every row, it'll still report zero affected rows.

This tip doesn't apply to your example, since count=count+1 is bound to change a value. I'm just mentioning it for your future use of mysql_affected_rows().

Answer 4

First thing I would do is strip out all of the custom error handling to be sure it really is connecting. Either that or change the query all together. Also, updating to mysqli couldn't hurt. So first try changing the connection:

    DEFINE ('DB_USER', 'root');
    DEFINE ('DB_PASSWORD', '');
    DEFINE ('DB_HOST', 'localhost');
    DEFINE ('DB_NAME', 'tags');

    $dbc = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);

    if ($dbc -> connect_error) {
        echo "Could not select the database!\n MySQL Error: ".$dbc -> connect_error;
        include ('./includes/footer.htm');
        exit(); 
    }

If you don't get an error with the above, you are connected. Then put in a dummy query like so:

    $test_query = "SELECT * FROM tags WHERE count = 1";
    $test_results = $dbc->query($test_query);

Then you could add a simple check so that you can move on to your real query, like:

    if($test_results -> num_rows > 0) {        

    $tag = mysql_real_escape_string($_POST['tag']);
    $update_query = "UPDATE tags SET count = count+1 WHERE tag = '$tag'";
    $dbc -> query($update_query);

Then have it throw an error if one exists for each query:

    echo ($dbc -> error) ? $dbc -> error : "";

    if($dbc -> affected_rows < 1 ) {
          $insert_query = "INSERT INTO tags (tag,count) VALUES('$tag',1)";
          $dbc -> query($insert_query);
          echo ($dbc -> error) ? $dbc -> error : "1 record added";
       }
    }

    $dbc -> close();