Convert PDO Bindings to Reqular Qury String

Hi Experts,

Is there any way I can convert a PDO Bound query into a regular string?  For example, I have:

$query  = "UPDATE reservations SET "
                .   "user_initials = :user_initials "
                . ", user_id = :user_id "
                . "WHERE id = :id;";
        
        $db = Database::getDB();
         
        echo $query . '<br>';
        
        $statement = $db->prepare($query);
        
            $statement->bindValue(':user_initials', $book['user_initials']);
            $statement->bindValue(':user_id', $book['user_id']);
            $statement->bindValue(':id', $book['id']);
  /*          
        $test = "";
        $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, $test);
        
        echo '<br>' . $test . '<br>';
*/
        $statement->execute();
        $statement->closeCursor();

Open in new window


However, I actually have 50+ bound values, and I'm getting an error Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' trans_paym_loc = 0 , trans_payee_type = D , trans_payee_id = 1 , trans_paym_dat' at line 1'  in ...

Whenever I get such an error I usually echo my query, copy it into phpMyAdmin, then I can see clearly what values are causing that problem, and go from there.

My question is, how can I translate this into a normal query, so that I can run it in phpMyAdmin?  I have read about PDO::ATTR_EMULATE_PREPARES, which I am trying at the bottom of my script, but I am not getting anything, so I am not sure how to use it. I have also read about MySQL's query log, but I am not sure how to access it.

Any help will be greatly appreciated.
APD TorontoSoftware DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
I don't know of an automated way to do this, but if you want to post the complete query string, I'll be glad to try and help.  FWIW, this is one of the reasons I prefer MySQLi over PDO.

You might also consider catching the PDO exception and looking at it with var_dump().  Not sure what you would find, but it would be one of the things I would try first.
APD TorontoSoftware DeveloperAuthor Commented:
The way of the query looks the same as above (ie- field name/assignment).

How would I do the var_dump() and do you know anything above that MySQL query log?
Ray PaseurCommented:
The way of the query looks the same as above
I kind of figured that.  I just wanted to try to give you some practical help to get the query right.  I can't do that unless you show us the real query.  But I can point you to some learning resources that show some ways of doing queries in MySQL, MySQLi, and PDO.  These are all done in parallel construction so you can map the functionality across the three extensions.

The try / catch / var_dump() to print out the exception data is shown in this article.  Look for "PDO - Prepare a Query" and "Create and Execute a SELECT Query" where there is a PDO example.  If you have time to read the whole article it will probably give you some ideas that can save you development / debug time.
http://www.experts-exchange.com/articles/11177/PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

I use MySQL's own error message diagnostics, a class of my own, and Sequel Pro when I'm working on SQL things,  so I'm not familiar with the MySQL query log, sorry.  I'm not even sure that a query with a syntax error would make it into a log file - it was never run, so there would not be much information to log about it.  MySQL is already telling you everything it knows - that there was a syntax error and approximately where the error was in the query string.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

FREE DOWNLOAD
APD TorontoSoftware DeveloperAuthor Commented:
I have a suspicion... if you have defined a field that can accept NULL as value, how do you send a null value?  I'm trying '"NULL"'
Ray PaseurCommented:
Honestly, I have no idea - I never use NULLs!  Maybe try creating a variable and setting it to NULL.  A quoted string containing the word NULL will be just that - a string of data.  So maybe something like this:

$x = NULL;
$statement->bindValue(':my_nullable_column', $x);

In PHP: http://php.net/manual/en/language.types.null.php
APD TorontoSoftware DeveloperAuthor Commented:
The issue was that I retrieved record from the database, which we already had null values, but I change it other fields and tried to re-save it to the database. The issue with that was that when you retrieve null from the database, they get returned as empty string, but the database will not accept an empty string, so you need to send it back as  'NULL'
Ray PaseurCommented:
If you define the fields with default values of '' (empty string) and NOT NULL,  you should be able to get a more reasonable response from the DB engine.  The empty strings will be just that - strings that are empty, and they can be used that way in queries.  I think this can make the programming easier.

All the best, ~Ray
APD TorontoSoftware DeveloperAuthor Commented:
Thanks Ray
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.