SELECT * from SQL table using prepared statement

Question

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 } ?>

Answer 1

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

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = mysqli_connect($host, $username, $password, $db);
$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();

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

While for PDO the proper code would be as follows, and it's indeed lighter.

$link = new PDO("mysql:host=$host;dbname=$db", $username, $password);
$link->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $link->prepare("SELECT * from `wp_posts` WHERE ID=?");
$stmt->execute([$pid]);

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

Answer 2

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.

Answer 3

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.