13

Possible Duplicate:
Retrieve (or simulate) full query from PDO prepared statement

I can't figure out why my query is returning 0 rows.. it implements some very dynamic search functionality, and a lot of if/loop statements etc. Therefor to debug it, I'd like to see EXACTLY what string is being sent to the server. Is there a way to do this through PHP?

Is there maybe a way to ask the server "what was the last query", or tell PDO "show me what you sent"?

I saw one response using str_replace to manually enter the values in place of :fieldValue, but it's likely a syntax problem (or maybe it's going through an incorrect loop, etc), which this method doesn't help with.

Using bindValue(":fieldValue", $value); if that makes a difference.

EDIT

Turns out it was a simple if ($var="true") { ... which should have been if ($var=="true") { .... PHP I guess is not the same as Java in that sense? Either way, the question still stands (as I run into this often). I had to use a series of echo "You are Here"; to find this error, as it was technically valid but not correct. If I had the final SQL statement, I could have seen "Oh, my code has added the where column = true, must have gone through the wrong IF...".

Community
  • 1
  • 1
StuckAtWork
  • 1,613
  • 7
  • 23
  • 37
  • 1
    If the prepares / binds are not emulated, the query of which you speak does not exist anywhere. Statements and binds are then integral to the database. However, enabling the `query_log` on your development server (never do it in production) may shed some light on what is done. – Wrikken Jun 20 '12 at 15:15
  • Well that's the thing; I don't know what to try. I usually do this using a simple `echo $sql` where `$sql` is my statement, but with PDO (which I'm new to), it simply shows me `SELECT column FROM table WHERE column = :fieldValue` which doesn't help me if the where statement ends up being wrong somewhere. – StuckAtWork Jun 20 '12 at 15:16
  • 2
    possible duplicate of [Retrieve (or simulate) full query from PDO prepared statement](http://stackoverflow.com/questions/3754530/retrieve-or-simulate-full-query-from-pdo-prepared-statement) and http://stackoverflow.com/questions/530627/getting-a-pdo-query-string-with-bound-parameters-without-executing-it and http://stackoverflow.com/questions/1786322/in-php-with-pdo-how-to-check-the-final-sql-parametrized-query – Lawrence Cherone Jun 20 '12 at 15:25
  • @StuckAtWork can you show us the code that dose not work. – Lawrence Cherone Jun 20 '12 at 15:33

1 Answers1

0

That's the single most common myth about SQL debugging. "I need to see the query after preparation to be able to tell if an error occurred". The fact is, you don't, and I'll tell you why.

Once a query has been prepared, the placeholder can be considered as a valid string/integer. You don't care what's in it.

Also, if you set up PDO correctly, you'll get a detailed PDOException detailing the error you've had along with a complete backtrace of where the error happened, plus you get the error string from MySQL, which makes syntax errors very easy to find.

To enable PDO Exceptions and disable emulated prepares:

$pdo = new PDO("mysql:host=localhost;dbname=database_name", "user", "password");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
Madara's Ghost
  • 172,118
  • 50
  • 264
  • 308
  • 1
    I've got my error handling proper; the problem is that there IS NO ERROR. I needed to know what was actually being sent so that I would be able to tell where in my code it was getting its information. Without being able to examine the final string, I could not tell what was doing what. "Returning 0 rows", not "throwing an error". – StuckAtWork Jun 20 '12 at 15:29
  • @StuckAtWork: If you're not using something like global space, you should have no problem to tell what's going into your query. – Madara's Ghost Jun 20 '12 at 15:31
  • 1
    See edit in answer; the problem wasn't something obvious, but it would have been had I been able to see what it exactly it was binding. Depending on several conditions, it binds something different to the `:fieldValue`s. I had to manually go through and check which lines were executing to see what the `:fieldValue` was being bound as. – StuckAtWork Jun 20 '12 at 16:25
  • Valid syntax does not mean no error. My debugging functions need to know what the bound values were after the fact, and there are large classes of errors with valid syntax. – spraff Jan 05 '14 at 13:15