In our place we're split between using mysqli and PDO for stuff like prepared statements and transaction support. Some projects use one, some the other. There is little realistic likelihood of us ever moving to another RDBMS. I prefer PDO for the single reason that it allows named parameters for prepared statements, and as far as I am a...
I'm using PHP's PDO layer for data access in a project, and I've been reading up on it and seeing that it has good innate support for persistant DB connections. I'm wondering when/if I should use them. Would I see performance benefits in a CRUD-heavy app? Are there downsides to consider, perhaps related to security? If it matters to you...
I am trying to paginate the results of an SQL query for use on a web page. The language and the database backend are PHP and SQLite. The code I'm using works something like this (page numbering starts at 0) http://example.com/table?page=0 page = request(page) per = 10 // results per page offset = page * per // take one extra record ...
I'm writing some DB routines and I'm using prepared statements. My environment is PDO with PHP5. I understand prepared statements primarily provide a performance benefit, as well as some auxiliary bonuses such as not having to manually SQL-escape input data. My question is about the performance part. I have two implementations of a g...
Trying to do this sort of thing... WHERE username LIKE '%$str%' ...but using bound parameters to prepared statements in PDO. e.g.: $query = $db->prepare("select * from comments where comment like :search"); $query->bindParam(':search', $str); $query->execute(); I've tried numerous permutations of single quotes and % signs and it's ...
I've been trying to use SQLite with the PDO wrapper in PHP with mixed success. I can read from the database fine, but none of my updates are being committed to the database when I view the page in the browser. Curiously, running the script from my shell does update the database. I suspected file permissions as the culprit, but even with ...
How can I get PDO to work on my mac (os x 10.5)? I'm using the built in php and php in Zend/Eclipse. Can't seem to find useful drivers for it at all. ...
Let's say I have code like this: $dbh = new PDO("blahblah"); $stmt = $dbh->prepare('SELECT * FROM users where username = :username'); $stmt->execute( array(':username' => $_REQUEST['username']) ); The PDO documentation says The parameters to prepared statements don't need to be quoted; the driver handles it f...
This is a bit of a weird one, and I could well be coding this completely wrong - hence why I've hit the same error twice in two days, in completely different parts of a script. The code I'm using is below: public function findAll( $constraints = array() ) { // Select all records $SQL = 'SELECT * FROM ' . $this->tableName...
I'm slowly moving all of my LAMP websites from mysql_ functions to PDO functions and I've hit my first brick wall. I don't know how to loop through results with a parameter. I am fine with the following: foreach ($database->query("SELECT * FROM widgets") as $results) { echo $results["widget_name"]; } However if I want to do somet...
I am working on a project that is built on an extended version of the default PDO and PDOStatement classes and as such it uses PDO style named parameters instead of the "?" placeholder for variables. However, the client is now requesting that we look into using a different driver because their version OS X Web Server apparently doesn't...
Why can't I pass the table name to a prepared PDO statement? $stmt = $dbh->prepare('SELECT * FROM :table WHERE 1'); if ($stmt->execute(array(':table' => 'users'))) { var_dump($stmt->fetchAll()); } Is there another safe way to insert a table name into a SQL query? With safe I mean that I don't want to do $sql = "SELECT * FROM $tab...
Is there a way to get the raw SQL string executed when calling PDOStatement::execute() on a prepared statement? For debugging purposes this would be extremely useful. ...
When I connect to a MySQL database using PDO, the way I need to connect is: $pdoConnection = new PDO("mysql:host=hostname;dbname=databasename",user,password); But, for PostgreSQL, the DSN is more standard (IMO): $pdoConnection = new PDO("pgsql:host=hostname;dbname=databasename;user=username;password=thepassword"); Is there any reason w...
I work on a app which has only one way to get a working DB connection: a function that returns a MySQL connection resource. Is there any way for me to convert that MySQL connection to a PDO MySQL connection? I don't have access to the MySQL server in any other way, no username, password, nothing. I can't see/get the file with the funct...
I'm having a little problem with a php-gtk app that keeps running into non-utf8 strings, I had found that the problem is in the database connection, even when the database is supposed to be in UTF-8. I had tried with the "SET CHARACTER SET utf8"(MySQL way) and the "SET NAMES UTF8" and nothing happen (there isn't any information about no...
I'm thinking of using PDO in all of my future webapp. Currently (using what I've learned from SO so far), what I have in my site to handle database connection is a Singleton class like this : class DB { private static $instance = NULL; private static $dsn = "mysql:host=localhost;dbname=mydatabase;"; private static $db_...
I'm trying to write an iterator for results from a PDO statement but I can't find any way of rewinding to the first row. I would like to avoid the overhead of calling fetchAll and storing all the result data. // first loop works fine foreach($statement as $result) { // do something with result } // but subsequent loops don't foreac...
Is there a list describing all of the data_type parameters you can use in PDOStatement::bindParam() ? If none, what do you commonly use, and for what type of field ? According to PHP manual: *data_type* Explicit data type for the parameter using the PDO::PARAM_* constants. I know about the PDO::PARAM_INT and PDO::PARAM_STR . I've hear...
I am using Zend_Db to insert some data inside a transaction. My function starts a transaction and then calls another method that also attempts to start a transaction and of course fails(I am using MySQL5). So, the question is - how do I detect that transaction has already been started? Here is a sample bit of code: try { ...