I would like to know what query is executed using PHP PDO. I have: <?php
try {
$DBH = new PDO("mysql:host=localhost;dbname=mytable", 'myuser', 'mypass');
}
catch(PDOException $e) {
echo $e->getMessage();
}
$DBH->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );
$DBH->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
$STH = $DBH->("INSERT INTO mytable (column1, column2, column3 /* etc...*/) value (:column1, :column2, :column3 /* etc...*/)");
$STH->bindParam(':column1', $column1);
$STH->bindParam(':column2', $column2);
$STH->bindParam(':column3', $column3);
/* etc...*/
$STH->execute();
// what is my query?
I would like to get something like: INSERT INTO mytable (column1, column2, column3) value ('my first column', 32, 'some text')
Is it possible? Thanks (PHP 5 >= 5.1.0, PHP 7, PHP 8, PECL pdo >= 0.9.0) PDOStatement::debugDumpParams — Dump an SQL prepared command Descriptionpublic PDOStatement::debugDumpParams(): ?bool This is a debug function, which dumps the data
directly to the normal output. Tip As with anything that outputs its result directly to the browser, the output-control functions can be used to capture the output of this function, and save it in a string (for example). This will only dump the parameters in the statement at the moment of the dump. Extra parameters are not stored in the statement, and not
displayed. ParametersThis function has no parameters. Return Values Returns null , or false in case of an error. Changelog
Version | Description |
---|
7.2.0
| PDOStatement::debugDumpParams() now returns the SQL sent to the database, including the full, raw query (including the replaced placeholders with their bounded values). Note, that this will only be available if emulated prepared statements are turned on.
|
ExamplesExample #1 PDOStatement::debugDumpParams() example with named parameters
<?php /* Execute a prepared statement by binding PHP variables */ $calories = 150; $colour = 'red'; $sth = $dbh->prepare('SELECT name, colour, calories FROM fruit WHERE calories < :calories AND colour = :colour'); $sth->bindParam(':calories', $calories, PDO::PARAM_INT); $sth->bindValue(':colour', $colour, PDO::PARAM_STR, 12); $sth->execute();$sth->debugDumpParams();?>
The above example will output: SQL: [96] SELECT name, colour, calories
FROM fruit
WHERE calories < :calories AND colour = :colour
Params: 2
Key: Name: [9] :calories
paramno=-1
name=[9] ":calories"
is_param=1
param_type=1
Key: Name: [7] :colour
paramno=-1
name=[7] ":colour"
is_param=1
param_type=2
Example #2 PDOStatement::debugDumpParams() example with unnamed parameters
<?php/* Execute a prepared statement by binding PHP variables */ $calories = 150; $colour = 'red'; $name = 'apple';$sth = $dbh->prepare('SELECT name, colour, calories FROM fruit WHERE calories < ? AND colour = ?'); $sth->bindParam(1, $calories, PDO::PARAM_INT); $sth->bindValue(2, $colour, PDO::PARAM_STR); $sth->execute();$sth->debugDumpParams();?>
The above example will output: SQL: [82] SELECT name, colour, calories
FROM fruit
WHERE calories < ? AND colour = ?
Params: 2
Key: Position #0:
paramno=0
name=[0] ""
is_param=1
param_type=1
Key: Position #1:
paramno=1
name=[0] ""
is_param=1
param_type=2
See Also- PDO::prepare() - Prepares a statement for execution and returns a statement object
- PDOStatement::bindParam() - Binds a parameter to the specified variable name
- PDOStatement::bindValue() - Binds a value to a parameter
mark at manngo dot net ¶ 8 years ago
As noted, this doesn’t actually simply print the prepared statement with data to be executed.
For trouble shooting purposes, I find the following useful:
<?php function parms($string,$data) { $indexed=$data==array_values($data); foreach($data as $k=>$v) { if(is_string($v)) $v="'$v'"; if($indexed) $string=preg_replace('/\?/',$v,$string,1); else $string=str_replace(":$k",$v,$string); } return $string; }// Index Parameters $string='INSERT INTO stuff(name,value) VALUES (?,?)'; $data=array('Fred',23);// Named Parameters $string='INSERT INTO stuff(name,value) VALUES (:name,:value)'; $data=array('name'=>'Fred','value'=>23);
print
parms($string,$data); ?>
Lucas ¶ 6 years ago
This function doesn't have a return, so if you want to do something with it you'll have to do something like
<?php function pdo_debugStrParams($stmt) { ob_start(); $stmt->debugDumpParams(); $r = ob_get_contents(); ob_end_clean(); return $r; }// omitted: connect to the database and prepare a statement echo '<pre>'.htmlspecialchars(pdo_debugStrParams($stmt)).'</pre>'; ?Source: http://stackoverflow.com/questions/22157331/something-like-debugdumpparams-in-pdo-settable-to-a-string
How do I get a query in PDO?
SELECT query without parameters
If there are no variables going to be used in the query, we can use a conventional query() method instead of prepare and execute. $stmt = $pdo->query("SELECT * FROM users"); This will give us an $stmt object that can be used to fetch the actual rows.
How can I get last insert ID in PDO?
You can get the id of the last transaction by running lastInsertId() method on the connection object($conn).
What does PDO
PDO::query() returns a PDOStatement object, or FALSE on failure.
What is PDO query in PHP?
PDO::query() prepares and executes an SQL statement in a single function call, returning the statement as a PDOStatement object.
|