Hi Nick:
> $sql = "select dt1.*, d.* from
> driver d join
> (select `driver`, date(`leave`), sum(`points`) as pnts,
> sum(`xpnts`) as xpnts from check_head
> where date(`leave`) = '".$sdate."'
> group by `driver`) dt1
> on dt1.driver = d.id_num where `store` = '".$userStore."' ";
Ouch. You need to read up on how MySQL queries work. There are sections
in the manual that explain it pretty well.
Most importantly, it's a really bad idea to use functions on a column in
a WHERE clause. The function has to be run on every row in the table,
then those results get compared to the value ($sdate in this case).
Second, joins only use indexes when you're joining tables. Here, you're
joining a subquery, so no love.
Third, don't delimit your identifiers. If you're using identifiers that
need delimiting, don't.
Fourth, start queries from the table where the WHERE clause does the
work.
Try something like this... Note, I'm leaving date(leave) out of the
output since you didn't put an alias (AS) on it makes me believe you're
not using it in your output because the likelyhood of you doing
$row['date(leave)'] in your PHP is low. Also, you better be sure the
value of $sdate and $userStore are safe for use in a query / can't be
tainted to perform an SQL injection attacks.
SELECT driver, SUM(points) AS pnts, SUM(xpnts) AS xpnts
FROM check_head
JOIN driver ON (driver.id_num = check_head.driver)
WHERE leave = '$sdate'
AND store = '$userStore'
GROUP BY driver
--Dan
--
T H E A N A L Y S I S A N D S O L U T I O N S C O M P A N Y
data intensive web and database programming
http://www.AnalysisAndSolutions.com/
4015 7th Ave #4, Brooklyn NY 11232 v: 718-854-0335 f: 718-854-0409
_______________________________________________
New York PHP Users Group Community Talk Mailing List
http://lists.nyphp.org/mailman/listinfo/talk
http://www.nyphp.org/Show-Participation
