CODEWITHSUNDEEP
phpmysql
Andy
Andy

Reputation: 3170

Ensuring Unique Rows Using PHP/MySQL

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)
                    ");
     }

 }

Upvotes: 0

Views: 65

Answers (2)

Marc B
Marc B

Reputation: 360902

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)

Upvotes: 1

a'r
a'r

Reputation: 37029

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.

Upvotes: 5

Related Questions