tweetok tweetok - 3 months ago 10
MySQL Question

PDO - Fetch Assoc in a while

I'm trying to echo a bit of html within a while loop.
I'm getting my stuff with PDO and PDO_ASSOC.

This is what I have:

$stmt = $this->conn->prepare('SELECT * FROM books');
$stmt->execute();
while($row = $stmt->fetch(PDO::FETCH_ASSOC))
{
$book_id = $row['id'];
$book_title = $row['title'];
$book_image = $row['image'];
$book_amz = $row['amazon'];
$book_desc = $row['description'];
$book_rating = $row['rating'];
$book_date = $row['date'];
$book_author = $row['author'];
$book_categorie = $row['categorie'];
$text = "ID: ' . $book_id . '";
}
return $text;


but it gives me only one row of the table. Even tried fetchAll, but it gives me nothing.

Answer

So making the assumption that the only element ever seen is the last element it is because what your are returning is being overwritten each loop. There are a few options to resolve this. The simplest is:

$stmt = $this->conn->prepare('SELECT * FROM books');
$stmt->execute();

$text = "";
while($row = $stmt->fetch(PDO::FETCH_ASSOC))
{
    $book_id = $row['id'];
    $book_title = $row['title'];
    $book_image = $row['image'];
    $book_amz = $row['amazon'];
    $book_desc = $row['description'];
    $book_rating = $row['rating'];
    $book_date = $row['date'];
    $book_author = $row['author'];
    $book_categorie = $row['categorie'];

    //String concatenation of text will 
    //give you one big string at the end to return.   
    $text .= "ID: '{$book_id}'";
}
return $text;

However this will not work well with your real bootstrap html. You need to make sure that the columns add up right.

You will need something a bit more intuitive

Using the actual code it would look something like

$stmt = $this->conn->prepare('SELECT * FROM books');
$stmt->execute();

$bookEcho = array();
while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
    $bookEcho[] = '<div class="col-md-3">
                   <div class="thumbnail">
                   <span>' . $book_title . '</span>
                   <img src="' . $book_image . '">
                   <div class="book-options">
                   <span>Bewertung</span><br/>
                   ' . $stars . '
                   <a href="books.php?id=' . $book_id . '" class="btn btn-read btn-block">Jetzt lesen</a>
                   </div>
                   </div>
                   </div>';
}
return $bookEcho;

Now in your function what ever it is you can do something like (this is not the most elegant thing I have ever written but should get the job done):

$cols = 4;
$colCount = 1;
foreach ($bookEcho as $book){
    if($colCount == 0){//create a row}
    echo $book;
    $coolCount++;
    if($colCount == 0){end a row}
    if($colCount == 4){ $colCount = 0;}
}