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
– D-Dᴙum, Commented Feb 15, 2018 at 21:42
Please stop using mysql_* functions. These extensions have been removed in PHP 7. Learn about prepared statements for PDO and MySQLi and consider using PDO, it's really pretty easy. — Jay Blanchard
– Jay Blanchard, Commented Feb 15, 2018 at 21:48
Update your code to use mysqli or PDO. Where is the actual query? — ryantxr
– ryantxr, Commented Feb 15, 2018 at 21:52

Barmar · Accepted Answer · 2018-02-16 21:21:47Z

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, 2018 at 21:21

answered Feb 15, 2018 at 22:07

Barmar

789k57 gold badges554 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Polonio Over a year ago

Thank you Barmar for your response.

Polonio Over a year ago

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?

Barmar Over a year ago

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 Over a year ago

BTW, you could use DAY(res_dateini) in the query so you don't need to use strtotime() in PHP.

Polonio Over a year ago

Yes, when I run var_dump($results) I can see both of them.

|

Collectives™ on Stack Overflow

Comparing values from a database using a while loop

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related