2

I want to create a php script using prepared statements to query a table in my database and return the results in json format. I have a table of doctors and i want to return the doctors of a given speciality. I have a version of the script that doesn't use prepared statements that works fine. But when i use prepared statements my script doesn't work.

Non - prepared statements version:

<?php
// include database constants
require_once("../config/config.php");             

// create db connection
$mysqli = new mysqli($DB_HOST, $DB_USER, $DB_PASS, $DB_NAME);

/* check connection */
if ($mysqli->connect_errno) {
    printf("Connect failed: %s\n", $mysqli->connect_error);
    exit();
}

$mysqli->set_charset("utf8");

$speciality = $_POST['speciality'];

$query = "SELECT * FROM `doctors` WHERE speciality='$speciality'";
$result = $mysqli->query($query) or die("Error executing the query");
while($row = $result->fetch_assoc()) {
    $output[]= $row;    
}
print(json_encode($output));

$mysqli->close();
?>

prepared statements version:

<?php
// include database constants
require_once("../config/config.php");             

// create db connection
$mysqli = new mysqli($DB_HOST, $DB_USER, $DB_PASS, $DB_NAME);

/* check connection */
if ($mysqli->connect_errno) {
    printf("Connect failed: %s\n", $mysqli->connect_error);
    exit();
}

$mysqli->set_charset("utf8");

$speciality = $_POST['speciality'];

$query = "SELECT * FROM `doctors` WHERE speciality=?";
if ($stmt = $mysqli -> prepare($query)){
    $stmt -> bind_param("s", $speciality);
    $stmt -> execute();
    $result = $stmt -> get_result();

    while($row = $result -> fetch_assoc()) {
        $output[]= $row;    
    }

    print(json_encode($output));
    $stmt -> close();

} else {
    echo $mysqli->error;
    echo "no entry found";
}

$mysqli->close();
?>

What am i doing wrong? I don't get a mysqli error which means that the problem is after the execution of the query but i just don't know what it is.

Edit: What i mean by saying it doens't work is that i don't get anything back. The html body of the page after the execution is completely empty. On the other hand if i use the other script i posted (without prepared statements) i get the expected result.

Christos Baziotis
  • 5,227
  • 15
  • 54
  • 78

4 Answers4

8

UPDATED:

Use this:

/* bind result variables */
$stmt->bind_result($col1,$col2,$col3,$col4);

/* fetch values */
while ($stmt->fetch()) {
    $output[]=array($col1,$col2,$col3,$col4);
}

Instead. Hope it helps.

anyone please give reason of putting downvote.

ncm
  • 1,765
  • 2
  • 19
  • 36
  • your welcome.(-: you know. I don't know why other gave me down vote without reason.(-: – ncm Oct 30 '13 at 18:10
2
ini_set('display_errors',1);
error_reporting(E_ALL);

and then look at HTML body again. Most likely get_result is not supported but I hate to guess.

Your Common Sense
  • 152,517
  • 33
  • 193
  • 313
  • Here is the result: Fatal error: Call to undefined method mysqli_stmt::get_result(). So what i do know? – Christos Baziotis Oct 30 '13 at 17:41
  • +1 a good answer. You should set error reporting `E_ALL` when you want to debug your codes. – ncm Oct 30 '13 at 17:42
  • @koukouloforos - so why you are not using `use_result` instead? please check my answer. – ncm Oct 30 '13 at 17:43
  • I get the same error with use_result. Check my commend to your answer. – Christos Baziotis Oct 30 '13 at 17:44
  • @imsiso you rather should have it ALWAYS – Your Common Sense Oct 30 '13 at 17:46
  • What you mean always? (I know you are right) but I was with the OP and I meant you should not let your errors be displayed in production mode. But I think I have a misunderstanding in what error_reporting is doing. – ncm Oct 30 '13 at 17:55
  • @imsiso as you can see, there is another setting responsible for fdisplaying – Your Common Sense Oct 30 '13 at 18:01
  • @YourCommonSense - what do you think about my answer is it wrong? – ncm Oct 30 '13 at 18:02
  • @imsiso i think its wrong to delete your answer and write another – Your Common Sense Oct 30 '13 at 18:02
  • @YourCommonSense - I know but what should I do then? I have posted an answer and some one had put a downvote with no reason. What should I do? I had posted and answer which had been accepted but 2 person had give it downvote without any reason. what should I do in such situations? thanks. – ncm Oct 30 '13 at 18:08
  • @imsiso just stick to your first answer. You can edit it freely, while deleting and writing it back would be surely considered as cheating. And also try to refrain from posing absolutely pointless answers, like your first one. – Your Common Sense Oct 30 '13 at 18:12
  • @YourCommonSense - again you are right I will undelete them now. The first one was not a good answer but please take a look at the second and third answer which are correct and some one had put -1.(now the new answer (accepted one) has 0=1-1) You are so experianced. What should I do this has happend to me for a complete correct and accepted answer. what's going on? - again thanks. – ncm Oct 30 '13 at 18:18
  • @YourCommonSense - did you see my last comment? – ncm Oct 30 '13 at 18:26
  • @imsiso it's the very cheating with deleting answers I told you before. Anyway, you are too much concerned in such trifle things. Just let it go. – Your Common Sense Oct 30 '13 at 18:33
0

Make sure your version of PHP is compatible with the method

http://php.net/manual/pt_BR/mysqli-stmt.get-result.php

Acácio Veit Schneider
  • 122
  • 5
0

To get data as associative array you can do as follow:

$stmt->bind_result($col1, $col2);
$rows = [];
while ($stmt->fetch()) {
    $rows[]=array("col1"=>$col1, "col2"=>$col2);
}
Hari Das
  • 7,849
  • 6
  • 46
  • 53