Days getting multiplied for each month

[Krissh]

       

 <?php

 $guestRoomsResult = mysqli_query($con,"SELECT * FROM product WHERE category =  'Guestroom' AND status  'Active' ORDER BY productName ASC")

    if (mysqli_num_rows($guestRoomsResult) > 0) {

                                    $month1 = [];

                                    $month2 = [];

                                    $month3 = [];

 while ($row= mysqli_fetch_array($guestRoomsResult)) {

                                        $productId = $row["id"];

                                        $month1_total = 0;

                                        $month2_total = 0;

                                        $month3_total = 0;

$sql = "SELECT bookingItem.* FROM bookingItem, product WHERE bookingItem.productId = product.id AND bookingItem.productId = $productId AND product.status = 'Active' AND bookingItem.startTime <= '$month3_endTime'AND bookingItem.endTime >= '$month1_startTime'";                  

$booking_result = mysqli_query($con,$sql);

if (mysqli_num_rows($booking_result) > 0) {

while($booking_row = mysqli_fetch_array($booking_result)) {

$start_date = new DateTime($booking_row["startTime"]);

$end_date = new DateTime($booking_row["endTime"]);

$end_date->modify('-1 day'); // Exclude the end date

while ($start_date <= $end_date) {

$month = $start_date->format("n");

if ($_GET["quarters"] == "Q1") {

switch ($month) {

case 1:

    $month1_total +=  $booking_row["quantity"];

    $month1[] =$booking_row[  "quantity"];

    break;

case 2:

$month2_total +=$booking_row["quantity"];

$month2[] =$booking_row["quantity"];

break;

case 3:

$month3_total +=$booking_row["quantity"];

$month3[] =$booking_row["quantity"];

break;

  }

         }          }           echo "<tr>";

   echo "<td>" .

            $row["productName"] .

            "</td>";

       if ($_GET["quarters"] == "Q1") {

         echo "<td>" .

    $month1_total ."</td>";   echo "<td>" .

 $month2_total . "</td>";    echo "<td>" .

  $month3_total .  "</td>";echo "<td><strong>" .

 ($month1_total +$month2_total + $month3_total) .

  "</strong></td>";

  }

 echo "</tr>";

 }

 } else {

   echo "<tr>";

echo '<td colspan="4">Inga produkter hittades</td>';

echo "</tr>";

  }

 ?>

  // Display the product total row

 echo "<tr>";

 echo "<td><strong>Total</strong></td>";

 if ($_GET["quarters"] == "Q1") {

 $month1_total = array_sum($month1);

 $month2_total = array_sum($month2);

$month3_total = array_sum($month3);

$quarterTotal = $month1_total + $month2_total +  $month3_total;

 echo "<td><strong>{$month1_total}</strong></td>";

echo "<td><strong>{$month2_total}</strong></td>";

 echo "<td><strong>{$month3_total}</strong></td>";

 echo "<td><strong>{$quarterTotal}</strong></td>";

  }

echo "</tr>";

  ?>

  </div>

                   <br /><br />

Test Scenario: Start date: 2020-01-01 and end date: 2024-07-31. It should fetch the no. of days correctly on the guestroom table. Instead, it multiplies or adds according to the year. So each month is calculated as 155 days(jan 31 days X 5 years) instead of 31 days.  The output should be Jan 30 Feb 29 Mar 31. But it shows as Jan 155 Feb 142  Mar 155. Please check the code and share the revised as per requirement. 

 

[Barand]

Your first query is redundant - you are joining product and booking_items in the second query.

You appear to have ignored @mac_gyver'scomments completely from your previous post.

Still no test data so we can't see for ourselves what is happening.

38 minutes ago, Krissh said:

The output should be Jan 30 Feb 29 Mar 31. . But it shows as Jan 155 Feb 142  Mar 155.

Are you saying the purpose of the two queries and all the php code is just to see how many days in each month?

 

[Krissh]

12 hours ago, Barand said:

Your first query is redundant - you are joining product and booking_items in the second query.

You appear to have ignored @mac_gyver'scomments completely from your previous post.

Still no test data so we can't see for ourselves what is happening.

Are you saying the purpose of the two queries and all the php code is just to see how many days in each month?

 

The purpose of the code is to fetch the no.of nights in each month and should not calculate the end date. So the start date will be 2020-01-01 and the end date will be 2024-07-31. So the output should be Jan-31 Feb-29 Mar-31. But the output here shown is Jan -155 Feb -142 and Mar -155, where it is getting multiplied for all year.

[Barand]

It would also help immensely if you posted the actual code that gives your claimed results.

The code you posted wil not even execute as it contains :

• SQL syntax error
• undefined variable values
• an infinite loop

[Krissh]

Hi,

I have attached the full code for your reference. 

Test scenario:

Start date: 2020-01-01-and end date: 2024-07-31. Here no.of nights needs to be fetched for each month from the year 2020 to 2024.

Reports.txt

[Barand]

I had to create my own test data (thanks for that) but naturally I don't know how it conforms with yours.

TABLE: product                                  TABLE: bookingItem
+----+-------------+-----------+--------+       +----+-----------+---------------------+---------------------+----------+
| id | productName | category  | status |       | id | productid | startTime           | endTime             | quantity |
+----+-------------+-----------+--------+       +----+-----------+---------------------+---------------------+----------+
|  1 | Room 1      | Guestroom | Active |       |  1 |         1 | 2024-01-01 11:32:01 | 2024-01-02 11:32:59 |        1 |
|  2 | Room 2      | Guestroom | Active |       |  2 |         2 | 2024-02-01 11:34:08 | 2024-02-03 11:34:24 |        2 |
|  3 | Room 3      | Guestroom | Active |       |  3 |         3 | 2024-03-01 11:34:56 | 2024-03-04 11:35:08 |        3 |
|  4 | Room 4      | Guestroom | NULL   |       |  4 |         2 | 2024-04-01 12:20:20 | 2024-04-07 12:20:41 |        6 |
|  5 | Room 5      | Guestroom | NULL   |       |  5 |         3 | 2024-05-01 01:21:49 | 2024-05-05 12:21:58 |        4 |
+----+-------------+-----------+--------+       |  6 |         5 | 2024-06-19 12:23:03 | 2024-06-29 12:23:28 |       10 |
                                                |  7 |         2 | 2024-06-01 13:02:51 | 2024-06-15 13:03:16 |       14 |
                                                +----+-----------+---------------------+---------------------+----------+

On running your code with my data I get these results for Q1 and Q2. I have written the correct totals in red. As you can see there is a distinct pattern - your totals are the correct totals squared. However, I could not spot any multiplication in the code

(I ran as separate query to confirm the correct totals)

                    

I have to say, in your code you really make a meal of those dates in the years and quarters considering that SQL can handle it easily. Here's my version...

<?php

###############################################
# CREATE YOUR OWN PDO DATABASE CONNECTION     #
#                                             #
    require 'db_inc.php';
    $pdo = mdbConnect('db1');
#                                             #
#                                             #
###############################################

$range = [ '2020-01-01', '2024-07-31' ];
$selectedYear = $_GET['year'] ?? 0;
$whereYear = '';

if ($selectedYear)  {
    $whereYear = 'AND YEAR(d.dt) = ?'; 
    $range[] = $selectedYear;
}

$res = $pdo->prepare("WITH RECURSIVE dates(dt) AS
                        (
                                SELECT ?
                                UNION ALL
                                SELECT dt + INTERVAL 1 MONTH
                                FROM dates WHERE dt < ?
                        )
                      SELECT 
                             YEAR(d.dt) AS yr
                           , QUARTER(d.dt) as qtr
                           , MONTHNAME(dt) AS mth
                           , productName AS room
                           , COALESCE(SUM(DATEDIFF(endTime, startTime)), '-') AS nights
                      FROM product p
                           CROSS JOIN dates d
                           LEFT JOIN bookingitem b ON b.productid = p.id
                                                   AND YEAR(d.dt) = YEAR(b.startTime)
                                                   AND MONTH(d.dt) = MONTH(b.startTime)
                      WHERE p.`status` = 'Active'
                            $whereYear 
                      GROUP BY yr, qtr, MONTH(d.dt), p.id
                      ");
$res->execute($range);
$results = $res->fetchAll();
$rooms = array_unique(array_column($results, 'room'));
$theads = "<tr><th>Quarter</th><th>Month</th><th>" . join('</th><th>', $rooms) . "</th><th>Total</th></tr>\n";

### RESTRUCTURE THE RESULTS ARRAY

foreach ($results as $r)  {
    $data[$r['yr']][$r['qtr']][$r['mth']][$r['room']] = $r['nights'];
}


?>
<!DOCTYPE html>
<html lang='en'>
<head>
<meta charset="utf-8">
<title>Example</title>
<link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css">
<style type='text/css'>
    table {
        width: 100%;
        border-collapse: collapse;
    }
    th {
        background-color: #808080;
        color: white;
        padding: 8px;
    }
    td {
        padding: 4px 12px;
        text-align: right;
    }
    .ca {
         text-align: center;
         background-color: #EEE;
    }
    .la {
        text-align: left;
        background-color: #EEE;
        color: black;
    }
    
</style>
</head>
<body>

<header class='w3-indigo w3-padding w3-margin-bottom'>
    <h1>Guestroom Occupancy</h1>
</header>

<div class='w3-content w3-padding'>

    <?php
    
        ## OUTPUTFROM RESTRUCTURED ARRAY
        
        foreach ($data as $yr => $ydata)  {
            echo "<h3>$yr</h3>\n
                  <table border='1'>
                  $theads
                 ";
            foreach ($ydata as $qtr => $qdata)  {
                $span = 3 + count($rooms);
                echo "<tr><th class='la' colspan='$span'>Quarter {$qtr}</th></tr>\n";
                foreach ($qdata as $mth => $mdata)  {
                    echo "<tr><td>&nbsp;</td><td>$mth</td><td>" . join('</td><td>', $mdata) . "</td><td><b>" . array_sum($mdata) . "</b></td></tr>\n";
                }
            }
            echo "</table>\n";
        }
    ?>
</div>
</body>
</html>

Output

[Krissh]

[Krissh]

Here you can see that rum A1 is getting added twice. For jan month it should be 31 days. But it shows 62 days which is wrong.

Test scenario here. The start date is 2020-01-01 and the end date is 2021-12-31. While testing between years it is not fetching the correct no.of days for each month.

 

[Krissh]

If test within a year, For example. The start date is 2020-01-01 and the end date is 2020-12-31. The code works fine with the requirement.

[Barand]

I am not prepared to do any more guessing without the data that is being used. Whether your code and query is wrong, and why, depends on the data is is used with.