Regexp in mysql

Anon

I need to write a query for a search form.

User can limit search by selecting some options from dropdown menus.

E.g. SELECT * FROM tbl WHERE state = 'NY';

User may also choose not to select any options, i.e. do a wildcard search.

In that case, how can I write the query with a WHERE clause, and still be doing a wildcard search.

Would the following be correct in that case:

SELECT * FROM tbl WHERE state LIKE '%';

I know it's easier to just drop the WHERE clause, but there's a reason why I need to have it in the query. Too long to explain.

Thanks.

Richie.

[deleted]

"I know it's easier to just drop the WHERE clause, but there's a reason why I need to have it in the query. Too long to explain."

I guess you need the WHERE to add more conditions later?

You should never put anything in the where that makes no sense, and LIKE '%' will allways be true, so it makes no sense, drop it.
You can use IF statements to find out wether you need to put the LIKE in (if someone selected an option) and if they did not seelcte anything, don't put in the LIKE.

If you need to add more conditions you'll probably have the problem of the AND that comes directly after the WHERE, so just put the AND after the LIKE. No LIKE, no AND. Problem solved.

Anon

Hi Vincent,

You said "LIKE '%' will always be true." Yes you're right, and that's what I want when user has chosen to not limit search by STATE.

I think I should do someting like this:

select * from tbl where state like '$s%'

That way, if user did not select any state, $s will be an empty string, and $s% will be just %.

Does that make sense?

My query looks like this:

select * from tbl where title like '$t%' || descr like '$d%' || city like '$c%' || state like '$s%'|| somethingelse = 'somevar' || ...

Richie.

[deleted]

Remember that LIKE 'hello%' will only match on records where the data begins with 'hello', not data that contains something like 'hi hello'

Also, if the options are always full strings, ie: what the user selects is 100% of what's in the database, then LIKE is not needed:

SELECT * FROM table WHERE title="$t" || bladebla

Also, in a series of ORs, only one condition has to be true to make the end-result true.
So if you want to select all records where state can be anything, you might aswell do

SELECT * FROM table

becuase every record contains a STATE field, and every value of that field is valid, thus all records are valid, no matter how many ORs you put in.

Anon

True $t% matches the beginning only. I'll decide later about the boundaries.

My query is correct with the series of || in it. Basically, I have about 35 columns in the table. I want to do a search on all columns that I include in the query.

I should probably split the table into two or more and then do joins. Unfortunately, I'm still learning my mysql 🙂

Thanks Vincent. You have been of tremendous help.

Richie.

[deleted]

I don't see why you'd want to split it up, having 35 tables in there is no problem. It's just that if one of the search parameters is empty, the rest is irrelevant, so you will have to leave it out.