CODEWITHSUNDEEP
phphtmlmysqli
Zuenie
Zuenie

Reputation: 973

Select the 5 latest records in a table with PHP and fill an ordered list with that in HTML

I am trying to learn how to fill an ordered list in a html page with the 5 records in a table with the latest timestamp using php. Most components I have figured out (struggling a bit with php) but that will be fine eventually. The challenge for me is mostly to let it talk to each other in a right way. As soon as someone visits my webpage the top 5 list should be filled. So the php should go off at the moment someone visits my page. Does that mean I just place it on top of the index page? I prefer to have it seperate in another php file and store the rows as session variables en take them back in at the index page.

The html list:

<?php
$connection=mysqli_connect("...", "...", "...", "...")
                or die ("kan niet connecten met database");

$top_5_overview = mysqli_query($connection, "SELECT * FROM search_table ORDER BY upgedate DESC LIMIT 5")
                    or die ("kan query1 niet uitvoeren");

$top_5 = mysqli_fetch_array($top_5_overview);

?>

<!DOCTYPE html>
<html>
<head>  
</head>
<body>
<ol>
<?php
foreach ($top_5 as $t){
    echo '<li> <a href="'.$t['url'].'" target='_blank'>'.$t['atlasnaam'].'</a></li>';
}
?>
</ol>
</body>
</html>

Upvotes: 0

Views: 68

Answers (2)

Raul Sauco
Raul Sauco

Reputation: 2705

There are a few things wrong in the code sample that you sent.

  1. The SQL should have DESC after the column name, not before it.
  2. The call to mysqli_fetch_array() returns only one row of the results. You can use a while loop to iterate through the 5 rows that you are expecting. The while loop also protects against empty results, if no rows are returned, the code inside the loop never gets called.
  3. Your foreach won't work as expected since you are passing one row as the array, it tries to iterate through the values on the array.
  4. There is a problem in the way you mix single and double quotes, ' and ", in your string, in concrete "target='_blank'> it makes _blank not part of the string, and so part of the "code".

This code example works for me:

<?php
$connection=mysqli_connect("...", "...", "...", "...")
    or die ("kan niet connecten met database");

$result = mysqli_query($connection, 
    "SELECT * FROM search_table ORDER BY upgedate DESC LIMIT 5")
    or die ("kan query1 niet uitvoeren");
?>

<!DOCTYPE html>
<html>
    <head>  
    </head>
    <body>
        <ol>
        <?php
        while ($row = mysqli_fetch_assoc($result)) {
            echo '<li><a href="' . 
                $row['url'] . '" target="_blank">' . 
                $row['atlasnaam'] . '</a></li>';
        } ?>
        </ol>
    </body>
</html>

Upvotes: 1

Athene noctua
Athene noctua

Reputation: 182

top_5_overview lacks $ in the beginning, it should be $top_5_overview.

And you don't bind the result of that query to your $top_5 -array. Instead in your code there is mysqli_fetch_array($query1)

Try this:

$top_5_overview = mysqli_query($connection, "SELECT * FROM search_table 
ORDER BY upgedate DESC LIMIT 5")
                or die ("kan query1 niet uitvoeren");
$top_5 = mysqli_fetch_array($top_5_overview);

Upvotes: 1

Related Questions