Not fetching the correct no.of quantity for each month.

[Krissh]

Based on the booking items there should be total of 9 nights for “Room 1” in (june) and (july) with quantity 1

Expected result in june= 5 nights Expected result in july = 4 nights Got 6 nights in june and 4 nights in july

code.txt

[Barand]

Any chance you could post a dump of the structure and data for those tables?

[mac_gyver]

what data type are startTime and endTime? just date or datetime? when someone books a single night, what are the startTime and endTime values, for example if startTime is 2024-06-27 (today where i am at), what is the endTime value? based on the answer to these questions, the most likely cause is this - while ($start_date <= $end_date) {. when start_date has a day added to it and is equal to end date, you could be counting a row of data twice.

some points about the code -

1. don't run queries inside of loops. just use a single query to get all the data that you want at once. to produce output broken into sections by some value (product id), just index/pivot the data when you fetch it using that value as the main array index. you can then simply use two nested foreach loops to loop over the data to produce the output.
2. if you use an array for the $month1, $month2, ... variables and use an array for the $month1_total, $month2_total, ... variables, with the array index being the month number, all the switch/case logic will go away. you can simply use the $month variable to directly reference the correct entry in these arrays.
3. i'm not sure what the '$month3_endTime' and '$month1_startTime' values are doing in the query. they are not the cause of the current problem, but they could exclude data, producing lower quantities then expected.

 

Edited June 27 by mac_gyver

[Krissh]

1 hour ago, mac_gyver said:

what data type are startTime and endTime? just date or datetime? when someone books a single night, what are the startTime and endTime values, for example if startTime is 2024-06-27 (today where i am at), what is the endTime value? based on the answer to these questions, the most likely cause is this - while ($start_date <= $end_date) {. when start_date has a day added to it and is equal to end date, you could be counting a row of data twice.

some points about the code -

1. don't run queries inside of loops. just use a single query to get all the data that you want at once. to produce output broken into sections by some value (product id), just index/pivot the data when you fetch it using that value as the main array index. you can then simply use two nested foreach loops to loop over the data to produce the output.
2. if you use an array for the $month1, $month2, ... variables and use an array for the $month1_total, $month2_total, ... variables, with the array index being the month number, all the switch/case logic will go away. you can simply use the $month variable to directly reference the correct entry in these arrays.
3. i'm not sure what the '$month3_endTime' and '$month1_startTime' values are doing in the query. they are not the cause of the current problem, but they could exclude data, producing lower quantities then expected.

 

This is how my page looks,

 

[Krissh]

1 hour ago, Barand said:

Any chance you could post a dump of the structure and data for those tables?

Thanks for your reply. I am not in position to share the dump structure right now.