Comparing values from a database using a while loop

Question

I have a MySql table where I saved all workers names and the dates workers have to work on. I want to show a list containg all days of the current month and the worker names who have to work on the day that corresponds to them. Example:

February

1
2
3 - John Wick
5
6 - Martha Beck

etc.

This is the code I have in PHP but the loop is not working. I just get a list from 1 to 30 but it is not showing the data from database. If I run the loop without the (while ($n < 31)), I get all the records from database but I want to show the names just beside the day that correspond.

<?php 

mysql_select_db($database_nineras, $nineras);
$query_res = sprintf("SELECT res_id, res_dateini, res_datefin, res_name  FROM reservas ORDER BY  res_dateini DESC");
$reservas = mysql_query($query_res, $nineras) or die(mysql_error());
$rreser = mysql_fetch_assoc($reservas);
$treser = mysql_num_rows($reservas);

$n = 1;

while ($n < 31)  {
    do {
        ++$n;
        if ($n == date('d', strtotime($rreser['res_dateini']))) {
            echo $n . ' - ' . $rreser['res_name'];
        }
        else {
            echo $n;
        }
    } while ($rreser = mysql_fetch_assoc($reservas));
}
?>

You need to post your code if people are to help in anyway. what have you tried so far? — D-Dᴙum, Feb 15 '18 at 21:42
***Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php).*** [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Feb 15 '18 at 21:48
Update your code to use mysqli or PDO. Where is the actual query? — ryantxr, Feb 15 '18 at 21:52

Barmar · Accepted Answer · 2018-02-16T21:21:47.467

1

The problem with your code is that the do-while loop is fetching all the rows returned by the query. So when you get to the second iteration of the while loop there's nothing left to fetch.

Rather than fetch the rows from the database each time through the loop, you can fetch them once and put them into an array whose index is the day numbers. Then you can loop through the days and print all the rows for each day.

Use date('j', ...) to get the date without a leading zero. Or change your SQL query to return DAY(res_dateini).

$results = array();
$reservas = mysql_query($query_res, $nineras) or die(mysql_error());
while ($rreser = mysql_fetch_assoc($reservas)) {
    $d = date('j', strtotime($rreser['res_dateini'])));
    $results[$d][] = $rreser['res_name'];
}
for ($day = 1; $day <= 31; $day++) {
    echo "$day - " . (isset($results[$day]) ? implode(", ", $results[$day]) : "") . "<br>\n";
}

DEMO

edited Feb 16 '18 at 21:21

answered Feb 15 '18 at 22:07

Barmar

741,623
53
500
612

Thank you Barmar for your response. – Polonio Feb 16 '18 at 14:23
This is the right solution, but I am still having problems with this code to display all the names corresponding to a day. If I have two dates in my database (2018-02-08, 2018-02-10), I only can see the records (names) for date 2018-02-10. I was trying to figure out how to solve this doing changes to your code but I could not. Do you have any idea how could I solve this? – Polonio Feb 16 '18 at 14:32
I'm not sure why it doesn't work. Do you see both of them if you do `var_dump($results)` after the first loop? – Barmar Feb 16 '18 at 16:35
BTW, you could use `DAY(res_dateini)` in the query so you don't need to use `strtotime()` in PHP. – Barmar Feb 16 '18 at 16:36
Yes, when I run **var_dump($results)** I can see both of them. – Polonio Feb 16 '18 at 20:59
When I did the **var_dump($results)** I could see that days with one digit were represented with a 0 in front. This means that where I had in my database (2018-02-08) I got day (08). When this was related with 8 in the array loop it showed nothing. The problem was in using **date('d', strtotime($rreser['res_dateini'])))** I changed it to **date('j', strtotime($rreser['res_dateini'])))** instead to get days with only one digit. Now it is working very good. Thank you very much **Barmar** for your help. – Polonio Feb 16 '18 at 21:20

Comparing values from a database using a while loop

1 Answers1