Mysql SELECT help please

Question

Hi.

I have a querystring that contains a search string entered by the user.

I want to search 2 fields in my table (description and headline) for the querystring..

Simply, I want to check both fields if any of the, OR BOTH, contains the querystring...

I am using PHP by the way...

     SELECT * FROM db WHERE xxxxxxxxxxxxxxxxxxx?

Thanks for all help...

OK, but there is a problem... When i try the simple query SELECT * FROM db WHERE description LIKE '$variable' OR headline LIKE '$variable'

PROBLEM is that it wants the field to be EXACTLY the same as the string value, why?

Thanks

Answer 1

you can either create a fulltext index over both columns and then use MATCH(description, headline) AGAINST('searchstring') command of mysql or you could do a simple LIKE query:

SELECT *
  FROM db
 WHERE description LIKE '%searchstring%'
    OR headline LIKE '%searchstring%'

Answer 2

The simplest solution you can apply is pattern matching using LIKE.

SELECT * FROM db WHERE description LIKE "%querystring%" OR headline LIKE "%querystring%"

Check this out for more on that:

http://www.webdevelopersnotes.com/tutorials/sql/mysql%5Freference%5Fguide%5Fpattern%5Fmatching%5Fwith%5Ftext%5Fdata.php3

There's also many third-party tools dedicated just to searching text in a database for you. If your queries will be heavy duty, you're better off using one of those solutions.

And the last thing to make sure of is that you sanitize the query string before you use it in the query such that a malicious user can't run SQL commands that you didn't intend.

Answer 3

Select * from mytable where column a = 'querystring' or columnb = 'querystring'

for either or if you want both to have it then

Select * from mytable where column a = 'querystring' and columnb = 'querystring'

or use like if you don't want an exact match

Select * from mytable where column a like '%querystring%' or columnb = '%querystring%'

Not sure if % is wildcard for mysql

Answer 4

SELECT * FROM db WHERE ( description LIKE '%querystring%' OR headline LIKE '%querystring%' )

If you are not using prepared statements, make sure you escape the user input with mysql_real_escape_string()

This method will become increasingly slow with large data sets however.

If your data set becomes large and your field types are text consider using full text indexing and

MATCH

My experience is that MATCH can be amazingly fast even on large data sets.

Answer 5

If you want to kill you database, the best practice is to do a LIKE '%SOME_STRING%' on a large text (description, etc.).

If you use MyISAM tables, you better have to use FULLTEXT search in boolean mode (It is really easy).