php - PDO 绑定(bind)参数空值

Question

执行 PDO bindParam 调用时，插入到数据库中的值为空。

查看日志，参数似乎没有传输

Prepare     INSERT INTO StockItemTypes SET  Name = ?, PartNumber = ?, BarCodeNumber = ?, LeadTimeWeeks = ?,  PriceGroupFK = ?, MinimumOrderQuantity = ?, ReorderPointOverride = ?,  MinimumStockLevelOverride = ?, CurrentQuantity = ?, ApplianceTypeFK = ?,  Rack = ?, Shelf = ?
Execute     INSERT INTO StockItemTypes SET  Name = '', PartNumber = '', BarCodeNumber = '', LeadTimeWeeks = '',  PriceGroupFK = '', MinimumOrderQuantity = '', ReorderPointOverride = '',  MinimumStockLevelOverride = '', CurrentQuantity = '', ApplianceTypeFK = '',  Rack = '', Shelf = ''

数据库由一个已知​​可以工作并且可以在站点的其他区域工作的类管理。

我还在类上创建了一个 getQuery 方法，当查看时，我得到了 SET 命令，但这只是输出出于调试目的而运行的内容。

我看不到任何问题，也没有触发任何错误，有什么想法吗？

插入代码

$query = "INSERT INTO StockItemTypes SET " . 
    " Name = :P_Name, PartNumber = :P_PartNumber, BarCodeNumber = :P_BarCodeNumber, LeadTimeWeeks = :P_LeadTimeWeeks, " . 
    " PriceGroupFK = :P_PriceGroup, MinimumOrderQuantity = :P_MinOrderQty, ReorderPointOverride = :P_ReorderPointOverride, " . 
    " MinimumStockLevelOverride = :P_MinimumStockLevelOverride, CurrentQuantity = :P_CurrentQuantity, ApplianceTypeFK = :P_ApplianceType, " . 
    " Rack = :P_Rack, Shelf = :P_Shelf;";

$dbpars = parent::$DB->NewParamList();
parent::$DB->addParam($dbpars, ':P_Name', $StockItemName);
parent::$DB->addParam($dbpars, ':P_PartNumber', $PartNumber);
parent::$DB->addParam($dbpars, ':P_BarCodeNumber', $Barcode);
parent::$DB->addParam($dbpars, ':P_LeadTimeWeeks', $LeadTime);
parent::$DB->addParam($dbpars, ':P_PriceGroup', $PriceGroup);
parent::$DB->addParam($dbpars, ':P_MinOrderQty', $MinOrderQTY);
parent::$DB->addParam($dbpars, ':P_ReorderPointOverride', $NewReOrderQTY);
parent::$DB->addParam($dbpars, ':P_MinimumStockLevelOverride', $MinStockLevelOverride);
parent::$DB->addParam($dbpars, ':P_CurrentQuantity', $CurrentStockQTY);
parent::$DB->addParam($dbpars, ':P_ApplianceType', $ApplianceType);
parent::$DB->addParam($dbpars, ':P_Rack', $Rack);
parent::$DB->addParam($dbpars, ':P_Shelf', $Shelf);

parent::$DB->Run($query, $dbpars);

数据库类

    public function Connect()
    {
        try 
        {
            $t = array(
                PDO::ATTR_EMULATE_PREPARES => false, 
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, 
                PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true
            );

            $this->db = new PDO('mysql:host=' . DB_HOST . ';dbname=' . DB_NAME . ';charset=utf8', DB_USER, DB_PASSWORD, $t);
            unset($t);
        }
        catch(PDOException $ex) 
        { 
            $this->Error($ex->getMessage(), 'Connection', $ex->getFile(), $ex->getLine(), $ex->getTraceAsString());
        }
    }

    public function Run($QUERY, $PARAMS = array())
    {

        $this->Connect();

        try 
        {
            $sql = $this->db->prepare($QUERY);

            if (count($PARAMS) > 0)
            {   
                foreach ($PARAMS as $KEY=>$VAL)
                {
                    $sql->bindParam(str_replace(":", "", $KEY), $VAL);
                }
            }

            $sql->execute();
            $this->SQLErrorInfo = $sql->errorInfo();

        }
        catch (Exception $ex)
        {
            $this->Error($ex->getMessage(), $QUERY, $ex->getFile(), $ex->getLine(), $ex->getTraceAsString());
            $this->SQLErrorCode = $sql->errorCode();
            $this->SQLErrorInfo = $sql->errorInfo();
            exit();
        }
        if (!$sql)
        {
            //fatal error
            $ex = $sql->errorInfo();
            $this->Error($ex[2], $QUERY, __FILE__, __LINE__, print_r(debug_backtrace(), true));
            exit();
        } 

        $this->last_insert_id = $this->db->lastInsertId();
        $this->affected_rows = $sql->rowCount();

        return $sql;//retval
    }

    public function addParam(&$PARAMLIST, $PARCODE, $VAL)
    {
        $PARAMLIST[$PARCODE] = $VAL;
    }

    public function getQuery($QUERY, $PARAMLIST)
    {
        $retval = '/* params */' . "\n";
        if (count($PARAMLIST) > 0)
        {
            foreach ($PARAMLIST as $key=>$val)
            {
                $newkey = str_replace(":", "", $key);
                $retval .= 'SET @'. $newkey . ' = "' . $val . '";' . "\n";
                $QUERY = str_replace($key, "@".$newkey, $QUERY);
            }
        }
        $retval .= "\n" . '/* Query */' . "\n" . $QUERY;
        return $retval;
    }

getQuery 调用的结果显示变量已设置

/* params */
SET @P_Name = "test";
SET @P_PartNumber = "test";
SET @P_BarCodeNumber = "test";
SET @P_LeadTimeWeeks = "1";
SET @P_PriceGroup = "1";
SET @P_MinOrderQty = "0";
SET @P_ReorderPointOverride = "1";
SET @P_MinimumStockLevelOverride = "0";
SET @P_CurrentQuantity = "1";
SET @P_ApplianceType = "2";
SET @P_Rack = "";
SET @P_Shelf = "";

/* Query */
INSERT INTO StockItemTypes SET  Name = @P_Name, PartNumber = @P_PartNumber, BarCodeNumber = @P_BarCodeNumber, LeadTimeWeeks = @P_LeadTimeWeeks,  PriceGroupFK = @P_PriceGroup, MinimumOrderQuantity = @P_MinOrderQty, ReorderPointOverride = @P_ReorderPointOverride,  MinimumStockLevelOverride = @P_MinimumStockLevelOverride, CurrentQuantity = @P_CurrentQuantity, ApplianceTypeFK = @P_ApplianceType,  Rack = @P_Rack, Shelf = @P_Shelf;

运行上述代码会将数据插入数据库，表明问题出在 PDO 中的某个位置。

Answer 1

bindParam 绑定(bind)到变量的引用，因此您将所有参数绑定(bind)到同一个变量 $VAL，并且它们都获取最后一个值变量的值。

您的数据库类应该使用bindVal而不是bindParam。它绑定(bind)到变量的当前值，而不是调用 execute() 时的值。

我不太清楚你为什么要做这些复杂的事情。您基本上是在复制 execute() 当给定一个数组作为参数时所做的事情，自动绑定(bind)该数组中的所有参数。您可以在您的函数中执行此操作。

public function Run($QUERY, $PARAMS = array())
{

    $this->Connect();

    try 
    {
        $sql = $this->db->prepare($QUERY);

        $sql->execute($PARAMS);
        $this->SQLErrorInfo = $sql->errorInfo();

    }
    catch (Exception $ex)
    {
        $this->Error($ex->getMessage(), $QUERY, $ex->getFile(), $ex->getLine(), $ex->getTraceAsString());
        $this->SQLErrorCode = $sql->errorCode();
        $this->SQLErrorInfo = $sql->errorInfo();
        exit();
    }
    if (!$sql)
    {
        //fatal error
        $ex = $sql->errorInfo();
        $this->Error($ex[2], $QUERY, __FILE__, __LINE__, print_r(debug_backtrace(), true));
        exit();
    } 

    $this->last_insert_id = $this->db->lastInsertId();
    $this->affected_rows = $sql->rowCount();

    return $sql;//retval
}

也没有必要从 $KEY 中删除 :。