Ensuring Unique Rows Using PHP/MySQL

Question

I have the following code that should, when run, update a table of "victims" of Her Royal Majesty Penelope the Queen of Sheep (it's work for someone, honest), however every time the code is executed it adds all new rows all over again. I was pretty sure I had safeguarded against that, but I guess not. What am I doing wrong here?

require_once 'victims.php';

 foreach( $victims as $vic )
 {
     $vic = mysql_real_escape_string($vic);

     if(!(mysql_query("
                      SELECT * FROM victims
                      WHERE ".$vic
                     )))
     {
         mysql_query("
                     INSERT INTO victims
                     (victim, amount) 
                     VALUES( '".$vic."', 0)
                    ");
     }

 }

Answer 1

You need to change the where clause of your first query to the following:

WHERE victim = $vic

Also, please consider using bind variables as this will protect your code from SQL injection attacks.

Answer 2

You could use an "INSERT ... ON DUPLICATE KEY" query instead, which will guarantee that existing rows won't be duplicated, but only updated. Assuming vic is the table's primary key, you'd do:

INSERT INTO victims (victim, amount)
VALUES ($vic, $amount)
ON DUPLICATE KEY UPDATE amount=VALUES(amount)