Geek Answers Handbook

Is there a shorter way to tune queries when filtering results?

I have two drop-down menus in which the user can use the drop-down menus to filter students and questions that they want to see. Possible types of filters:

  • Select all students and all questions
  • Select all students and one question.
  • Select all questions and one student
  • Choose one student and one question.

Below are the drop down menus:

<p> <strong>Student:</strong> <select name="student" id="studentsDrop"> <option value="All">All</option> <?php while ( $currentstudentstmt->fetch() ) { $stu = $dbStudentId; if(isset($_POST["student"]) && $stu == $_POST["student"]) echo "<option selected='selected' value='$stu'>" . $dbStudentAlias . " - " . $dbStudentForename . " " . $dbStudentSurname . "</option>" . PHP_EOL; else echo "<option value='$stu'>" . $dbStudentAlias . " - " . $dbStudentForename . " " . $dbStudentSurname . "</option>" . PHP_EOL; } ?> </select> </p> <p> <strong>Question:</strong> <select name="question" id="questionsDrop"> <option value="All">All</option> <?php while ( $questionsstmt->fetch() ) { $ques = $dbQuestionId; if(isset($_POST["question"]) && $ques == $_POST["question"]) echo "<option selected='selected' value='$ques'>" . $dbQuestionNo . "</option>" . PHP_EOL; else echo "<option value='$ques'>" . $dbQuestionNo . "</option>" . PHP_EOL; } ?> </select> </p>

Now I want to configure a mysqli query that identifies students and questions selected from the drop-down menu.

My question is simply that I need to configure 4 queries, checking the 4 possibilities that I mentioned in the drop-down menus, or is there a shorter way?

Should I use:

  if ($_POST['question'] == 'All' && if ($_POST['student'] == 'All'){){ //NO WHERE CLAUSE if ($_POST['question'] == 'All' && if ($_POST['student'] != 'All'){){ //WHERE CLAUSE FOR FINDING SELECTED STUDENT if ($_POST['question'] != 'All' && if ($_POST['student'] == 'All'){){ //WHERE CLAUSE FOR FINDING SELECTED QUESTION if ($_POST['question'] != 'All' && if ($_POST['student'] != 'All'){){ //WHERE CLAUSE FOR FINDING SELECTED QUESTION AND SELECTED STUDENT

UPDATE:

What I have at the moment:

  function AssessmentIsSubbmitted() { if(isset($_POST['answerSubmit'])) // we have subbmited the first form { //QUERY 1: Student details depending on selected student(s) if ($_POST['student'] == 'All'){ $selectedstudentqry = " SELECT StudentAlias, StudentForename, StudentSurname FROM Student s INNER JOIN Student_Session ss ON s.StudentId = ss.StudentId WHERE SessionId = ? ORDER BY StudentAlias "; global $mysqli; $selectedstudentstmt=$mysqli->prepare($selectedstudentqry); // You only need to call bind_param once $selectedstudentstmt->bind_param("i",$_POST["session"]); // get result and assign variables (prefix with db) $selectedstudentstmt->execute(); $selectedstudentstmt->bind_result($detailsStudentAlias,$detailsStudentForename,$detailsStudentSurname); $selectedstudentstmt->store_result(); $selectedstudentnum = $selectedstudentstmt->num_rows(); }else{ $selectedstudentqry = " SELECT StudentAlias, StudentForename, StudentSurname FROM Student WHERE (StudentId = ?) ORDER BY StudentAlias "; global $mysqli; $selectedstudentstmt=$mysqli->prepare($selectedstudentqry); // You only need to call bind_param once $selectedstudentstmt->bind_param("i",$_POST["student"]); // get result and assign variables (prefix with db) $selectedstudentstmt->execute(); $selectedstudentstmt->bind_result($detailsStudentAlias,$detailsStudentForename,$detailsStudentSurname); $selectedstudentstmt->store_result(); $selectedstudentnum = $selectedstudentstmt->num_rows(); } //QUERY 2: Question details depending on selected question(s) if ($_POST['question'] == 'All'){ $selectedquestionqry = " SELECT q.QuestionNo, q.QuestionContent, o.OptionType, q.NoofAnswers, GROUP_CONCAT( DISTINCT Answer ORDER BY Answer SEPARATOR ',' ) AS Answer, r.ReplyType, q.QuestionMarks FROM Question q LEFT JOIN Answer an ON q.QuestionId = an.QuestionId LEFT JOIN Reply r ON q.ReplyId = r.ReplyId LEFT JOIN Option_Table o ON q.OptionId = o.OptionId WHERE SessionId = ? GROUP BY q.QuestionId ORDER BY q.QuestionId"; "; global $mysqli; $selectedquestionstmt=$mysqli->prepare($selectedquestionqry); // You only need to call bind_param once $selectedstudentstmt->bind_param("i",$_POST["session"]); // get result and assign variables (prefix with db) $selectedquestionstmt->execute(); $selectedquestionstmt->bind_result($detailsQuestionNo,$detailsQuestionContent,$detailsOptionType,$detailsNoofAnswers, $detailsAnswer,$detailsReplyType,$detailsQuestionMarks); $selectedquestionstmt->store_result(); $selectedquestionnum = $selectedquestionstmt->num_rows(); }else{ $selectedquestionqry = " SELECT q.QuestionNo, q.QuestionContent, o.OptionType, q.NoofAnswers, GROUP_CONCAT( DISTINCT Answer ORDER BY Answer SEPARATOR ',' ) AS Answer, r.ReplyType, q.QuestionMarks FROM Question q LEFT JOIN Answer an ON q.QuestionId = an.QuestionId LEFT JOIN Reply r ON q.ReplyId = r.ReplyId LEFT JOIN Option_Table o ON q.OptionId = o.OptionId WHERE QuestionId = ? GROUP BY q.QuestionId ORDER BY q.QuestionId "; global $mysqli; $selectedquestionstmt=$mysqli->prepare($selectedquestionqry); // You only need to call bind_param once $selectedquestionstmt->bind_param("i",$_POST["question"]); // get result and assign variables (prefix with db) $selectedquestionstmt->execute(); $selectedquestionstmt->bind_result($detailsQuestionNo,$detailsQuestionContent,$detailsOptionType,$detailsNoofAnswers, $detailsAnswer,$detailsReplyType,$detailsQuestionMarks); $selectedquestionstmt->store_result(); $selectedquestionnum = $selectedquestionstmt->num_rows(); } //QUERY 3: Student Answers depending on selected student(s) and selected question(s) $studentanswerqry = " SELECT sa.StudentId, sa.QuestionId, GROUP_CONCAT(DISTINCT StudentAnswer ORDER BY StudentAnswer SEPARATOR ',') AS StudentAnswer, ResponseTime, MouseClick, StudentMark FROM Student_Answer sa INNER JOIN Student_Response sr ON sa.StudentId = sr.StudentId WHERE (sa.StudentId = ? AND sa.QuestionId = ?) GROUP BY sa.StudentId, sa.QuestionId "; global $mysqli; $studentanswerstmt=$mysqli->prepare($studentanswerqry); // You only need to call bind_param once $studentanswerstmt->bind_param("ii",$_POST["student"], $_POST["question"]); // get result and assign variables (prefix with db) $studentanswerstmt->execute(); $studentanswerstmt->bind_result($detailsStudentAnswer,$detailsResponseTime,$detailsMouseClick,$detailsStudentMark); $studentanswerstmt->store_result(); $studentanswernum = $studentanswerstmt->num_rows(); } ?>
0
source share
3 answers

You can iteratively build a WHERE . Consider explicit filtering in the WHERE , so for cases where you select "all", you do not need to add any filters. Other filters are built on top of each other, so we can just attach them to AND in WHERE :

 $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); } $result = prepare_and_execute_query($query, $parameters);

Good, therefore, looking at your update, you have a rather complicated set of queries, but you can combine it into one operator. Try:

 SELECT s.StudentId, s.StudentAlias, s.StudentForename, -- Student fields s.StudentSurname, q.QuestionId, q.QuestionNo, q.QuestionContent, -- Question fields q.OptionType, q.NoofAnswers, q.Answer, q.ReplyType, q.QuestionMarks, GROUP_CONCAT(DISTINCT sa.StudentAnswer ORDER BY -- Answer fields sa.StudentAnswer SEPARATOR ',') AS StudentAnswer, sr.ResponseTime, sr.MouseClick, sr.StudentMark FROM Student s INNER JOIN Student_Answer sa ON (s.StudentId = sa.StudentId) INNER JOIN Question q ON (sa.QuestionId = q.QuestionId) INNER JOIN Student_Response sr ON (sa.StudentId = sr.StudentId) WHERE -- This WHERE clause may be entirely removed, -- depending on the filters s.StudentId = ? AND -- This is removed if $_POST['student'] is 'All' q.QuestionId = ? -- This is removed if $_POST['question'] is 'All' GROUP BY sa.StudentId, q.QuestionId

I think this will do what you want. I was not sure which fields are part of Student_Response and are part of Student_Answer , so you may have to deal with columns in SELECT .

Unfortunately, this approach is not suitable for your use case. But we can still consider how the original logic that I proposed will work with one of your tasks:

 $selectedstudentqry = " SELECT StudentAlias, StudentForename, StudentSurname FROM Student "; if($_POST['student'] !== 'All') { // Check here $selectedstudentqry .= " WHERE (StudentId = ?) "; } $selectedstudentqry .= " ORDER BY StudentAlias "; global $mysqli; $selectedstudentstmt=$mysqli->prepare($selectedstudentqry); if($_POST['student'] !== 'All') { // You only need to call bind_param once $selectedstudentstmt->bind_param("i",$_POST["student"]); } // get result and assign variables (prefix with db) $selectedstudentstmt->execute(); $selectedstudentstmt->bind_result($detailsStudentAlias,$detailsStudentForename,$detailsStudentSurname); $selectedstudentstmt->store_result(); $selectedstudentnum = $selectedstudentstmt->num_rows();

Note that we simply moved the external if to more specific places inside the code to reduce code duplication. If you see duplication, as you have, there is a very good chance that you are doing something like that your conditional conditions are too broad. You are definitely on the right track, trying to simplify this and reduce redundancy.

+3
source

This is the code I used when there are 64 user input permutations. There may be a rider identifier from another page, a partial first and / or last name, year, month or course identifier.

Build part of the where clause with lots of AND, IS LIKE, etc.

 $params = array(); $types = ""; if ( isset($_GET["riderid"]) && $_GET["riderid"] ) { $where = sprintf (' AND %s.id = ?', $tables["rider"]); $params[]= $_GET["riderid"]; $types = $types .'i'; } else { if ( isset($_GET["last"]) && $_GET["last"] ) { $where = sprintf(' AND %s.last like ?', $tables["rider"]); $params[] = $_GET["last"] . "%"; $types = $types . "s"; } if ( isset($_GET["first"]) && $_GET["first"] ) { $whereFirst = sprintf(' AND %s.first like ?', $tables["rider"]); $where = $where . $whereFirst; $params[] = $_GET["first"] . "%"; $types = $types . "s"; } } if ( isset($_GET["year"]) && $_GET["year"] && $_GET["year"] != -1 ) { $whereYear = sprintf(' AND YEAR(%s.date) = ?', $tables["race"]); $where = $where . $whereYear; $params[] = $_GET["year"]; $types = $types . "i"; } if ( isset($_GET["month"]) && $_GET["month"] && $_GET["month"] != -1 ) { $whereMonth = sprintf(' AND month(%s.date) = ?', $tables["race"]); $where = $where . $whereMonth; $params[] = $_GET["month"]; $types = $types . "i"; } if ( isset($_GET["courseid"]) && $_GET["courseid"] && $_GET["courseid"] != -1 ) { $whereCourse = sprintf(' AND %s.courseid = ?', $tables["race"]); $where = $where . $whereCourse; $params[] = $_GET["courseid"]; $types = $types . "i"; }

Here is the request.

  // // Show selected races // $listQuery = "SELECT {$tables["race"]}.raceid, {$tables["race"]}.typeid, " . " tag, {$tables["race"]}.date, " . " {$tables["location"]}.name, {$tables["course"]}.dist, " . " {$tables["course"]}.description, " . " {$tables["result"]}.time, {$tables["result"]}.dnf, " . " {$tables["rider"]}.first, {$tables["rider"]}.last ". " FROM {$tables["race"]}, {$tables["sysracetype"]}, " . " {$tables["course"]}, {$tables["location"]}, " . " {$tables["result"]}, {$tables["rider"]} " . " WHERE {$tables["race"]}.courseid = {$tables["course"]}.courseid " . " AND {$tables["race"]}.typeid = {$tables["sysracetype"]}.typeid " . " AND {$tables["course"]}.locid = {$tables["location"]}.locid " . " AND {$tables["race"]}.raceid = {$tables["result"]}.raceid" . " AND {$tables["result"]}.riderid = {$tables["rider"]}.id" . $where . $orderby;

Prepare and bind the parameters.

 $stmt = mysqli_prepare ($db_connection, $listQuery); // // Handle the varying number of SQL parameters / place holders. // Push the first two parameter of mysqli_stmt_bind_param // $bindArgs = array ($stmt, $types); // // Change parameters to refs (depends on PHP version) // foreach ($params as $key => $value) $bindArgs[] =& $params[$key]; // // Use "call back" to pass all params. // call_user_func_array("mysqli_stmt_bind_param", $bindArgs);
0
source

I solved a similar problem this way. Where $ parmMap is created with permissions of 2 (1,2) when I discover every optional element of the WHERE clause and add the corresponding field ANDName = ?.

 switch ($parmMap ) { case 0: break; case 1: mysqli_stmt_bind_param ($stmt, "i", $courseParm ); break; case 2: mysqli_stmt_bind_param ($stmt, "i", $yearParm ); break; case 3: mysqli_stmt_bind_param ($stmt, "ii", $courseParm, $yearParm ); break; default: break; }

I will not use this, although for another request that has six optional conditions, conditions of the offer! I discovered call_user_func_array .

-1
source

More articles:


All Articles