ajax - Verifying if a row already exist in the database

Question

I have a simple form for submitting an email address to a database. I want to check first, using javascript and AJAX, if that email address already exists in the table. The code I am using always returns true regardless if the row already exists in the database. Any hints?

JS Code

<script> 
$('form').submit(function(e) 
{
  alert('submit intercepted');
  e.preventDefault(e);
  var u = $('#email').val();
  $.ajax({
      url: './test2.php',
      type: 'POST',
      data: u,
      success: function (response) {
      //get response from your php page (what you echo or print)
        $('#status').append('<p>The email is ok to use and the row has been inserted </p><p>' + response);
        console.log('Submitted');
      },
      error: function(jqXHR, textStatus, errorThrown) {
        console.log(textStatus, errorThrown);
        alert('Email exists');
      }
    });
});
</script>

HTML Code

<form id="form" action="./test2.php" method="post"> 
    E-mail: <input type="email" name="email" id="email" required><br>
    <span id="status">   </span>
    <input type="submit" id="btnsub">
  </form>

PHP Code

if(isset($email)) {

  $sql_email_check = $conn->query("SELECT Email FROM test WHERE Email='$email' LIMIT 1") ;

  $email_check = $sql_email_check->num_rows;

  if ($email_check < 1) {

    //return "Available";
    return true;   
    //header('Location: /test.php');
    exit();

  } 
  else 
  {
    //return "Unavailable";
    return false;
    //header('Location: /test.php');
    exit(); 

  }
}

Check the response, not the success/error of ajax - it will still be successful even if there was no rows returned. — Qirel, Sep 12 '17 at 08:07
So I need to verify my response? if returned true then this otherwise that? — noel293, Sep 12 '17 at 08:07
If you're checking existance doing a `SELECT COUNT(1)` instead of a limit is usually better. — apokryfos, Sep 12 '17 at 08:08
Also https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php#answer-60496 — Dmitry, Sep 12 '17 at 08:11
`console.log(response)` in the success-block, see what it spits out. You should also consider formatting and returning it as JSON instead. — Qirel, Sep 12 '17 at 08:12
this is just for practise on the basics, Im not concerned about security issues at the momemnt @Dimitry but thanks anyway! — noel293, Sep 12 '17 at 08:13
Where did you declare the variable `$email` you are using in your PHP code ...? — CBroe, Sep 12 '17 at 08:18
You are sending a parameter value only with your AJAX request, but it is lacking a parameter name. — CBroe, Sep 12 '17 at 08:19
I am declaring it on the top of the php file, I just did not post it! @cBroe — noel293, Sep 12 '17 at 08:19
Why are you enforcing this rule in your program code and wasting a request to the db? Set the email col in the database to be indexed and unique. Your attempt to store will either succeed (email does not already exist) or it will fail due to index constraints (email exists). — Chase, Sep 12 '17 at 08:40
@Chase As I have already said above this is clearly for practicing different methods.. — noel293, Sep 12 '17 at 08:55
Practicing a SELECT and reading the return value vs. the question as you framed it are two different concepts. The requirement that email be unique in a table is an issue with the data model and best handled at the database layer. That's just how you (properly) deal with that scenario. I can appreciate exploring options but enforcing data column uniqueness in your code is not the correct path and learning this now will save you many headaches and gray hairs. — Chase, Sep 12 '17 at 09:05

noel293 · Accepted Answer · 2019-10-12T17:46:02.567

So basically the problem was returning the answer from the php code to the ajax function. for some reason the return values were not interpreted correctly as they were expected (from my understanding anyway)

PHP CODE

if(isset($email)) {    
  $sql_email_check = $conn->query("SELECT Email FROM test WHERE Email='$email' LIMIT 1") ;     
  $email_check = $sql_email_check->num_rows;    

  if ($email_check == 0) {
    echo 'true';  
    return; 
    } 
  else 
  {
    echo 'false';
    return;    
  }
}

JS CODE

<script> 
$('form').submit(function(e) 
{
  alert('submit intercepted');
  e.preventDefault(e);
  var u = { 'email': $('#email').val() };
  $.ajax({
      url: 'test2.php',
      type: 'POST',
      //dataType: 'string',
      data: u,
      success: function (response) {
        //get response from your php page (what you echo or print)
        console.log(response);

        if (response === 'true') {
          $('#status').append('<p>The email is ok to use and the row has been inserted </p><p>' + response);

        } else {
          console.log('Email does exist ');

        }
      },
      error: function(jqXHR, textStatus, errorThrown) {
        console.log(textStatus, errorThrown);

      }
    });
});
</script>

score 0 · Answer 2 · answered Sep 12 '17 at 08:14

0

Returning true and false in PHP outputs nothing in the browser. Try something like this:

header('Content-Type: application/json');
if (isset($email)) {
  $sql_email_check = $conn->query("SELECT Email FROM test WHERE Email='$email' LIMIT 1") ;
  $email_check = $sql_email_check->num_rows;
  if ($email_check < 1) {
    echo json_encode(['status' => 1]);
    die();
  } 
}

echo json_encode(['status' => 0]);

Then, in your JavaScript code, you do something like this:

success: function (response) {
  if (response.status === 1) {
    $('#status').append('<p>The email is ok to use and the row has been inserted </p><p>' + response);
  } else {
    alert('Email exists');
  }
},

Btw, you should really use prepared statements. I have no idea if you query works or not, but the root of the problem in the original question was that echoing true or false will not work like you expect.

answered Sep 12 '17 at 08:14

OptimusCrime

14,662
13
58
96

1

@OptimusCrime Why does a user who has almost 10k point concatenates user's input into SQL!? – Dmitry Sep 12 '17 at 08:21
@Dmitry I just copied the code from the answer? The question here was about the function not correctly returning the results as expected. I already pointed out prepared statements in my answer. – OptimusCrime Sep 12 '17 at 08:28
@OptimusCrime Then why not to include the proper code in the answer? – Dmitry Sep 12 '17 at 08:31
@Dmitry I'm not sure if you are new here or not, but I provided help to solve the question asked here. Of course I could alter the code beyond and recognition, but it goes past the question posted here. I also don't know if this user uses PDO or MySQLI. Secondly I guess this is just a small part of the code, there could be much more logic happening somewhere. For inexperienced users, people altering more than what is obvious may be confusing. I therefore inform the user about prepared statements instead of doing the alterations myself. – OptimusCrime Sep 12 '17 at 08:35

score 0 · Answer 3 · answered Sep 12 '17 at 08:15

It would help to know how $email is set in you test2.php. Is this part of a function where you create actual output later on?

My guess is your jquery is not actually filling the $_POST['email'] variable, since you're just giving the data a string value when what you really want is giving it an object

var u = { 'email': $('#email').val() };

Additionally you need to handle both the error; and the success case in the success callback of your request. The error callback is for actual request-errors.

Has this solved your question?

no, tho I used it as well, but i found the solution, please see my answer — noel293, Sep 12 '17 at 09:03

ajax - Verifying if a row already exist in the database

3 Answers3