PHP/MySQL question on search / sort

March 9, 2006 9:27 PM

I'm writing a small app to store my contacts. There's a little tool to search for names. When I search for "Jo", the results should display all names starting with "jo", followed by those with "jo" in the middle: 1. Joanna 2. John 3. Jonathan 4. Marjorie 5. Mojo How do I do this using a single SQL statement?
posted by arrowhead to computers & internet (14 comments total)

What's the ordering criteria for names that simply contain jo? Is a name higher ranked if jo appears sooner in the name? If there are multiple occurrences of jo? Just list the secondary results alphabetically?
posted by odinsdream at 9:38 PM on March 9, 2006

select from table where name like '%jo%';
posted by knave at 9:42 PM on March 9, 2006

The results should rank higher, the sooner "jo" appears in the name. Doing an SQL LIKE "%jo%" doesn't quite achieve the results.
posted by arrowhead at 10:00 PM on March 9, 2006

SELECT name FROM contacts WHERE name like '%jo%' ORDER BY INSTR(name,'jo');
posted by nicwolff at 10:10 PM on March 9, 2006

Or to sort the names that don't start with jo alphabetically, not by where 'jo' appears in the name:
SELECT * FROM contacts WHERE name LIKE '%jo%' ORDER BY name LIKE 'jo%' DESC, name ASC;
posted by inkyz at 10:14 PM on March 9, 2006

nicwolff's answer gets 4 and 5 backwards (but is probably fine, in reality)

Getting those right would involve sorting by the ratio of characters before and after the search string... or some such nonsense.
posted by I Love Tacos at 10:46 PM on March 9, 2006

I don't really code in anything but have you tried % jo% ?
posted by sourwookie at 11:29 PM on March 9, 2006

Surely nobody can answer this unless we know how your database looks?

Names are just one long field? The database-normalisation police will be here soon to beat you up.

Or are there fields for name, middlename, familyname etc.?
posted by AmbroseChapel at 12:52 AM on March 10, 2006

select * from table where name like 'jo%'

union

select * from table where name like '%jo%' and name not like 'jo%'
posted by SNACKeR at 4:50 AM on March 10, 2006

Well, if they've got names in separate fields, it would just be
(to extend inkyz statement)
SELECT * FROM contacts
WHERE firstName LIKE '%jo%' OR lastName LIKE '%jo%'
ORDER BY firstName LIKE 'jo%' DESC, lastName LIKE 'jo%' DESC, firstName ASC, lastName ASC

and you can add the middlename field if you have one, etc, or extend any of the other suggested statements in this way.
(I don't know how that ordering will turn out, it seems like it would be a little odd. Also: I haven't used SQL for a while, so I could be wrong).
posted by jacalata at 5:56 AM on March 10, 2006

I would do it like snacker said, with a UNION of the various queries.

Can you actually use LIKE in an ORDER BY clause like in some suggestions here?
posted by smackfu at 10:16 AM on March 10, 2006

Huh? arrowhead said The results should rank higher, the sooner "jo" appears in the name so his example in the post is wrong - "Mojo" should come before "Marjorie" - and my INSTR solution is correct. Although to handle ties it should be

SELECT name FROM contacts WHERE name like '%jo%' ORDER BY INSTR(name,'jo'), name;
posted by nicwolff at 10:38 AM on March 10, 2006

SELECT name FROM my_table
WHERE name LIKE '%jo%'
ORDER BY (CASE WHEN name LIKE 'jo%' THEN 0 ELSE 1), name
posted by klausness at 11:44 PM on March 10, 2006

By the way, a UNION is not guaranteed to return the first part before the second (though in practice it usually does). Also, if you know that the two parts of the UNION are disjoint, use a UNION ALL instead.
posted by klausness at 11:50 PM on March 10, 2006