Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

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.

share|improve this question
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 10 hours ago
I updated my OP, I have written it by hand first, and it works just fine with only one value from one category – user2406611 10 hours ago
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 10 hours ago
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 10 hours ago
@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 10 hours ago
show 4 more comments

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.