Hi all,
This post is an extension of How to get results from database and display like this? (which I cant reopen, so Ive created a new post).
Thanks to the users on this forum, and @Drummin in particular, the prices are displaying correctly. My question is, how can these be ordered so the months starts at Jan and ends with December? (rather than Oct - Sept as it currently shows).
Ive tried Order BY t.month_numbers , but that has no effect.
Many thanks
You wonât be able to split the DB records where month is 6,7,8 but by converting the varchar to integer (which will turn 6,7,8 into 6 (I believe)) you should be able to use ORDER BY on this field.
GROUP BY t.year, CONVERT(t.month_numbers, UNSIGNED INTEGER)
This works great @Drummin! I didnt even have to Order By on t.month_numbers!
Now, I dont know if this is even possible, but the final thing is to display any special offers for the relevant month within the relevant table cell. The big challenge being that the schema of the 2 tables is completely different.
The standard_prices table:
The special_prices table:
Is this even possible?
Anything is possible. You might, for example, load the special offers into an array before you start, then each time you display one of your results, call a function that decides whether any offers apply to the specific combination of dates and days, and display accordingly.
And once again, gotchas lie in the weeds with this one.
What do you do if multiple offers are available in a given segment?
What do you display in the Midweek column if an offer is only valid on Tuesdays?
OK, some more detail:
Special offers will be applied to the same week segments as the standard prices: Fri/Sat/Mid-week (mon-thurs).
There will only be one offer on any given segment
I presume that makes things a lot easier!?
As long as thereâs one more checkbox to check:
at that point you could simplify your special_prices table down.
Not sure exactly what information youâre trying to get, or how you evaluate what to display.
Yes, I was just going to clarify that point: Special offers WILL be applied in month blocks. 
I can style up the look of the special offer prices, but I dont know how to make the discounted price show in the relevant table cells.
Remind us of what your query currently looks like?
Sure, current query looks like this:
$query = "SELECT t.year,t.month_numbers,
MAX(t.midweek) midweek,
MAX(t.friday) friday,
MAX(t.saturday) saturday,
MAX(t.sunday) sunday
FROM (
SELECT `year`,`month_numbers`,
CASE name when 'MID WEEK' THEN price ELSE 0 END midweek,
CASE name when 'FRIDAY' THEN price ELSE 0 END friday,
CASE name when 'SATURDAY' THEN price ELSE 0 END saturday ,
CASE name when 'SUNDAY' THEN price ELSE 0 END sunday
FROM `standard_prices`
) t
GROUP BY t.year, CONVERT(t.month_numbers, UNSIGNED INTEGER)";
$result = $con->query($query);
Hi all, I just thought Id bump this post to see if someone could help getting it to work. Im hopelessly lost with it! 
Sorry, itâs been a busy week.
That query is going to get ugly real fast without reshaping that table. It will be a lot easier, as droopsnoot said earlier, to pull a separate query of all the offers for the selected year, and use PHP to make a determinant about whether to display a regular or sale price.
Well it is possible to query the second special prices table as a join to the first table on the year and month fields, just to need to use CONCAT to make a year-month value to compare to stand date fields. What is really hindering things is the GROUP BY used in the query as you will never get multiple special pricing results. If we remove the GROUP BY we can join the tables as follows grabbing data from both tables.
FROM `standard_prices` AS p
LEFT JOIN `special_prices` AS s
ON CONCAT(p.year, '-', LPAD(CONVERT(p.month_numbers, UNSIGNED INTEGER), 2, '0')) BETWEEN DATE_FORMAT(s.date_start,'%Y-%m') AND DATE_FORMAT(s.date_end,'%Y-%m')
ORDER BY p.year, CONVERT(p.month_numbers, UNSIGNED INTEGER)
As before we are setting a value for day of the week based on what part of the week each record holds, i.e. if record is for Friday, friday = price otherwise friday = 0; In the old query we used MAX and GROUP BY to then get the highest value. With the new joined query we will need to do something a little different to group things by Year, Month, Day of the week, plus handle multiple records for these days, let alone deal with special pricing.
This is where a data array can really come in handy.
This order of Year, Month, Day of the week and multiple records per day is a simple TREE like structure. Much like your PC has folders within folders you would build a data array starting from the outside.
$data = array();
Within the WHILE loop we place the Year as the primary array key.
$data[$row['year']]
We then place the Month as the secondary array key.
$data[$row['year']][$row['month_numbers']]
And as we gathering information from 2 DB tables for any given month i.e. Specials or those of the Week, we will use these named key(s) to keep things straight.
$data[$row['year']][$row['month_numbers']]['week']
OR
$data[$row['year']][$row['month_numbers']]['specials']
Under the WEEK key we have our 4 subweek keys for midweek, friday, saturday and sunday.
$data[$row['year']][$row['month_numbers']]['week']['midweek']
$data[$row['year']][$row['month_numbers']]['week']['friday']
$data[$row['year']][$row['month_numbers']]['week']['saturday']
$data[$row['year']][$row['month_numbers']]['week']['sunday']
Under each of these days we will have multiple records so we will use the record ID as the key and corresponding result FIELD as the value.
$data[$row['year']][$row['month_numbers']]['week']['midweek'][$row['id']] = $row['midweek'];
$data[$row['year']][$row['month_numbers']]['week']['friday'][$row['id']] = $row['friday'];
$data[$row['year']][$row['month_numbers']]['week']['saturday'][$row['id']] = $row['saturday'];
$data[$row['year']][$row['month_numbers']]['week']['sunday'][$row['id']] = $row['sunday'];
Now our query will only return results on Special Pricing if the query conditions match, so in our DATA building we only want to add values to our array IF we have query result data. We can check for a special ID and if not empty build the array for specials with the fields from that table.
if(!empty($row['specialID'])):
$data[$row['year']][$row['month_numbers']]['specials'][$row['specialID']]['specialname'] = $row['specialname'];
$data[$row['year']][$row['month_numbers']]['specials'][$row['specialID']]['date_start'] = $row['date_start'];
$data[$row['year']][$row['month_numbers']]['specials'][$row['specialID']]['date_end'] = $row['date_end'];
$data[$row['year']][$row['month_numbers']]['specials'][$row['specialID']]['amount'] = $row['amount'];
$data[$row['year']][$row['month_numbers']]['specials'][$row['specialID']]['adjustment_type'] = $row['adjustment_type'];
endif;
You end up building an array that looks like this. (Not showing YEAR in this image)
Now instead of having a single WHILE statement within the display, we use several foreach statements to loop through this data.
Within the monthly foreach loop you check for the âspecialsâ key and if found, loop through these âSpecialsâ records to build for example a tooltip display.
$specials='';
//check for specials and build tooltip
if(array_key_exists('specials', $row)):
$plural = (count($row['specials']) > 1 ? 's' : '');
$specials .= '<div class="tooltip">Special'.$plural;
$specials .= '<span class="tooltiptext">'."\r";
foreach($row['specials'] as $s):
//format special display
$specials .= '<h3>'.$s['specialname'].'</h3><br />'."\r";
$specials .= $s['date_start'].' <i>through</i> '.$s['date_end'].'<br />'."\r";
$specials .= '<span style="float:left;"><i>Amount:</i> £'.$s['amount'].'</span>'."\r";
$specials .= '<span style="float:right;"><i>Type:</i> '.$s['adjustment_type'].'</span><br /><br />'."\r";
endforeach;
$specials .= '</span>'."\r";
$specials .= '</div>'."\r";
endif;
By hovering over the tooltip link for a special, the details of the special can be seen.
Note that I added another special record to my DB for âsummer monthsâ and so the tooltip link is now plural (with âSâ) and 2 records are shown in the popup.
Hereâs is my complete sample.
<?php
$data = array();
$sql = "SELECT
p.id
, p.year
, p.month_numbers
, CONCAT(p.year, '-', LPAD(CONVERT(p.month_numbers, UNSIGNED INTEGER), 2, '0')) AS yearmonth
, CASE p.name when 'MID WEEK' THEN p.price ELSE 0 END midweek
, CASE p.name when 'FRIDAY' THEN p.price ELSE 0 END friday
, CASE p.name when 'SATURDAY' THEN p.price ELSE 0 END saturday
, CASE p.name when 'SUNDAY' THEN p.price ELSE 0 END sunday
, s.id AS specialID
, s.name AS specialname
, s.date_start
, s.date_end
, s.amount
, s.adjustment_type
FROM `standard_prices` AS p
LEFT JOIN `special_prices` AS s
ON CONCAT(p.year, '-', LPAD(CONVERT(p.month_numbers, UNSIGNED INTEGER), 2, '0')) BETWEEN DATE_FORMAT(s.date_start,'%Y-%m') AND DATE_FORMAT(s.date_end,'%Y-%m')
ORDER BY p.year, CONVERT(p.month_numbers, UNSIGNED INTEGER)";
$result = $con->query($sql);
while ($row = $result->fetch_assoc()){
$data[$row['year']][$row['month_numbers']]['week']['midweek'][$row['id']] = $row['midweek'];
$data[$row['year']][$row['month_numbers']]['week']['friday'][$row['id']] = $row['friday'];
$data[$row['year']][$row['month_numbers']]['week']['saturday'][$row['id']] = $row['saturday'];
$data[$row['year']][$row['month_numbers']]['week']['sunday'][$row['id']] = $row['sunday'];
if(!empty($row['specialID'])):
$data[$row['year']][$row['month_numbers']]['specials'][$row['specialID']]['specialname'] = $row['specialname'];
$data[$row['year']][$row['month_numbers']]['specials'][$row['specialID']]['date_start'] = $row['date_start'];
$data[$row['year']][$row['month_numbers']]['specials'][$row['specialID']]['date_end'] = $row['date_end'];
$data[$row['year']][$row['month_numbers']]['specials'][$row['specialID']]['amount'] = $row['amount'];
$data[$row['year']][$row['month_numbers']]['specials'][$row['specialID']]['adjustment_type'] = $row['adjustment_type'];
endif;
}
/*
echo "<pre>";
print_r($data);
echo "<pre>";
*/
?>
<!DOCTYPE html>
<html lang="en">
<head>
<title>Standard Prices</title>
<style type="text/css">
.pricelist{
margin:50px auto;
background-color:#FFFFFF;
font-size:18px;
font-family:'Segoe UI', Arial;
border-collapse: collapse;
border:1px solid #C6C6C6;
}
.pricelist td{
padding:2px 9px;
border:1px solid #D4D4D4;
}
.pricelist td.title{
text-align:center;
font-size:32px;
font-weight:550;
border:1px solid #D4D4D4;
}
.pricelist td.heading{
text-align:center;
font-size:20px;
font-weight:550;
border:1px solid #D4D4D4;
}
.pricelist td.monthhead{
text-align:left;
font-size:20px;
font-weight:550;
border:1px solid #D4D4D4;
}
.right{
text-align:right;
}
.tooltip {
position: relative;
display: inline-block;
cursor: pointer;
}
.tooltip .tooltiptext {
visibility: hidden;
width: 400px;
background-color:#fff;
color: #000;
text-align: center;
padding: 8px;
border-radius: 8px 0 8px 8px;
border:3px solid #009933;
position: absolute;
z-index: 1;
}
.tooltip:hover .tooltiptext {
visibility: visible;
}
.tooltiptext {
display: block;
margin-top: -.5em;
margin-left: -428px;
}
.tooltiptext h3 {
font-size: 18px;
display: inline-block;
text-align: center;
line-height:1em;
padding:10px 0;
margin:0;
}
.tooltiptext i {
font-weight:lighter;
}
</style>
</head>
<body>
<table border=0 class="pricelist">
<?php
$years = array();
$months = ['ImNotAMonth','January','February','March','April','May','June','July','August','September','October','November','December'];
foreach($data as $year => $arr):
if(!in_array($year,$years)):
$years[] = $year;
echo '<tr>
<td colspan="6" class="title">Prices '.$year.'</td>
</tr>
<tr>
<td style="width:16.666%;" class="heading"> </td>
<td style="width:16.666%;" class="heading">Midweek</td>
<td style="width:16.666%;" class="heading">Friday</td>
<td style="width:16.666%;" class="heading">Saturday</td>
<td style="width:16.666%;" class="heading">Sunday</td>
<td style="width:16.666%;" class="heading">Specials</td>
</tr>'."\r";
endif;
foreach($data[$year] as $month_numbers => $row):
$specials='';
//check for specials and build tooltip
if(array_key_exists('specials', $row)):
$plural = (count($row['specials']) > 1 ? 's' : '');
$specials .= '<div class="tooltip">Special'.$plural;
$specials .= '<span class="tooltiptext">'."\r";
foreach($row['specials'] as $s):
//format special display
$specials .= '<h3>'.$s['specialname'].'</h3><br />'."\r";
$specials .= $s['date_start'].' <i>through</i> '.$s['date_end'].'<br />'."\r";
$specials .= '<span style="float:left;"><i>Amount:</i> £'.$s['amount'].'</span>'."\r";
$specials .= '<span style="float:right;"><i>Type:</i> '.$s['adjustment_type'].'</span><br /><br />'."\r";
endforeach;
$specials .= '</span>'."\r";
$specials .= '</div>'."\r";
endif;
echo '<tr>
<td class="monthhead">'.implode(", ",array_map(function ($a) use ($months) { return $months[(int) $a]; },explode(",",$month_numbers))).'</td>
<td class="right">£ '.max($row['week']['midweek']).'</td>
<td class="right">£ '.max($row['week']['friday']).'</td>
<td class="right">£ '.max($row['week']['saturday']).'</td>
<td class="right">£ '.max($row['week']['sunday']).'</td>
<td>'.$specials.'</td>
</tr>'."\r";
endforeach;
endforeach;
?>
</table>
</body>
</html>
Sorry for the long post but wanted to explain things. Also the suggestion of making a separate query for specials and doing checks within primary result loop is also viable. As the data array comes very natural to me for dealing with difficult result sets I tend to use it. Also note I most certainly would avoid a nested query if at all possible though some might see that as a solution.
@m_hutley - dont apologise! I appreciate you even thinking about this!
@Drummin - that works great!! Well done! Echoing out the arrays I can see (sort of) how the code is working.
Ive been trying to tweak your code so that the user can hover over a relevant month/day cell to display the tooltip. (rather than having it displayed in its own column). But I cant see how to connect the special offer to the relevant week segment. To my mind the code would need to be something like:
<td class="right">
<?
if($row['year']][$row['month_numbers']]['specials'][$row['specialID']]['week_segment'] == "Midweek" && $row['year']][$row['month_numbers']]['specials'][$row['specialID']]['week_segment'] != ""){
echo $data[$row['year']][$row['month_numbers']]['specials'][$row['specialID']]['amount'] = $row['amount'];
}else{
echo max($row['week']['saturday'])
}
?
</td>
But obviously that doesnt work as theres no âweek segmentâ for the special offer.
Gawd, I dont think Ive explained that at all well. 
This version uses the month column as the hover link.
I donât believe (maybe you do??) you have any DB field for specials that applies the day of the week.
Anyway give this a try.
<?php
$data = array();
$sql = "SELECT
p.id
, p.year
, p.month_numbers
, CONCAT(p.year, '-', LPAD(CONVERT(p.month_numbers, UNSIGNED INTEGER), 2, '0')) AS yearmonth
, CASE p.name when 'MID WEEK' THEN p.price ELSE 0 END midweek
, CASE p.name when 'FRIDAY' THEN p.price ELSE 0 END friday
, CASE p.name when 'SATURDAY' THEN p.price ELSE 0 END saturday
, CASE p.name when 'SUNDAY' THEN p.price ELSE 0 END sunday
, s.id AS specialID
, s.name AS specialname
, s.date_start
, s.date_end
, s.amount
, s.adjustment_type
FROM `standard_prices` AS p
LEFT JOIN `special_prices` AS s
ON CONCAT(p.year, '-', LPAD(CONVERT(p.month_numbers, UNSIGNED INTEGER), 2, '0')) BETWEEN DATE_FORMAT(s.date_start,'%Y-%m') AND DATE_FORMAT(s.date_end,'%Y-%m')
ORDER BY p.year, CONVERT(p.month_numbers, UNSIGNED INTEGER)";
$result = $con->query($sql);
while ($row = $result->fetch_assoc()){
$data[$row['year']][$row['month_numbers']]['week']['midweek'][$row['id']] = $row['midweek'];
$data[$row['year']][$row['month_numbers']]['week']['friday'][$row['id']] = $row['friday'];
$data[$row['year']][$row['month_numbers']]['week']['saturday'][$row['id']] = $row['saturday'];
$data[$row['year']][$row['month_numbers']]['week']['sunday'][$row['id']] = $row['sunday'];
if(!empty($row['specialID'])):
$data[$row['year']][$row['month_numbers']]['specials'][$row['specialID']]['specialname'] = $row['specialname'];
$data[$row['year']][$row['month_numbers']]['specials'][$row['specialID']]['date_start'] = $row['date_start'];
$data[$row['year']][$row['month_numbers']]['specials'][$row['specialID']]['date_end'] = $row['date_end'];
$data[$row['year']][$row['month_numbers']]['specials'][$row['specialID']]['amount'] = $row['amount'];
$data[$row['year']][$row['month_numbers']]['specials'][$row['specialID']]['adjustment_type'] = $row['adjustment_type'];
endif;
}
/*
echo "<pre>";
print_r($data);
echo "<pre>";
*/
?>
<!DOCTYPE html>
<html lang="en">
<head>
<title>Standard Prices</title>
<style type="text/css">
.pricelist{
margin:50px auto;
background-color:#FFFFFF;
font-size:18px;
font-family:'Segoe UI', Arial;
border-collapse: collapse;
border:1px solid #C6C6C6;
}
.pricelist td{
padding:2px 9px;
border:1px solid #D4D4D4;
}
.pricelist td.title{
text-align:center;
font-size:32px;
font-weight:550;
border:1px solid #D4D4D4;
}
.pricelist td.heading{
text-align:center;
font-size:20px;
font-weight:550;
border:1px solid #D4D4D4;
}
.pricelist td.monthhead{
text-align:left;
font-size:20px;
font-weight:550;
border:1px solid #D4D4D4;
}
.right{
text-align:right;
}
.tooltip {
position: relative;
display: inline-block;
cursor: pointer;
}
.tooltip .tooltiptext {
visibility: hidden;
width: 400px;
background-color:#fff;
color: #000;
text-align: center;
padding: 8px;
border-radius: 8px 0 8px 8px;
border:3px solid #009933;
position: absolute;
z-index: 1;
}
.tooltip:hover .tooltiptext {
visibility: visible;
}
.tooltiptext {
display: block;
margin-top: 1px;
margin-left: 200px;
}
.tooltiptext h3 {
font-size: 18px;
display: inline-block;
text-align: center;
line-height:1em;
padding:10px 0;
margin:0;
}
.tooltiptext i {
font-weight:lighter;
}
</style>
</head>
<body>
<table border=0 class="pricelist">
<?php
$years = array();
$months = ['ImNotAMonth','January','February','March','April','May','June','July','August','September','October','November','December'];
foreach($data as $year => $arr):
if(!in_array($year,$years)):
$years[] = $year;
echo '<tr>
<td colspan="6" class="title">Prices '.$year.'</td>
</tr>
<tr>
<td style="width:16.666%;" class="heading"> </td>
<td style="width:16.666%;" class="heading">Midweek</td>
<td style="width:16.666%;" class="heading">Friday</td>
<td style="width:16.666%;" class="heading">Saturday</td>
<td style="width:16.666%;" class="heading">Sunday</td>
</tr>'."\r";
endif;
foreach($data[$year] as $month_numbers => $row):
$specials='';
//check for specials and build tooltip
if(array_key_exists('specials', $row)){
$specials .= '<div class="tooltip">'.implode(", ",array_map(function ($a) use ($months) { return $months[(int) $a]; },explode(",",$month_numbers)));
$specials .= '<span class="tooltiptext">'."\r";
foreach($row['specials'] as $s):
//format special display
$specials .= '<h3>'.$s['specialname'].'</h3><br />'."\r";
$specials .= $s['date_start'].' <i>through</i> '.$s['date_end'].'<br />'."\r";
$specials .= '<span style="float:left;"><i>Amount:</i> £'.$s['amount'].'</span>'."\r";
$specials .= '<span style="float:right;"><i>Type:</i> '.$s['adjustment_type'].'</span><br /><br />'."\r";
endforeach;
$specials .= '</span>'."\r";
$specials .= '</div>'."\r";
}else{
$specials .= implode(", ",array_map(function ($a) use ($months) { return $months[(int) $a]; },explode(",",$month_numbers)));
}
echo '<tr>
<td class="monthhead">'.$specials.'</td>
<td class="right">£ '.max($row['week']['midweek']).'</td>
<td class="right">£ '.max($row['week']['friday']).'</td>
<td class="right">£ '.max($row['week']['saturday']).'</td>
<td class="right">£ '.max($row['week']['sunday']).'</td>
</tr>'."\r";
endforeach;
endforeach;
?>
</table>
</body>
</html>
If there IS a day of the week field then you could probably add the tooltip to the amount display and or adjust the price shown.
EDIT: Actually you do have a day of the week fieldâŚ
This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.