Check if user exists in database (form)

Question

I have a script which allows users to sign up, which imports data into a MySQL table.

user_availability.php
http://pastebin.com/PSRndXbq

number.js
http://pastebin.com/iYAPuwN7

This script will tell the user if that number is present in the table. However it continually says its not in the table despite it being in there (looking through PMA).

I have tried a few things, like var_dump etc to check the query and it returns fine. I've also tried adding "no" to both the if/else string and it's the same. With that in mind id it would seem the JS is at fault?

Do I have an error in my code?

Cheers

Answer 1

EDIT:

JS:

$(document).ready(function()
{
    $("#number").blur(function()
    {
        $("#msgbox").removeClass().addClass('messagebox').text('Checking').fadeIn("slow");
        //check the username exists ajax
        // switch to ajax so we can handle errors...
        $.ajax({
            url: "user_availability.php",
            data: { user_name:$(this).val() },
            type: 'post',
            dataType: 'json',
            success: function(data) {
                if(!data.userExists) //if username not avaiable
                  {
                      $("#msgbox").fadeTo(200,0.1,function() //start fading the messagebox
                    { 
                      $(this).html('<img src="img/off.png" alt="Number registered" />').addClass('messageboxerror').fadeTo(900,1);
                    });

                  }
                  else
                  {
                      $("#msgbox").fadeTo(200,0.1,function()  //start fading the messagebox
                    { 
                      $(this).html('<img src="img/on.png" alt="Number unregistered"/>').addClass('messageboxok').fadeTo(900,1);    
                    });
                  }
            },
            error: function(request, status, exception){
                $("#msgbox").fadeTo(200,0.1,function() //start fading the messagebox
                { 
                  $(this).html('<img src="img/off.png" alt="Number registered" />').addClass('messageboxerror').fadeTo(900,1);
                });
                // for debug in firefox/firebug only, if you open your js console you should see some error reporting
                try {
                    var data = $.parseJSON(request.responseText);
                    var error = (typeof data.error != 'undefned') ?  data.error : request.responseText;
                    console.log("Error: \"" + error +"\"");
                } catch (e) {
                    console.log("Couldn't parse as JSON: \""+request.responseText+"\"");
                }

            }
        });
});

PHP:

<?php
// config 
include("../config.php");

function return_error($error, $json = array()) {
   $json['error'] = $error;
   header('HTTP/1.0 500 Internal Server Error');
   header('Content-type: application/json'); // send json headers
   // dump the json value should look like {'userExists': true, 'error': 'Some MySQL Error text'}
   echo json_encode($json);
   exit;
} 

$json = array('userExists' => false);

if(!mysql_connect($host, $user, $pass) || !mysql_select_db($database))
{
    return_error(mysql_error(), $json);
}

// dont know your logic for checking with is_numeric so just apply that however, 
// i just changed the variable name so it matches up with what youre passing in the JS
$query = "SELECT email FROM recipients where email ='" . $_POST['user_name'] ."'";


$result = mysql_query($query);

if(!$result){
   return_error(mysql_error(), $json); 
}

$result = array('userExists' => false); // default to false

if(mysql_num_rows($result) > 0) {
  //username already exists
  $json['userExists'] = true; // we found a user so set to true
}

header('Content-type: application/json'); // send json headers
echo json_encode($json); // dump the json value should look like {'userExists': true}
exit;
?>

---

EDIT:

Ok here you use the variable name user_name:

$.post("user_availability.php",{ user_name:$(this).val() } ,function(data){...});

Yet in your php you are using $_POST['number']. Unless I am missing something you need to use $_POST['user_name'] in the php or use {number: $(this).val()} in the js... They should use the same var name.

---

'SELECT email FROM recipients where email =' . intval($_POST['number']);

IS this query correct? shouldnt it be something more like one of the following:

'SELECT email FROM recipients where id =' . intval($_POST['number']);

OR

"SELECT id FROM recipients where email ='" . mysql_real_escape_string($_POST['email'])."'";

Also for readability sake (which will help you avoid typos) you might want to use sprintf to format your query strings:

$query = sprintf(
  "SELECT id FROM recipients where email ='%s'", 
  mysql_real_escape_string($_POST['email'])
);