Search mysql database before inserting data

Question

I cant quite think about how to do this with mysql and php. Basically I want to be able to submit data into a mysql database but before it is inserted, it will check to see if that entry already exists.

$guid=$_POST['guid'];
$name=$_POST['name'];
//Username
$user="webhost";
//Password
$pass="*******";
//IP To Host
$ip="***********";
//Database
$db="dayz2";
//Table
$table="whitelist";

//Database Connection
$con=@mysql_connect("$ip", "$user", "$pass")
            or die(mysql_error());

//Select Database
$dbcon=@mysql_select_db($db, $con)
            or die(mysql_error());
$dupesql = "SELECT * FROM $table where (name = '$name' AND guid = '$guid')";

$duperaw = mysql_query($dupesql);

if (mysql_num_rows($duberaw) > 0) {
  echo "Entry Already Exists";
}
else {
//Query Data Into Whitelist Table
$sql="INSERT INTO $table (name, guid) VALUES ('$name', '$guid')";

//Submit Data into Whitelist Table
$result=@mysql_query($sql, $con) or die(mysql_error());
}
?>

And what should it do if the record already exists? Nothing? Return an error? Increment a counter? Something else? Please include relevant information in your question. — Mark Byers, Sep 16 '12 at 09:37
This might help http://stackoverflow.com/questions/7719039/php-duplicate-checking-before-insert — , Sep 16 '12 at 09:38
You should probably define unique constraints on your data, and just check for error `1062` ("duplicate entry") after you attempt the insert. — lanzz, Sep 16 '12 at 09:40
I have updated my post to a php script I have tried to use but it doesnt display the error and stop the submittion — Ryahn, Sep 16 '12 at 09:49

score 1 · Accepted Answer · answered Sep 16 '12 at 09:39

You can do it in another way, instead of:

submit data into a mysql database but before it is inserted, it will check to see if that entry already exists.

You can do:

INSERT data into a mysql database if not existed, else ignore them

Something like :

INSERT IGNORE INTO table
INSERT IGNORE INTO yourtablename
SET fieldname = 'blah'
,..

score 0 · Answer 2 · answered Sep 16 '12 at 09:39

It depends what you are trying to do - what is the exact criteria for your query?

You have several options:

use INSERT IGNORE ... if you only want to insert new rows that don't have a duplicate primary key. See http://dev.mysql.com/doc/refman/5.5/en/insert.html.
use INSERT ... ON DUPLICATE KEY UPDATE to insert new rows and update rows where there is a primary key match. See http://dev.mysql.com/doc/refman/5.5/en/insert-on-duplicate.html.
use a normal SQL SELECT ... to pull the results first before performing business logic on the results before deciding which to INSERT ... or UPDATE ... depending on your requirements.

score 0 · Answer 3 · answered Sep 16 '12 at 09:39

0

It depends how you want to handle case when the entry exists. I you want to throw some error then you can create table trigger for insert event and put some checks there, but it will be slow because every insert will do this check.

answered Sep 16 '12 at 09:39

bary

1,699
2
15
24

Search mysql database before inserting data

3 Answers3