Problem comparing dates, but not getting results.

[TapeGun007]

In mySQL, I have a date field rc_date and it's formatted Y-m-d.

I'm not sure if I have done this right...and I've read a ton of examples but here is the code:

$sqlDate = $numYear."/".$numMonth."/".$i;
	$temp = date_create($sqlDate);
	$sqlDate = date_format($temp, "Y-m-d");
	echo $sqlDate;

	$sql = "SELECT * FROM RepairCalendar WHERE DATE(rc_date) = '".$sqlDate."'";
	$stmt 	= $pdo->prepare($sql);
    $stmt->execute();

This is by no means optimized code.

When I output echo $row['rc_date']; there is nothing.   In the $sql statement, I tried just putting rc_date = $sqlDate.  I tried using date_format in comparing the two dates as well which nothing seems to work.

Whatever the case, I'm stumped as to what I'm doing wrong after reading so many examples.

I had the echo statement just to ensure that the $sqlDate coming out correctly.

[Barand]

$sqlDate appears to have the correct format (more by good luck than design) so the question is "What format are the dates in the RepairCalendar table?"

And that is not how you use prepared queries.

    $sql = "SELECT * FROM RepairCalendar WHERE DATE(rc_date) = ?";
    $stmt 	= $pdo->prepare($sql);
    $stmt->execute( [ $sqlDate ] );

The whole point is not to put values directly into the SQL string.

[mac_gyver]

33 minutes ago, TapeGun007 said:

echo $row['rc_date'];

you would be getting at least a php error if the query didn't match any data. can you echo anything, just as a test, after the posted code? what is the rest of the code up to the point where you are tyring to echo $row['rc_date']?

do you have php's error_reporting set to E_ALL and display_errors set to ON, preferably in the php.ini on your system, so that php would help you by reporting and displaying all the errors it detects?

do you have any error handling for the database statements that can fail - connection, query, prepare, and execute? the simplest way of adding error handling, without adding code at each of these statements is to set the PDO error mode to exceptions when you make the database connection, then simply let php catch and handle the exception, where php will use its error related settings to control what happens with the actual error information (database statement errors will 'automatically' get displayed/logged the same as php errors.)

38 minutes ago, TapeGun007 said:

$i

something tells me you are running this code inside of a loop, which could be the cause of the apparent incorrect operation, depending on how you are fetching the data and where you tried to echo it. you should instead query for all the data you want in a single query.

[TapeGun007]

@Barand, yes I know it's horrible code.  I didn't write it.  I was just trying to get it functional until I could get back to it and rewrite the entire page.

@mac_gyver, yes it's in a loop.  It's a calendar generated with HTML <table>'s.  The echo was just to see what the db was outputting.  As the calendar would come up to a specific date like today, it would bring up whatever was stored in the db called RepairCalendar for that date.  There are deprecated HTML attributes being used in the code, so I have to correct all of that as well. Very fun.  I'll probably just rewrite the page from the ground up. 

 

[mac_gyver]

technically, the posted code will work. that means there's something outside of the posted code that's preventing it from working. since you haven't done anything that was suggested or showed any of the requested code, i'm not sure what help we can give you.

[TapeGun007]

Oh I did exactly what was posted.  I've just been super busy at work.  I was contemplating just rewriting the entire page, but I just cannot find the time at the moment.  Here is the entire piece of code:

Quote

if(0 != $dayOfWeek) { echo('<td colspan="'.$dayOfWeek.'"> </td>'); }

for($i=1;$i<=$daysInMonth;$i++) {

    if($i == $numDay) { echo('<td id="today" bgcolor="#cccccc" valign="top">'); } else { echo("<td valign='top'>"); }

    echo "<a href='calendarday.php?day=".$numMonth.$i.$numYear."'>".$i."</a>";

    echo "<br>";

    $sqlDate = $numYear."/".$numMonth."/".$i;

    $temp = date_create($sqlDate);

    $sqlDate = date_format($temp, "Y-m-d");

   

   

    $sql = "SELECT * FROM RepairCalendar WHERE DATE(rc_date) = ?";

    $stmt   = $pdo->prepare($sql);

    $stmt->execute( [ $sqlDate ] );

    while ($row = $stmt->fetch());

    {

        echo $row['rc_date'];  // Echos nothing

}

    echo("</td>");

    if(date('w', mktime(0,0,0,$numMonth, $i, $numYear)) == 6) {

        echo("</tr><tr>");

    }

}

The rc_date is a Date field in the mySQL database.  I used to have a well written calendar page, but... *sigh*... I cannot find it anywhere.

[Barand]

43 minutes ago, TapeGun007 said:

while ($row = $stmt->fetch());

Remove the ";" from the end of that line. It is saying "while(...) do nothing"

PS

• If you only expect a single row for a date, use "if()" instead of "while()".
• Prepare your statement once before the loop - execute within the loop with new value

[TapeGun007]

That did the trick.  Thank you kindly as always.  Yeah, the while is correct as there can be multiple entries in a day.  

[mac_gyver]

when you re-write this, before the for() loop you have now, execute one query to get all the data matching the $numMonth value, then index/pivot this data using the rc_date column when you fetch it. PDO has a fetch mode, PDO::FETCH_GROUP, that is used with the fetchAll() method, that will do this for you, if the first column you SELECT is the rc_date column.

then, as you are looping over the dates in the month, at the point where you have the $sqlDate value, just test (use isset()) and get the matching sub-array of rows in the fetched data and loop over them to produce the output.

[Barand]

Here's my version of a calendar script. It uses PDO::FETCH_GROUP as @mac_gyver suggested. I am using it to group by week number to make it easy to start a new row for each week.

My test data is a schedule of fictional adult evening classes in English, pottery, dog grooming and origami. It also makes use of my "digit" table to generate dates.

+----+------------+------------+                              mysql> select num from digit;
| id | coursecode | coursedate |                              +------+
+----+------------+------------+                              | num  |
| 21 | ENG        | 2022-05-06 |                              +------+
| 22 | ENG        | 2022-05-13 |                              |    0 |
| 23 | ENG        | 2022-05-27 |                              |    1 |
| 24 | ENG        | 2022-06-03 |                              |    2 |
| 25 | ENG        | 2022-06-10 |                              |    3 |
| 26 | DOG        | 2022-05-02 |                              |    4 |
| 27 | DOG        | 2022-05-09 |                              |    5 |
| 28 | DOG        | 2022-05-16 |                              |    6 |
| 29 | DOG        | 2022-05-23 |                              |    7 |
| 30 | DOG        | 2022-05-30 |                              |    8 |
| 31 | POT        | 2022-05-03 |                              |    9 |
| 32 | POT        | 2022-05-10 |                              +------+
| 33 | POT        | 2022-05-17 |
| 34 | POT        | 2022-05-24 |
| 35 | POT        | 2022-05-31 |
| 36 | ORI        | 2022-06-03 |
| 37 | ORI        | 2022-06-17 |
| 38 | ORI        | 2022-07-01 |
| 39 | ORI        | 2022-05-13 |
| 40 | ORI        | 2022-05-16 |
| 41 | ORI        | 2022-06-17 |
| 42 | DOG        | 2022-06-10 |
+----+------------+------------+

and the output for May 2022 is

CODE

<?php
include 'db_inc.php';                 # use your own
$pdo = pdoConnect('test');            # connection code


$numYear = 2022;
$numMonth = 5;

$dt1 = (new DateTime("{$numYear}-{$numMonth}-01"));
$lastday = (clone $dt1)->modify('+1 month')->format('Y-m-d');
$monthname = $dt1->format('F');
$dstr = $dt1->modify('last monday')->format('Y-m-d');

$res = $pdo->query("SELECT 
                            weekofyear(thedate)
                          , thedate
                          , day(thedate) as day
                          , month(thedate) as mth
                          , dayofweek(thedate) as dayno
                          , GROUP_CONCAT(coursecode SEPARATOR '<br>') as courses
                    FROM (
                            SELECT '$dstr' + INTERVAL a.num*10 + b.num DAY as thedate      -- subquery to generate all
                            FROM digit a, digit b                                          -- dates in required range
                            WHERE '$dstr' + INTERVAL a.num*10 + b.num DAY < '$lastday'
                         ) d
                         LEFT JOIN a_course_date a ON d.thedate = a.coursedate
                         GROUP BY thedate
                    ");
$dates = $res->fetchAll(PDO::FETCH_GROUP);
?>
<!DOCTYPE html>
<html lang='en'>
<head>
<title>Sample Calendar</title>
<meta http-equiv='Content-Type' content='text/html; charset=utf-8'>
<style type='text/css'>
    body {
        font-family: calibri, sans-serif;
        font-size: 11pt;
    }
    table {
        border-collapse: collapse;
        width: 700px;
        margin: 50px auto;
    }
    tr {
        vertical-align: top;
    }
    th {
        background-color: #EEE;
        width: 14%;
        padding: 8px;
    }
    th.we {
        background-color: #CCC;
    }
    td {
        background-color: #e6ffe6;
        width: 14%;
        height: 75px;
        padding: 4px;
        text-align: center;
    }
    td.we {
        background-color: #b3ffb3;
    }
    td.today {
        border: 3px solid #8F1FCF;
    }
    td.blank {
        background-color: white;
        color: #AAA;
    }
    span.dno {
        float: right;
        font-size: 9pt;
    }
</style>

</head>
<body>
    <table border='1'>
        <caption><?="$monthname $numYear"?></caption>
        <tr><th>Mon</th>
            <th>Tue</th>
            <th>Wed</th>
            <th>Thu</th>
            <th>Fri</th>
            <th class='we'>Sat</th>
            <th class='we'>Sun</th>
        </tr>
        
        <?php
            foreach ($dates as $wk => $days) {
                echo "<tr>\n";
                foreach ($days as $d) {
                    if ($d['mth'] == $numMonth) {
                        $today = $d['thedate'] == date('Y-m-d') ? 'today' : '';
                        $we = in_array($d['dayno'], [1,7]) ? 'we' : '';
                        $cls = "$we $today";
                    }
                    else {
                        $cls = 'blank';
                    }
                    echo  "<td class='$cls' > 
                              <span class='dno'>{$d['day']}</span>
                              <br>
                              {$d['courses']}
                          </td>\n";
                }
                echo "</tr>\n";
            }
        ?>
    </table>
</body>
</html>