Php foreach loop concatenation

Question

I am working on a script to loop over what check boxes a user clicks on, depending on which ones were clicked that will result in a "dynamic" query. I am trying to build a where statement with a array to execute in pdo.

The problem I am having is that within the foreach loop I am trying to use

.=

to concatenate a string to build my query. However when a user clicks on the same group of checkboxes like

'why_and'

only one $where will be created so when I print out my query with a var_dump I get this :

SELECT student.anum, 
       student.first, 
       student.last, 
       student.email, 
       session.aidyear, 
       reasons.reason, 
       COUNT(session.anum) as Total, 
       MIN(DATE_FORMAT(session.signintime, '%b %d, %Y - %l:%i %p')) as 'First',      
       MAX(DATE_FORMAT(session.signintime, '%b %d, %Y - %l:%i %p')) as 'Last' 
FROM  
      student INNER JOIN session 
           ON session.anum = student.anum 
      INNER JOIN session_status 
           ON session_status.status = session.status 
      INNER JOIN reasons 
           ON reason_id = session.why 
WHERE 1 AND why = :reason 
GROUP BY session.anum

and then I var_dump the placeholder array and get this :

[":reason"]=> string(9) "4 , 5 , 6" }

Notice how I have three values in the :reason array and only 1 :reason in the query. Is there anything I am missing?

This is a example with my thought process (please take a look at the comments) :

   $placeholder = array();
    $where = "";

if($and !== "") // This is the "super array" if you will 
{
    if(array_key_exists('why_and', $and)) // Now I check for each specific thing a user can search for in the array / database 
    {
       foreach($and as $key => $value) // Take the $value of the array index why_and
       {
           $where .= " AND why = :reason "; // Create a where variable with a named placer holder for each value that exists in the array 
           $placeholder[':reason'] .= rtrim($value, ' ,'); // Then add to the $placeholder array so I can I add it to the PDO execute as an array
       }
    }
    if(array_key_exists('status_and', $and))
    {
       foreach($and as $key => $value)
       {
           $where .= " AND status = :status ";
           $placeholder[':status'] .= rtrim($value, ' ,');
       }
    }
}
$finSQL = $sql . ' WHERE 1 ' . $where; // COncate the final results
$dynamic = $this-> db-> conn_id-> prepare($finSQL); // prepare
$dynamic-> execute($placeholder); // Use the placeholder array and pass that to the execute as a [key => value pair as shown in the manual example number 2][1]

Edit 1 @Your Common Sense

Just to clear things up THIS WORKS but only for one value in each check box category, why_and, status_and but if I try to do more then one value from the same category then it does not work.

When posting a question on a complex dynamical query (or rather when starting with this query) it is **essential** to write this query **by hand** first, then try to run it, and then go for creating it dynamically only if it runs. — Your Common Sense, May 23 '13 at 19:08
I updated my OP, I have written it by hand first, and it works just fine with only one value from one category — user2406611, May 23 '13 at 19:11
You know, if you need a query that works with multiple categories, you have to write it by hand and test it first as well — Your Common Sense, May 23 '13 at 19:12
This is on my live system where I am able to run very basic and queries with upwards of 1000 unique clients. However I need to add more AND's from the same category and for that is what I am having a issue with — user2406611, May 23 '13 at 19:13
@YourCommonSense Not trying to annoy you man, but I'm confused? I have written the query with all information needed, I now need to add dynamic capabilities to the $where variable. I am confused - Sorry — user2406611, May 23 '13 at 19:15
Before adding whatever capabilities dynamically, you have to write them all *statically*. To create a working model, a working example. It is essential thing. You need to see first, if your dynamically built query ever works (even being written statically by hand). — Your Common Sense, May 23 '13 at 19:24
I think I figured it out... you can't have the same place holder :reason with two different values? I would have to have :reason then :reason1 then :reason2 and then values 1,2,3 corresponding to the index name / place holder name? Just a hypothesis, I am working building it right now. — user2406611, May 23 '13 at 19:31
Yes, you can't. If only you were using OR instead of AND for your statuses, it would make things much easier, by implementing just IN(1,2,3). But with ANDs dynamical query building is the only choice — Your Common Sense, May 23 '13 at 19:40
Alright, I'm already looking at creating a ++ or +1 "thingy" for each placeholder, I'm confident I can do it in a foreach. — user2406611, May 23 '13 at 19:49

Php foreach loop concatenation

0 Answers0