NYCPHP Meetup

[Daniel Convissor]

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