Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Search on name using LIKE statement

 
Jeppe Sommer
Ranch Hand
Posts: 270
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello,

I have a search textfield on my website and want to be able to search on peoples firstname and lastname stored in the database. I have a database record for firstname and one for lastname.

If I type in the name 'Peter Anderson' and use a SQL statement like the below:

SELECT * FROM customer WHERE firstname LIKE '%firstname%' OR lastname LIKE '%lastname%'

- then I won�t get any results as Peter doesn�t exist as a lastname and Anderson doesn�t exist as firstname...

So what can I do?
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

I have a database record for firstname and one for lastname.

Do you mean you have a record with a field for first name and a field for last name? Or do you use two records?


If I type in the name 'Peter Anderson' and use a SQL statement like the below:

SELECT * FROM customer WHERE firstname LIKE '%firstname%' OR lastname LIKE '%lastname%'

- then I won�t get any results as Peter doesn�t exist as a lastname and Anderson doesn�t exist as firstname...

I'm not sure what you are asking here. If 'Peter' doesn't exist as a lastname then you wouldn't expect any results if you search for it? Or am I missing something?
[ August 08, 2007: Message edited by: Paul Sturrock ]
 
Jeppe Sommer
Ranch Hand
Posts: 270
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Paul Sturrock:

Do you mean you have a record with a field for first name and a field for last name? Or do you use two records?


Yes I was unclear. I have a record with a field for first name and a field for last name.



If I type in the name 'Peter Anderson' and use a SQL statement like the below:

SELECT * FROM customer WHERE firstname LIKE '%firstname%' OR lastname LIKE '%lastname%'

- then I won�t get any results as Peter doesn�t exist as a lastname and Anderson doesn�t exist as firstname...
[/code]
I'm not sure what you are asking here. If 'Peter' doesn't exist as a lastname then you wouldn't expect any results if you search for it? Or am I missing something?[/QB]

Lets say Peter Anderson is stored in the database. And I just have one textfield in the webform. If I "think" there is a person called Peter Anderson, then how can I search on his name and get information on him if I type in the whole name (Peter Anderson)?
[ August 08, 2007: Message edited by: Jeppe Fjord ]
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you have two fields in the database does it not make more sense to have two input fields in your GUI? That way you know the string "Peter" represents a first name, so you can easily do this:

If you've got one input you will need to first try to work out which part of its input corresponds to which field. So its easy if you have "Peter Anderson" ; you just split the string. But what if it just contains "Anderson"? Is that a first name or a last name? Or what if it is "Jon Dahl Tomasson"? If the firstname double-barrelled or the lastname? Or what if you have an Chinese name, where the last name comes first?

If you have defined attributes, it makes like much easier to tailor your GUI to match them. If you are trying to do "free text" searching (sort of google-like behaviour) you probably don't want to use the database directly, rather an IR tool like Lucene.
 
Jeppe Sommer
Ranch Hand
Posts: 270
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Okay, thanks for your reply. I got it :-)

The topics you just mentioned was just what I hoped could be solved somehow, but I see they just can�t without making several input fields.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic