[SOLVED] Run loop in SQL query

[JayVee]

I want to add array values to a sql query by using a loop but I'm unsure of the syntax.

 

This is my array.

if (array_key_exists('box', $_POST)) {

  $box1 = $_POST["box"];

  }

 

$myarray = $_POST['box'];

 

I've echoed out the values so I know they are being passed correctly.

 

Here's where I'm going wrong somewhere.

 

$query_Recordset1 = "SELECT DISTINCT `id` ,`field1` , `field2` FROM `SomeTbl` WHERE "foreach ($box as $suggestion) {

      "field1` LIKE '%'.$suggestion.'%' OR field2` LIKE '%'.$suggestion.'%' AND";

  }" LIMIT 0 , 30";

 

Also, I've seen a bug with this as it will leave an extra "AND" in the statement. Can I remove this?

I know its probably riddled with errors but any help would be appreciated.

 

 

[discomatt]

I dont think you can throw a loop statement in the middle of a string definition...

[JayVee]

How would I go about rewriting that query so that I could loop in my values?

[discomatt]

I would do it like this

 

<?php

foreach ( $box as $suggestion ) {
$where[] = "`field1` LIKE '%$suggestion%' OR `field2` LIKE '%$suggestion%'";
}

$query = "SELECT DISTINCT `id` ,`field1` , `field2` FROM `SomeTbl` WHERE " .
implode( ' AND ', $where ) .
" LIMIT 0 , 30";

echo $query;

?>

[lastdino]

if the case about "editing the loop results" that contains extra "AND" ...

you can do the loop in separate lines to insure it is well made then later on embed it in the sql..i would try the following...

 

 

<?php

$sqlwhere=$sqland="";

foreach ($box as $suggestion) { 
$sqlwhere .= $sqland." field1` LIKE '%'.$suggestion.'%' OR field2` LIKE '%'.$suggestion.'%' ";
$sqland=" AND ";
//it will assign ANDs if it loops more than once!
}


$query_Recordset1 = "SELECT DISTINCT `id` ,`field1` , `field2` FROM `SomeTbl` WHERE ". $sqlwhere." LIMIT 0 , 30"; 

?>

[JayVee]

Thanks for the replies. I used discomatts code and got it to work. Both where a great help, I owe ya, thanks.