为什么这个准备插入到MySql中，PDO插入错误的值？

Question

I am trying to seed a database table with map points reflecting each point in a jpg map. The code is a test with tiny 3x7 image, but the application is intended for much larger maps, in which map point information will associated with each point and stored in the table. The map_terrain field represents the color hex value for a given coordinate. But it is the associated x,y values that are going wrong.

I am ending up with each row in the table with map_point_x = 7 and map_point_y = 3. The map_terrain field has the correct values.

How is this going astray?

$image_filespec = '../test.jpg';
$map_id = 1;

include_once '../includes/functions.php';

function heximagecolorat($image, $x, $y, $sharp_prefix = TRUE) {

        $rgb = imagecolorat($image, $x, $y);

        $r = ($rgb >> 16) & 0xFF;
        $g = ($rgb >> 8) & 0xFF;
        $b = $rgb & 0xFF;

   If ($sharp_prefix == TRUE) {
       $hex = "#";
   } else {
       $hex = "";
   }
   $hex .= str_pad(dechex($r), 2, "0", STR_PAD_LEFT);
   $hex .= str_pad(dechex($g), 2, "0", STR_PAD_LEFT);
   $hex .= str_pad(dechex($b), 2, "0", STR_PAD_LEFT);

   return $hex; // returns the hex value including the number sign (#)
}


if ($image_filespec == 'nnn.jpg' ){
    echo 'you need to edit teh php file to make this work and alter $image_filespec as appropriate';
    return;
}


//$result = query_db('SELECT * FROM users');
//  
//while($row = $result->fetch()){
//    echo $row['uID'] . " - " . $row['uUsername'] . "<br/>";
//}

$image = imagecreatefromjpeg($image_filespec); // imagecreatefromjpeg/png/

$width = imagesx($image);
$height = imagesy($image);
$colors = array();


for ($y = 0; $y < $height; $y++) {

    for ($x = 0; $x < $width; $x++) {

        $colors[] = heximagecolorat($image, $x, $y, $sharp_prefix=FALSE) ; 
    } 
}

// connect and insert arrya to database $row in this context is talking about table row as opposed to row from the original image
$dbh = db_connect();

$query = "INSERT INTO map_points (  map_id, map_point_x, map_point_y, map_terrain ) VALUES "; //Prequery
$qPart = array_fill(0, count($colors), "( ?, ?, ?, ?)");
$query .=  implode(",",$qPart);
$stmt = $dbh -> prepare($query); 
$i = 1;

$point=0;
for ($y = 0; $y < $height; $y++) {
    for ($x = 0; $x < $width; $x++) { //bind the values one by one
        $stmt -> bindParam($i++, $map_id);
        $stmt -> bindParam($i++, $x);
        $stmt -> bindParam($i++, $y);
        $stmt -> bindParam($i++, $colors[$point]);
        $point += 1;
    }
}
$temp=$stmt;
$stmt -> execute(); //execute

Answer 1

I believe the problem is the use of PDOStatement::bindParam(): this binds the value of the variables (like $x, $y, ...) at the moment of query execution. This will be the final values after the loops, i.e. 3 and 7 in your example.

Use PDOStatement::bindValue() instead.

Additionally, it may be more easy to read and maintain your code if you execute the statement for each point, instead of using as many tuples as there are rows. In other words, use multiple INSERT INTO map_points(map_id, map_point_x, map_point_y, map_terrain) VALUES (?, ?, ?, ?) instead of one INSERT INTO map_points(map_id, map_point_x, map_point_y, map_terrain) VALUES (?, ?, ?, ?), (?, ?, ?, ?), .... Multiple tuples have better performance, but a similar performance can be reached by wrapping the INSERT statements in a transaction.

$dbh = db_connect();

$stmt = $dbh->prepare('INSERT INTO map_points (map_id, map_point_x, map_point_y, map_terrain) VALUES (?, ?, ?, ?)');
$y = 0;
$x = 0;
$stmt->bindValue(1, $map_id);
$stmt->bindParam(2, $x);
$stmt->bindParam(3, $y);
$dbh->beginTransaction();
for (; $y < $height; $y++) {
    for (; $x < $width; $x++) {
        $stmt->bindValue(4, heximagecolorat($image, $x, $y, $sharp_prefix=FALSE));
        $stmt->execute();
    }
}
$dbh->commit();