Stop SQL adding mutliple rows of the same information

Question

Is there a way I can check in my PHP file that adds users to my database when used. I.E.

John uses my program and it grabs his information when he gets an error and sends it to my database, it has his name, email, and computer OS.

Now thats all I want, but if john goes to start the program again it will send ANOTHER, DUPLICATE copy of the row to my database, this is useless and clogs it full of useless information.

Code currently being used:

$connect = mysql_connect('host','user','pass') or die("Error");
mysql_select_db('DB') or die("Error");
$Name    = $_GET['name'];
$Email   = $_GET['email'];
$OS      = $_GET['os'];
$add     ="INSERT INTO UserDB(Name, Email, OS, ) VALUES ('$Name', '$Email', '$OS')";
mysql_query($add,$connect);
mysql_close($connect);

in addition to any php logic you add (like fixing the complete absense of variable escaping - "I'm sorry Bobby O'Mally - you'll just have to change your name") - just add a unique index to the table UserDB such that duplicates are impossible. — AD7six, Jan 04 '13 at 13:18
Im not sure what you mean or how to do that, im quite new to MySQL and have little experience with it. — Cacoon, Jan 04 '13 at 13:22
please do learn about [SQL Injection](http://en.wikipedia.org/wiki/SQL_injection) or [this will happen to you](http://xkcd.com/327/) — Phil Frost, Jan 04 '13 at 13:28

score 4 · Answer 1 · edited May 23 '17 at 10:24

4

You want a unique constraint or appropriate primary key in your database. Then, when your code attempts to insert a duplicate row, the database will raise an error, and you can handle it. If you want to replace old rows with new rows, you may want an upsert.

Here is a good article on selecting a primary key.

edited May 23 '17 at 10:24

Community

1
1

answered Jan 04 '13 at 13:24

Phil Frost

3,668
21
29

I agree. @user1948659 should just keep that sort of work in the database otherwise you would have to execute one query just to check if the record already exists and then a second query to insert it if it doesn't. Unique Keys are your friend. – EmmanuelG Jan 04 '13 at 13:29

score 0 · Accepted Answer · answered Jan 04 '13 at 13:27

0

Without creating a multitude of extra logic you can create a UNIQUE key association on your database. Since email addresses are unique, you could do it this way:

Run this SQL

This adds the unique key and disallows a multiple insert on an email address.

ALTER TABLE `UserDB` ADD UNIQUE(`Email`);

Change your PHP Code Query

Use this query to update if no insert was made.

if(mysql_insert_id() < 1) {
    $add = sprintf(
            "UPDATE `UserDB` SET `Name`='%s', `Email`='%s', `OS`='%s' WHERE `Email`='%s'",
            $Name,
            $Email,
            $OS,
            $Email
    );
}

Please switch to mysqli or PDO, your very vulnerable to SQL Injections!!

answered Jan 04 '13 at 13:27

phpisuber01

7,585
3
22
26

This answer works, but glosses over important aspects of database design. If email addresses are unique in this table, then `email` is a prime candidate for a primary key, not an ordinary unique constraint. Further, if the intent is to update existing rows on duplicate, rather than ignore new duplicate rows, than an [upsert](http://stackoverflow.com/questions/1218905/how-do-i-update-if-exists-insert-if-not-aka-upsert-or-merge-in-mysql) is a much better solution. – Phil Frost Jan 04 '13 at 14:15
@PhilFrost What you have said is true. :-) – phpisuber01 Jan 04 '13 at 14:20

score -1 · Answer 3 · answered Jan 04 '13 at 13:24

-1

You can never an exact copy of a row in a database. There must alway be a unique id. This is often a number that is auto incremented for each new row. If that is the case in your table you must simply ask the database if the current user is new or existing. Something like:

$query = "SELECT id FROM UserDB WHERE Name='$name' AND Email = '$email'";
$result = mysql_query($query);
if(mysql_num_rows($result) == 0)
//Add user
else
//Update user? Or something else

answered Jan 04 '13 at 13:24

EirikO

617
8
19

You absolutely can have duplicate rows. There's nothing that requires a table to have a primary key. Adding an `auto_increment` column to have a primary key isn't always a good idea. [this article is a good read](http://www.agiledata.org/essays/keys.html). – Phil Frost Jan 04 '13 at 13:26
This should be done using the database's own methods, like unique keys. – Nils Werner Jan 04 '13 at 13:26

Stop SQL adding mutliple rows of the same information

3 Answers3