How to get JQuery plugin Validate to check for duplicate values in DB

Question

As stated in the title. I am using JQuery and Validate plugin to validate user inputs in a form.

I am trying to implement check for duplicate values in a table.

For this I am using following code:

$().ready(function(){
    //alert('JQuery Functioning');
    $("#cat_name").change(function()
    {
        var cat_name = $("#cat_name").val();
        $.ajax({
            type:"POST",
            url:"ws/cat_duplicate.php",
            data:"cat_name="+cat_name,
            cache: false,
            success: function(html)
            {
                //alert(html);
                if(html == 1)
                {
                    $("#duplicate").html("<br/>Duplicate category Found. Please, Enter Unique Category Name");
                    $("#add_rec").attr("disabled",true);
                }
                else
                {
                    $("#duplicate").html("");
                    $("#add_rec").attr("disabled",false);
                }
            }
        }); //$.ajax({
    }); //$("#cat_name").change(function()
    
    $("#frmAddCategory").validate({
        rules: {
            cat_name: "required"
        },  //rules: {
        messages: {
            cat_name: "<br />Please provide Category Name here"
        }   //messages: {
    }); //$("#frmAddCategory").validate
});

Here is the HTML part:

<h2>Category Edit</h2>
<form id="frmAddCategory" name="frmAddCategory" method="post" action="">
  <table width="25%" border="0" align="center" cellpadding="0" cellspacing="0">
    <tr>
      <td>ID</td>
      <td>Auto Generated</td>
    </tr>
    <tr>
      <td>Category Name</td>
      <td><input name="cat_name" type="text" id="cat_name" value="" maxlength="60" /><span id="duplicate"></span></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr align="center" valign="middle">
      <td colspan="2"><input type="submit" name="add_rec" id="add_rec" value="Add" />
      &nbsp;&nbsp;&nbsp;<a href="cat_list.php">Cancel</a></td>
    </tr>
  </table>
</form>

The PHP code that I am calling is:

<?php
if(isset($_POST['cat_name']))
{
require_once('../conn/blog.php');

mysqli_select_db($blog, $database_blog);
$cat_name = $_POST['cat_name'];
$query = "SELECT cat_name FROM category WHERE cat_name = '$cat_name'";

$query_retrived = mysqli_query($blog, $query) or die("Error:".mysqli_error($blog));
$rows = mysqli_fetch_assoc($query_retrived);
$result = mysqli_num_rows($query_retrived);

if ($result>0)
{
    echo '1';
}
else
{
    echo '0';
}
mysqli_free_result($query_retrived);
}
?>

But in this code I have to enable or disable the submit button. Instead of that I want the Validate plugin to handle checking of duplicate values.

How can we do this?

TIA

have you tried jquery validate remote method to achive same? — Sandeep Modak, Oct 15 '20 at 06:20
Sorry I don't know about remote method. But I will look it up. Thanks for the tip. — Yogi Yang 007, Oct 15 '20 at 14:33
It is a very bad idea to use `die(mysqli_error($conn));` in your code, because it could potentially leak sensitive information. See this post for more explanation: [mysqli or die, does it have to die?](https://stackoverflow.com/a/15320411/1839439) — Dharman, Oct 16 '20 at 11:16

score 0 · Accepted Answer · answered Oct 15 '20 at 06:29

You may try "remote" method to check if same category exist in database or not

$( "#frmAddCategory" ).validate({
  
  rules: {
    cat_name: {
      required: true,
      remote: {
        url:"ws/cat_duplicate.php",
        type: "post",
        async: false,
        data: {
          cat_name: function() {
            return $( "#cat_name" ).val();
          }
        }
      }
    }
  },
  messages: {
            cat_name: { 
                            required:"Please provide Category Name here",
                            remote:"This Category alredy exist"
                       },
        } 
  
});

in the PHP code you may i have to return string 'true' or 'false' like

<?php
if(isset($_POST['cat_name']))
{
require_once('../conn/blog.php');

mysqli_select_db($blog, $database_blog);
$cat_name = $_POST['cat_name'];
$query = "SELECT cat_name FROM category WHERE cat_name = '$cat_name'";

$query_retrived = mysqli_query($blog, $query) or die("Error:".mysqli_error($blog));
$rows = mysqli_fetch_assoc($query_retrived);
$result = mysqli_num_rows($query_retrived);

if ($result>0)
{
    echo 'true';
}
else
{
    echo 'false';
}
mysqli_free_result($query_retrived);
}
?>

It is a very bad idea to use `die(mysqli_error($conn));` in your code, because it could potentially leak sensitive information. See this post for more explanation: [mysqli or die, does it have to die?](https://stackoverflow.com/a/15320411/1839439) — Dharman, Oct 16 '20 at 11:15
This post helped me, so I'd like to return the favor with a minor improvement. Return "true" or "false" from the query itself: `$query = "select case when count(*) = 0 then 'true' else 'false' end as is_unique from category where cat_name = '$cat_name'";` I don't have mysql available, so I can't help with the rest, sorry. — wmeitzen, Jul 28 '22 at 19:24

How to get JQuery plugin Validate to check for duplicate values in DB

1 Answers1