asked on 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:
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
Any help will be greatly appreciated.
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();
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
Any help will be greatly appreciated.
PHPMySQL ServerWindows 7SQL
Last Comment
APD Toronto
ASKER
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?
How would I do the var_dump() and do you know anything above that MySQL query log?
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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"'
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
In PHP: http://php.net/manual/en/language.types.null.php
$x = NULL;
$statement->bindValue(':my
In PHP: http://php.net/manual/en/language.types.null.php
ASKER
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'
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
All the best, ~Ray
ASKER
Thanks Ray
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.