mySQL - PHP : how to prepare queries with variables number of variables

Question

I've already been through Stack Overflow for answers but questions still hasn't been answered. Here's my issue:

I had 48 $_POST['textarea'] values from a form I wanted to save in a mySQL database through PHP.
Hence the array execute method was having trouble to process due to large number of parameters,

I found help in this post (PHP Mysql PDO number of bound variables does not match number of tokens) which allowed me to 'prepare', 'bindParam', and 'execute': and the first code was already big at that time. But I choose not to be too touchy and accepted the inelegance.

And here my pain begins: I will have variable number (meaning different number for each user) of $_POST['things'] from now on, and since a loop appeared to possibly configure each variable name (for which to process htmlspectialchar(), bindParam()), I face this uncommoding problem: I don't know how to generate those different variables names.

Thus I tried the above code, but I face a no-issue situation.

How can I organise the code in order to create as many variables to save in database as my $nbr_of_domain variable?
And a pedagogical question: I've previously learnt to bind parameters via execute(aray['param'=>$param... Is bindParam() function doing the same? Thus is-it useful only for a certain number of param?

PS: at the end you'll find the html <form> code (with sensible information removed possibly roughly, but the main structure is here)

public function saveInfos()
{   //Save info from main board into  database table user_board_items.
    $domain_assig='';
    $UM = new UserManager;
    if(isset($_SESSION['user_pseudo'])){
        $user_id=$UM->getUserId($_SESSION['user_pseudo']);
    }
    $DB=$this->dbConnect();
    $nbr_of_domain=$this->getNumberOfDomains();
    //BUILD REQUEST
    for($i=1; $i<=$nbr_of_domain;$i++){
      if($i<$nbr_of_domain){
        $domain_assig .='DM'.$i.'_ST_G=:DM'.$i.'_ST_G,'
                        .'DM'.$i.'_MT_G=:DM'.$i.'_MT_G,'
                        .'DM'.$i.'_LT_G=:DM'.$i.'_LT_G,'
                        .'DM'.$i.'_ST_T=:DM'.$i.'_ST_T,'
                        .'DM'.$i.'_MT_T=:DM'.$i.'_MT_T,'
                        .'DM'.$i.'_LT_T=:DM'.$i.'_LT_T,';
      }else {
        $domain_assig .='DM'.$i.'_ST_G=:DM'.$i.'_ST_G,'
                        .'DM'.$i.'_MT_G=:DM'.$i.'_MT_G,'
                        .'DM'.$i.'_LT_G=:DM'.$i.'_LT_G,'
                        .'DM'.$i.'_ST_T=:DM'.$i.'_ST_T,'
                        .'DM'.$i.'_MT_T=:DM'.$i.'_MT_T,'
                        .'DM'.$i.'_LT_T=:DM'.$i.'_LT_T';
      }
    }
    $req = sprintf("UPDATE user_board_items SET %s WHERE user_id=:user_id",$domain_assig);
    //PREPARING REQUEST
    $saveRequest = $DB->prepare($req);
    //PROTECTING PARAMETERS BEFORE BOUNDING
    for($i=1; $i<=$nbr_of_domain;$i++){
        $saveRequest->bindParam(':DM'.$i.'_ST_G',htmlspecialchars($_POST['DM'.$i.'_ST_G']));
        $saveRequest->bindParam(':DM'.$i.'_MT_G',htmlspecialchars($_POST['DM'.$i.'_MT_G']));
        $saveRequest->bindParam(':DM'.$i.'_LT_G',htmlspecialchars($_POST['DM'.$i.'_LT_G']));
        $saveRequest->bindParam(':DM'.$i.'_ST_T',htmlspecialchars($_POST['DM'.$i.'_ST_T']));
        $saveRequest->bindParam(':DM'.$i.'_MT_T',htmlspecialchars($_POST['DM'.$i.'_MT_T']));
        $saveRequest->bindParam(':DM'.$i.'_LT_T',htmlspecialchars($_POST['DM'.$i.'_LT_T']));
    }
        $saveRequest->bindParam(':user_id',$user_id);

    $saveRequest->execute();
    $saveRequest->closeCursor();
}

And for the bravest here is my old working but rigid code version that did not admit variations of variables number ( so procedural! :):

public function saveInfos()
{   //Save info from main borad into  database table user_board_items
    //ini_set('memory_limit', '1024M'); // or you could use 1G
    $UM = new UserManager;
    if(isset($_SESSION['user_pseudo'])){
        $user_id=$UM->getUserId($_SESSION['user_pseudo']);
    }
    $DB=$this->dbConnect();

    $req = "UPDATE
            user_board_items
            SET
            DM1_ST_G=:DM1_ST_G,
            DM1_MT_G=:DM1_MT_G,
            DM1_LT_G=:DM1_LT_G,
            DM1_ST_T=:DM1_ST_T,
            DM1_MT_T=:DM1_MT_T,
            DM1_LT_T=:DM1_LT_T,
            DM2_ST_G=:DM2_ST_G,
            DM2_MT_G=:DM2_MT_G,
            DM2_LT_G=:DM2_LT_G,
            DM2_ST_T=:DM2_ST_T,
            DM2_MT_T=:DM2_MT_T,
            DM2_LT_T=:DM2_LT_T,
            DM3_ST_G=:DM3_ST_G,
            DM3_MT_G=:DM3_MT_G,
            DM3_LT_G=:DM3_LT_G,
            DM3_ST_T=:DM3_ST_T,
            DM3_MT_T=:DM3_MT_T,
            DM3_LT_T=:DM3_LT_T,
            DM4_ST_G=:DM4_ST_G,
            DM4_MT_G=:DM4_MT_G,
            DM4_LT_G=:DM4_LT_G,
            DM4_ST_T=:DM4_ST_T,
            DM4_MT_T=:DM4_MT_T,
            DM4_LT_T=:DM4_LT_T,
            DM5_ST_G=:DM5_ST_G,
            DM5_MT_G=:DM5_MT_G,
            DM5_LT_G=:DM5_LT_G,
            DM5_ST_T=:DM5_ST_T,
            DM5_MT_T=:DM5_MT_T,
            DM5_LT_T=:DM5_LT_T,
            DM6_ST_G=:DM6_ST_G,
            DM6_MT_G=:DM6_MT_G,
            DM6_LT_G=:DM6_LT_G,
            DM6_ST_T=:DM6_ST_T,
            DM6_MT_T=:DM6_MT_T,
            DM6_LT_T=:DM6_LT_T,
            DM7_ST_G=:DM7_ST_G,
            DM7_MT_G=:DM7_MT_G,
            DM7_LT_G=:DM7_LT_G,
            DM7_ST_T=:DM7_ST_T,
            DM7_MT_T=:DM7_MT_T,
            DM7_LT_T=:DM7_LT_T,
            DM8_ST_G=:DM8_ST_G,
            DM8_MT_G=:DM8_MT_G,
            DM8_LT_G=:DM8_LT_G,
            DM8_ST_T=:DM8_ST_T,
            DM8_MT_T=:DM8_MT_T,
            DM8_LT_T=:DM8_LT_T
            WHERE user_id=:user_id";

    $saveRequest = $DB->prepare($req);

    $DM1_ST_G= htmlspecialchars($_POST['DM1_ST_G']);
    $DM1_MT_G= htmlspecialchars($_POST['DM1_MT_G']);
    $DM1_LT_G= htmlspecialchars($_POST['DM1_LT_G']);
    $DM1_ST_T= htmlspecialchars($_POST['DM1_ST_T']);
    $DM1_MT_T= htmlspecialchars($_POST['DM1_MT_T']);
    $DM1_LT_T= htmlspecialchars($_POST['DM1_LT_T']);
    $DM2_ST_G= htmlspecialchars($_POST['DM2_ST_G']);
    $DM2_MT_G= htmlspecialchars($_POST['DM2_MT_G']);
    $DM2_LT_G= htmlspecialchars($_POST['DM2_LT_G']);
    $DM2_ST_T= htmlspecialchars($_POST['DM2_ST_T']);
    $DM2_MT_T= htmlspecialchars($_POST['DM2_MT_T']);
    $DM2_LT_T= htmlspecialchars($_POST['DM2_LT_T']);
    $DM3_ST_G= htmlspecialchars($_POST['DM3_ST_G']);
    $DM3_MT_G= htmlspecialchars($_POST['DM3_MT_G']);
    $DM3_LT_G= htmlspecialchars($_POST['DM3_LT_G']);
    $DM3_ST_T= htmlspecialchars($_POST['DM3_ST_T']);
    $DM3_MT_T= htmlspecialchars($_POST['DM3_MT_T']);
    $DM3_LT_T= htmlspecialchars($_POST['DM3_LT_T']);
    $DM4_ST_G= htmlspecialchars($_POST['DM4_ST_G']);
    $DM4_MT_G= htmlspecialchars($_POST['DM4_MT_G']);
    $DM4_LT_G= htmlspecialchars($_POST['DM4_LT_G']);
    $DM4_ST_T= htmlspecialchars($_POST['DM4_ST_T']);
    $DM4_MT_T= htmlspecialchars($_POST['DM4_MT_T']);
    $DM4_LT_T= htmlspecialchars($_POST['DM4_LT_T']);
    $DM5_ST_G= htmlspecialchars($_POST['DM5_ST_G']);
    $DM5_MT_G= htmlspecialchars($_POST['DM5_MT_G']);
    $DM5_LT_G= htmlspecialchars($_POST['DM5_LT_G']);
    $DM5_ST_T= htmlspecialchars($_POST['DM5_ST_T']);
    $DM5_MT_T= htmlspecialchars($_POST['DM5_MT_T']);
    $DM5_LT_T= htmlspecialchars($_POST['DM5_LT_T']);
    $DM6_ST_G= htmlspecialchars($_POST['DM6_ST_G']);
    $DM6_MT_G= htmlspecialchars($_POST['DM6_MT_G']);
    $DM6_LT_G= htmlspecialchars($_POST['DM6_LT_G']);
    $DM6_ST_T= htmlspecialchars($_POST['DM6_ST_T']);
    $DM6_MT_T= htmlspecialchars($_POST['DM6_MT_T']);
    $DM6_LT_T= htmlspecialchars($_POST['DM6_LT_T']);
    $DM7_ST_G= htmlspecialchars($_POST['DM7_ST_G']);
    $DM7_MT_G= htmlspecialchars($_POST['DM7_MT_G']);
    $DM7_LT_G= htmlspecialchars($_POST['DM7_LT_G']);
    $DM7_ST_T= htmlspecialchars($_POST['DM7_ST_T']);
    $DM7_MT_T= htmlspecialchars($_POST['DM7_MT_T']);
    $DM7_LT_T= htmlspecialchars($_POST['DM7_LT_T']);
    $DM8_ST_G= htmlspecialchars($_POST['DM8_ST_G']);
    $DM8_MT_G= htmlspecialchars($_POST['DM8_MT_G']);
    $DM8_LT_G= htmlspecialchars($_POST['DM8_LT_G']);
    $DM8_ST_T= htmlspecialchars($_POST['DM8_ST_T']);
    $DM8_MT_T= htmlspecialchars($_POST['DM8_MT_T']);
    $DM8_LT_T= htmlspecialchars($_POST['DM8_LT_T']);

    $saveRequest->bindParam(':DM1_ST_G',$DM1_ST_G);
    $saveRequest->bindParam(':DM1_MT_G',$DM1_MT_G);
    $saveRequest->bindParam(':DM1_LT_G',$DM1_LT_G);
    $saveRequest->bindParam(':DM1_ST_T',$DM1_ST_T);
    $saveRequest->bindParam(':DM1_MT_T',$DM1_MT_T);
    $saveRequest->bindParam(':DM1_LT_T',$DM1_LT_T);
    $saveRequest->bindParam(':DM2_ST_G',$DM2_ST_G);
    $saveRequest->bindParam(':DM2_MT_G',$DM2_MT_G);
    $saveRequest->bindParam(':DM2_LT_G',$DM2_LT_G);
    $saveRequest->bindParam(':DM2_ST_T',$DM2_ST_T);
    $saveRequest->bindParam(':DM2_MT_T',$DM2_MT_T);
    $saveRequest->bindParam(':DM2_LT_T',$DM2_LT_T);
    $saveRequest->bindParam(':DM3_ST_G',$DM3_ST_G);
    $saveRequest->bindParam(':DM3_MT_G',$DM3_MT_G);
    $saveRequest->bindParam(':DM3_LT_G',$DM3_LT_G);
    $saveRequest->bindParam(':DM3_ST_T',$DM3_ST_T);
    $saveRequest->bindParam(':DM3_MT_T',$DM3_MT_T);
    $saveRequest->bindParam(':DM3_LT_T',$DM3_LT_T);
    $saveRequest->bindParam(':DM4_ST_G',$DM4_ST_G);
    $saveRequest->bindParam(':DM4_MT_G',$DM4_MT_G);
    $saveRequest->bindParam(':DM4_LT_G',$DM4_LT_G);
    $saveRequest->bindParam(':DM4_ST_T',$DM4_ST_T);
    $saveRequest->bindParam(':DM4_MT_T',$DM4_MT_T);
    $saveRequest->bindParam(':DM4_LT_T',$DM4_LT_T);
    $saveRequest->bindParam(':DM5_ST_G',$DM5_ST_G);
    $saveRequest->bindParam(':DM5_MT_G',$DM5_MT_G);
    $saveRequest->bindParam(':DM5_LT_G',$DM5_LT_G);
    $saveRequest->bindParam(':DM5_ST_T',$DM5_ST_T);
    $saveRequest->bindParam(':DM5_MT_T',$DM5_MT_T);
    $saveRequest->bindParam(':DM5_LT_T',$DM5_LT_T);
    $saveRequest->bindParam(':DM6_ST_G',$DM6_ST_G);
    $saveRequest->bindParam(':DM6_MT_G',$DM6_MT_G);
    $saveRequest->bindParam(':DM6_LT_G',$DM6_LT_G);
    $saveRequest->bindParam(':DM6_ST_T',$DM6_ST_T);
    $saveRequest->bindParam(':DM6_MT_T',$DM6_MT_T);
    $saveRequest->bindParam(':DM6_LT_T',$DM6_LT_T);
    $saveRequest->bindParam(':DM7_ST_G',$DM7_ST_G);
    $saveRequest->bindParam(':DM7_MT_G',$DM7_MT_G);
    $saveRequest->bindParam(':DM7_LT_G',$DM7_LT_G);
    $saveRequest->bindParam(':DM7_ST_T',$DM7_ST_T);
    $saveRequest->bindParam(':DM7_MT_T',$DM7_MT_T);
    $saveRequest->bindParam(':DM7_LT_T',$DM7_LT_T);
    $saveRequest->bindParam(':DM8_ST_G',$DM8_ST_G);
    $saveRequest->bindParam(':DM8_MT_G',$DM8_MT_G);
    $saveRequest->bindParam(':DM8_LT_G',$DM8_LT_G);
    $saveRequest->bindParam(':DM8_ST_T',$DM8_ST_T);
    $saveRequest->bindParam(':DM8_MT_T',$DM8_MT_T);
    $saveRequest->bindParam(':DM8_LT_T',$DM8_LT_T);
    $saveRequest->bindParam(':user_id',$user_id);

    $saveRequest->execute();
    $saveRequest->closeCursor();
}

And the html :

<form id="theForm" enctype="multipart/form-data" action="index.php?action=saveBoardInfo" method="post">
<table>
<thead>
  <th class="head_row"> TITLES</th>
  <th class="head_row" >SINGULAR SAMPLE PROCESS</th>
  <th class="head_row" >FILE</th>
  <th class="head_row" >MEDIUM SAMPLE PROCESS</th>
  <th class="head_row" >FILE</th>
  <th class="head_row" >LARGE SAMPLE PROCESS</th>
  <th class="head_row" >FILE</th>
</thead>
<tbody>
  <?php
      foreach ($names as $number=>$domain) {
       ?>
          <!-- FIRST HALF ROW -->
          <tr <?=$number+1?>">
            <!-- 2 merged rows-->
            <td not_editable" rowspan="2">
              <span class="color_category" style="background-color:<?=$color[$number]?>;"></span>
              <span ><?=$number + 1 ?></span>
            </td>
            <!-- Description cell ST-->
            <td class="inputContainerTdCell">
                <textarea id="<?='DM'.($number+1) .'_'.'ST_G'?>" name="<?='DM'.($number+1) .'_'.'ST_G'?>" class="userInput" value="" placeholder="SINGULAR TEST SAMPLE GENERAL DESCRIPTION"><?= htmlspecialchars($board_items['DM'.($number+1) .'_'.'ST_G'])?></textarea>
            </td>
            <!-- Description cell MT-->
            <td class="inputContainerTdCell">
                <textarea id="<?='DM'.($number+1) .'_'.'LT_G'?>" name="<?='DM'.($number+1) .'_'.'MT_G'?>" class="userInput" value="" placeholder="MEDIUM TEST SAMPLE GENERAL DESCRIPTION"><?= htmlspecialchars($board_items['DM'.($number+1) .'_'.'LT_G'])?></textarea>
            </td>
            <!-- Description cell LT-->
            <td class="inputContainerTdCell">
                <textarea id="<?='DM'.($number+1) .'_'.'LT_G'?>" name="<?='DM'.($number+1) .'_'.'LT_G'?>" class="userInput" value="" placeholder="LARGE TEST SAMPLE DESCRIPTION"><?= htmlspecialchars($board_items['DM'.($number+1) .'_'.'LT_G'])?></textarea>
            </td>

            <!-- File import part not mentionned here: 2 merged rows-->

          <!-- SECOND HALF ROW -->
          <tr>
            <td class="inputContainerTdCell userInput">
              <textarea id="<?='DM'.($number+1) .'_'.'ST_T'?>" name="<?='DM'.($number+1) .'_'.'ST_T'?>" value="" placeholder="SINGULAR TEST IN PREPARATION"><?= htmlspecialchars($board_items['DM'.($number+1) .'_'.'ST_T'])?></textarea>
            </td>
            <td class="inputContainerTdCell userInput">
              <textarea id="<?='DM'.($number+1) .'_'.'MT_T'?>" name="<?='DM'.($number+1) .'_'.'MT_T'?>" value="" placeholder="MEDIUM TEST IN PREPARATION"><?= htmlspecialchars($board_items['DM'.($number+1) .'_'.'MT_T'])?></textarea>
            </td>
            <td class="inputContainerTdCell userInput">
              <textarea id="<?='DM'.($number+1) .'_'.'LT_T'?>" name="<?='DM'.($number+1) .'_'.'LT_T'?>" value="" placeholder="LARGE TEST IN PREPARATION"><?= htmlspecialchars($board_items['DM'.($number+1) .'_'.'LT_T'])?></textarea>
            </td>
          </tr>

          <!-- SEPARATORS: INVISIBLE SEPARATION ROW -->
          <tr style="height:2px;"></tr>
          <?php
          } 
          ?>
</tbody>
</table>
</form>

Are you using msqli or PDO? Also you don't need to use sprintf to concat strings $v2 = "text $var "; will work. — Jason K
– Jason K, Commented Nov 21, 2019 at 19:23
About PROTECTING PARAMETERS BEFORE BOUNDING ... bound parameters don't need protecting -- are you sure that you want to do this? If you are worried about the input quality (and you should be), then the first task after receiving user input is to validate and sanitize it ...then the data is ready for regular processes. — mickmackusa
– mickmackusa ♦, Commented Nov 21, 2019 at 20:01
A couple of points. Never store HTML-escaped data in your database. You escape it when you're going to display it. And PDO does not need binding. You can simply pass your parameters in an array to the execute function. — miken32
– miken32, Commented Nov 21, 2019 at 22:24
Note that a database table is not a spreadsheet. This is a more important lesson — Strawberry
– Strawberry, Commented Nov 22, 2019 at 0:06

miken32 · Accepted Answer · 2019-11-23 22:50:12Z

4

Your code suggests a very poor database structure. Having massive numbers of columns like this speaks to a very not normal database. That said, you're doing the best you can with the database you have; a couple of points that can compress the code significantly though.

You do not escape data for HTML display until you are in fact displaying it in HTML. Never store it in your database escaped, or you will be unhappy when someone wants data in a PDF or output to command line.

PDO does not require parameters to be bound; this is only necessary in obscure situations like when the data type is not being deduced correctly, or you need to get data back from stored procedures. Just pass the array of parameters to the execute function.

I've also condensed the code you use to build the query, and the parameter array is built from $_POST within that same loop.

<?php
public function saveInfos()
{
    $UM = new UserManager;
    if(isset($_SESSION['user_pseudo'])){
        $user_id = $UM->getUserId($_SESSION['user_pseudo']);
    }
    $DB = $this->dbConnect();
    $nbr_of_domain = $this->getNumberOfDomains();
    $fields = ["ST_G", "MT_G", "LT_G", "ST_T", "MT_T", "LT_T"];
    //BUILD QUERY AND PARAMETERS
    $params[':user_id'] = $user_id;
    for($i = 1; $i <= $nbr_of_domain; $i++) {
        foreach ($fields as $field) {
            $domain_assig[] = "DM{$i}_{$field} = :DM{$i}_{$field}";
            $params[":DM{$i}_{$field}"] = $_POST["DM{$i}_{$field}"];
            // if passing parameters to execute() truly is a problem,
            // you could delete the line above and then run this same
            // loop again to bind parameters, as in the comment below
        }
    }
    $req = sprintf(
        "UPDATE user_board_items SET %s WHERE user_id=:user_id",
        implode(",", $domain_assig)
    );
    //PREPARING REQUEST
    $saveRequest = $DB->prepare($req);
    // if passing parameters to execute() truly is a problem...
    /*
    $saveRequest->bindParam(":user_id", $user_id);
    for($i = 1; $i <= $nbr_of_domain; $i++) {
        foreach ($fields as $field) {
            $saveRequest->bindParam(":DM{$i}_{$field}", $_POST["DM{$i}_{$field}"]);
        }
    }
    */
    $saveRequest->execute($params);
    $saveRequest->closeCursor();
}

As regards database normalization, where your database structure currently looks like this:

+----+---------+----------+----------+----------+     +----------+
| id | user_id | DM1_ST_G | DM1_MT_G | DM1_LT_G | ... | DM8_LT_T |
+----+---------+----------+----------+----------+     +----------+
| 17 | 12345   | aaa      | aaa      | aaa      | ... | hhh      |
+----+---------+----------+----------+----------+     +----------+

It should look like this:

+----+---------+----+------+------+------+------+------+------+
| id | user_id | DM | ST_G | MT_G | LT_G | ST_T | MT_T | LT_T |
+----+---------+----+------+------+------+------+------+------+
| 11 | 12345   | 1  | aaa  | aaa  | aaa  | aaa  | aaa  | aaa  |
| 12 | 12345   | 2  | bbb  | bbb  | bbb  | bbb  | bbb  | bbb  |
...
| 18 | 12345   | 8  | hhh  | hhh  | hhh  | hhh  | hhh  | hhh  |
+----+---------+----+------+------+------+------+------+------+

Then you can select however many rows there are, based on the user ID. Imagine a situation where you have a few hundred thousand rows, and you decide you want to add another set of DM9_* columns. The way it is now, the entire table has to be rebuilt, your code has to be adjusted, and it's very messy.

edited Nov 23, 2019 at 22:50

answered Nov 21, 2019 at 22:44

miken32

42.5k16 gold badges127 silver badges177 bronze badges

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

6 Comments

theophile69 Over a year ago

Whaaaou... PHP seems on your side!! :) It's stating "Invalid parameter number: number of bound variables does not match number of tokens"... Can you intuitively see the issue?? I'll have a more precise check anyway. Must have a rest, lights are off in France. Thanks miken32

miken32 Over a year ago

Sorry, I was assuming the only things in $_POST were the variables you were binding. Will need to filter out others. Check back in the morning for an update!

Lightness Races in Orbit Over a year ago

Probably wanted 17 all down the "id" column in that last example, if it's supposed to represent a transformation of the single row from the example before it. Unless id is just a meaningless row-counting autoincrement with no semantic value (then it should be removed & the PK should instead be (user_id, DM)) which, hmm, seems likely some to think of it.

miken32 Over a year ago

@LightnessRaceswithMonica yes, just an autoincrement; you're right it should be a compound primary key but wanted to keep it simple and assumed the existing table has such a column.

theophile69 Over a year ago

Thank you very much miken32. I not entirely in the developers party that's why I estimate a lot your really clear and pedagogic advices. Your post will clearly give me a lot to study for several days on. Wish you the best of course and thank you one more time!

|

Collectives™ on Stack Overflow

mySQL - PHP : how to prepare queries with variables number of variables

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related