SQL & LIKE in more detail

[Rulkster]

I deleted my previous post, i actually want to get more detailed in my problem i am having here.

I have a string that contains a list, each item of the list is seperated by a # sign. so my string looks like this:

$fruitstring = "apple#orange#cherrie#bannana#grape#";

now i want to perform a query on a table so if just one of those fruits is there, it will be selected. When i do this:

$sql = "select * from mytable where field1 like \"$fruitstring\" ";

it is looking for that whole string. How do i get it to select if field1 contains grape in one record, field1 in another recored is cherrie, and etc.....

Now i cant manipulate $fruitstring because it is actually pulling that from a different table so i am stuck with this string with # sign separators.

Please help , thanks

[barand]

You need to manipulate $fruitsring so that it looks like

$fruitstring = "('apple','orange','cherrie','bannana','grape')";

Function like [man]explode[/man], [man]foreach[/man] and [man]join[/man] should come in very useful here.

Your sql the becomes

$sql = "SELECT * FROM mytable WHERE field1 IN $fruitstring";

hth

[Rulkster]

thanks for your reply, but i solved it, i dont know if this is the most effecient way but here is what i did

// separate each element

$fruit_choices= explode ("#", $fruitstring);
$j = count($fruit_choices) -2;
$tstring .= "&& (";

for ($i=0; $i<=$j; $i++)
{
$tstring .= "text LIKE \"%$fruit_choices[$i]%\" ";
if ($i != $j) { $tstring .=" || "; }
}
$tstring .=")";

}

so it is actually building the part after the WHERE clause in the sql statement. Then i just do this:

$sql= "Select * from mytable where $tstring";

i didnt try your solution cause i didnt see it till after i got it to work so i dont know if your solution would work or not (for anyone else out there looking at this post)

Thanks again for your time and interest.

[barand]

you need to rearrange the code slightly to get your ")" in the right places.

I had in mind

$fruitstring = "apple#orange#cherry#banana#grape"; 
$fruit_choices= explode ("#", $fruitstring); 
foreach ($fruit_choices as $k=>$f) {$fruit_choices[$k] = "'".$f."'";};
$sql = "select * from mytable where text in (" . join(",",$fruit_choices) . ")";
echo "<br>$sql";

which gives

select * from mytable where text in ('apple','orange','cherry','banana','grape')