0

When executing a PDO bindParam call the values being inserted into the DB are empty.

Looking at the log the parameters don't appear to be transmitting

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 = ''

The database is managed by a class that is known to work and does work on other areas of the site.

I have also created a getQuery method on the class and when viewing that I get the SET commands present, but this just outputs what would be ran for debug purposes.

I can't see any issues and no errors are triggered, any ideas?

Insert Code

$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 Class

 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; }

Result of getQuery call showing the variables are set

/* 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;

running the above inserts the data into the DB indicating the issue is somewhere in PDO.

Improve this question
4
  • Why not extend PDOStatement? I mean, having to parse $dbpars everytime seems deficient. Commented Mar 15, 2017 at 23:04
  • Also you should PDO::quote() the $val in the $retval assignment. Commented Mar 15, 2017 at 23:14
  • @Xorifelse on what method should I be using the PDO::quote() ? Commented Mar 15, 2017 at 23:15
  • 1
    $retval .= 'SET @'. $newkey . ' = "' . $this->db->quote($val) . '";' . "\n";, if you don't try adding a " as 1 of the values and you will see why. Commented Mar 15, 2017 at 23:17

1 Answer 1

Reset to default
2

bindParam binds to a reference to the variable, so you're binding all your parameters to the same variable $VAL, and they're all getting the last value of the variable.

Your database class should use bindVal rather than bindParam. This binds to the variable's current value, not its value when the execute() call is made.

I'm not really sure why you're doing all this complicated stuff. You're basically replicating what execute() does when it's given an array as an argument, automatically binding all the parameters from that array. You can just do that in your function.

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 }

It's also not necessary to remove : from $KEY.

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

6 Comments

this doesn't explain why the same system works elsewhere on the same site with multiple parameters? Originally bindVal didn't work in the call but bindParam did on earlier versions of php, I'll try swapping back see if this has any adverse affects
I can't explain that, it looks to me like it should never work.
Switching to bindVal has corrected this issue, doesn't explain why bindParam works everywhere else but I'll mark this as the accepted answer
I really suggest you just use execute($PARAMS);. You're just duplicating what that does.
The other developers that use this come from a non PDO environment so they found it is easier this way
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.