CODEWITHSUNDEEP
phppdomysqli
Stephenmelb
Stephenmelb

Reputation: 457

Converting to PDO prepared statements from mysqli

I have the following bit of code, that selects a table, loops through all the values which match an actor and displays the reviews for that actor.

include("app/config/db.php");
$aid=$actor['id'];

$query = "SELECT * FROM `reviews` WHERE `actor_id` = $aid";

$result = $mysqli->query($query);

$num_results = $result->num_rows;

if( $num_results ){

//loop to show each records

while( $row = $result->fetch_assoc() ){

extract($row);

echo "<span class=\"review-score\">";
echo $row['score']*10;
echo "</span>";
echo " by <strong>";
echo $row['author'];
echo "</strong>";
echo " on <strong>";
echo $row['created_at'];
echo "</strong>";
echo "<p class=\"review-body\">";
echo $row['body'];
echo "</p>";
echo "</br>";


}


}else{

echo "No records found.";

}

$result->free();

$mysqli->close();

I want to convert to using PDO prepared statements. This is what I've tried. It doesn't display any of the reviews assigned to the actor. Please help

include("app/config/db.php");
$aid=$actor['id'];


$st = DBase::singleton()
        ->prepare(
            'select * ' .
            'from `reviews` ' .
            'where `actor_id` like :aid ' .
            'limit 0,10');

$st->bindParam(':aid', $aid, PDO::PARAM_STR);

if ($st->execute())
{
       while ($row = $st->fetch(PDO::FETCH_OBJ))
        {

       echo "<span class=\"review-score\">";
echo $st['score']*10;
echo "</span>";
echo " by <strong>";
echo $st['author'];
echo "</strong>";
echo " on <strong>";
echo $st['created_at'];
echo "</strong>";
echo "<p class=\"review-body\">";
echo $st['body'];
echo "</p>";
echo "</br>";


}
}

DBase:singleton

  static public function singleton()
    {
        if (!is_object(self::$pdo))
        {
            self::$pdo = new PDO('mysql:dbname=' . self::DATABASE . ';host=' . self::HOST, 
                                    self::USERNAME, 
                                    self::PASSWORD);
        }
        return self::$pdo;
    }

Upvotes: 1

Views: 348

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562260

You're storing each row's result in $row, but not accessing $row when you want to display the data.

while ($row = $st->fetch(PDO::FETCH_OBJ))
{
    ...
    echo $st['score']*10;

The echo line should be:

    echo $row->score*10;

You're also fetching objects but accessing the data as an array.

thats how I was taught to write a while loop.. how else could I do it?

My point is that if assign a variable with successive values of the row, then the data is in that variable, not in the object that iterates over rows. What you've done is analogous to this:

for ($i = 0; $i < $count; $i++) {
    echo $count;
}

Whereas in that example one should do this instead to get successive values in the loop:

for ($i = 0; $i < $count; $i++) {
    echo $i;
}

Upvotes: 3

Related Questions