How do I get a result from a prepared statement using PHP?

Question

I am able to get the result from a standard SQL query however when it comes to prepared statements I am fine up until it comes to getting the result from the query.

As background the query will result with more than one row.

$sql = "SELECT * FROM blog WHERE ID=?";

if (!$stmt = $con -> prepare($sql)) {
    echo "Prepare failed: (" . $con->errno . ") " . $con->error;
}

if (!$stmt->bind_param("i", $_GET["ID"])) {
    echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
}

if (!$stmt->execute()) {
    echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
}

while($row = $stmt->fetch_assoc()){
    $blog_title = $row['title'];
    $blog_body = $row['body'];
    $blog_blurb = $row['blurb'];
    $blog_date = $row['posted'];
    $blog_tags = $row['tags'];  
}

This results in

Fatal error: Call to undefined method mysqli_stmt::fetch_assoc()

However, I have tried what was outlined in the PHP manual but have not succeeded.

try `var_dump(get_class_methods($stmt))` to see available methods. — Loïc, May 11 '14 at 16:40
funny, but the function is called exactly the same - get_result() — Your Common Sense, May 11 '14 at 17:15
@Loïc when I try that I get `"array(17) { [0]=> string(11) "__construct" [1]=> string(8) "attr_get" [2]=> string(8) "attr_set" [3]=> string(10) "bind_param" [4]=> string(11) "bind_result" [5]=> string(5) "close" [6]=> string(9) "data_seek" [7]=> string(7) "execute" [8]=> string(5) "fetch" [9]=> string(12) "get_warnings" [10]=> string(15) "result_metadata" [11]=> string(8) "num_rows" [12]=> string(14) "send_long_data" [13]=> string(11) "free_result" [14]=> string(5) "reset" [15]=> string(7) "prepare" [16]=> string(12) "store_result" } "` — Daniel Ward, May 12 '14 at 11:50

score 1 · Answer 1 · edited Jan 12 '22 at 20:33

1

Here is better way to do it.

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mydatabase = new mysqli('localhost', 'root', '', 'database');

$id = $_GET['id'];
$stmt = $mydatabase->prepare("SELECT * FROM `blog` where ID = ?");
$stmt->bind_param('i', $id);
$stmt->execute();
$result = $stmt->get_result(); //get the results

while ($row = $result->fetch_assoc()) {
    echo $row['whatever']; //do whatever here
}

If get_result() doesn't exist in your installation, use this:

$stmt->bind_result($column1, $column2);
while ($stmt->fetch()) {
    echo $column1;
    echo $column2;
}

edited Jan 12 '22 at 20:33

Dharman

30,962
25
85
135

answered May 11 '14 at 17:19

Magna

598
3
13
23

With this code I get "Fatal error: Call to undefined method mysqli_stmt::get_result()" on line "//get the results" – Daniel Ward May 12 '14 at 11:47
it works on my system, if it doesn't work on yours then the possible reason could be that MySQL Native Driver (mysqlnd) is not installed on your web server. in that case you can change the code to the edited version. – Magna May 12 '14 at 22:17

How do I get a result from a prepared statement using PHP?

1 Answers1