I want to get the number if items with state 4
and 1
from my database for each day between a certain date. Is there a smarter and more performative way than this one?
I am aware that I should use MySQLi and that MySQL is deprecated.
Code explanation:
- the
for
loop is to go through everyday between 2 dates $sql_condition
is in case the query has a specific item, otherwise empty variable
My code:
for( $thisDay = $start; $thisDay <= $end; $thisDay = $thisDay + 86400){
$formatedDate = date('Y-m-d', $thisDay);
// booked houses
$sql="SELECT id FROM ".T_BOOKINGS." WHERE id_state=1 ".$sql_condition." AND the_date='".$formatedDate."'";
$res=mysql_query($sql) or die("Error getting states<br>".mysql_Error());
//reserved houses
$sql2="SELECT id FROM ".T_BOOKINGS." WHERE id_state=4 ".$sql_condition." AND the_date='".$formatedDate."'";
$res2=mysql_query($sql2) or die("Error getting states<br>".mysql_Error());
$bookings['booked'][] = mysql_num_rows($res);
$bookings['reserved'][] = mysql_num_rows($res2);
$dates[] = $formatedDate;
}