Avoiding code repetition for MySQL queries (multiple while)

Question

I have the following code:

<?php
include_once "connect.php";

$question_01 = mysqli_real_escape_string($con, $_POST['question_01']);
// $question_02 - $question_09 go here...
$question_10 = mysqli_real_escape_string($con, $_POST['question_10']);

    $i = 0;
    $array_sum=[];
     while ($i < 10){
      $i++;
       $sql =  "SELECT * FROM parteners WHERE question_no = 1 AND answer_variant = '$question_01'";
       $result = mysqli_query($con, $sql);

       $final_array_1 = array();

      while ($row = mysqli_fetch_array($result, MYSQLI_NUM))
      {

        $final_array_1 = $row;
            $array_sum = array_map(function () {
                return array_sum(func_get_args());
            }, $array_sum, $final_array_1);
      }
}
print_r($final_array_1);

As you can see, I need to repeat the code for each $question_##. Is there a smarter way of doing this other than repeating the code? I'm not only concerned about turning everything into a code spaghetti but also about the efficiency of the operations as in loading times.

Let me know if you need clarification.

Update: Basically it should increase the value of "question_no" in the query until it reaches 10 and pick the corresponding $_POST value for each question.

so what is the problem? use $i to build the sql string inside the loop. Basic string concatenation. — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Feb 13, 2017 at 12:10
@JuanCarlosOropeza I got the concatenation part. I was more interested into the looping process of this. Should I keep the two whiles? — SporeDev
– SporeDev, Commented Feb 13, 2017 at 12:13
$sql = "SELECT * FROM parteners ORDER BY question_no ASC LIMIT 10"; check this in db directly and let me know showing desired output or not? — Death-is-the-real-truth
– Death-is-the-real-truth, Commented Feb 13, 2017 at 12:13
Not sure what is the result you want. Concatenation should be simpler, but you should try always use parameters instead php.net/manual/es/mysqli-stmt.bind-param.php — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Feb 13, 2017 at 12:14
@SporeDev :use WHERE question_no IN(1,2,3.....) instead WHERE question_no = $i without loop — Maninderpreet Singh
– Maninderpreet Singh, Commented Feb 13, 2017 at 12:27

James Hunt · Accepted Answer · 2017-02-13 12:12:18Z

1

There are two ways, variable variables or arrays. I'd suggest arrays as they are less prone to throwing errors everywhere.

<?php
include_once "connect.php";
$questions = array();
$questions[1] = mysqli_real_escape_string($con, $_POST['question_01']);
// $question_02 - $question_09 go here...
$questions[10] = mysqli_real_escape_string($con, $_POST['question_10']);

    $i = 0;
    $array_sum=[];
     while ($i < 10){
      $i++;
       $sql =  "SELECT * FROM parteners WHERE question_no = $i AND answer_variant = '".$questions[$i]."'";
       $result = mysqli_query($con, $sql);

       $final_array_1 = array();

      while ($row = mysqli_fetch_array($result, MYSQLI_NUM))
      {

        $final_array_1 = $row;
            $array_sum = array_map(function () {
                return array_sum(func_get_args());
            }, $array_sum, $final_array_1);
      }
}
print_r($final_array_1);

EDIT: The reason I used an array instead of just straight up using the POST variable in the while loop is so there is room before you run anything for validation (ensuring your question array contains 10 posted values etc)

answered Feb 13, 2017 at 12:12

James Hunt

2,53813 silver badges24 bronze badges

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

2 Comments

radicarl Over a year ago

when you do db queries from a loop use at least prepared statements to safe some of the overhead.

James Hunt Over a year ago

Agreed Radicarl, I was answering his question in terms on how to handle the passed POSTs, rather than fixing the entire code block. I would agree when they implement a solution, prepared statements are always the way to go

radicarl · Accepted Answer · 2017-02-13 12:33:49Z

I would build one SQL-Statement which contains all questions and anwsers and do the rest with programming logic. SQL-Queries in a loop are a bad idea, because you have to do a lot of overhead for getting a task done, which the database server can do better. Also you should use prepared statements for performance and security.

$query = "SELECT * FROM parteners WHERE (question_no = 1 AND answer_variant = ?) OR (question_no = 2 AND answer_variant = ?) OR (question_no = 3 AND answer_variant = ?) OR (question_no = 4 AND answer_variant = ?) OR (question_no = 5 AND answer_variant = ?) OR (question_no = 6 AND answer_variant = ?) OR (question_no = 7 AND answer_variant = ?) OR (question_no = 8 AND answer_variant = ?) OR (question_no = 9 AND answer_variant = ?) OR (question_no = 10 AND answer_variant = ?)" 
$stmt = myqli_prepare($query);
mysqli_stmt_bind_param($stmt, 'ssssssssss', $question_01, $question_02, $question_03,.....);
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);

sidyll · Accepted Answer · 2017-02-13 12:45:54Z

First, to make your code modern and efficient, you should be using PHP Data Objects, or PDO for short. You will have access to prepared statements, which are made exactly for this: you build a query "template" and execute with different data, very efficiently and secure.

The loop is the proper way to do it. Also, your $questions array is a bit unecessary since you can retrieve data from $_POST right inside your loop. But if you want to use it, there is no need to "escape" the string for the database, since it's handled by PDO. So you can build your array in a easier way:

$questions = [
    $_POST['question_01'],
    $_POST['question_02'],
    $_POST['question_03'],
    # ...
    $_POST['question_10'],
];

Your loop with PDO:

$dbh = ... # create your database handle, connect to it
$st = $dbh->prepare("
    SELECT * FROM parteners
    WHERE question_no = ? AND answer_variant = ?;
");

foreach (range(1, 10) as $i) {

    $result = $st->execute([ $i, $questions[$i-1] ]);
    # or, to build directly
    $result = $st->execute([
        $i, $_POST[ sprintf("question_%02d", $i) ]
    ]);

    $final_array[] = $result->fetchAll(PDO::FETCH_NUM);

}

print_r($final_array);

Collectives™ on Stack Overflow

Avoiding code repetition for MySQL queries (multiple while)

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related