• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 616
  • Last Modified:

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



0
Pedro Chagas
Asked:
Pedro Chagas
  • 5
  • 3
  • 2
3 Solutions
 
Kevin CrossChief Technology OfficerCommented:
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
0
 
Pedro ChagasWebmasterAuthor Commented:
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

0
 
Kevin CrossChief Technology OfficerCommented:
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.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Ray PaseurCommented:
Not sure of the syntax when you have a lot of different substrings and wildcards, but this page might be helpful.
http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html

I'll try to expand on this test a little bit and see if I can show you the exact syntax to use.  Not sure whether you  want disjunctive or conjunctive tests, but that's up to you to choose based on your business needs.
<?php // RAY_temp_joao.php
error_reporting(E_ALL);

// THE TEST DATA HAS WORDS SEPARATED BY BLANKS
$str = "word hello wave sun dream guitar computer something";

// MAKE AN ARRAY OF THE TEST DATA
$arr = explode(' ', trim($str));

// TRANSFORM THE WORDS INTO SQL WILD-CARD SELECTORS
foreach ($arr as $val)
{
    $new[] = "'%" . $val . "%'";
}

// CREATE THE CLAUSE
$whr = '(' . implode(' OR ', $new) . ')';

// SHOW THE WORK PRODUCT
echo "WHERE url LIKE $whr";

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
Ray, would that not have to be:
implode(' OR url LIKE ', $new)
??
0
 
Ray PaseurCommented:
@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.
0
 
Kevin CrossChief Technology OfficerCommented:
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*
0
 
Pedro ChagasWebmasterAuthor Commented:
I'm *laughing* too! The way I write php is extremely sloppy, but I am improving every day. Now I create my own library, every time I learn something new, go directly to my library, I don't want depend from EE, EE is just a good help.

About this solution, the code in snippet code work well with the last solution of Ray and complement with your post 37039280!
 
<?php // RAY_temp_joao.php
include("../../database.php");
error_reporting(E_ALL);

// THE TEST DATA HAS WORDS SEPARATED BY BLANKS
$str = "casa estrume";

// MAKE AN ARRAY OF THE TEST DATA
$arr = explode(' ', trim($str));

// TRANSFORM THE WORDS INTO SQL WILD-CARD SELECTORS
foreach ($arr as $val)
{
    $new[] = "'%" . $val . "%'";
}

// CREATE THE CLAUSE
$whr = implode(' OR teste1 LIKE ', $new);

// SHOW THE WORK PRODUCT
echo $search = "WHERE teste1 LIKE $whr";

$teste_result = mysql_query("SELECT * FROM a2f228e $search ", $db);
$teste_rows = mysql_num_rows($teste_result);
while($teste = mysql_fetch_object($teste_result)){
    echo $teste->teste1; }
?>

Open in new window

Thanks for both!

The best regards, JC
0
 
Pedro ChagasWebmasterAuthor Commented:
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.
0
 
Kevin CrossChief Technology OfficerCommented:
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
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now