Show Ordered Numerical Numbers of returned MySQL results

Question

I have a web page where I want to display the PHP/MySQL results in a list, but I want to add a number before each title, like:

01 ... Title of the first article

02 ... Title of the second article

And these will be based on the number of results I get from the database. Here is my code to get the list:

ERROR

How do I get it to show 01, 02, 03, etc in place of $post_number?

`$counter = 1;` Declare a starting variable (in this case, 1) and then for each loop through have it `$counter++` Use the counter variable in place of your $post_number. — Fata1Err0r, Sep 09 '16 at 18:51
Put `$post_counter++` outside of the variable string. It isn't HTML markup, it's PHP code. Also, put your `$post_counter = 1;` outside (and before) your loop. — Fata1Err0r, Sep 09 '16 at 19:18

Fata1Err0r · Answer 1 · 2016-09-09T19:11:35.750

2

You'll want to declare a counter variable so we can keep track of each loop through and print it. $counter is the variable I'm going to use in this example, and I declare it BEFORE the loop.

Replace your $post_numbers variable with the $counter variable, and INSIDE your loop, but at the END of it, we'll want to increment the $counter variable by 1. $counter++ will increase after each loop through, so it will keep track of how many records there are.

    $posts_sql = "SELECT id, added, title FROM posts ORDER BY added DESC LIMIT $offset, $post_limit";
    $posts_res = mysqli_query($con, $posts_sql);
    $counter = 1;
    while($post = mysqli_fetch_assoc($posts_res)){

        $post_id = $post["id"];
        $post_added = $post["added"];
        $post_title = ucwords($post["title"]);

        $display_posts .= "

        <b>$counter</b> ... <strong class='resultsLink'>$post_title</strong>";

        $counter++;

    };

^For every loop through, it'll increment your counter variable by one AFTER it posts the current number. Since it will only loop while there are results, that will effectively count for you so you can number your list.

EDIT As suggested by CatalinB:

<b>sprintf("%02d",$counter)</b> ... <strong class=\"resultsLink\">$post_title</strong> to show 01 02 03

This will allow you to have the preceding 0 in front of single-digit numbers.

edited Sep 09 '16 at 19:11

answered Sep 09 '16 at 18:53

Fata1Err0r

836
1
6
14

1

sprintf("%02d",$counter) ... $post_title to show 01 02 03 – CatalinB Sep 09 '16 at 18:58
Thanks, but this just shows the number `1` before both of the results. It is also displaying `1++;` after each listed title – Unhappy Blogger Sep 09 '16 at 19:02
I just noticed that there wasn't an ending " in your $display_posts variable. Try it again. I'm pretty sure the lack of ending quotes was causing it to not increment the $counter variable as it should have since it was forcing it to be part of the string instead. – Fata1Err0r Sep 09 '16 at 19:05
Actually that was an error on my part while copying and editing the code on here. The ending " does exist on my site code – Unhappy Blogger Sep 09 '16 at 19:11
Make sure you declare your `$post_counter = 1;` outside of the loop, and don't put the `$post_counter++` variable inside of the variable string. `$post_counter++;` is equiv to `$post_counter = $post_counter + 1;` – Fata1Err0r Sep 09 '16 at 19:16
Thanks @Fata1Err0r. I had to change `$post_counter = 1` to `$post_counter = 0`, because it was showing the results as `2` and `3`, without the `1`. How can I make it `01`, `02`, `etc`? – Unhappy Blogger Sep 09 '16 at 19:24
Yea, that's why it's very important where you place your variables. You don't want it to increment prior to it executing your code. As for the `01` portion, I've updated that to my post. You're basically going to have it format it prior to displaying, which is what the `sprintf("%02d", $counter)` handles in the string. – Fata1Err0r Sep 09 '16 at 19:26
Ok @Fata1Err0r. If I add my whole code could you show me where to place each variable? – Unhappy Blogger Sep 09 '16 at 19:32
@FootBlogger I'd be happy to. I need to leave work and go home, so give me 40 mins though. http://stackoverflow.com/questions/1699958/formatting-a-number-with-leading-zeros-in-php <-- Good link for preceding zeroes. Also, just make sure your `$variable++` is AFTER the variable that contains the string you display. – Fata1Err0r Sep 09 '16 at 19:33
Thanks @Fata1Err0r. I've managed to get things working correctly so I'm going to leave things as they are now. Thanks again. – Unhappy Blogger Sep 09 '16 at 19:47

score 1 · Answer 2 · answered Sep 09 '16 at 19:00

Use mysqli_fetch_all function to fetch all the record at once and then using foreach loop display all results and use key of $results table to display the numbers of rows.

$posts_sql = "SELECT id, added, title FROM posts ORDER BY added DESC LIMIT $offset, $post_limit";
$posts_res = mysqli_query($con, $posts_sql);

$results = mysqli_fetch_all($posts_res,MYSQLI_ASSOC);

foreach($results as $key=>$post){

    $post_id = $post["id"];
    $post_added = $post["added"];
    $post_title = ucwords($post["title"]);

    $display_posts .= "

    <b>".sprintf("%02d",$key)."</b> ... <strong class=\"resultsLink\">$post_title</strong>
};

Show Ordered Numerical Numbers of returned MySQL results

2 Answers2