当执行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);
DB类
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;
运行上面的命令会将数据插入DB,表明问题出在PDO中。
最佳答案
bindParam
绑定到对变量的引用,因此将所有参数都绑定到同一个变量$VAL
,它们都将获取变量的最后一个值。
您的数据库类应使用bindVal
而不是bindParam
。这绑定到变量的当前值,而不是execute()
调用时的值。
我不确定自己为什么要做所有这些复杂的事情。当给定数组作为参数时,基本上就是在复制execute()
的行为,并自动绑定该数组中的所有参数。您可以在函数中执行此操作。
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
。关于php - PDO bindparam空值,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/42822312/