At 19:34 +0200 19/9/07, thomas Armstrong wrote:
I've got this table in mySQL:
item 1:
-- firstname: John (Johnie)
-- phone: 555-600-200
item 2:
-- firstname: Peter
-- phone: 555-300-400
I created this SQL query to find 'johnie':
------------------------
SELECT friends.id FROM friends WHERE ((friends.firstname LIKE '%
johnie %' OR friends.firstname LIKE 'johnie %' OR friends.firstname
LIKE '% johnie' OR friends.firstname LIKE 'johnie' OR friends.phone
LIKE '% johnie %' OR friends.phone LIKE 'johnie %' OR friends.phone
LIKE '% johnie' OR friends.phone LIKE 'johnie')) ORDER BY
friends.firstname LIMIT 0, 9999
------------------------
But it doesn't match anything, because it's considers "(johnie)" as a
single word. Is there any way to make mySQL consider "(johnie)" as
"johnie".
Well I'm hardly the world's greatest expert, but I'm curious as to
why you're always separating '%' from 'johnie' with a space, because
that way it will only find Johnie if he has a space before or after
him or both.
Hmmm... and why the double parentheses? In fact, why any parentheses at all?
This oughta do it:
SELECT friends.id FROM friends WHERE friends.firstname LIKE '%johnie%' ORDER BY
friends.firstname LIMIT 0, 9999
That should find 'johnie' or 'Johnie' with absolutely any characters
before and/or after him.
... and if you want to simplify your queries as much as possible you
don't need to specify the table every time unless ambiguities might
arise (which they only will if there's more than one table involved),
so try:
SELECT id FROM friends WHERE firstname LIKE '%johnie%' ORDER BY
firstname LIMIT 0, 9999
... and unless you've really got more than 9999 friends that limit
clause is redundant too. :-)
--
Cheers... Chris
Highway 57 Web Development -- http://highway57.co.uk/
I think I think; therefore I think I am.
-- Ambrose Bierce
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
