I'm trying to remove a mysql query from inside a PHP while loop and use a JOIN instead.
I have a query
SELECT *,
SUM(CASE WHEN posmonth like '2013%' THEN ext ELSE NULL END) AS year2013,
SUM(CASE WHEN posmonth like '2012%' THEN ext ELSE NULL END) AS year2012,
SUM(CASE WHEN posmonth like '2011%' THEN ext ELSE NULL END) AS year2011,
SUM(CASE WHEN posmonth like '2010%' THEN ext ELSE NULL END) AS year2010
FROM combined c
LEFT JOIN customermaster cm ON c.alias=cm.customerkey
WHERE cm.company LIKE 'A%'
GROUP BY customerkey
ORDER BY cm.company ASC
LIMIT 0, 50
Then with these results fetch data from another table "areamaster" using:
<?php
$result = mysqli_query($link, $query);
while ($r = mysqli_fetch_object($result)) {
$alias = $r->alias;
$company = $r->company;
$a = mysqli_query($link, "SELECT area FROM areamaster WHERE areakey='$alias' LIMIT 1");
while ($t = mysqli_fetch_object($a)) { $aliasdisplay = $t->area; }
//Instead of this ^^ USE JOIN IN ORIGINAL QUERY
?>
<tr>
<td><?php print (substr($company,0,20)); ?></td>
<td><?php print (strtoupper($r->location)); ?></td>
<td><?php print $aliasdisplay; ?></td>
<td>$<?php print number_format($r->$year4V,2,".",","); ?></td>
<td>$<?php print number_format($r->$year3V,2,".",","); ?></td>
<td>$<?php print number_format($r->$year2V,2,".",","); ?></td>
<td>$<?php print number_format($r->$year1V,2,".",","); ?></td>
</tr>
<?php
}
mysqli_free_result($result);
?>
Two mysql tables structures:
areamaster:
areakey: Primary
area: What I want displayed
combined:
id:primary
posmonth: for totaling ext by Year
alias: compare to areamaster.areakey and get "areamaster.area"
location:
ext: being totaled by SUM
IF anyone could help write the JOIN I'd appreciate it.
Also, if there is any way of further optimizing this query, please let me know.
YEAR(posmonth)
instead of the string matching you're doing. doing the string version forces mysql to reformat its internal date/time format into a string, instead of directly poking at the year bits inside the field's native structure. Of course, that assumes posmonth is a native date/time field. – Marc B Feb 19 '13 at 17:40