How to use bindParam property correctly?-CodePudding

How could this code inserted 0 values to my database?

<?php
  $params = [];
  $table = "log";
  $values = [
    "text" => "missing socket file named ../../app/shop/socket/sectionheaderNavigationView.php",
    "level" => 2,
        "created_at" => 1672806059253,
        "created_by" => 0
  ];

  $queryColumn = "";
  $queryValues = "";
  $n = 0;

  foreach($values as $x => $y) {
    $queryColumn .= "__" . $x;
    $queryValues .= ":" . $x;
    $params[":" . $x] = $y;
    $n  ;
    if($n < count($values)) {
      $queryColumn .= ", ";
      $queryValues .= ", ";
    }
  }

  $query = "INSERT INTO $table($queryColumn) VALUES($queryValues)";
  $db = new PDO("mysql:host=127.0.0.1;dbname=db_shop", "root", "");
  $stmt = $db->prepare($query);

  if(count($params) > 0) {
    foreach($params as $x => $y) {
      switch(true) {
        case is_int($y): $type = PDO::PARAM_INT; break;
        case is_string($y): $type = PDO::PARAM_STR; break;
        case is_bool($y): $type = PDO::PARAM_BOOL; break;
        default: $type = PDO::PARAM_NULL; break;
      }
      var_dump($x . " " . $y . " " . $type, $query);
      if(!$stmt->bindParam($x, $y, $type)) {
        break;
      }
    }
  }

  try {
    $stmt->execute();
  } catch(PDOException $e) {
    echo "Exception: " . $e;
  }
?>

The above code resulted

MariaDB [db_shop]> select * from log;
 ------- ----------- -------- --------- -------------- -------------- 
| __key | __message | __text | __level | __created_at | __created_by |
 ------- ----------- -------- --------- -------------- -------------- 
|     1 |           | 0      |       0 | 0            |            0 |
|     2 |           | 0      |       0 | 0            |            0 |

Isn't if even its null when checking $params shouldn't it be empty not 0 instead?

MariaDB [db_shop]> desc log;
 -------------- ------------- ------ ----- --------- ---------------- 
| Field        | Type        | Null | Key | Default | Extra          |
 -------------- ------------- ------ ----- --------- ---------------- 
| __key        | int(11)     | NO   | PRI | NULL    | auto_increment |
| __text       | varchar(82) | NO   |     | NULL    |                |
| __level      | int(11)     | NO   |     | NULL    |                |
| __created_at | char(16)    | NO   |     | NULL    |                |
| __created_by | int(11)     | NO   |     | NULL    |                |
 -------------- ------------- ------ ----- --------- ----------------

How could i properly handle this error, there also no exception. Here the var_dump inside the looping checking for $params.

string(87) ":text missing socket file named ../../app/shop/socket/sectionheaderNavigationView.php 2"
string(108) "INSERT INTO log(__text, __level, __created_at, __created_by) VALUES(:text, :level, :created_at, :created_by)"
string(10) ":level 2 1"
string(108) "INSERT INTO log(__text, __level, __created_at, __created_by) VALUES(:text, :level, :created_at, :created_by)"
string(27) ":created_at 1672806059253 2"
string(108) "INSERT INTO log(__text, __level, __created_at, __created_by) VALUES(:text, :level, :created_at, :created_by)"
string(15) ":created_by 0 1"
string(108) "INSERT INTO log(__text, __level, __created_at, __created_by) VALUES(:text, :level, :created_at, :created_by)"

php -v; PHP 8.1.10 (cli) (built: Aug 30 2022 18:05:49) (ZTS Visual C 2019 x64)

Thanks for any feedback

CodePudding user response：

The answer to your question of why it inserted 0 is that bindParam() doesn't take the value of $y when you bind it. It takes the value of $y when you execute the query. But by then the loop has finished, so $y has a different value — the last value it had during the loop. The last value is 0, as we can see:

    "created_by" => 0

You could fix this by using bindValue() instead of bindParam(). Then it would make a copy of the value of $y at the time you bind it, and use that when you eventually execute the query.

You could also fix this by simply passing an array of values to execute().

Here's how I would write it:

  $queryColumns = implode(",", array_map(function($col) { return "`__$col`"; }, array_keys($values)));
  $paramPlaceholders = implode(",", array_fill(0, count($values), "?");

  $query = "INSERT INTO `$table`($queryColumns) VALUES($paramPlaceholders)";
  $db = new PDO("mysql:host=127.0.0.1;dbname=db_shop", "root", "");

  try {
    $stmt = $db->prepare($query);
    $stmt->execute(array_values($values));
  } catch(PDOException $e) {
    error_log($e);
    // display a more friendly error to the client
  }

There is no need to bind each value individually, and there is no need to use the PDO::PARAM_STR or other type constants when using the MySQL PDO driver. They are all treated as strings regardless of how you bind them.