[RESOLVED] trying to create dynamic query using array

mellow

I am trying to build a dynamic query based on user choices from a checkbox form. The user can select up to 12 boxes.

My problem. How can I build a query based on how many selections they make.

Basic query says:
$sql = 'SELECT number, name, notes, retail FROM attachment_options WHERE attachment_id ='.$attn[0];

My array values are $attn[X] and I can echo all of them chosen.

How can i build the $sql to add AND '.$attn[1].' AND '.$attn[2].'....etc for however many the user chooses? I know there must be way to incorporate a for() or foreach() statement into the query but I have not been able to find the correct syntax for such a situation.

I know I could do a series of if statements based on the count($attn) but there must be a more efficient way. Here is one thing i have tried that must be close:
$countatt=count($attn);
if ($countatt==1)
$sql = 'SELECT number, name, notes, retail FROM attachment_options WHERE attachment_id ='.$attn[0];
elseif($countatt>1)
{
for ($i=1;$i<$countatt;$i++)
{
$value=$attn[$i];
$string="AND '.$value.'";
}
$sql = 'SELECT number, name, notes, retail FROM attachment_options WHERE attachment_id ='.$attn[0].$string;
}
echo "$sql<br>";
$result = mysql_query($sql) or die ("I cannot do query because" . mysql_error());

When I try this code, $sql will not echo(when count is >1) and die error says query was empty.

I know the PHP geniuses here know how to do this. I would appreciate any help here. Thanks.

bpat1434

foreach() would be the best approach (as you stated), and you'd just have to concatenate it properly.

$query = "SELECT number, name, notes, retail
FROM `attachment_options`
WHERE (";
$max = count($attn)-1;
foreach($attn as $key=>$val)
{
  $query .= "attachment_id='$val'";
  if($key<$max){ $query .= " OR "; }
  if($key==$max){ $query .= ")"; }
}

// Always echo the query before running it just to make sure it's set up properly:
echo $query;

mellow

WOW! That worked perfect! I am impressed. I read the PHP5/MySql Bible but it didn't have anything like that in it. Neat example of concatenating a query. I hope I can figure out how to do this for similar situations.

Just a question though. I usually single-quote queries in PHP unless I would use mysql_query(). Did you use double quotes because there has to be a substring in the query in singles?

Also thanks for correcting me on the OR instead of AND. I sometimes have trouble remembering which to use in certain situations.

bpat1434

I usually use double quotes on all queries, that way I can reference variables without escaping the string. And, it's easier for me to tell at a glance what it is I'm looking at.

Double quotes = Query
Single quotes = Anything else

Well, you should be able to adapt this in a myriad of ways. It's a simple loop... nothing more. The hardest part was figuring the commas and parenthesis if() statements. But really, the only reason I used parenthesis was to emphasize that it can be true in ANY or ALL of those circumstances.

Easiest way I've found to decide between OR and AND is this:

	Will both (or all) statements need 
		to evaluate to true?
			|
		+-------+-------+
		|		|
	       Yes	        No
	        |		|
	     -------	     -------
	     Use AND	     Use  OR

Hope that helps you....

mellow

Yes, it does. I really appreciate the pointers. The other thing I learned was--I am familiar with concatenating but didn't realize I could use a dot after the variable name to add to it rather than resetting it.

Much obliged.

bpat1434

yes, .= means append string to the end of the variable....