View Full Version : How do I get a different result for the first key in a foreach() ?

03-12-2009, 11:59 PM
I built a form with category CB's from which I want to build the WHERE clause from my SELECT statement.

<label>Diodes </label><input type="checkbox" name="cat[]" value="diode" />
<label>Resistors </label><input type="checkbox" name="cat[]" value="res" />
<label>etc.. </label><input type="checkbox" name="cat[]" value="blah" />

After I capture the array from the $_POST, if I do

foreach($catArray as $var){
$where="WHERE field=$var"
}I would get a bunch of standalone WHERE clauses instead of getting Where x=y OR x=z.

But if I do

foreach($catArray as $var){
$where="OR field=$var"
}I don't get the initial WHERE.

How do I make it do something for the first key that is different from all/any others?

~ Mo

03-13-2009, 12:03 AM
You can use a little trick: Add "WHERE 1" to the base query. This WHERE condition will always evaluate to "true" (1 represents true) and then you are free to add additional WHERE clauses down the line without worrying about the initial WHERE.

03-13-2009, 12:15 AM
.. use a little trick: Add "WHERE 1" to the base query...

I'm having a little trouble visualizing it.

Can you show an example?

~ Mo

03-13-2009, 12:28 AM
I think I'm getting it.

$where="WHERE 1";

foreach($catArray as $var){
$where.="OR field=$var"
Is this what you mean?

~ Mo

03-13-2009, 01:01 AM
The "WHERE 1" would be included in the query no matter what, so you could just add it to the base query, ie SELECT * FROM tablename WHERE 1 and then you proceed to add conditions to this base query as needed.

But on second thought, this trick is not going to work with "OR" conditions. Every row will be returned because your first condition (WHERE 1) will always be true. I've just used this trick to add "AND" conditions to a query, and looking at your application, I really think you want to be using "AND" conditions. Right? A user checks a few options and for each option you want to further filter the query?

03-13-2009, 01:30 AM
NOTE/FYI: this is being used to build a custom function

... I really think you want to be using "AND" ...
... user checks a few options, and for each option, you want to further filter the query?
Not this time, unfortunately.

I need the results to show all the parts in any selected categories.

For example, all "diodes" and all "resistors".
WHERE category="diode" OR category="resistor"

Any thoughts?

~ Mo

03-13-2009, 06:10 AM
In this case, I would build an "IN" condition statement.

SELECT * FROM table1
WHERE category IN ('diode', 'resistor', 'etc', 'etc2')

With this strategy you just have to build the list of values, enclosed in quotes, separated by commas. You already have an array at your disposal, so just use explode on it to add the commas, oh but before that, loop through the array to add the quotes.

foreach ($catArray as $key => $val) {
$catArray[$key] == "'".$val."'";
$inString = explode(",", $catArray);

03-13-2009, 09:54 PM

I never heard of the "IN" condition. That looks perfect.
I'll look into it and give it a try. Thanks.

~ Mo