How to query MySQL and return value from PHP to JQuery

Question

I am trying to query MySQL to see if a product number exists in the table. I am using the .get() JQuery method, but unable to get a proper return value.

Essentially, I want to to see if 'partNumber' is in the db, and add it to the HTML if so. The way I understand it from various sites is that the parameter of the success function in .get() is where the return value from the PHP is stored, so I would expect 'found' to hold

mysql_query("SELECT Part Number FROM Part List WHERE Part Number =  $part_number");

This has not been the case, 'found' actually holds the entire "widget.php" page. I am not sure if my error lies on the server or client side of things.

jQuery:

$(document).ready(function(){
    var partNumber = buildPartNumber();
    $.get("widget.php", partNumber, function(found) {
        if(found) {
            $('#partNumber').html(partNumber);  
        }
        else {
            $('#partNumber').html("Sorry, we do not carry a product matching these specifications.")
        }
    });
}

PHP:

<?php
   $hostname="hhh";
   $database="ddd";
   $username="uuu";
   $password="ppp";

   $link = mysql_connect($hostname, $username, $password);
   mysql_select_db($database, $link);
   $part_number = $_GET["partNumber"];
   return mysql_query("SELECT Part Number FROM Part List WHERE Part Number =  $part_number");
   mysql_close($link);
?>

Answer 1

To answer the SQL syntax errors, your table and column contain spaces, use backticks around them.

mysql_query("SELECT `Part Number` FROM `Part List` WHERE `Part Number` =  $part_number")

also make sure that $part_number is an int. If is isn't, quote it with '".$part_number."'

Yet, you should be using underscores between the words, as a seperator.

Using

error_reporting(E_ALL);
ini_set('display_errors', 1);

and

or die(mysql_error()) to mysql_query()

would have signaled that error.

---

Edit:

Your present code is open to SQL injection. Use prepared statements, or PDO with prepared statements; those are much better to work with and a much safer.

Plus using a deprecated MySQL API. Don't wait till it's too late to switch over.

Answer 2

Okay, there are a few problems here:

• You have not posted the function buildPartNumber() but if that returns a part number, it's wrong, you need to send key - value pairs like: {'partNumber': your_part_number};
• When you want to return things from php back to your javascript / jQuery, you need to echo them out;
• You have an sql injection problem;
• You need to quote the partNumber if it is a string and not an integer;
• You cannot return the result of the - deprecated - mysql_query to javascript / jQuery. You would need to fetch a row and return information from that row (or the whole row as for example JSON). In your case a truthy value would do;
• As @Fred-ii- correctly pointed out, you need to quote table names with spaces in them with backticks.

Answer 3

Please take note of the other answers as they both give critical information which I have also included in this answer. Credit to Fred -ii- and jeroen. (And sorry I didn't put this in the post before, I wasn't trying to take credit, just give the best answer I could.)

Well a PHP return won't actually return to the jQuery. It would be lovely if it did, but what's really happening is that the jQuery is asking your server for a page, and PHP is making that page. What you should do is return a JSON object with true or false by printing that to the page. Then the jQuery can look for that and react. Like this:

<?php
    $hostname="hhh";
    $database="ddd";
    $username="uuu";
    $password="ppp";

    $link = mysqli($hostname, $username, $password, database);
    $part_number = $_GET["partNumber"];
    $part_number = mysqli_real_escape_string($link, $part_number);
    $result = mysqli_query("SELECT `Part Number` FROM `Part List` WHERE `Part Number` = '$part_number'");
    if(mysqli_num_rows($result) > 0) {
        echo '{"partNumExists": true}';
    }
    else {
        echo '{"partNumExists": false}';
    }
    mysqli_close($link);
?>

You were also using the deprecated mysql, this is the mysqli way and should always be used. I've also added some filtering to help protect you against injection attacks.

Here's the jQuery side of things:

$(document).ready(function(){
    var partNumber = buildPartNumber();
    $.getJSON("widget.php", { "partNumber": partNumber}, function(data) {
        if(data.partNumExists) {
            $('#partNumber').html(partNumber);  
        }
        else {
            $('#partNumber').html("Sorry, we do not carry a product matching these specifications.")
        }
    });
}

EDIT: A small caveat here, there are more steps needed in returning JSON data, namely changing the MIME type of the php page. A JSON object is a notation for storing information, it stands for JavaScript Object Notation. It started out just being used in JavaScript but is now used in all sorts of languages because it's such a good way to store Object Oriented data.