How do I Check for duplicate entries in mysql

You can query before insertion this is also fine, and you can also add a UNIQUE index on the custref column after that just wrap the insert query inside the try...catch(){} construct and handle the exception if custref already inserted.

@Mukesh Thanks for reply. Could you possibly do an example for before insertion. Thanks

I thought that this may work, but nothing coming back if I alert(data).

<?php session_start();  ?>
    <?php

    $con = mysql_connect("localhost","root","");
    if(!$con) { die('Could not connect: ' . mysql_error()); }
    mysql_select_db("sample", $con);
    $dept = $_POST['dept'];
    $custref = $_POST['sub'];
		
    $result = mysql_query("SELECT * FROM boxes WHERE custref = '".$custref."'");
    $found = mysql_num_rows($result);
		
     if ($found > 0)
		{
			
			echo "true";
			
		} else {
			
			$query = "SELECT * FROM boxes WHERE department = '".$dept."' AND status = 1 AND custref = '".$custref."'";
			$result = mysql_query($query) or die(mysql_error());
			$row = mysql_fetch_array($result) or die(mysql_error());
			$r = $row['custref'];

			$str =  json_encode($r);
			echo trim($str, '"');
			
		}

	
      ?>

Select allOpen in new window

Put a unique key on the fields that define the duplicate - MySQL will error if you try and insert a duplicate.

You can also look at ON DUPLICATE KEY (https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html)
Or REPLACE INTO (https://dev.mysql.com/doc/refman/5.7/en/replace.html)
Just be aware with the latter that it will result in a new auto-number id as it effectively does a delete and insert. The first method does not do this.

If you are not interested in replacing then just define your unique key and do a mysqli_query - it will return false if there is a duplicate key.

The mysqli::$errno will tell you what the failure is. I think the dupe entry error is 1062 - will have to check.

@julian Could I not adapt my code just to check for true or false and check result in my jquery code. MySql is not my best friend :-) Thanks

I have got this far and cannot see why my alert is not being triggered. The value in data is true so alert should triggered? Not sure about the return:false is it's needed or in the right place. I need to halt the script so it dosen't print true in '$('#srcBoxRslt').val(data);'.

Here's an example of a query that will create a UNIQUE column.

        $sql
        =
        "
        CREATE TEMPORARY TABLE $this->mytable
        ( id                 INT                   NOT NULL AUTO_INCREMENT PRIMARY KEY
        , $this->mycolumn    VARCHAR(8)  UNIQUE NOT NULL DEFAULT '?'
        )
        ENGINE=MyISAM DEFAULT CHARSET=ascii
        "
        ;

Select allOpen in new window

And here is an example showing how to catch the errno=1062 signal of a duplicate in a UNIQUE index.

$sql = "INSERT INTO $this->mytable ( $this->mycolumn ) VALUES ( '$key' )";
$res = $this->mysqli->query($sql);

// IF THERE IS A QUERY ERROR
if (!$res)
{
    // THE EXPECTED QUERY ERROR WOULD BE A DUPLICATE VALUE
    if ($this->mysqli->errno == 1062)
    {
        trigger_error("1062 Duplicate Key Event: $key at " . number_format($this->cnt), E_USER_NOTICE);
    }
    // OTHER UNEXPECTED QUERY ERROR
    else
    {
        $err
        = 'QUERY FAILURE:'
        . ' ERRNO: '
        . $this->mysqli->errno
        . ' ERROR: '
        . $this->mysqli->error
        . ' QUERY: '
        . $sql
        ;
        trigger_error($err, E_USER_ERROR);
    }
}

Select allOpen in new window

@Ray thanks for that. However, surely this can be done just using success(data) scenario. I am not allowed to change anything on the db. Thanks

Sorry should posted updated code earlier with my previous comment.

function processResponse(data) {
		 if (data === 'true') {
			 
			 alert('That box is not on the system');
			 
		 }
		 <----STOP HERE SO DATA VALUE(true) ENTERED INTO # BELOW.
		 $('#srcBoxRslt').val(data);
		 
		
  };

Select allOpen in new window

typo. should be: <----STOP HERE SO DATA VALUE(true) NOT ENTERED INTO # BELOW.

to exit a function you have a number of options

Option 1

function processResponse(data) {
		 if (data === 'true') {
			 
			 alert('That box is not on the system');
			 
		 }
                 else {
		 <----STOP HERE SO DATA VALUE(true) ENTERED INTO # BELOW.
		 $('#srcBoxRslt').val(data);
		 }
		
  };

Select allOpen in new window

function processResponse(data) {
		 if (data === 'true') {
			 
			 alert('That box is not on the system');
	                 return;		 
		 }
		 <----STOP HERE SO DATA VALUE(true) ENTERED INTO # BELOW.
		 $('#srcBoxRslt').val(data);
		 
		
  };

Select allOpen in new window

@julian Thanks for that. However after inserting return; it still does not trigger the alert and also puts 'true' in '$('#srcBoxRslt').val(data);' which I don't want.

You will need to change this as well. The string 'true' is not equal to the boolean value true

function processResponse(data) {
		 if (data === true) {
			 
			 alert('That box is not on the system');
			 
		 }
                 else {
		 <----STOP HERE SO DATA VALUE(true) ENTERED INTO # BELOW.
		 $('#srcBoxRslt').val(data);
		 }
		
  };

Select allOpen in new window

Thanks very much Julian. Well thought out answer

You are welcome.