Главная » Языки программирования

Php pdo query string

Содержание
  1. PDO::query
  2. Parameters
  3. Return Values
  4. Errors/Exceptions
  5. Examples
  6. See Also
  7. The PDO class
  8. Class synopsis
  9. Table of Contents
  10. User Contributed Notes 8 notes
  11. PDO::query
  12. Список параметров
  13. Возвращаемые значения
  14. Примеры
  15. Смотрите также

PDO::query

PDO::query() prepares and executes an SQL statement in a single function call, returning the statement as a PDOStatement object.

For a query that you need to issue multiple times, you will realize better performance if you prepare a PDOStatement object using PDO::prepare() and issue the statement with multiple calls to PDOStatement::execute() .

If you do not fetch all of the data in a result set before issuing your next call to PDO::query() , your call may fail. Call PDOStatement::closeCursor() to release the database resources associated with the PDOStatement object before issuing your next call to PDO::query() .

Note:

If the query contains placeholders, the statement must be prepared and executed separately using PDO::prepare() and PDOStatement::execute() methods.

Parameters

The SQL statement to prepare and execute.

If the SQL contains placeholders, PDO::prepare() and PDOStatement::execute() must be used instead. Alternatively, the SQL can be prepared manually before calling PDO::query() , with the data properly formatted using PDO::quote() if the driver supports it.

The default fetch mode for the returned PDOStatement . It must be one of the PDO::FETCH_* constants.

If this argument is passed to the function, the remaining arguments will be treated as though PDOStatement::setFetchMode() was called on the resultant statement object. The subsequent arguments vary depending on the selected fetch mode.

Return Values

Returns a PDOStatement object or false on failure.

Errors/Exceptions

Emits an error with level E_WARNING if the attribute PDO::ATTR_ERRMODE is set to PDO::ERRMODE_WARNING .

Throws a PDOException if the attribute PDO::ATTR_ERRMODE is set to PDO::ERRMODE_EXCEPTION .

Examples

Example #1 SQL with no placeholders can be executed using PDO::query()

$sql = ‘SELECT name, color, calories FROM fruit ORDER BY name’ ;
foreach ( $conn -> query ( $sql ) as $row ) print $row [ ‘name’ ] . «\t» ;
print $row [ ‘color’ ] . «\t» ;
print $row [ ‘calories’ ] . «\n» ;
>
?>

The above example will output:

apple red 150 banana yellow 250 kiwi brown 75 lemon yellow 25 orange orange 300 pear green 150 watermelon pink 90

See Also

  • PDO::exec() — Execute an SQL statement and return the number of affected rows
  • PDO::prepare() — Prepares a statement for execution and returns a statement object
  • PDOStatement::execute() — Executes a prepared statement

Источник

The PDO class

Represents a connection between PHP and a database server.

Class synopsis

public __construct (
string $dsn ,
? string $username = null ,
? string $password = null ,
? array $options = null
)

public query ( string $query , ? int $fetchMode = PDO::FETCH_COLUMN , int $colno ): PDOStatement | false

public query (
string $query ,
? int $fetchMode = PDO::FETCH_CLASS ,
string $classname ,
array $constructorArgs
): PDOStatement | false

public query ( string $query , ? int $fetchMode = PDO::FETCH_INTO , object $object ): PDOStatement | false

Table of Contents

  • PDO::beginTransaction — Initiates a transaction
  • PDO::commit — Commits a transaction
  • PDO::__construct — Creates a PDO instance representing a connection to a database
  • PDO::errorCode — Fetch the SQLSTATE associated with the last operation on the database handle
  • PDO::errorInfo — Fetch extended error information associated with the last operation on the database handle
  • PDO::exec — Execute an SQL statement and return the number of affected rows
  • PDO::getAttribute — Retrieve a database connection attribute
  • PDO::getAvailableDrivers — Return an array of available PDO drivers
  • PDO::inTransaction — Checks if inside a transaction
  • PDO::lastInsertId — Returns the ID of the last inserted row or sequence value
  • PDO::prepare — Prepares a statement for execution and returns a statement object
  • PDO::query — Prepares and executes an SQL statement without placeholders
  • PDO::quote — Quotes a string for use in a query
  • PDO::rollBack — Rolls back a transaction
  • PDO::setAttribute — Set an attribute

User Contributed Notes 8 notes

«And storing username/password inside class is not a very good idea for production code.»

Good idea is to store database connection settings in *.ini files but you have to restrict access to them. For example this way:

my_setting.ini:
[database]driver = mysql
host = localhost
;port = 3306
schema = db_schema
username = user
password = secret

Database connection:
class MyPDO extends PDO
public function __construct ( $file = ‘my_setting.ini’ )
if (! $settings = parse_ini_file ( $file , TRUE )) throw new exception ( ‘Unable to open ‘ . $file . ‘.’ );

$dns = $settings [ ‘database’ ][ ‘driver’ ] .
‘:host=’ . $settings [ ‘database’ ][ ‘host’ ] .
((!empty( $settings [ ‘database’ ][ ‘port’ ])) ? ( ‘;port=’ . $settings [ ‘database’ ][ ‘port’ ]) : » ) .
‘;dbname=’ . $settings [ ‘database’ ][ ‘schema’ ];

parent :: __construct ( $dns , $settings [ ‘database’ ][ ‘username’ ], $settings [ ‘database’ ][ ‘password’ ]);
>
>
?>

Database connection parameters are accessible via human readable ini file for those who screams even if they see one PHP/HTML/any_other command.

PDO and Dependency Injection

Dependency injection is good for testing. But for anyone wanting various data mapper objects to have a database connection, dependency injection can make other model code very messy because database objects have to be instantiated all over the place and given to the data mapper objects.

The code below is a good way to maintain dependency injection while keeping clean and minimal model code.

class DataMapper
public static $db ;

public static function init ( $db )
self :: $db = $db ;
>
>

class VendorMapper extends DataMapper
public static function add ( $vendor )
$st = self :: $db -> prepare (
«insert into vendors set
first_name = :first_name,
last_name = :last_name»
);
$st -> execute (array(
‘:first_name’ => $vendor -> first_name ,
‘:last_name’ => $vendor -> last_name
));
>
>

// In your bootstrap
$db = new PDO (. );
DataMapper :: init ( $db );

// In your model logic
$vendor = new Vendor ( ‘John’ , ‘Doe’ );
VendorMapper :: add ( $vendor );

Keep in mind, you MUST NOT use ‘root’ user in your applications, unless your application designed to do a database maintenance.

And storing username/password inside class is not a very good idea for production code. You would need to edit the actual working code to change settings, which is bad.

I personnaly create a new instance of PDO like this :

$dbDatas = parse_ini_file( DB_FILE );
$dbOptions = [
\PDO::ATTR_DEFAULT_FECTH_MODE => \PDO::FETCH_OBJ,
\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION
];

$dsn = sprintf( ‘mysql:dbname=%s;host=%s’, $dbDatas[‘dbname’],
$dbDatas[‘host’] );

$this->cn = new \PDO( $dsn, $dbDatas[‘user’], $dbDatas[‘password’],
$dbOptions );
$this->cn->exec( ‘SET CHARACTER SET UTF8’ );

Starting with PHP 5.4 you are unable to use persistent connections when you have your own database class derived from the native PDO class. If your code uses this combination, you will encounter segmentation faults during the cleanup of the PHP process.
You can still use _either_ a derived PDO class _or_ persistent connections.

For more information, please see this bug report: https://bugs.php.net/bug.php?id=63176

For some Database Environment, such as Aliyun DRDS (Distributed Relational Database Service), cannot process preparing for SQL.
For such cases, the option `\PDO::ATTR_EMULATE_PREPARES` should be set to true. If you always got reports about «Failed to prepare SQL» while this option were set to false, you might try to turn on this option to emulate prepares for SQL.

Here is an singleton PDO example:

[db_options]PDO::MYSQL_ATTR_INIT_COMMAND=set names utf8

private static function getLink ( ) if ( self :: $link ) return self :: $link ;
>

$ini = _BASE_DIR . «config.ini» ;
$parse = parse_ini_file ( $ini , true ) ;

$driver = $parse [ «db_driver» ] ;
$dsn = » $ < driver >:» ;
$user = $parse [ «db_user» ] ;
$password = $parse [ «db_password» ] ;
$options = $parse [ «db_options» ] ;
$attributes = $parse [ «db_attributes» ] ;

self :: $link = new PDO ( $dsn , $user , $password , $options ) ;

foreach ( $attributes as $k => $v ) self :: $link -> setAttribute ( constant ( «PDO:: < $k >» )
, constant ( «PDO:: < $v >» ) ) ;
>

public static function __callStatic ( $name , $args ) $callback = array ( self :: getLink ( ), $name ) ;
return call_user_func_array ( $callback , $args ) ;
>
> ?>

$stmt = Database :: prepare ( «SELECT ‘something’ ;» ) ;
$stmt -> execute ( ) ;
var_dump ( $stmt -> fetchAll ( ) ) ;
$stmt -> closeCursor ( ) ;
?>

1) Do not use your ddbb info in the same file

2) DO NOT NEVER USE «All privileges user» for everything, always create an alternative user with restricted permissions (basic: SELECT, INSERT and so on)

Оцените статью
© 2024 Программирование