I have the following PHP code that I'm aiming to create an HTML email with that should output the details of all models with an expiry date landing between 2 set dates:
$sql = 'SELECT
GROUP_CONCAT(op.products_model),
GROUP_CONCAT(products.products_warranty_default),
GROUP_CONCAT(orders.date_purchased),
orders.customers_name
FROM orders_products op
INNER JOIN products
ON op.products_id = products.products_id
INNER JOIN orders
ON op.orders_id = orders.orders_id
INNER JOIN customers
ON orders.customers_id = customers.customers_id
GROUP BY op.orders_id';
$result = mysql_query($sql, $connection);
while ($data = mysql_fetch_row($result)) {
$models = explode(",",$data[0]); // create array from all models in the GROUP_CONCAT
$years = explode(",",$data[1]); // create array from all years in the GROUP_CONCAT
$dates = explode(",",$data[2]); // create array from all dates in the GROUP_CONCAT
$name = $data[3];
echo $header; // header of email (only the name changes on each loop)
foreach ($models as $index => $model) {
echo '
<tr>
<td width="149">
<center>'.$model.'</center>
</td>
<td width="149">
<center>'.date('d/m/Y', strtotime('+'.$years[$index].' years', strtotime($dates[$index]))).'</center>
</td>
</tr>'; // echos the model in first column and date+years in second column
}
echo $footer; // static HTML again like the header
}
What I'm stuck on is how to only output the combination of the header, table and footer if a model exists in the table where the date is between 2 dates i.e. 1st May 2014 and 31st May 2014. It's currently outputting all dates.
I've tried various ways to do this but after about 6 hours of failure I'm about ready to throw my computer out the window. I only have a few weeks experience with PHP (and programming altogether for that matter) so it may (hopefully) be a simple fix that I'm unaware of.
NOTE: I should note that with the current database I'm using to test this I could amend the select query to output the relevant dates but on the actual database I'm using (which I only have access to at work) there is no warranty field and I have to extract the information from the product's long description via php and feed this into a function.
Final code:
$sql = 'SELECT op.products_model,
products.products_warranty_default,
orders.date_purchased,
orders.customers_name
FROM orders_products op
INNER JOIN products ON op.products_id = products.products_id
INNER JOIN orders ON op.orders_id = orders.orders_id
INNER JOIN customers ON orders.customers_id = customers.customers_id
ORDER BY orders.customers_name, op.orders_id,
op.products_model,
products.products_warranty_default,
orders.date_purchased';
$result = mysql_query($sql, $connection);
$previous_name = "";
while ($data = mysql_fetch_row($result)) {
$model = $data[0];
$year = $data[1];
$date = $data[2];
$name = $data[3];
$expiry = strtotime('+'.$year.' years', strtotime($date));
if ($previous_name != $name && $expiry >= strtotime('2014-05-01') && $expiry <= strtotime('2014-05-31')) { // on a new customer name ...
if ($previous_name != "") {
// if there is a previous customer, write the footer
echo $footer;
$previous_name = $name;
}
}
if ($expiry >= strtotime('2014-05-01') && $expiry <= strtotime('2014-05-31')){
echo '
<tr>
<td width="149">
<center>'.$model.'</center>
</td>
<td width="149">
<center>'.date('d/m/Y', $expiry).'</center>
</td>
</tr>'; // echos the model in first column and date+years in second column
}
}
if ( $previous_name != "" && $expiry >= strtotime('2014-05-01') && $expiry <= strtotime('2014-05-31')) {
echo $footer;
}
It seems a bit strange that you're aggregating a lot of stuff in your SQL query and then disaggregating it in your PHP. This is making it hard for you to match dates, I believe.
You could try this non-aggregated query:
SELECT op.products_model,
products.products_warranty_default,
orders.date_purchased,
orders.customers_name
FROM orders_products op
INNER JOIN products ON op.products_id = products.products_id
INNER JOIN orders ON op.orders_id = orders.orders_id
INNER JOIN customers ON orders.customers_id = customers.customers_id
WHERE orders.date_purchased >= DATE('2014-05-01')
AND orders.date_purchased < DATE('2014-05-31') + INTERVAL 1 DAY
ORDER BY orders.customer_name, op.orders_id,
op.products_model,
products.products_warranty_default,
orders.date_purchased,
This will give you back the detail records, the ones that match the date range.
It's quite easy, I think, to then process that result set with php. You'll need to detect the change in customer name to output the headers and footers. That's what $previous_name is about.
$previous_name = "";
while ($data = mysql_fetch_row($result)) {
$model = $data[0];
$year = $data[1];
$date = $data[2];
$name = $data[3];
if ($previous_name != $name) { /* on a new customer name ... */
if ($previous_name != "") {
/* if there is a previous customer, write the footer */
echo $footer; /* static HTML again like the header */
}
/* write the header for the new customer */
echo $header;
/* pick up the new customer name */
$previous_name = $name;
}
echo '
<tr>
<td width="149">
<center>'.$model.'</center>
</td>
<td width="149">
<center>'.date('d/m/Y', strtotime('+'.$year.' years',
strtotime($date))).'</center>
</td>
</tr>'; /* echos the model in first column and date+years in second column */
}
} /* end while ($data... */
/* wrap up the report by writing the final footer if need be */
if ( $previous_name != "" ) {
echo $footer; /* static HTML again like the header */
}
See how this goes? You request a detail resultset from SQL, and then use some programming techniques in PHP to divide that result set customer-by-customer.
That's easier and more reliable than getting a GROUPed result set and then trying to ungroup it.