Jump to content

Insert using prepared procedure puts bad data in the table

doni49

By doni49
in MySQL Help

 Share
Go to solution Solved by mac_gyver,

Recommended Posts

doni49 Member

doni49

Posted
Posted

I'm trying to setup a prepared statement to insert data in my table.  When I insert the data, it appears to work fine unti........... I look at the data that was actually inserted -- the date is listed in the DB with all zeros. 

 

Here's what the statement looks like:

$isTrue = 'true';
$q = "INSERT INTO `" . $tableName . "` (`payee`,`date`,`memo`,`acctID`,`ref`,`hasChildren`,`userID`) VALUES (?,?,?,?,?,?,?);";
$stmt = $conn->prepare($q);
$stmt->bind_param("sssisii",$txn['payee'] , $txn['date'] , $txn['memo'] ,$txn['acctID'] ,$txn['ref'] ,$isTrue, $uID);

This is the code I'm using to execute the insert:

echo "<pre>";
print_r($txn);
echo "</pre>";
if($stmt->execute()){
  $txnegories[]=$lastID = $conn->insert_id;
} else {
  throw new Exception($conn->error);
}

This is what the print_r displays in the browser.

Array
(
    [ref] => null
    [date] => 2015-12-10
    [payee] => John SMith
    [memo] => null
    [acctID] => 1
)

This is what the table definition looks like as well as the contents of the table:

CREATE TABLE IF NOT EXISTS `transactions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `ref` varchar(10) DEFAULT NULL,
  `date` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `payee` varchar(50) DEFAULT NULL,
  `amount` decimal(10,2) DEFAULT NULL,
  `memo` varchar(50) DEFAULT NULL,
  `catID` bigint(20) unsigned DEFAULT NULL,
  `acctXID` smallint(6) unsigned DEFAULT NULL,
  `acctID` smallint(6) unsigned DEFAULT NULL,
  `version` int(11) DEFAULT NULL,
  `userID` bigint(20) DEFAULT NULL,
  `type` enum('normal','transfer') DEFAULT 'normal',
  `hasChildren` tinyint(1) NOT NULL DEFAULT '0',
  `parentID` bigint(20) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fkTxnParentID` (`parentID`),
  KEY `fkAcctID` (`acctID`),
  KEY `fkCatID` (`catID`),
  KEY `fkAcctXID` (`acctXID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4315 ;

INSERT INTO `transactions` (`id`, `ref`, `date`, `payee`, `amount`, `memo`, `catID`, `acctXID`, `acctID`, `version`, `userID`, `type`, `hasChildren`, `parentID`) VALUES
(4283, NULL, '0000-00-00 00:00:00', 'Jack McCoy', '10.25', 'null', 4, NULL, 1, NULL, 4, 'normal', 0, NULL);
doni49 Member

doni49

Posted
  • Author
Posted

Mac,

 

It's worse that I thought.  After seeing your post, I went back and looked again.  I found a spot in my php file where $txn['date'] did actually have an invalid value -- outside of the for loop where the execute is located.  So I removed that line and have found that it's not using the variable values as they're defined at the time the execute statement runs.  I thoguht that was one of the main points of prepared statements (in addition to better control of what gets entered).

 

Now that I removed the line that set the variable outside the for loop, this is what the entire php file looks like.  The entire row of data that gets added to the table has NULL values.  But the directly above the execute line, I did a print_r showing that the values aren't NULL.

<doctype HTML>
<html>
<head>
</head>
<body>
<?php
//header ("Content-type: application/json; charset=utf-8");
require("/home/user/includes/db_Connect.inc.php");
$tableName = 'transactions';
$putData = json_encode(array(
  array("ref"=>"null","date"=>"2015-12-17","payee"=>"Jack McCoy","memo"=>"null","acctID"=>'1',"amount"=>"10.25","catID"=>"4","acctXID"=>"NULL","type"=>"normal")
));
$arr = json_decode($putData);

$cnt = -1;
$uID = 4;
  try{
    $isTrue = 'true';
    $isFalse = 'false';
    $q = "INSERT INTO `" . $tableName . "` (`payee`,`date`,`memo`,`acctID`,`acctXID`,`ref`,`type`,`hasChildren`,`amount`,`catID`,`userID`) VALUES (?,?,?,?,?,?,?,?,?,?,?)";
    if($normalStmt = $conn->prepare($q)){
      $normalStmt->bind_param("sssiissidii",$txn['payee'],$txn['date'],$txn['memo'],$txn['acctID'],$txn['acctXID'],$txn['ref'],$txn['type'],$isFalse,$txn['amount'] ,$txn['catID'], $uID);
    }else {
      printf("(Normal) Error message: %s\n<br>", $conn->error);
    }
    foreach ($arr as $txn){
      $cnt++;
      $txn = (array)$txn;
      unset($query);
    echo "<pre>";
    print_r($txn);
    echo "</pre><br />";
        $query = "INSERT INTO `" . $tableName . "` (`payee`,`date`,`memo`,`acctID`,`ref`,`amount`,`catID`,`userID`) VALUES ('" . $txn['payee'] . "'," . $txn['date'] . ",'" . $txn['memo'] . "'," . $txn['acctID'] . "," . $txn['ref'] . "," . $txn['amount'] . "," . $txn['catID'] . "," . $uID . ");";
        if ($normalStmt->execute()){//$conn->query($query) === TRUE) {
          $rowID[] = $conn->insert_id;
        } else {
          throw new Exception($conn->error);
        }
      }
  }catch(Exception $e) {
    echo '\nCaught exception: ',  $e->getMessage(), "\n";
  }
}
?>
</body>
</html>
doni49 Member

doni49

Posted
  • Author
Posted

This is what my browser displays (per the print_r instruction).

Array
(
    [ref] => null
    [date] => 2015-12-17
    [payee] => Jack McCoy
    [memo] => null
    [acctID] => 1
    [amount] => 10.25
    [catID] => 4
    [acctXID] => NULL
    [type] => normal
)

And this is what was added to the db table.

(4403, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 4, NULL, 0, NULL)
  • Solution
mac_gyver Prolific Member

mac_gyver

Posted
  • Solution
Posted (edited)

this is a problem with the foreach($arr as $txn) loop and references. the $txn variable that the foreach loop creates is a new variable and any reference(s) when you ran the bind_param() statement no longer exists.

 

your example is only looping over a single set of data. if you don't need to do this in a loop, don't.

 

if you do need to do this in a loop, either 1) bind individually named variables, $payee, $date, , ..., then, inside the foreach(){} loop, assign each named element of $txn to the correct named variable, $payee = $txn['payee'];, ...or 2) bind elements of a differently  named array, $temp['payee'], $temp['date'], ..., then, inside the foreach loop, assign each named element of $txn to the correct named element in $temp (you can actually use a second foreach loop to do this). if you use the $temp array method, you must assign each element individually inside the loop. you cannot simply do $temp = $txn; because this will end up referencing the last element in the $arr every time through the loop, if i remember correctly.

Edited by mac_gyver
doni49 Member

doni49

Posted
  • Author
Posted

Thanks Mac.  I would never have guessed that.  I think I can handle the temp var suggestion.

 

The data set you're seeing is only test data.  After I get this working, this php script will be receiving data in json format and there will almost always be a lot more transactions.  That's why I'm doing it in a for loop.

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.