SELECT * from SQL table using prepared statement

php mysql mysqli prepared-statement

15,166

Solution 1

Nothing wrong with Darwin's answer, but wanted to point out PDO as an alternative with much lighter syntax:

<?php
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];
$link = new PDO("mysql:host=$host;dbname=$db", $username, $password, $options);
$stmt = $link->prepare("SELECT * from `wp_posts` WHERE ID=?");
$stmt->execute([$pid]);
$result = $stmt->fetchAll();

// Now you have a plain array to work with, database work is over
foreach ($result as $row):
?>

<h2 style="text-align:center;margin:0 auto">
    <?=$row["post_title"]?>
</h2>
<br/>
<div class="paracenter">
    <p id="cont">
        <?=$row["post_content"]?>
    </p>
    <hr style="color:black;width:10%"/>
</div>

<?php endforeach;?>

No need for any binding at all, and personally I find it much easier to work with.

Solution 2

Dunno if anyone will be interested in the proper answer for this already answered and accepted question, but what the heck.

To answer your question using mysqli, you have to use get_result()

So, the proper mysqli-based solution will be

$query = "SELECT * from `wp_posts` WHERE ID=? ";
$stmt = $mysqli->prepare($query);
$stmt->bind_param("i", $pid);
$stmt->execute();
$res = $stmt->get_result();
$data = $res->fetch_all(MYSQLI_ASSOC);

(the full explanation for this code can be found in my article, Mysqli SELECT query with prepared statements)

and then you can use $data in the foreach loop for the output as it showed in the other answer.

Solution 3

You don't use bind_result() with fetch_array(). You either make repeated calls to fetch() to read the columns into the individual variables bound with bind_result() OR don't use bind_result(), call mysqli_smt_get_result() to pull the results into a mysqli_result object, and make repeated calls to mysqli_fetch_array() to load the row into your $row array.

Since you're using SELECT *, the unbound-result approach would be more logical. For your code:

$link = mysqli_connect($host, $username, $password, $db);
$query = "SELECT * FROM wp_posts WHERE ID = ? ";
$stmt = mysqli_prepare($link, $query)
    or die("Unable to prepare statement: " . $link->error);

mysqli_stmt_bind_param($stmt, "i", $pid);
mysqli_stmt_execute($stmt)
    or die("Unable to execute query: " . $stmt->error);

$rslt = mysqli_stmt_get_result($stmt);

while($row = mysqli_fetch_array($rslt))
{
    ?>
<h2 align="center"> <?php echo $row['post_title']; ?> </h2><br>
<div class="paracenter">

    <p id="cont"><?php echo $row['post_content']; ?></p>
    <hr color="black" width="10%">

</div>
<?php } ?>

Just for comparison, here's how you would use bind_result() (and also how to use the object syntax):

$link = new mysqli($host, $username, $password, $db);
$query = "SELECT post_title, post_content FROM wp_posts WHERE ID = ? ";

$stmt = $link->prepare($query);
    or die("Unable to prepare statement: " . $link->error);
$stmt->bind_param("i", $pid);
$stmt->execute()
    or die("Unable to execute query: " . $stmt->error);
$stmt->bind_result($postTitle, $postContent)
    or die("Unable to bind result: " . $stmt->error);

while($stmt->fetch()){
    ?>
<h2 align="center"> <?php echo $postTitle; ?> </h2><br>
<div class="paracenter">

    <p id="cont"><?php echo $postContent; ?></p>
    <hr color="black" width="10%">

</div>
<?php } ?>

Note that when using bind_result() your result values are returned as individual scalars rather than in an array, and that you need to bind the result variables to the columns in order, so you need to know what columns are in the result.

Hope that helps.

15,166

Author by

Yohan Blake

Beginner at coding but ready to learn :)

Updated on June 14, 2022

Comments

Yohan Blake almost 2 years

I'm using a prepared statement to SELECT * from a MySQL table and I'm not sure how to use while($row = mysqli_fetch_array($stmt)) to loop through and select items from the result array. This is my code, what am I doing wrong?

    $link = mysqli_connect($host, $username, $password, $db);
    $query = "SELECT * from `wp_posts` WHERE ID=? ";
    //$result = mysqli_query($link, $query);
    $stmt = mysqli_prepare($link, $query);
    if($stmt){
        mysqli_stmt_bind_param($stmt, "i", $pid);
        mysqli_stmt_bind_result($stmt, $dbpid);
        mysqli_stmt_execute($stmt);
        mysqli_stmt_fetch($stmt);
    }
    while($row = mysqli_fetch_array($stmt)){
        ?>
    <h2 align="center"> <?php echo $row['post_title']; ?> </h2><br>
    <div class="paracenter">

        <p id="cont"><?php echo $row['post_content']; ?></p>
        <hr color="black" width="10%">

    </div>
    <?php } ?>

Yohan Blake about 8 years

idk why, when I use the first set of code, the page loads without the data from the db. When I use the second set of code, the page doesn't load
Kevin about 8 years

just slip a note inside get_result with mysqlnd, some folks have and some don't have it on their env
Darwin von Corax about 8 years

First set had a typo which I've corrected, and (as it turns out) so did the second. Try it again.
Darwin von Corax about 8 years

@Ghost Good point, the docs for get_result don't mention that.
Darwin von Corax about 8 years

Do you see any error messages, either in the browser or in the server's error.log file?
Yohan Blake about 8 years

I corrected $rslt = mysqli_statement_get_result($stmt); to $rslt = mysqli_stmt_get_result($stmt);. but still nothing
Yohan Blake about 8 years

on the docs it says while ($row = mysqli_fetch_array($result, MYSQLI_NUM)). added the MYSQLI_NUM too. still nothing
Yohan Blake about 8 years

Let us continue this discussion in chat.
Darwin von Corax about 8 years

@Ghost You were right about the order. I must have misinterpreted the docs. Maybe it just means "before you fetch().
Darwin von Corax about 8 years

Another advantage of PDO prepared statements is that you can name your placeholders, as ... WHERE id = :pid. You then pass execute an associative array of arguments, as execute(array(':pid' => $pid)).
Your Common Sense about 8 years

Please do not catch errors to report them.. Besides, it will make syntax even more lighter.
miken32 about 8 years

@YourCommonSense this is a simple proof of concept. I'm not going to write a global error handling routine to answer a basic question.
miken32 about 8 years

@YourCommonSense like I said, I've got an open mind so it's no bother. If I'm actually doing something wrong I'd like to know, I just need more than someone saying, "that's wrong." An uncaught exception will result in a fatal error, which results in a blank page, does it not?
Your Common Sense about 8 years

Of course not. Not necessarily. On a dev server with display_errors=on the error will be shown on screen. While on a live server whatever blank screens are just not a concern of a humble database wrapper. Neither should it be a concern of "a simple proof of concept."
miken32 about 8 years

@YourCommonSense Your point is taken about unnecessarily complicating sample code. By saying "blank screens are just not a concern of a humble database wrapper" you mean the exceptions should be caught, just higher up than the DB code? Obviously showing a blank page to a user on a live site is not an option.
Your Common Sense about 8 years

Still a blank page is not necessary. If you tell your web-server to show something in case of 5xx error returned from PHP (ErrorDocument in Apache for example), this something will be shown. And I consider this method the most preferred as it will handle even parse errors. But you are right, the most common way is setting up an error handler. But by catching the error too early you are making this handler useless. So, either way using try-catch around every query makes very little sense.
miken32 about 8 years

@YourCommonSense that makes sense; I'll keep it in mind for the future. Thanks!