Yohan Blake Yohan Blake - 7 months ago 27
SQL Question

SELECT * from SQL table using prepared statement

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

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

<?php
try {
    $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]);
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
    echo "Database error: " . $e->getMessage();
}

// 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.

Comments