0

I start off with a query like this:

$query_price = "SELECT price_id, dt FROM lh_dates WHERE (dt BETWEEN '$firstdate' AND '$lastdate') AND hid = '$hid'";

Then manipulate the result like this:

$i = 0;
$price_id_list = array();
while ($row_price = mysql_fetch_assoc($price)) {
$price_id_list[$i] = $row_price['price_id'];
$date_list[$i] = $row_price['dt'];
$i++;
}

I later use $i again to construct a table with date columns. The problem I have is that some dates in the range $firstdate to $lastdate may not exist in the lh_dates table. This reduces the number of iterations of $i and messes up the resulting table.

Is there any way that I can get a NULL or zero or nothing result in the query when the date doesn't exist in the database table?

UPDATE

Having looked at the similar questions, I realise that I need to create an array from $query_price and then loop through the array for each date in the date range and add in any missing dates. So I've got to this point:

$firstdate = date('Y-m-d',$startdate);
$lastdate = date('Y-m-d',strtotime('+17 days',strtotime($firstdate)));
$query_price = "SELECT price_id, dt FROM lh_dates WHERE (dt BETWEEN '$firstdate' AND '$lastdate') AND hid = '$hid'";
$price = mysql_query($query_price, $MySQL) or die(mysql_error());
$alldates = array(); // Array of all rows within the date range
while ($row_price = mysql_fetch_assoc($price)) {
$alldates[]=$row_price;
}
//Now loop through the date range and fill in any empty dates in the array
$thisdate = $firstdate;
while (strtotime($thisdate) <= strtotime($lastdate)){
// WHAT GOES IN HERE?
$thisdate = date('Y-m-d',strtotime('+1 day',strtotime($thisdate)));
};

Now I am struggling to add the necessary code to fill in the missing dates.

TrapezeArtist
  • 777
  • 1
  • 13
  • 38

1 Answers1

0
$price_id_list = array();
  while ($row_price = mysql_fetch_assoc($price)) {
  $price_id_list[$row_price['price_id']] = $row_price['price_id'];
  $date_list[$row_price['price_id']] = $row_price['dt'];
}
Legionar
  • 7,472
  • 2
  • 41
  • 70