mysql string contained within a search string

Question

What I need seems simple, but I haven't been able to pull it off so far. Maybe it's just these late hours, or maybe it's not that simple after all, I don't know any more :) So, here's the thing. I want to be able to check whether the search string from my site contains any of the fields from a particular column in my database. So, it would be the opposite of the usual one:

mysql_query("  
SELECT *  
FROM `table`  
WHERE `column` LIKE '%{$search}%'  
");

which looks for the fields with values where the search string is contained.
What would be the easiest way, using some regular expressions or...?

Thanks a bunch!

Danosaure · Accepted Answer · 2010-12-14 05:37:40Z

3

Just do it the other way around

SELECT *  
FROM `table`  
WHERE '{$search}' LIKE concat('%', `column`, '%')

bear with me for the proper syntax for variable escaping for SQL-injection.

answered Dec 14, 2010 at 5:37

Danosaure

3,6654 gold badges30 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Spaceploit Over a year ago

hmm, this makes sense, but it didnt work for me...no errors, just didnt filter the right ones out.

Danosaure Over a year ago

@spaceploitator: Hum.. would need to show table definition, few entries and expected results then.

Spaceploit Over a year ago

the column field type is varchar in latin1_swedish_ci, with all the entries actually being 5 digit numbers. so i'd like to match a '12345' entry if i search for '123456'.

Danosaure Over a year ago

@spaceploitator: select '123456' like concat('%', '12345', '%'); yield true, so maybe the syntax/escape of '{$search}' isn't correct?

Spaceploit Over a year ago

yes, you were right, i was doing something wrong. thanks a lot! im sorry i cant give you a 1up :)

Chandresh M · Accepted Answer · 2010-12-14 05:00:53Z

0

your query is also good as you want. but still you can try this.

You can search your string by using MATCH() AGAINST() statement in mysql.

mysql_query("SELECT * FROM table_name WHERE MATCH(field11,field12) AGAINST ('searchstring')"); 


Here your field must be having Fulltext datatype.

This may work good.

Thanks.

answered Dec 14, 2010 at 5:00

Chandresh M

3,4932 gold badges27 silver badges48 bronze badges

1 Comment

Spaceploit Over a year ago

hi chandresh, yes, i was familiar with match against, but it wont work for me cos of the datatype. thanks anyway!

Collectives™ on Stack Overflow

mysql string contained within a search string

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related