asked on

Mysql - Search in field for different words

Hi E's, if I want to search in my data base in fields with one word, I use the code in snippet code:

$search_result = mysql_query("SELECT * FROM tutorial where url LIKE '%something%' order by id asc", $db);
$search_rows = mysql_num_rows($search_result);
$search = mysql_fetch_object($search_result)){

Open in new window

The code above work just to find one word in some field, in this case I get the rows that the field "url" contain the word "something" in my table "tutorial".

What I want to know is how I do for search with (more) then one word?
I need change my code for two objectives, first, all the words required:
Ex: Field Contain (word hello wave sun dream guitar computer something).
if in Select I write "word+guitar" I get the row!
if in Select I write "sounds+blue+dream I don't get the row, because all words are required.

The second is:
if in Select I write "sounds+blue+dream I get the row because not all words are required!

What changes I have to do in Select for the two examples (AND / OR)?

Regards, JC

Kevin Cross

If you are doing this manually, then you will need to add AND for + and OR for cases where any of the words is fine. e.g., url LIKE '%word%' AND url LIKE '%guitar%'
It sounds like though, you are trying to build fulltext search: http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

Pedro Chagas

ASKER

Hi @mwvisa1, please check the snippet code and tell me if Select line is correct!
But is not possible do that dynamic, because can be 2 words or more...?
Is possible put a variable like this inside Select?
$string = "url LIKE '%something%' and url LIKE '%other_word%'";

Regards, JC

$search_result = mysql_query("SELECT * FROM tutorial where url LIKE '%something%' and url LIKE '%other_word%' order by id asc", $db);
$search_rows = mysql_num_rows($search_result);
$search = mysql_fetch_object($search_result)){

Open in new window

Kevin Cross

Yes. You can put $keyword1 and $keyword2... You can typically embed this, but you can also concatenate Using dots as in "select '".$variable."' from x;" for example.
It can be made more dynamic by taking one string into a full text search as indicated above and demonstrated in the manual page I sent. Additionally, since you are in PHP, there is nothing stopping you from having the content in an array for example and then using a loop dynamically build the and URL like conditions by concatenating to same string.

ASKER CERTIFIED SOLUTION

Ray Paseur

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Kevin Cross

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ray Paseur

@mwvisa1: Maybe. I haven't tested it in query syntax yet. I was just looking for a generalized way to get the string into something that might be constructed into a WHERE clause.

Kevin Cross

Figured. It was a good example of what I meant by looping through an array. Glad you joined. No one at EE wants to see my PHP syntax unleashed. *laughing*

SOLUTION

Pedro Chagas

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Pedro Chagas

ASKER

I accept my solution, because I thing in the others users of EE. If I accept directly the solution of Ray, the next customer not will see the correct answer. In this way who will see, see the correct answer directly.

Kevin Cross

Glad that worked for you! Just remember, OR is for searches of any word and AND is for all words.
Best regards and happy coding,

Kevin