Help with LIKE Search

[limitphp]

select * from artist where artistName LIKE '%es%'

 

The above query will return all artists that contain "es" in their name.  A whole bunch, basically.

 

How would I modify the search to just show me artists that contain the word es in their name?

 

Lets say there are two bands with weird names like "ES Buzz" and "Through ES".

 

 

[revraz]

Well if you want to to Start or End with ES, you can do es% and %es.

[limitphp]

Well if you want to to Start or End with ES, you can do es% and %es.

 

but then I would have to do 2 different searches to get the above results.  PLus I would get alot of irrelevant results like strokes, fires, etc

[dennismonsewicz]

try this

 

select * from artist where artistName LIKE '%es' OR artistName LIKE 'es%'

[limitphp]

try this

 

select * from artist where artistName LIKE '%es' OR artistName LIKE 'es%'

 

isn't that the same as LIKE '%es%'?

[revraz]

So you want to have only things with ES (space) and (space) ES returned?

 

But no, you wouldn't have to do two different searches, it can be done with 1 query, that's why they make AND and OR

[revraz]

No, but it would return what you said above, with things that end with es and start with es.  %es% would return even if in the middle.

 

try this

 

select * from artist where artistName LIKE '%es' OR artistName LIKE 'es%'

 

isn't that the same as LIKE '%es%'?

[limitphp]

So you want to have only things with ES (space) and (space) ES returned?

 

Yes.  I want all results that contain the word "es".

 

I tried doing a LIKE '% es %'

 

but if the artistName is "ES Buzz", it won't return it, because there is no space before es.

[limitphp]

No, but it would return what you said above, with things that end with es and start with es.  %es% would return even if in the middle.

 

Oh yeah...true.

Again, though.  It would return irrelevant results like strokes, fires, etc.

 

I tried LIKE 'es'

without the %, but it didn't seem to work either.

[revraz]

select * from artist where artistName LIKE '%es ' OR artistName LIKE ' es%'

[limitphp]

Basically, what I want to do is search a column that contains an exact word, or contains the exact word plus other words.

[revraz]

Try what I posted.

[limitphp]

select * from artist where artistName LIKE '%es ' OR artistName LIKE ' es%'

 

Try what I posted.

 

ok.  I'm not getting any results now. 

[revraz]

You need to change it to adjust for your table and fieldnames.

 

Post your actual code.

[limitphp]

select * from artist where artistName LIKE '%es ' OR artistName LIKE ' es%'

 

Post your actual code.

 

I'm actually doing it in the phpadmin.  i'm testing the sql.

here's what I'm typing in:

select artistName from artist where artistName LIKE '%es ' OR artistName LIKE ' es%'

 

I added "ES Buzz" and "Through ES" to the artist table.

 

still getting no results.

 

Problem is, just looking at the code, I assume it will give me anything that ends with es or anything that begins with es.

 

What I want is anything that contains exactly the query word or contains exactly the query word plus other words.

 

[limitphp]

I guess I could word it a different way....

What I want is to do this:

select * from artist where artistName = "$word"

 

only I want all of those results from above, plus any results that contain the $word plus other words.

[limitphp]

Maybe its not a LIKE operator that I need.

 

As, LIKE suggests, doing just that...finding anything that is like the word.

 

Are there only =, like and match to use for searching?

[premiso]

It is hard to determine that but:

 

select artistName from artist where artistName LIKE 'es ' OR artistName LIKE ' es' 

 

Would/should work for just the ES, the % (wild) card is optional.

[limitphp]

It is hard to determine that but:

 

select artistName from artist where artistName LIKE 'es ' OR artistName LIKE ' es' 

 

Would/should work for just the ES, the % (wild) card is optional.

 

Problem with that is, lets say they search for a band named Watercolors, with that search it looks for watercolors with a space after it, and watercolors with a space before it.

 

Well, in the database, artistName for the band watercolors is not going to have any spaces, just the word Watercolors, so it actually doesn't return that record.

 

 

[revraz]

I've already showed you how to do it, so I'm not sure what your current question is now.

[limitphp]

I've already showed you how to do it, so I'm not sure what your current question is now.

 

One of us has misunderstood something here.

I'm trying to do a query that searches for a $word and matches records containing exactly that $word or records containing exactly that word plus other words.

 

I have yet to see any post in here that shows me how to do that.  What I'm gathering here, is that a simple query that uses LIKE cannot do that.  Hopefully, I'm wrong about that (I'm pretty much a newbie when it comes to MYSQL), but I'm starting to think that is the case.

[revraz]

How is this not working?

 

select * from artist where artistName LIKE '%es ' OR artistName LIKE ' es%'

[limitphp]

How is this not working?

 

select * from artist where artistName LIKE '%es ' OR artistName LIKE ' es%'

 

If someone were to type in "Watercolors", it will not return the band Watercolors because in the artistName field for Watercolors there is no space after Watercolors and there is no space before Watercolors.

 

Basically, that query would fail to return all results of bands where artistname contained only 1 word.

[revraz]

select * from artist where artistName = '$name' OR LIKE '%{$name} ' OR artistName LIKE ' {$name}%'

 

Something like that may work.

 

 

[limitphp]

select * from artist where artistName = '$name' OR LIKE '%{$name} ' OR artistName LIKE ' {$name}%'

 

Something like that may work.

 

 

that doesn't seem to work either.

What exactly are the braces supposed to do?