MySQL returning PDO placeholder names

Question

There's a [similar post][1], but without a solution.

The following code is resulting in a MySQL query containing the placeholder names:

    $the_image_itself = "abcde123def.jpg";
    $title = "A Book";
    $description = "Something to Read";
    $the_image_itself = "%".$the_image_itself;

    $stmt = $db->prepare("UPDATE nky_posts SET `post_title`=:title, `post_content`=:description WHERE `guid` LIKE :the_image_itself");

    $stmt->bindParam(':title', $title);
    $stmt->bindParam(':description', $description);
    $stmt->bindValue(':the_image_itself', $the_image_itself, PDO::PARAM_STR);
    $stmt->execute();
    $stmt->debugDumpParams();
    echo "<hr/>";
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    $affected_rows = $stmt->rowCount();

The result looks like:

 start SQL: [105] UPDATE nky_posts SET `post_title`=:title,
     `post_content`=:description     
     WHERE `guid` LIKE :the_image_itself 
     Params: 3 Key: 
     Name:     [6] 
     :title paramno=-1 
     name=[6] ":title" 
     is_param=1 param_type=2 
     Key: Name: [12] :description 
     paramno=-1 name=[12] ":description" 
     is_param=1 param_type=2 
     Key: Name: [17] :the_image_itself paramno=-1 
     name=[17] ":the_image_itself" 
     is_param=1 param_type=2 

This is the object call:

try{
$db=new PDO('mysql:host=localhost;dbname=viewingr_ssdevwp;   charset=utf8',$db_username,$db_password);
} 
catch(PDOException $e){
echo 'Error connecting to MySQL!: '.$e->getMessage();
exit();
}

Answer 1

I don't know where you got the impression that debugDumpParams() will display the raw SQL query -- it will not. When using parameterized queries, you create a prepared statement at the database, and then send the parameter values alone. They are not sent together, meaning there's no way print the raw SQL query.

debugDumpParams() will only display the list of parameters, their names, types etc. but not their values. One thing you can do, however, is to inspect your MySQL query log to see the raw SQL query that was executed.

Once you've found the logs, you can use the following command to see the recently executed queries (provided you have SSH access):

$ sudo tail -f /usr/local/mysql/data/yourQueryLog.log

The above path is just an example. The actual path might be different on your system.

Answer 2

Well the "answer" is posted below, but the real answer is that I should have ceased banging my head against this problem and come back to it at a later date, which seems to be one of the most difficult things for me to do. At one point in my obsession I discovered a mysterious <br/> followed by some whitespaces in one of the placeholder values. I ended up doing substr($var, 0, -6) on the variable to remove the anomaly until noticing that I had inadvertently concatenated a <br/> to the end of the line that populated the variable; .<br/> - probably when deleting a line of output code for testing.

I was on the line with hostMonster tech support to try and get to MySQL logs because people say that that is the only place one can find out exactly WHAT MySQL is "seeing" when you use placeholders, but they don't log MySQL queries, because the file would be in the terrabytes.

At 3 or 4 am, I gave up.

Came back to it with a fresh head today and went through the following steps confirming each worked:

1. Create a simple SELECT statement without WHERE or placeholders:

   $sql = "SELECT * FROM nky_posts";
   

2. Add a WHERE clause using "=" (not LIKE) with variable being something literal I know is in the DB:

   $the_image = "image_url_from_phpMyAdmin";
   $sql = "SELECT post_title FROM nky_posts WHERE guid = $the_image";
   

3. Substitute the literal variable with a single placeholder holding a known value:

   $the_image = "image_url_from_phpMyAdmin";
   
   $stmt->bindParam(':the_image', $the_image, PDO::PARAM_STR);
   
   $sql = "SELECT post_title FROM nky_posts WHERE guid = :the_image";
   

4. Add the LIKE instead of = (remembering to concatenate placeholder variable with "%")

   $the_image = "%" . $the_image . "%";
   
   $stmt->bindParam(':the_image', $the_image, PDO::PARAM_STR);
   
   $sql = "SELECT post_title FROM nky_posts WHERE guid LIKE :the_image_itself";
   

5. Replace the "known" variable with dynamic variable (from XML result in this case):

   basename($oBookNode->getElementsByTagName('actual-link')->item(0)->nodeValue);
   

   (Using basename() function to return just the image name from URL string in wordpress database)

6. Finally replace the SELECT statement with my UPDATE statement, adding two additional placeholders to hold the variables to be inserted. Final code:

   $sql = "UPDATE nky_posts SET post_title=:title, post_content=:description WHERE guid LIKE :the_image";
   
   $stmt = $db->prepare($sql);
   
   //foreach loop begins here
   foreach ($oDOM->getElementsByTagName('item') as $oBookNode)
   {
   
   $the_image = basename($oBookNode->getElementsByTagName('actual-link')->item(0)->nodeValue);
   
   $title = $oBookNode->getElementsByTagName('title')->item(0)->nodeValue;
   
   $description = $oBookNode->getElementsByTagName('actual-content')->item(0)->nodeValue;
   
   //concat % to variable for LIKE clause (probably only needed first one in this case, but...)
   
   $the_image = "%" . $the_image . "%";
   
   
   $stmt->bindParam(':title', $title, PDO::PARAM_STR);
   
   $stmt->bindParam(':description', $description, PDO::PARAM_STR);
   
   $stmt->bindParam(':the_image_itself', $another_image_itself, PDO::PARAM_STR);
   
   $stmt->execute();
   //end foreach loop
   }
   

   Thanks for the help, everyone.

Answer 3

The output from the debugDumpParams function call looks right.

That debugDumpParams function doesn't display the values of the bind parameters; it only shows the SQL text along with the placeholder names/positions, and their respective datatypes.

I'm not sure I understood the question you asked.

---

There's no need to invoke the fetchAll method on stmt, since it is an UPDATE statement.

Note that for rowCount, MySQL returns the number of rows that were actually changed by the statement, not the number of rows matched. That is, if the values in the columns being set were already set to the specified value, then MySQL doesn't "count" that row as being affected.