Getting multiple rows with prepared statement

Question

I'm quite new to prepared statements and am not sure I am doing this right.

Here is what I try:

$currgame = 310791;

$sql = "SELECT fk_player_id, player_tiles, player_draws, player_turn, player_passes, swapped FROM ".$prefix."_gameplayer WHERE fk_game_id = ?";
$stmt = $mysqli->stmt_init();

$data = array();
if($stmt->prepare($sql)){
    $stmt->bind_param('i', $currgame);
    $stmt->execute();

    $fk_player_id = null; $player_tiles = null; $player_draws = null; $player_turn = null; $player_passes = null; $swapped = null;
    $stmt->bind_result($fk_player_id, $player_tiles, $player_draws, $player_turn, $player_passes, $swapped);

    $res = $stmt->get_result();
    
    while ($row = $res->fetch_assoc()){
        $data[] = $row;
    }
    $stmt->close(); 
}

// to display own games
foreach ($data as $row) {
    if ($row['fk_player_id'] == $playerid) {
        
        $udraws = $row['player_draws']+1; 
        $upass = $row['player_passes'];
        $uswaps = $row['swapped'];
        
        echo 'uDraws: '.$udraws.'<br>';
        echo 'uPass: '.$upass.'<br>';
        echo 'uSwaps: '.$uswaps.'<br><br>';
    }
}
// to display other games
foreach ($data as $row) {
    if ($row['fk_player_id'] != $playerid) {
        
        $opponent = $row['fk_player_id'];
        $oppTiles = $row['player_tiles'];
        
        $odraws = $row['player_draws']+1;
        $opass = $row['player_passes'];
        $oswaps = $row['swapped'];
        
        echo 'oID: '.$opponent.'<br>';
        echo 'oTiles: '.$oppTiles.'<br>';
        
        echo 'oDraws: '.$odraws.'<br>';
        echo 'oPass: '.$opass.'<br>';
        echo 'oSwaps: '.$oswaps.'<br><br>';

    }
}

I get an "ServerError" when trying to run this: It is the $res = $stmt->get_result(); that makes the error, but not sure why.

PHP Fatal error: Call to undefined method mysqli_stmt::get_result() in /home/mypage/public_html/TEST/preparedstatement.php on line 61

Please elaborate on "ServerError". Any indication where the error comes from (MySQL, Apache, PHP)? Any unusual HTTP Status code? What's the full error message? — Oswald, Feb 03 '13 at 10:42

EM-Creations · Accepted Answer · 2013-02-03T12:30:49.883

14

Depending on your PHP/MySQL setup you may not be able to use get_result().

The way to get around this is to bind the results.

For example:

$stmt->execute();

$fk_player_id = null; $player_tiles = null; $player_draws = null; $player_turn = null; $player_passes = null; $swapped = null;

$stmt->bind_result($fk_player_id, $player_tiles, $player_draws, $player_turn, $player_passes, $swapped);

while ($stmt->fetch()) { // For each row
    /* You can then use the variables declared above, which will have the 
    new values from the query every time $stmt->execute() is ran.*/
}

For more information click here

edited Feb 03 '13 at 12:30

answered Feb 03 '13 at 10:47

EM-Creations

4,195
4
40
56

Tried this (see edit in original post) but I still get the error :(HTTP-error 500 (Internal Server Error)). If I exit just before the "$res = $stmt->get_result();" the page loads fine? Don't know what is wrong? – Mansa Feb 03 '13 at 11:03
Nope... Don't have access :-/ – Mansa Feb 03 '13 at 11:19
@Mansa Could you ask whoever your host is to take a look? – EM-Creations Feb 03 '13 at 11:22
Will do. Returning with an answer soon. – Mansa Feb 03 '13 at 11:44
And here is what i says: PHP Fatal error: Call to undefined method mysqli_stmt::get_result() in /home/mypage/public_html/TEST/preparedstatement.php on line 61 – Mansa Feb 03 '13 at 11:59
@Mansa That means `get_result()` isn't enabled for you due to your PHP config. As I explained in my answer, you'll need to use `bind_result()`. – EM-Creations Feb 03 '13 at 12:02
OK, so instead of: $res = $stmt->get_result(); I should do this: $res = $stmt->bind_result($fk_player_id, $player_tiles, $player_draws, $player_turn, $player_passes, $swapped); – Mansa Feb 03 '13 at 12:03
Well, made the error go away and thanks... But I dont get anything in my array (See edited post)!? – Mansa Feb 03 '13 at 12:08
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/23854/discussion-between-em-creations-and-mansa) – EM-Creations Feb 03 '13 at 12:16
`$stmt->fetch()` doesn't return the array, it just fetches the next row's variables and puts them into the bound result variables, so you can use `while ($stmt->fetch()) { // Here you can use the bound result variables }`. – EM-Creations Feb 03 '13 at 12:29

score -2 · Answer 2 · answered Feb 03 '13 at 10:42

Since I don't see it in your code, make sure you're instantiating the mysqli object before trying to query on it:

$mysqli = new mysqli("127.0.0.1", "user", "password", "mydb"); 
if($mysqli->connect_error){
    die("$mysqli->connect_errno: $mysqli->connect_error");
}

Also, a ServerError would certainly show up in your logs and point you in the right direction.

score -2 · Answer 3 · answered Jul 03 '13 at 18:22

-2

while (mysqli_stmt_fetch($stmt)) {
        printf ("%s (%s)\n", $name, $code);
    }

This might help you:

http://php.net/manual/en/mysqli-stmt.fetch.php

answered Jul 03 '13 at 18:22

user2534211

9
3

1

Please offer an explanation for your code so that it's clearer what it's doing and how it helps the asker resolve their problem. – Bobulous Jul 03 '13 at 18:43

Getting multiple rows with prepared statement

3 Answers3

Linked