PHP int被MySQL转换为不同的字符串

Question

I've got this piece of code in a Magento script:

$order = $observer->getEvent()->getOrder();
$customer_id = $order->getBillingAddress()->getCustomerId();

$idArray = array('LN123456789XZY'); // this is just arbitray sample data, the array can be any length

$prequery = "UPDATE $detailedTable SET ordered=1 WHERE customer_id=$customer_id AND image_id IN (";
$qPart = array_fill(0, count($idArray), "?");
$prequery .= implode(",", $qPart) . ")";
$query = $dbLink->prepare($prequery);
$i = 1;
foreach($idArray as $elem){
        $query->bindValue($i++, (string) $elem, PDO::PARAM_STR);                
}
$query->execute();

And what I'm seeing is the value for $customer_id is being converted into a string with a completely different value. The data types in my table for those two columns are:

customer_id => VARCHAR(64)
image_id => TEXT

So I realize that my first problem is that I'm not surrounding $customer_id with single quotes. But what’s happening because of it is an int value like 1011 in PHP is getting converted to something like 'g-608311' when it gets written to MySQL. And its happenening intermittently.

So as I'm pondering how to clean things up in my table, I'm curious how MySQL is coming up with that 'g-608311' string from the int 1011 when there's the data type mismatch?

EDIT: I updated the code snippet to show where the $customer_id value actually comes from. Also, I thought of this earlier today while pondering this issue, but when I initially ran this code, I was getting an exception with the message:

message SQLSTATE[22007]: Invalid datetime format: 1292 Truncated incorrect DOUBLE value: 'g-608311' 

Answer 1

Although I can't tell you exactly why you get different values due to your unquoted string input, but I can point out how ridiculous it is to use prepared statements... and then embed variables directly in the query anyway.

Your query should be:

$prequery = "UPDATE `literal_table_name_here` SET `ordered`=1
   WHERE `customer_id` = ?
   AND `image_id` IN (".str_repeat("?,",count($idArray)-1)."?)";
$query = $dbLink->prepare($prequery);
$query->bindValue(1,$customer_id);
foreach($idArray as $i=>$elem) {
   $query->bindValue($i+2, $elem, PDO_PARAM_STR);
}
$query->execute();