Prevent duplicates being inserted

[fearless1]

I have a simple db with three columns, id name and details.
There are two forms for adding and editing details, to be sure that the add details form does not create duplicates i need to:

check that a name does not already exist before the new data is inserted, if the name already exists then i want to simply update the details.

Can this be done easily?

[bpat1434]

Yes, just as you said:

Do a quick query to see if there is already something there:

"SELECT name FROM table WHERE name='".$_POST['name']."';"

Then IF there is no match, insert:

if(!$result){
    do_insert($_POST);
}

Else update it:

else{
    do_update($_POST);
}

So you could conceivably have:

<?php

$query = "SELECT * FROM `table` WHERE name='".$_POST['name']."'";
$result = mysql_query($query);

if(!$result){
    do_insert();
}
else{
    do_update();
}

?>

Obviously I'm counting on you to define the functions, or replace the functions with the code to update/insert the data.

~Brett

[krotkruton]

I'm not sure if this is the most efficient way to do it, but this is what I do.

I'm assuming your form submits to a new page, but it isn't that hard to do it all in one page either using isset.

On the page that gets the form information, lets say you don't want duplicate IDs being inserted (this works for other ways too, just change from ID), then you just check to see if there are any rows with that ID.

$current_id = $_POST['form_id'];

$query_find = "SELECT * FROM `table` WHERE `id` = '$current_id'"
$find = mysql_query($query_find, $connection) or die(mysql_error());
$num_rows_find = mysql_num_rows($find);

if($num_rows_find < 1) {
 $query_insert = "INSERT INTO `table` ... the information";
 $insert = mysql_query($query_insert, $connection) or die(mysql_error());
}
else {
 $query_update = "UPDATE `table` SET ...the information... WHERE `id` = '$current_id'";
 $update = mysql_query($query_update,$connection) or die(mysql_error());
}

Obviously there is some pseudocode in there so you will have to make some changes, and I'm not completely sure it works, but that's really close to what works for me. What it does is counts the number of results for the query, and if there aren't any results ($num_rows_find < 1), then it inserts the information, otherwise it updates the information for the given ID.

Now that I think about it, I'm pretty sure this isn't the most efficient way to do things because there is a COUNT function in mysql that may save some time by avoiding calling mysql_num_rows and also avoiding the if statement, but I'm not sure how important efficiency is for you. I also don't really know much about COUNT so I can't help you there.

[bpat1434]

ahem... isn't that exactly what I said? Well, anyway here's an even better one.....

INSERT INTO `table` (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE a=1, b-2, c=3

Now, that insert statement will return either a 1 or 2 integer.
1 --> inserted
2 --> updated

Let's let mySQL do the work. You can read more on it here (mySQL Documentation)

~Brett

[fearless1]

<?php
include ("dbconn.php");

$name=$POST["name"];
$details=$POST["details"];

$sql="SELECT * from tbl_details where name='$name'";
$result = mysql_query($sql, $conn) or die(mysql_error());

while ($row = mysql_fetch_array ($result)) {
$id=$row["id"];
$name=$row["name"];
$details=$row["details"];

if(!$result){
$query_insert = "insert into northwestminis_treenodes node_id, node_name, node_parent, parent_name values('','$name','', '')";
$insert = mysql_query($query_insert, $connection) or die(mysql_error());
}
else {
$query_update = "update northwestminis_treenodes set node_parent='$nodeparent', parent_name='$parentname' WHERE node_name = '$current_id'";
$update = mysql_query($query_update,$connection) or die(mysql_error());
}

}

?>

This code looks right to me, but the resulting page is blank and nothing happens in the database - what am i doing wrong?

[fearless1]

oops hang on a mo

[fearless1]

<?php
include ("dbconn.php");
$current_id = $POST['name'];
$parentname=$POST['parentname'];
$nodeparent=$_POST['parentid'];

$query = "SELECT * FROM treenodes WHERE node_name='".$_POST['name']."'";
$result = mysql_query($query);

if(!$result){
$query_insert = "insert into treenodes node_id, node_name, node_parent, parent_name values('','$name','', '')";
$insert = mysql_query($query_insert, $conn) or die(mysql_error());
}
else {
$query_update = "update treenodes set node_parent='$nodeparent', parent_name='$parentname' WHERE node_name = '$current_id'";
$update = mysql_query($query_update,$conn) or die(mysql_error());
}

?>

This is the actual code i am trying to update, used name, id and details earlier to try to make it as simple as possible, but just confusing myself now, still same as i said returns blank page and doesnt alter anything in database - any idea's

[bpat1434]

Add an "or die(msyql_error())" sattement....

<?php
include ("dbconn.php");
$current_id = $_POST['name'];
$parentname=$_POST['parentname'];
$nodeparent=$_POST['parentid'];


$query = "SELECT * FROM `treenodes` WHERE node_name='".$_POST['name']."'";
$result = mysql_query($query) or die(mysql_error());



if(!$result){
    $query_insert = "insert into treenodes node_id, node_name, node_parent, parent_name values('','$name','', '')";
    $insert = mysql_query($query_insert, $conn) or die(mysql_error());
}
else {
    $query_update = "update treenodes set node_parent='$nodeparent', parent_name='$parentname' WHERE `node_name` = '$current_id'";
    $update = mysql_query($query_update,$conn) or die(mysql_error());
}

?> 

You could also do this:

<?php

$que = "CREATE UNIQUE KEY node ON treenodes ('node_name')";
mysql_query($que) or die('Can\'t create Unique Key<br>'.mysql_errno().'<br>'.mysql_error());

$query = "INSERT INTO `treenodes` (node_name) 
VALUES ('$name') 
ON DUPLICATE KEY UPDATE node_parent='$nodeparent', parent_name='$parentname'";
$result = mysql_query($query);
$rows = @mysql_affected_rows($dbconnection_link_id);

if(!$result){
    if($rows == 2){
        echo 'Nothing to update!';
    }
    else{
        echo 'Error updating / inserting!!<br>'.mysql_error();
    }
}
if($result == 1){
    echo 'Inserted Successfully!';
}
if($result == 2){
    echo 'Updated Successfully!';
}

?>

~Brett

[fearless1]

I used the first suggestion and received that blank page and no change in the database.
I used the second suggestion and got an error, so i added a $query echo to check that the right details are coming through, and they are

The error i got was: (with the $query running infront)

INSERT INTO treenodes (node_name, node_parent, parent_name) VALUES ('cars', '', '') ON DUPLICATE KEY UPDATE node_parent='2', parent_name='cars'Error updating / inserting!!
You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON DUPLICATE KEY UPDATE node_parent='2', parent_name='cars'' at

is this just me or am i making something seem a lot more complicated than it should be??

[bpat1434]

Um... could me be me... Perhaps you could use it more like:

$query = "INSERT INTO `treenodes` (node_name)
VALUES ('$name')
ON DUPLICATE KEY UPDATE  SET node_parent='$nodeparent' AND parent_name='$parentname'";

~Brett

[Sxooter]

Use a unique index. It's the only way to be sure, since you can do a select for a pre-existing row, not find it, and then have someone else insert one while you're busy inserting your.

ONLY A UNIQUE index is guaranteed to work reliably.

[bpat1434]

I've been waitin for you to reply to this Sxooter.... I learned what I said here from you 😉

~Brett

[Roger_Ramjet]

Just to stir the pot :p

Since the on duplicate key update syntax is version specific, version 4.1.0 and up, you may have a version problem.

You can simplify the whole thing anyway by just using REPLACE instead of INSERT etc.
If the unique key already exists then that row will be deleted and the new record inserted; if it does not exist then a new record is just inserted anyway.

Whichever you choose, replace or duplicate key update, depends on the context. If you need to preserve some data in the existing record, such as a timestamp or autoinc column, then use the duplicate key form; otherwise just use a replace query. 🆒

[Sxooter]

bpat1434 wrote:

I've been waitin for you to reply to this Sxooter.... I learned what I said here from you 😉

~Brett

Hehe. Thanks, I think.

The thing is, no matter what you do in your code, if something goes wrong somewhere, without that unique key / index, you CAN get duplicates. Could be routine maintenance, a partial data load, all kinds of stuff. and it's so easy to just let the database handle the issue for you.

Then, you can use replace and everything else with the confidence that even if you screw up, your database is watching out for you, and not letting those mistakes fark up your data.

[krotkruton]

First, I was typing that post when Brett posted his which was why mine was so similar.
As for the actual problem, I would suggest using the code in your post (#6) and adding in some echo statements.

<?php
include ("dbconn.php");
$current_id = $_POST['name']; 
$parentname=$_POST['parentname'];
$nodeparent=$_POST['parentid'];

echo "Before the SELECT <br>";
$query = "SELECT * FROM `treenodes` WHERE node_name='".$_POST['name']."'"; 
$result = mysql_query($query); 


echo "Before the IF <br>";
if(!$result){ 
echo "Before the INSERT <br>";
$query_insert = "insert into treenodes node_id, node_name, node_parent, parent_name values('','$name','', '')"; 
$insert = mysql_query($query_insert, $conn) or die(mysql_error()); 
echo "After the INSERT <br>";
} 
else { 
echo "Before the UPDATE <br>";
$query_update = "update treenodes set node_parent='$nodeparent', parent_name='$parentname' WHERE `node_name` = '$current_id'"; 
$update = mysql_query($query_update,$conn) or die(mysql_error()); 
echo "After the UPDATE <br>";
} 
echo "After the IF <br>";

?>

This way you will know exactly where the code terminates and will know which query is causing the problem. Of course, if the last echo is displayed on the screen, then you will know that your page is crashing from something else.

EDIT: Wow, and for some reason none of the last 4 or so responses showed up in this thread until after I posted... so I guess this post is pretty worthless