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

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.

Answer 2

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.