Order BY query

laugharne

I have numerous tables that are searched by the following query:

$query = "SELECT date,meet,fname,sname,event,perf,mid,perf2 FROM out2006 WHERE fname LIKE '" . $fn. "%' AND sname ='$sn' UNION ALL SELECT date,meet,fname,sname,event,perf,mid,perf2 FROM indoor200506 WHERE fname LIKE '" . $fn. "%' AND sname ='$sn' UNION ALL SELECT date,meet,fname,sname,event,perf,mid,perf2 FROM out2005 WHERE fname LIKE '" . $fn. "%' AND sname ='$sn' UNION ALL SELECT date,meet,fname,sname,event,perf,mid,perf2 FROM indoor200405 WHERE fname LIKE '" . $fn. "%' AND sname ='$sn' UNION ALL SELECT date,meet,fname,sname,event,perf,mid,perf2 FROM out4 WHERE fname LIKE '" . $fn. "%' AND sname ='$sn' UNION ALL SELECT date,meet,fname,sname,event,perf,mid,perf2 FROM indoor3 WHERE fname LIKE '" . $fn. "%' AND sname ='$sn' ORDER BY 'event'";

I have tried to then run an ORDER BY query, byt fails. The above produces a list of athletes performances, I just want them ordered by "event " and "perf," "mid," "perf2."

Charles

bpat1434

What's the mySQL error output?

Have you tried ordering by tablename.event?

laugharne

I have worked on , now getting following :You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ''Kathryn' AND sname = "Hunter" AND club = 'Pembroke' AND event

So seems to be geting there- I had a " extra before

Thanks

Charles

Roger_Ramjet

Your db schema is wrong. No need to have seperate tables for each year etc. The you would have no need to be running a UNION query.

As it is, you can order the results by the judiciouse use of barckets around the individual SELECTs.

$query = "(SELECT date,meet,fname,sname,event,perf,mid,perf2 FROM out2006 WHERE fname LIKE '" . $fn. "%' AND sname ='$sn')
UNION ALL 
(SELECT date,meet,fname,sname,event,perf,mid,perf2 FROM indoor200506 WHERE fname LIKE '" . $fn. "%' AND sname ='$sn') 
UNION ALL 
(SELECT date,meet,fname,sname,event,perf,mid,perf2 FROM out2005 WHERE fname LIKE '" . $fn. "%' AND sname ='$sn') 
UNION ALL 
(SELECT date,meet,fname,sname,event,perf,mid,perf2 FROM indoor200405 WHERE fname LIKE '" . $fn. "%' AND sname ='$sn') 
UNION ALL 
(SELECT date,meet,fname,sname,event,perf,mid,perf2 FROM out4 WHERE fname LIKE '" . $fn. "%' AND sname ='$sn') 
UNION ALL 
(SELECT date,meet,fname,sname,event,perf,mid,perf2 FROM indoor3 WHERE fname LIKE '" . $fn. "%' AND sname ='$sn') 
ORDER BY event";

Your current mysql error is because you are mixing single and double quotes. Just put single quotes around the string values.