Edit report at http://bugs.php.net/bug.php?id=54861&edit=1
ID: 54861
User updated by: harrieva at gmx dot de
Reported by: harrieva at gmx dot de
Summary: query() optionaly prepared and
PDO::PARAM_FIELDNAME(quoting)
-Status: Feedback
+Status: Open
Type: Feature/Change Request
Package: PDO related
Operating System: linux
PHP Version: 5.3.6
Block user comment: N
Private report: N
New Comment:
1. SQL-Quoting:
Postgresql whants a query like this: Select "Name" from "Persons"
Mysql wants the same query like this: Select `Name` from `Persons`
Mysql has a unique interpretation of the Standard by default. When i
want to write a query whitch runs on mysql and other sql-servers i have
to quote fielnames (and tablenames) diffrent. In my eyes this is
something that should be done by PDO->quote().
(This is importend for captalized fieldnames)
2. Queryparameter:
The second thing is an idea i had. This idea is on quoting to. Here is
an example:
How it is often done:
$sql = "select * from a where bla = " . $bla;
$res = $db->query($sql);
How it should be done:
$sql = "select * from a where bla = " . $db->quote($bla);
$res = $db->query($sql);
How should be done (the nicer way):
$stmt = $db->prepare("select * from a where bla = ?");
$stmt->execute($bla)
And now i like it to be done:
$stmt = $db->query("select * from a where bla = ?",$bla);
I like the ? and :-Syntax that i can use with prepared statements. And
i like to use this syntax in query() too. Like prepare(), query()
returns a PDO::Statement, so my idea is, that query() should return an
executed prepared statement, when a second parameter is given. It saves
one line of code and it feels smother, then getting an object back, call
execute() for this object, and then call fetchall() on the same object.
Back in the days you mysql returnd resultsets, and so people are still
used to the thinking that db returns results, The Statement-Objects are
diffrent, but most people do not recordnice it because they only use
query(), Furthermore i think many people use prepare() only when a sql
is used more then one time. This is psychological, and so they don't use
the advantages of the ? and :-Syntax, because query() does not support
it...
I hope everything is clearer now ... ? ...
regards, Hendrik
Previous Comments:
------------------------------------------------------------------------
[2011-05-26 14:54:26] johan...@php.net
I do not understand what you want. Could you be more precise please?
About the " vs. ` thing: You can set the SQL mode in MySQL to be more
standards compliant. The MySQL developers are conservative in changing
the default as it will break many applications unfortunately.
http://dev.mysql.com/doc/refman/5.5/en/server-sql-mode.html (can be set
per session if you don't want/can change it globally)
------------------------------------------------------------------------
[2011-05-19 13:33:54] harrieva at gmx dot de
Description:
------------
I like prepared statements and its templating. Since query returns a
statement, why not making it a prepared one, when the second parameter
is an array, and execute it directly... Example: See my exPDO-Class at
the bottom.
Since mysql quotes fieldnames(and tablenames) different then
standardconform sqlservers, it is not easy to write/generate sql that
work everywhere... Eg. postgre lowercases fieldnames when they are not
quoted in "... Mysql wants `...
I help my self by deriving from PDO and overwrite quote...
public function quote($txt,$parameter_type = PDO::PARAM_STR ){
if($parameter_type == "12345"){
if($this->getAttribute(PDO::ATTR_DRIVER_NAME) ==
'mysql'){
return '`' . $txt . '`';
}else{
return '"' . $txt . '"';
}
}else{
return parent::quote($txt,$parameter_type);
}
}
By the way: Here is my hole extention.... Now it is possible to see all
the executed querys, and the time it took to get the result....
<?php
class extPDO extends PDO{
public $query_count = 0;
public $exec_count = 0;
public $prepared_count = 0;
public $query_time = 0;
public $sqls = array();
public function __construct($dsn, $username, $passwd,
$options=array()){
parent::__construct($dsn, $username, $passwd, $options);
self::setAttribute(PDO::ATTR_STATEMENT_CLASS,
array("extPDOStatement",array($this)));
}
public function query($statement,$args = array()){
$this->query_count++;
if(is_array($args)){
if(empty($args)){
$this->sqls[] = 'q: '.$statement;
$start = microtime(true);
$res = parent::query($statement);
$this->query_time += microtime(true) - $start;
return $res;
}else{
//keine zeitmessung da diese durchs statement
übernomen wird
$res = self::prepare($statement);
$res->execute($args);
$this->prepared_count--;
return $res;
}
}else{
$res = parent::prepare($statement);
$res->execute(array($args));
$this->prepared_count--;
return $res;
}
}
public function exec($statement,$args = array()){
$this->exec_count++;
if(is_array($args)){
if(empty($args)){
$this->sqls[] = 'e: '. $statement;
$start = microtime(true);
$res = parent::exec($statement);
$this->query_time += microtime(true) - $start;
return $res;
}else{
$res = self::prepare($statement);
$res->execute($args);
$this->prepared_count--;
return $res->rowCount();
}
}else{
$res = self::prepare($statement);
$res->execute( array($args) );
$this->prepared_count--;
return $res->rowCount();
}
}
public function quote($txt,$parameter_type = PDO::PARAM_STR ){
if($parameter_type == "12345"){
if($this->getAttribute(PDO::ATTR_DRIVER_NAME) ==
'mysql'){
return '`' . $txt . '`';
}else{
return '"' . $txt . '"';
}
}else{
return parent::quote($txt,$parameter_type);
}
}
public function prepare($statement,array $options = array()){
return parent::prepare($statement,$options);
}
}
class extPDOStatement extends PDOStatement{
private $db;
protected function __construct($db){
$this->db = $db;
}
public function execute(array $input_parameters = array()){
$this->db->sqls[] = 'p: '. $this->queryString;
$this->db->prepared_count++;
$start = microtime(true);
$res = parent::execute($input_parameters);
$this->db->query_time += microtime(true) - $start;
return $res;
}
}
?>
------------------------------------------------------------------------
--
Edit this bug report at http://bugs.php.net/bug.php?id=54861&edit=1
