peter-cooper
asked on
Check database for duplicate entries
I am trying to find a way to check for duplicate entries in a mysql database. When I say duplicate, if the value already exists in a table, then deny the entry.
I am using jquery to connect to php and would appreciate some guidance as to how I can possibly code this. I have tried various if statements, but all error or fail miserably. I have posted the relevant part of the code as all other areas ie; validation are working ok. Also assume, all db calls and connections are in place. Thanks
PHP Code
I am using jquery to connect to php and would appreciate some guidance as to how I can possibly code this. I have tried various if statements, but all error or fail miserably. I have posted the relevant part of the code as all other areas ie; validation are working ok. Also assume, all db calls and connections are in place. Thanks
submitHandler: function() {
if ($("#USRboxint").valid() === true) {
var data = $("#USRboxint").serialize() + '&submit=true';
$.post('/domain/users/boxesadd.php', data, function(msgs) {
var messageOutputs = '';
for (var i = 0; i<msgs.length; i++){
messageOutputs += msgs[i].box+' ';
//console.log(messageOutputs);
}
$("#USRboxint").get(0).reset();
var $dialog = $('<div id="dialog"></div>').html('<br /><b>Your entry successfully submitted.<br /><br />Thank you.</b>');
$dialog.dialog({
autoOpen: true,
modal: true,
title: 'New Entry successfull',
width: 400,
height: 200,
draggable: false,
resizable: false,
buttons: {
Close: function() {
$( this ).dialog( "close" );
}
}
});
}, 'json');
} else
{
return;
}
},
success: function(msg) {
}
PHP Code
<?php
$status = mysql_real_escape_string($_POST['status']);
$company = mysql_real_escape_string($_POST['company']);
$requested = mysql_real_escape_string($_POST['requested']);
$activity = mysql_real_escape_string($_POST['activity']);
$address = mysql_real_escape_string($_POST['address1']);
$service = mysql_real_escape_string($_POST['service']);
$box = mysql_real_escape_string($_POST['box_add']);
$date = DateTime::createFromFormat('d/m/Y', $_POST['datepicker']);
$destdate = $date->format('Y-m-d');
$authorised = mysql_real_escape_string($_SESSION['kt_name_usr']);
$submit = mysql_real_escape_string($_POST['submit']);
$dept = mysql_real_escape_string($_POST['dept']);
$array = split('[,]', $_POST['box_add']);
if (isset($_POST['submit'])) {
$form = array();
foreach ($array as $box) {
// **check for dupe here**
$form[] = array('dept'=>$dept,
'company'=>$company,
'address'=>$address,
'service'=>$service,
'box'=>$box,
'destroydate'=>$destroydate,
'authorised'=>$authorised,
'submit'=>$submit);
$sql = "INSERT INTO `temp` (service, activity, department, company, address, user, destroydate, date, item, new) VALUES ('$service', '$activity', '$dept', '$company', '$address', '$requested', '$destdate', NOW(), '$box', 1)";
$result = runSQL($sql) or die(mysql_error());
}
}
$result=json_encode($form);
echo $result;
?>
php jquery
ASKER
Hi Chris
The record in question is the field 'item'. I am not familiar at all with this way of working. How would I amend my db to accept this 'UNIQUE' entry? Thanks
The record in question is the field 'item'. I am not familiar at all with this way of working. How would I amend my db to accept this 'UNIQUE' entry? Thanks
Peter,
Several ways to do it depending on how you manage your database, but basically you run this query:
ALTER TABLE temp ADD UNIQUE (item);
If you're using phpMyAdmin, select your 'temp' table, then the Structure tab. click on the Unique button for the 'item' column.
Several ways to do it depending on how you manage your database, but basically you run this query:
ALTER TABLE temp ADD UNIQUE (item);
If you're using phpMyAdmin, select your 'temp' table, then the Structure tab. click on the Unique button for the 'item' column.
ASKER
Only problem I foresee with that approach chris, is would I not need 2 entries for the sql query. 1 for the item and 1 for the rest or can I incorporate your suggestion as 1 query. Thanks
Not sure I follow.
You run the ALTER TABLE query once - now! And the unique constraint is permanently added to your table.
Then in your PHP script, you just run as normal, but whenever you try to insert a record that contains a duplicate 'item', error 1062 would be raised.
You run the ALTER TABLE query once - now! And the unique constraint is permanently added to your table.
Then in your PHP script, you just run as normal, but whenever you try to insert a record that contains a duplicate 'item', error 1062 would be raised.
ASKER
Ok at the moment I have this query:
How do I adapt your code to include to also insert the other values. ie; does it become
$sql = "INSERT INTO `temp` (service, activity, department, company, address, user, destroydate, date, item, new) VALUES ('$service', '$activity', '$dept', '$company', '$address', '$requested', '$destdate', NOW(), '$box', 1)";
How do I adapt your code to include to also insert the other values. ie; does it become
$sql = "ALTER TABLE temp ADD UNIQUE (item) (service, activity, department, company, address, user, destroydate, date, item, new) VALUES ('$service', '$activity', '$dept', '$company', '$address', '$requested', '$destdate', NOW(), '$box', 1)";
which I know is incorrect. Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
An article showing how to make the required move away from the deprecated MySQL extension is available here:
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html
A page mapping the familiar but deprecated MySQL_xxx() functions to the modern extensions is available here:
http://www.iconoun.com/mysql_mysqli_pdo_function_map.php
In my experience there are some things that can help you make this conversion smoothly. It is quite OK to make two simultaneous connections to the DB server, so you can keep the existing MySQL connection and add a MySQLi connection. Once you have done that you can convert the PHP code one query at a time. The PHP syntax is easiest if you use object-oriented MySQLi as shown in the article.
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html
A page mapping the familiar but deprecated MySQL_xxx() functions to the modern extensions is available here:
http://www.iconoun.com/mysql_mysqli_pdo_function_map.php
In my experience there are some things that can help you make this conversion smoothly. It is quite OK to make two simultaneous connections to the DB server, so you can keep the existing MySQL connection and add a MySQLi connection. Once you have done that you can convert the PHP code one query at a time. The PHP syntax is easiest if you use object-oriented MySQLi as shown in the article.
ASKER
Thanks very much Chris. Helpful as usual.
ASKER
Thanks Ray. Will check it out.
Normally the way you do this is to set a UNIQUE index on the particular field in your table. When you then try and insert the record in PHP, the database will automatically fire an error with a code of 1062. You then check for this error an act accordingly:
mysql_query(yourInsertStatement);
if (mysql_errno() == 1062) {
//you have a duplicate
}