jQuery form, exist checking

Question

So i am creating a simple form that checks whether or not the value that the user is inputting exists or not in my DB using jQuery. Everything up until now is working so far however i find myself stuck at this next part.

To easily explain i will just show an example of what i am trying to achieve. For this example i will be "weeden"

weeden has an ID of 255 in the company table of my database.

1. If the user types in "weeden" into the client field
2. To the right of the client field (on the web form), the text "weeden is unavailable" will appear

what i would like to have happen instead is this: "ID 255 is unavailable"

Here is the relevant code.

HTML FORM

<form action="addrecord.php" method="post" autocomplete="off"/>  
<div class="form-field">
  <label for="client">Client: </label>
  <input type="text" name="client" id="client" class="check-exists" data-type="client" placeholder="#">
  <span class="check-exists-feedback" data-type="client"></span>
</div>

jQuery Function

$.fn.existsChecker = function(){
  return this.each(function(){   
        var interval;

        $(this).on('keyup', function(){
            var self = $(this),
                selfType = self.data('type'),
                selfValue,
                feedback = $('.check-exists-feedback[data-type=' + selfType + ']');

            if(interval === undefined){
                interval = setInterval(function(){

                  if(selfValue !== self.val()){
                      selfValue = self.val();

                      if(selfValue.length >= 1){
                          $.ajax({
                              url: 'check.php',
                              type: 'get',
                              dataType: 'json',
                              data: {
                                    type: selfType,
                                    value: selfValue
                              },
                              success: function(data){
                                  if(data.exists !== undefined){
                                      if (data.exists === true){
                                          feedback.text(selfValue + ' is already taken.');
                                      }else {
                                          feedback.text(selfValue + ' is available');
                                      }
                                  }
                              },
                              error: function(){

                              }
                           });
                       }
                  }
              }, 1000);
          }
      });
  });
};

Check.php

$db= new PDO('mysql:host=host;dbname=mydb', 'user', 'pass');

if(isset($_GET['type'], $_GET['value'])){

$type = strtolower(trim($_GET['type']));
$value= trim($_GET['value']);

$output = array('exists' => false);


if(in_array($type,
            array('client')
            )
   ){

  switch($type){
      case 'client':
           $check = $db->prepare("
                SELECT COUNT(*) AS count
                FROM company
                WHERE name = :value
            ");
       break;

$check->execute(array('value'=> $value));
$output['exists'] = $check->fetchObject()->count ? true: false;
echo json_encode($output);

Any help/suggestions would be greatly appreciated. I consider myself a beginner, this is my first time working on a web project.

Just to clarify ahead of time, there are many other input fields on the same webform such as: email, date, first, last, etc. I hope my question was clear enough. Thank you

Answer 1

You have to change your Query to something like this:

$check = $db->prepare("
            SELECT id, COUNT(*) AS count
            FROM company
            WHERE name = :value
        ");

I assume that your primary key field on the company-table is named id.

And finally store the id in the output-Array

$result = $check->fetchObject();
$output['exists'] = $result->count ? true: false;
$output['id'] = $result->id;

Then you can output the id like so:

if (data.exists === true){
  feedback.text('ID ' + data.id + ' is unavailable');
}

Answer 2

You can handle everything in query

     $db= new PDO('mysql:host=host;dbname=mydb', 'user', 'pass');

     if(isset($_GET['type'], $_GET['value'])){

         $type = strtolower(trim($_GET['type']));
         $value= trim($_GET['value']);

         $output = array('exists' => false);


        if(in_array($type,array('client'))){

            switch($type){
                 case 'client':
                       $check = $db->prepare("
                    SELECT (CASE WHEN(COUNT(id)>0) THEN id ELSE FALSE END) AS count 
                    FROM company WHERE name = :value ");
                  break;
            }  
            $check->execute(array('value'=> $value));
            $output['exists'] = $check->fetchObject()->count ? true: false;
            echo json_encode($output);
       }

In Ajax success

      if(data.exists !== undefined){
          if (!data.exists){
                feedback.text(selfValue + ' is already taken.');
          }else {
               feedback.text(selfValue + ' is already taken.');
          }
       }