PHP, mySQL, and Smarty template. while loop only displays 1 database entry

Question

i'm using php/mysql with smarty (php template generator). I'm looping through an sql query and getting the data to display on the .tpl file.

$query = "SELECT * from recipes";
    $result = mysqli_query($db_server, $query);
    if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        // assign user information to template
        $tpl->assign('title', $row['title']);
        $tpl->assign('submission_date', $row['submission_date']);
        $tpl->assign('instructions', $row['instructions']);
        $tpl->assign('category', $row['category']);
    }
    } else {
        echo "0 results";
    }

my html:

<div class="content">
    {if $signedin}
        <h4>{$title}<h4>
        <h6>{$submission_date}</h6>
        <p>{$instructions}</p>
        <p>{$category}</p>
    {else}
        You are currently not signed in.
    {/if}

</div>

The problem is that this is only displaying the most recent entry and i'm trying to display every entry in the database.

What's wrong with my loop?

I have placed echo in between each $tpl->assign, and it loops and displays all data, so i'm wondering if this is a Smarty issue.

Answer 1

Just like what I've said in the comments, the reason why you're only getting the last row value is because every iteration inside your loop, the values gets overwritten.

A way that you can do is to create a container, then use your while loop and put them all inside first. After you're done, then ->assign() it inside the template and make your loop presentation and logic and other stuff that you need to do.

Here's the basic idea:

// Backend

$data = array(); // initialize a simple container
$query = "SELECT * from recipes";
$result = mysqli_query($db_server, $query);

if ($result->num_rows > 0) {
    // fetch rows
    while($row = $result->fetch_assoc()) {
        $data[] = $row; // push them inside
    }
}

// assign user information to template
$tpl->assign('values', $data);


// Front end

<div class="content">
    {foreach from=$values key=k item=value}
        <h4>{$value.title}<h4>
        <h6>{$value.submission_date}</h6>
        <p>{$value.instructions}</p>
        <p>{$value.category}</p>
    {/foreach}
</div>