Solved

Check database for duplicate entries

Posted on 2014-03-03
10
491 Views
Last Modified: 2014-03-04
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

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)   {

        }

Open in new window


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 

Open in new window

0
Comment
Question by:peter-cooper
  • 5
  • 4
10 Comments
 
LVL 42

Expert Comment

by:Chris Stanyon
ID: 39900073
Firstly, which part of your record identifies it as a duplicate? i.e which field needs to be unique in your database.

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
}
0
 

Author Comment

by:peter-cooper
ID: 39900089
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
0
 
LVL 42

Expert Comment

by:Chris Stanyon
ID: 39900117
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.
0
 

Author Comment

by:peter-cooper
ID: 39900146
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
0
 
LVL 42

Expert Comment

by:Chris Stanyon
ID: 39900151
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.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:peter-cooper
ID: 39900165
Ok at the moment I have this query:

$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)";

Open in new window

which I know is incorrect. Thanks
0
 
LVL 42

Accepted Solution

by:
Chris Stanyon earned 500 total points
ID: 39900184
No. On your database, using a tool such as phpMyAdmin, you run the ALTER TABLE query. You only do that once - do it now! Once that's done, your table will have a unique constraint on it permanently - job done! It has nothing to do with your PHP script.

After that's done, you adapt your PHP to catch the 1062 error:

$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)";
mysql_query($sql);
if (mysql_errno() == 1062) {
   //you have a duplicate
} else {
   //you don't have a duplicate
}

Open in new window

As an aside, at some point in the very near future you are going to have to change your code from using the mysql library to using the PDO or mysqli library - the mysql library is deprecated and will be removed from PHP altogether.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39900246
An article showing how to make the required move away from the deprecated MySQL extension is available here:
http://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.
0
 

Author Closing Comment

by:peter-cooper
ID: 39900400
Thanks very much Chris. Helpful as usual.
0
 

Author Comment

by:peter-cooper
ID: 39900403
Thanks Ray. Will check it out.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
spacing 5 29
C3-->D3 Line Chart 4 18
Paging Using PHP 7 32
SQL inner join confusion 15 19
Requirements JQuery 1.6+ HTML CSS Introduction This article was inspired by an EE question (http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28372511.html) on how to make a page show some balloons animate up a page…
Introduction A frequently asked question goes something like this:  "I am running a long process in the background and I want to alert my client when the process finishes.  How can I send a message to the browser?"  Unfortunately, the short answer …
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now