How to set up php where statement array()

Question

I want to use php to set dynamic WHERE clause by storing details in an array. But I want to have a default WHERE where it has to check for SchoolId = ?, no matter which option is chosen. My question is where do I store the default WHERE for SchoolId = ? Best to put it straight in the $query or put it in the $where array?

$query = 'SELECT ... FROM ...';

// Initially empty
$where = array();
$parameters = array();

// Check whether a specific student was selected
if($stu !== 'All') {
    $where[] = 'stu = ?';
    $parameters[] = $stu;
}

// Check whether a specific question was selected
// NB: This is not an else if!
if($ques !== 'All') {
    $where[] = 'ques = ?';
    $parameters[] = $ques;
}

// If we added to $where in any of the conditionals, we need a WHERE clause in
// our query
if(!empty($where)) {
    $query .= ' WHERE ' . implode(' AND ', $where);
}

Also to set up the bind_param(), what is the correct set up to include this? Do I need to set them up in the if statements above or include seperate if statements?

Below are the bind params:

$selectedstudentanswerstmt=$mysqli->prepare($selectedstudentanswerqry);
// You only need to call bind_param once
$selectedstudentanswerstmt->bind_param("iii",$_POST["school"],$_POST["student"],$_POST["question"]); 

//$_POST["school"] -- SchoolId parameters
//$_POST["student"] -- StudentId parameters
//$_POST["question"] -- QuestionId parameters

I got the idea from that question but that is not my question. That is somebody else's? Probably somebody is doing the same work — user1723760
– user1723760, Commented Jan 27, 2013 at 14:59

Levi · Accepted Answer · 2013-01-27 21:34:43Z

1

My personal preference is to put the default in the $where array. That way if you ever need to debug or track the values you get a complete look at what is being put into the array.

As far as where to bind the params, you will need to do it after you prepare the query so you will need a second set of if statements after your query is constructed.

// Initially empty
$where = array('SchoolId = ?');
$parameters = array($schoolID);
$parameterTypes = 'i';

// Check whether a specific student was selected
if($stu !== 'All') {
    $where[] = 'stu = ?';
    $parameters[] = $stu;
    $parameterTypes .= 'i';
}

// Check whether a specific question was selected
// NB: This is not an else if!
if($ques !== 'All') {
    $where[] = 'ques = ?';
    $parameters[] = $ques;
    $parameterTypes .= 'i';
}

// If we added to $where in any of the conditionals, we need a WHERE clause in
// our query
if(!empty($where)) {
    $query .= ' WHERE ' . implode(' AND ', $where);
    $selectedstudentanswerstmt=$mysqli->prepare($query);
    // You only need to call bind_param once
    $selectedstudentanswerstmt->bind_param($parameterTypes,implode($parameters));
}

edited Jan 27, 2013 at 21:34

answered Jan 27, 2013 at 14:36

Levi

12.5k14 gold badges47 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user1723760 Over a year ago

so if you look at my code snippet, do I just put underneath $parameters = array(); ... $where[] = '$_POST['school'] = ?'; $parameters[] = $_POST['school']; to set the default up?

user1723760 Over a year ago

Can you show me the second set of if/else statements with comments stating which bind params should go in each. DO i need to just include the three if statements I have and do similar for second set of if/else statements for bind params?

Levi Over a year ago

I edited a code in my answer a tad. After looking at it again I thought it made sense to set the options in the "if" blocks already there. I was't sure the variable name of the school id, but I set it as a default in $parameters() at the top.

Collectives™ on Stack Overflow

How to set up php where statement array()

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related