pdo

How can I implement commit/rollback for MySQL in PHP?

Well basically I have this script that takes a long time to execute and occasionally times out and leaves semi-complete data floating around my database. (Yes I know in a perfect world I would fix THAT instead of implementing commits and rollbacks but I am forced to not do that) Here is my basic code (dumbed down for simplicity): $data...

How can I convert all values of an array to floats in PHP?

I am fetching an array of floats from my database but the array I get has converted the values to strings. How can I convert them into floats again without looping through the array? Alternatively, how can I fetch the values from the database without converting them to strings? EDIT: I am using the Zend Framework and I am using PDO...

Named parameters, caching and PDO

If i have a parameterized SQL statement like this: SELECT * FROM table WHERE my_field = :field_value Does anyone know if PDO will recognize this(see below) as the same SQL statement and use the cache instead of assuming it's a completely different SQL statement: SELECT * FROM table WHERE my_field = :new_field_value So, I guess the...

PDO MySQL Driver on Mac

I have a mac with a custom PHP 5 install that built from about a year ago. I remember it took all Sunday and I had to compile about 20 times to get it right. The MySQL I have is from entropy and was precompiled. Now I need to get PDO with the MySQL driver working and the driver is not installed. I tried the "pecl install pdo_mysql" and ...

How do I tell Apache which PHP to use?

I am running Apache2 on a Mac OS X (10.5). I just compiled PHP 5.2.8 and finally got pdo-mysql working (or so I think). My command line "php --version" is showing 5.2.8 and I have the right modules installed. But, when do a phpinfo(), Apache dumps out PHP 5.2.6 (my earlier version, without pdo_mysql). How do I tell Apache which PHP to lo...

PDO query problem

I am updating some code from the old mysql_* functions to PDO. It connects without a problem, runs the query without a problem, but the resultset is empty. PDO::query() is supposed to return a PDOStatement object, yet I am getting true in return. No errors are reported. Here is my code: try { $DB = new PDO("mysql:host=localhost...

Work-around for PHP5's PDO rowCount MySQL issue

I've recently started work on a new project using PHP5 and want to use their PDO classes for it. The problem is that the MySQL PDO Driver doesn't support rowCount() so there's no way to run a query and then get the number of affected rows, or rows returned, which is a pretty big issue as far as I'm concerned. I was wondering if anyone el...

Getting a PDO query string with bound parameters without executing it

Is it possible to get a query string from a PDO object with bound parameters without executing it first? I have code similar to the following (where $dbc is the PDO object): $query = 'SELECT * FROM users WHERE username = ?'; $result = $dbc->prepare($query); $username = 'bob'; $result->bindParam(1, $username); echo $result->queryString; ...

When *not* to use prepared statements?

I'm re-engineering a PHP-driven web site which uses a minimal database. The original version used "pseudo-prepared-statements" (PHP functions which did quoting and parameter replacement) to prevent injection attacks and to separate database logic from page logic. It seemed natural to replace these ad-hoc functions with an object which u...

SQLite transaction doesn't work as expected

I prepared 2 files, "1.php" and "2.php". "1.php" is like this. <?php $dbh = new PDO('sqlite:test1'); $dbh->beginTransaction(); print "aaa<br>"; sleep(55); $dbh->commit(); print "bbb"; ?> and "2.php" is like this. <?php $dbh = new PDO('sqlite:test1'); $dbh->beginTransaction(); print "ccc<br>"; $dbh->commit(); print "ddd"; ?> and...

PHP PDO Buffered query problem

I'm having some serious problems with the PHP Data Object functions. I'm trying to loop through a sizeable result set (~60k rows, ~1gig) using a buffered query to avoid fetching the whole set. No matter what I do, the script just hangs on the PDO::query() - it seems the query is running unbuffered (why else would the change in result se...

How do I create a PDO parameterized query with a LIKE statement in PHP?

Here's my attempt at it: $query = $database->prepare('SELECT * FROM table WHERE column LIKE "?%"'); $query->execute(array('value')); while ($results = $query->fetch()) { echo $results['column']; } ...

How do I build a parameterized PDO statement in PHP for a dynamic query?

Apologies if this has been asked already. I've seen answers regarding static SQLs, but in this case I'd like to use PDO->prepare() for a query string that is built dynamically at runtime. Breaking down into a simple example: $TempSQL = "SELECT field1, field2, field3 FROM table WHERE "; if ($numberParams == 1) { $TempSQL = $TempSQ...

How to make PDO run SET NAMES utf8 each time I connect, In ZendFramework.

How to make PDO adapter run SET NAMES utf8 each time I connect, In ZendFramework. I am using an INI file to save the adapter config data. what entries should I add there? If it wasn't clear, I am looking for the correct syntax to do it in the config.ini file of my project and not in php code, as I regard this part of the configuration c...

Database return records as object or array?

Which is the better alternative? I've noticed PDO let's you return as an array or an object. I've always used arrays when using my old functions (mysql_fetch_assoc()) but I've just written a wrapper class for the PDO object and I was curious as to which is better. I suppose an object would be stricter... you can change/add to a returned...

What databases used with PHP share the same (or most) of the SQL syntax?

I've read that although SQL is meant to be standardised, it is implemented different etc with different databases. I have only ever used MySQL for databases. What I would like to know is what other databases share the same syntax? I am using PDO now, and I would like to set a flag to allow the user to specify which database they would l...

Does PHP's Oracle PDO hang on DELETE?

This hangs in Php (5.2.6-Win32 + Oracle10g) is it a bug, or I'm doing something fundamentally wrong? try { $conn = new PDO($DB,$USER,$PASSWORD); $conn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION ); //connected try { $conn->exec("DELETE FROM MY_TABLE"); echo "done"; ... Note: I don't know if this ...

Is this wrapper for PDO 'good code' ? Are there any potential problems?

I built this class to work with PDO, to make SQL queries 'easier' and less to worry about. Here are my thoughts Should it be more like class DB extends PDO? Is the query method too big? Should it be split into private methods which are called.. is this what is known as loose coupling? Is my way for detecting a SELECT query too ugly f...

Why is this MySQL query failing?

This query keeps failing with Integrity constraint violation: 1048 Column 'login_name' cannot be null My insert statement is... $insertUserQuery = 'INSERT INTO `users` ( `login_name`, `password`, `first_name`, `last_name`, `company_n...

What is the advantage of using try {} catch {} versus if {} else {}

I am switching from plain mysql in php to PDO and I have noticed that the common way to test for errors is using a try / catch combination instead of if / else combinations. What is the advantage of that method, can I use one try / catch block instead of several nested if / else blocks to handle all errors for the different steps (conne...