• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Search on name using LIKE statement

 
Ranch Hand
Posts: 270
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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?
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
I am mighty! And this is a mighty small ad:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic