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

 }

a'r · Accepted Answer · 2010-08-20 00:00:21Z

5

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.

answered Aug 20, 2010 at 0:00

a'r

37.2k7 gold badges68 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Marc B · Accepted Answer · 2010-08-20 00:35:48Z

1

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)

answered Aug 20, 2010 at 0:35

Marc B

362k44 gold badges433 silver badges508 bronze badges

Collectives™ on Stack Overflow

Ensuring Unique Rows Using PHP/MySQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related