PDO:使用foreach和$ _POST定义参数 [英] PDO: defining parameters using foreach and $_POST

问题描述

我正在尝试创建一个页面，该页面允许用户使用PDO更改sql数据库中的信息.我是新来的人，我遇到了一个错误.我将其用作来源:

I'm trying to create a page that allows users change their info in a sql database using PDO. I'm new to this and I'm running into an error. I'm using this as a source: http://forums.devshed.com/php-faqs-stickies-167/program-basic-secure-login-system-using-php-mysql-891201.html

我不确定我是否包含太多代码.我希望有人能指出我犯了一个错误.

I'm not sure if I included too much code. I'm hoping someone could point out where I made a mistake.

<?php
require("common.php");

Common运行以下代码:

Common runs this code:

<?php
$username = "";
$password = "";
$host = "";
$dbname = "";
$options = array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8');
try
{
    $db = new PDO("mysql:host={$host};dbname={$dbname};charset=utf8", $username, $password, $options);
}
catch(PDOException $ex)
{
    die("Failed to connect to the database: " . $ex->getMessage());
}

$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

header('Content-Type: text/html; charset=utf-8');
session_start();

现在，我创建初始查询和参数:

Now I create the initial query and params:

//initial query and params
    $query = "
        UPDATE users
        SET
        ";
    $query_params = array();
    $query_params[':user_id'] = $_SESSION['user']['id'];

现在我想使用参数数组(字段)定义参数

Now I want to define the parameters using an array of parameters (the fields)

      $params = array(info1,info2,info3);
      foreach($params as $param):
        if(!empty($_POST[$param])){
          $query_params[':$param'] = $_POST[$param];
          $query .= "$param = :$param,";
        }       
      endforeach;

然后我完成查询并运行它:

Then I finish the query and run it:

    //trim last comma
    $query = rtrim($query, ',');
    $query .= "
        WHERE
            id = :user_id
    ";

    try
    {
        // Execute the query
        $stmt = $db->prepare($query);
        $result = $stmt->execute($query_params);
    }
    catch(PDOException $ex)
    {
        die("Failed to run main query: " . $ex->getMessage() . "\n" . $query );
    }

这是我使用的HTML(带有jquery):

This is the HTML (with jquery) I used:

<form method="post" action="edit_account.php" data-ajax="false">

<div class="ui-field-contain">
    <p>Gebruikersnaam: <b><?php echo htmlentities($_SESSION['user']['username'], ENT_QUOTES, 'UTF-8'); ?></b></p>
    <label for="email">E-mail:</label>
    <input autocomplete="off" type="text" name="email" id="email" value="<?php echo htmlentities($_SESSION['user']['email'], ENT_QUOTES, 'UTF-8'); ?>">
    <label for="password">Wachtwoord:</label>
    <input autocomplete="off" type="password" name="password" id="password" placeholder="(alleen als je deze wijzigt)">
  </div>

  <div class="ui-field-contain">
    <label for="info1">info1:</label>
    <input type="text" name="info1" id="info1" placeholder="<?php echo htmlentities($_SESSION['user']['info1'], ENT_QUOTES, 'UTF-8'); ?>">
  </div>

  <div class="ui-field-contain">
    <label for="info2">info2:</label>
    <textarea type="text" name="info2" id="info2" placeholder="<?php echo htmlentities($_SESSION['user']['info2'], ENT_QUOTES, 'UTF-8'); ?>"></textarea>
    <label for="info3">info3:</label>
    <input type="text" name="info3" id="info3" placeholder="<?php echo htmlentities($_SESSION['user']['info3'], ENT_QUOTES, 'UTF-8'); ?>">
    <label for="info4">info4:</label>
    <input type="text" name="info4" id="info4" placeholder="<?php echo htmlentities($_SESSION['user']['info4'], ENT_QUOTES, 'UTF-8'); ?>">
  </div>
</form>

我收到此错误:SQLSTATE [HY093]:无效的参数编号:未定义参数.

I get this error: SQLSTATE[HY093]: Invalid parameter number: parameter was not defined.

推荐答案

我简化了代码，以说明如何创建动态UPDATE查询.这使用惰性"绑定和常规占位符?.

I have simplified the code to illustrate how to create dynamic UPDATE query. This uses "lazy" binding and regular placeholders ?.

文本输入被设置为值的常规数组(即，非关联).

The text inputs are set as a regular array (ie. not associative) of values.

<?php
if(isset($_POST['submit'])){
    if (isset($_POST["info"]) && !empty($_POST["user_id"])) {
        $query = "UPDATE `users` SET ";
        $query_params = array();
        $i =1;
        foreach ($_POST['info'] as $value) {
            // push array with each valid info entry ...
            if ($value) {
                $query .= "`info".$i. "` = ?,"; 
                array_push($query_params,$value);
            }
            $i++;
        }
        $query = rtrim($query, ',');//remove trailing ,
        //  
        $query .= " WHERE `id` = ?"; 
        array_push($query_params,$_POST['user_id']);
        //Show query and parameter array Remove after testing
        echo $query;    
        echo "<br>";
        print_r($query_params);
        // Execute the query
        $stmt = $db->prepare($query);
        $result = $stmt->execute($query_params);
    }
}
?>
<form method="POST" action="#">
<input type="text" name="info[]" id="info" value=""> 
<input type="text" name="info[]" id="info"  value="">
<input type="text" name="info[]" id="info"  value="">
<input type="text" name="info[]" id="info" value="">
<input type="text" name="user_id" id="user_id" value="">
<input type="submit" name="submit" value="Submit">

典型结果

UPDATE `users` SET `info1` = ?,`info2` = ? WHERE `id` = ?
Array ( [0] => john [1] => smith [2] => johnsmith ) 

这篇关于PDO:使用foreach和$ _POST定义参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！