Search for multiple entries in SQL field

[sonny100]

Hi there,

I am trying to write an SQL command that will search for multiple entries in a column.

Basically, a user will enter several key words into a text box, and the SQL command needs to return the rows where all of those words are found in a column, if that makes sense?

So we have a field in the table called 'Interests' in which would be something like "I like to swim, play football and cycle" and I need to find this row when a user enters "Swim, Football, Cycle" into the search form.

I have tried using the LIKE command, but this only works when one keyword is entered.

Any thoughts?

[dagon]

SELECT foo WHERE Interests LIKE '%$one%' OR Interests LIKE '%$two%' ...

[sonny100]

Just given that a go, and it hasn't worked.

In the search text box I entered "Sport, Art" which are both in the Interests column in one of the rows and it didn't return a result. It will work however if I search for the words individually, just not more than one word at a time.

There is other criteria in the search string too, with the change that you suggested it reads:

$query = "SELECT * from user WHERE sex = '$sex' AND age = '$age' AND location = '$location' AND Interests LIKE '%$interests%' OR Interests LIKE '%$interests%'";

[Bjom]

Sounds like you need a "full text search".

[dagon]

you have to separate the values in ther search box, explode() and add one "Interests LIKE '%$foo%' per word in search text box

[dagon]

Bjom;10923488 wrote:

Sounds like you need a "full text search".

while i love the mysql full text search function, it seems a little extreme in this case.

[sonny100]

If I explode the string to seperate the values, then I would have to have a pre-determined maximum amount of words, wouldnt I?

I don't want to limit the amount of words that a user can search on.

[dagon]

no just loop through the results of the explode array to build the query

[sonny100]

Awesome....erm....you couldn't by chance give me a brief example of what that would look like could you to get me started? 🙂

I've used loops before, but never in this capacity,

Thanks for your help!

[dagon]

$foo='one two three';
$bang=explode(' ',$foo);

foreach ($bang as $word){
$q .=" AND Interests LIKE '%$word%' "; 
}

tehres some issues there you will have to work out, but that should get you started.

[sonny100]

Brilliant, I have that working now! 🙂

Only one small problem, which I can probably figure out a way around, is that if one of the search results matches two of the keywords, it is showing that entry twice in the results table.

Would it just be best to write some sort of validation so that it will only show a result once?

[dagon]

that makes no sense, it the table row matches it should only be returned once.

[sonny100]

That's what I thought! 🙂

But if one keyword in the column is matched, it will return the row once, if two keywords are matched, it will return the row twice, and so on...

Been trying to figure out why, here's the code for the query and to display the results....

$bang = explode(' ',$interests);
foreach ($bang as $word)
{

 echo "<TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0 BORDERCOLOR=#540EAD>";
$query = "SELECT * from user WHERE sex = '$sex' AND age = '$age' AND location = '$location' AND Interests LIKE '%$word%'";
$result = mysql_query($query) or die(mysql_error());
while ($row = mysql_fetch_array($result)) {

$id = $row['ID'];
$photo = $row['photo'];
$username = $row['username'];
$location = $row['location'];
$email = $row['email'];

echo "

        <TR>
		<TD width=75><a href= 'masterdetails.php?iden=$id''><img src=upload_images/{$photo} width=150 height=125 /></a></TD>
		<TD width=300 border=1 border-style=\"solid\" border-color=\"RED\" align=left>$username<p>{$row['age']}<p>$location</TD>
        <TD width=300>Interests<p>{$row['interest1']}<p>{$row['interest2']}<p>{$row['interest3']}</TD>
		</TR>";

}
}
echo "</TABLE>"; 

[dagon]

your not using the foreach loop correctly, you use it to create he query, your running the query once per word.

$bang = explode(' ',$interests);
foreach ($bang as $word){
$q .=" AND Interests LIKE '%$word%' "; 
}

echo "<TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0 BORDERCOLOR=#540EAD>";
$query = "SELECT * from user WHERE sex = '$sex' AND age = '$age' AND location = '$location' $q "; 

[/code]

[sonny100]

Awesome, all working now!