prevent duplicate entry data still no function

Question

dear all. i have tried to prevent the duplicate data at my project. but until now it still make duplicate. i try this code but still not work:

  $cek_user= "SELECT Model, Serial_number, Line FROM inspection_report WHERE Model='".$Model."' AND Serial_number='".$Serial_number."' AND Line='".$Line."'";
  $cek_data=mysql_num_rows($cek_user);
  if($cek_data!=0){
                    echo "Data already exists!";
                   }
  else{
  $sql = "INSERT INTO inspection_report ";
  $sql.= "(Model,       Serial_number,       Line,        Shift,     Inspection_datetime,         Range_sampling,       Packing, ";
  $sql.= "Accesories,       Appearance,      Tuner,        General_operation,       Remark, ";
  $sql.= "NIK) ";
  $sql.= "VALUES ('";
  $sql.= $Model."','".$Serial_number."', '".$Line."','".$Shift."','".postVar('insp_date')." ".postVar('time')."','".$Range_sampling."','".$Packing."','";
  $sql.= $Accesories."','".$Appearance."','".$Tuner."','".$General_operation."','".$Remark."','";
  $sql.= $NIK."')";
//echo $sql;

$result=mysql_query($sql) or die(_ERROR26.": ".mysql_error());
//echo $result;
}
mysql_close($dbc);
}

but still not work,please help.

Answer 1

This will not prevent duplicates unless your table also has a UNIQUE constraint somewhere allowing the database to determine what you mean by a duplicate. If you have such a constraint, perhaps you could post your table definition.

Answer 2

You can do a select before insert, eg. Select id from table where serial_number = '$serial_number' If mysql_num_rows equals 0, do insert. This assumes serial_number is unique for each row.

$sql = "SELECT ID FROM inspection_report WHERE Serial_number = '$Serial_number'";
$result = mysql_query($sql);

if(mysql_num_rows($result) == 0){
  $sql = "your insert sql..."
  $result = mysql_query($sql);
}

Answer 3

You do realize your're running the INSERT query twice, right?

if ( mysql_query($sql) ) {
     ^^^^^^^^^^^--- here
    [.... snip ....]
} 
$result=mysql_query($sql) or die(_ERROR26.": ".mysql_error());
        ^^^^^^^^^^^--- and here

As well, you should look into using HEREDOCs to build your query string. That long chunk of string concatenation and quote-soup you've got could look like this with a HEREDOC:

$insp_date = postVar('insp_date') . ' ' . postVar('time');
$sql = <<<EOL
INSERT INTO inspection_report
    (Model, Serial_number, Line, Shift, Inspection_datetime,
    Range_sampling, Packing, Accesories, Appearance, Tuner,
    General_operation, Remark, NIK)
VALUES (
    $Model, $Serial_number, $Line, $Shift, $insp_date,
    $Range_sampling, $Packing, $Accesories, $Appearance, $Tuner,
    $General_operation, $Remark, $NIK)
EOL;

every so slightly more readable.

edit/comment followup:

You're running the query twice, in the spots where I've put the '^^^^^--- here' lines.

First instance: if ( mysql_query($sql) ) {
Second instance: $result = mysql_query($sql) or die.......

You haven't changed the contents of $sql between the two mysql_query() calls, so when you do the second call, it runs the exact same query string, which is your INSERT query. So you end up inserting the data TWICE.

Beyond that, your error handling is atrocious. Scanning an error string for a particular string is the wrong way to go about it. The error text might change (think of what would happen if your code runs on a server running in (say) a German location, which has localized error messages and spits out "Doppelter eintrag für ..." instead of "Duplicate entry for". What you should have is something like this:

$sql = "... your query here ... "

$result = mysql_query($sql); // if query fails, this returns FALSE

if ($result === FALSE) {
    die("MySQL error: " . mysql_error());
}

If you need to check for a particular error that could be corrected by your code, you can use mysql_errno() to retrieve the server error code, and work from there. Using your example, 'Duplicate entry' is error # 1062 (full error codes documented here), so you'd do

if (mysql_error() == 1062) {
    ... handle error here ...
}

Answer 4

first of all:

ALTER inspection_report ADD UNIQUE(Model, Serial_number, Line);

Then:

$sql = "INSERT IGNORE INTO..........";