How to return database query into ajax?

Question

I want to check if a user has favourited an item but I'm unsure how to return the result of a database query to ajax.

I will show different html depending on the result.

Php

$query = "SELECT itemID from favourites WHERE userid = '" . $user. "'";
$result = mysql_query($query);

echo json_encode($result);

Jquery

$.ajax({
  url: "inc/functions.php",
  type: "POST",
  data: {--result--},
  success: function () {

      // if result found in database 
         $('favourite').hide();

      // if result not found
         $('favourite').show();
      }
});

I can't figure out how to display $result in the jquery code.

Any help much appreciated.

***Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php).*** [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Dec 06 '16 at 17:19
[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)***. Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! — Jay Blanchard, Dec 06 '16 at 17:19
The content of the HTTP response is passed to the `success` handler function, so you would add an argument to that function in order to observe that response. What that response is and how you use it is up to you, really. I don't think `$result` is what you think it is in this case. — David, Dec 06 '16 at 17:20
@JayBlanchard Thanks for info, I updated to mysqli and got it working in the process. — sol, Dec 07 '16 at 16:05

Shikatsu Kagaminara · Answer 1 · 2016-12-06T17:26:17.587

1

$result in this case is a PHP object representing a result. You will have to use a fetch() method in order to extract the result before sending it back to your JS.

See this link. There's a list of all fetch-family method right above the comments.

Also, you will need to make a connection with you database beforehand using mysqli_connect (or mysql_connect in your case).

As stated in the comments, you should however use mysqli* functions family instead of mysql*.

edited Dec 06 '16 at 17:26

answered Dec 06 '16 at 17:21

Shikatsu Kagaminara

351
4
17

Thanks for the info. I will look into fetch() now. – sol Dec 06 '16 at 17:33

score 1 · Accepted Answer · answered Dec 07 '16 at 16:03

Thanks to the comments for info regarding mysqli. I updated the code and solved the ajax part.

For anyone else stuck, I got it working like this:

PHP

require ("../../connection.php");

$sql = "SELECT * FROM favourites WHERE userID = ? AND itemID = ?";
$user = $_POST['userID'];
$item = $_POST['itemID'];

$statement = $db->prepare($sql);
if($statement === false) {
    trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $db->error, E_USER_ERROR);
}

$statement->bind_param('ii',$user,$item);

$statement->execute();

$statement->bind_result($user,$item);

while($statement->fetch()){
    echo 1;
}

$statement->close();

Jquery

$.ajax({
url: "inc/userList.php",
data: userList,
type: "POST",
success: function (result) {

   if (result == 1){
      $('#addItem').css('display', 'none');
      $('#removeItem').css('display', 'inline-block');
    } else {
      $('#addItem').css('display', 'inline-block');
      $('#removeItem').css('display', 'none');
    }    
}
});

How to return database query into ajax?

2 Answers2