Looping within a loop

Joe_UK

I am writing a search engine and if a user enters more than one keyword I split the keywords into an array... I then have trouble searching individual columns with these keywords, heres what I ahve:

$statement = "SELECT a FROM b WHERE ";
$keyword = "epiphone explorer";
$k = explode(" ",$keyword);
$k2 = count($k);
$colum = "product_code product_design product price ip";
$c = explode(" ",$colum);
while (list(,$ca) = each($c))
{
$i++;
while (list(,$ka) = each($k))
{
$statement .= "$ca LIKE $ka ";
}
if ($i < count($c))
$statement .= " OR ";
}
echo $statement;

It does the first column then stops... does anyone have suggestions for how I could get this to work so it searches every field.

cheers

drawmack

$statement = "SELECT a FROM b WHERE ";
$keyword = "epiphone explorer";
$k = explode(" ",$keyword);
$k2 = count($k);
$colum = "product_code product_design product price ip";
$c = explode(" ",$colum);
$i = 0;
foreach($c as $cur_c)
{
    $i++;
    foreach($k as $cur_k)
    {
        $statement .= "$ca LIKE $ka ";
    } //end foreach
    if ($i < count($c)) {
        $statement .= " OR ";
    } //end if
    $i++;
    echo $statement;
} //end foreach

This is very inefficient. You are executing the if statement n times, where n is the number if items in $c, and you are executing the $statement .= line n*m times where m is the number of entries in $k. This leads to an exponential time growth in processing time, that's not good. You should look for a way to trim this down - here is my solution:

Create a single column that contains the values of all the columns you want to search on. Then use this new conglomo column for searching and the rest for displaying. Then you only have to search a single column and you do away with the inner loop all together.

Joe_UK

Hi drawmack,

really appricate the help I got my loop working

$statement = "SELECT a FROM b WHERE ";
$keyword = "epiphone explorer";
$k = explode(" ",$keyword);
$k2 = count($k);
$colum = "product_code product_design product price ip";
$c = explode(" ",$colum);
while (list(,$ca) = each($c))
{
$i++;
for ($x=1; $x<=count($k); $x++)
{
$x = $x - 1;
$statement .= "$ca LIKE $k[$x] ";
$x = $x +1;
if ($i < count($c))
$statement .= " OR ";
}
}

However as you point out this is inefficient...

The problem lies in this search searches several tables where the values are constantly changing - so putting all the values into one column of one table is near impossible.

Can you think of any alternative way to efficiently searching through these tables - baring in mind the data I'm searching is constantly changing?

Any help would be greatly appriciated.

cheers