...

View Full Version : SELECT WHERE x LIKE row1+row2



soneen
06-23-2010, 08:18 AM
I am trying to figure out how to make a simple query that finds where a variable is LIKE two rows in combination. For example, in my database, I have split names between first and last names. I want to be able to search these names put together. I know this seems like a simple question, but it is VERY difficult to come up with search terms for this problem. (Think about how many results "combining rows mysql" turns up!)

Old Pedant
06-23-2010, 09:31 AM
Well, you could do

SELECT * FROM table WHERE CONCAT(firstName,' ',lastName) = 'John Doe'

(where you would of course put an actual value in place of John Doe).

But...

But what happens if the person who enters the name puts in two space? Or thinks you want it last name first ? Or or or ???

I think a *better* way to do it is to use your server side coding (PHP, ASP, JSP, whatever) to get *separate* names and then do something like this:


SELECT * FROM table WHERE firstname = 'Joe' AND lastname = 'Blow'

If you aren't sure which is the first and which is the last name (though I think you could design your form to ensure that!) then you could even do:


SELECT * FROM table WHERE firstname IN ('John','Doe') AND lastname IN ('John','Doe')


In short, there are indeed many many answers to this question because there are so many variants on the question.

soneen
06-23-2010, 05:32 PM
Perfect, Thank you!

I ignored the possible double space problem, but did see the "last name first" problem as a real killer. I remedied that with this code. Note that this search is dynamic and pops up new results are every letter. It probably would be better to go with Old_Pedant's approach if you are doing an old fashioned search query.


SELECT * FROM table WHERE CONCAT(firstName,' ',lastName) LIKE '%John Doe%'

guelphdad
06-24-2010, 04:53 AM
Using a search term beginning with a wild card means no index will be used, a sure performance killer as your table grows in size.

Old Pedant
06-24-2010, 08:33 AM
Using a search term beginning with a wild card means no index will be used, a sure performance killer as your table grows in size.

Note that this is a MySQL restriction. Not true of all databases.

BUt oh man is it a killer with MySQL. Found that out the hard way a couple of years back. In fact, MySQL isn't very clever, at all, in its use of indexes. *sigh*



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum