Not sure where to put the GROUP BY clause in this SQL. Can anyone help?
select * from uk_postcode_towns where town like '%madi%'
order by case
when town like 'madi%' then 0
when town like '% %madi% %' then 1
when town like '%madi' then 2
else 3 end, town
Nowhere You don't have any group by functions (MIN, MAX, etc) in your query, why would you want to group?
Anyway, the GROUP BY goes after the WHERE and before the ORDER BY. I'm sure the mysql reference manual has this bit of info.
I have a problem in that if I enter "Madison" into the query, it should retrurn all places with the name "Madison" in them. Currently this query is only returning the first exact match of Madison. Do you know what I need to change in the query to return all Madison matches? I thought a group by was the answer, but maybe not
can we see the query where you tried this?
because if it's the same as the query you posted, then something else is wrong
The query I tried is:
select * from uk_postcode_towns where town like '%madison%'order by casewhen town like 'madi%' then 0when town like '% %madi% %' then 1when town like '%madi' then 2else 3 end, town
that query should work as intended, to return all rows that have madison somewhere in the town name
did you test it outside of php? maybe your php code is only showing the first one