Hi

Can anybody tell me if it's possible to run a php loop inside a mysql query please? It seems quite a logical thing to do but i can not get it to work - somthing along the lines of this:

PHP Code:

$query="SELECT * FROM table WHERE ".
$e='0'; 
while ($e< 5)
{
."column='$e' and".
$e++;
}
." id>'0'"; 


Thanks

How are you going to run PHP inside a SQL query????

SQL servers don't "do" PHP.

You have to build the query then send it, if you need a loop in a query it will have to be coded in a stored procedure (MySQL 5+) and call that.

in your code you have a few problems

$e is declared as a string, incrementing a string is not going to have the effect you would expect.
You have no space in your concatenated code so it would end up as
... WHERE column='0' andcolumn='1' andcolumn='5' andcolumn='3' andcolumn='4' andcolumn='5' and id>'0'
Which as you can tell would raise an error

not 100% sure what you are trying to achieve,
but it seems to me that;

select fieldlist from table WHERE column <= 5 and id = 0 ;

would do exactly what the code above is attempting

sorry, bad example of what im trying to achieve.

I have a form field where a user can enter more than one search term if
desired, seperated by commas.

I need to run a sql query which will loop a "column LIKE '%$term%' OR" for the number of search terms.

The query would look something like this:

PHP Code:

$querystart="SELECT * FROM table WHERE ". 
$soundslik = explode(",", $soundslike_master); 
foreach($soundslik as $el => $soundslik) 
{  
  $term = str_replace(" ", "_", trim($soundslik)); ."bandname LIKE '%$term%' or".  
el++; 
} 

."some other search criteria"; 


Any Ideas?

That should build the query ok (didn't test it)

you will need a space before "bandname" and after "or" in the concatenation. ( ." bandname LIKE '%$term%' or ". )

And be aware of the underscore ("_") being a single character wildcard when using LIKE queries.

duplicate

For some reason it trips up at '.' when i output the resluts of my loop in the if statement shown below. I cant figure out why. I have tied alot of variations on this and get an error either at the { or at one of the '.' s.

Confused

PHP Code:

   $querystart="SELECT * FROM band WHERE ".
  $soundslik = explode(",", $soundslike_master);
  foreach($soundslik as $el => $soundslik)
  { 
    $term = str_replace("_", " ", trim($soundslik));
    if($el=='0'){  ." bandname LIKE '%$term%' ".}
    else        {."OR bandname LIKE '%$term%' ".}
  }  
.""; 


keep echoing out the SQL string while it is being concatenated so you can see what is happening

//Try this...


$querystart="SELECT * FROM band WHERE ";

$soundslik = explode(",", $soundslike_master);
foreach($soundslik as $el => $soundslik)
{

$term = str_replace("_", " ", trim($soundslik));
if($el=='0')
{

$querystart.=" bandname LIKE '%$term%' ";

}
else
{

$querystart.="OR bandname LIKE '%$term%' ";

}

}
$result=mysql_query($querystart);