On Tue, Feb 02, 2010 at 11:19:29PM +0100, Rene Veerman wrote:
> oh, on using adodb.sf.net and 0-overhead for jumping between mysql and
> postgresql;
>
> keep all your queries to as simple & early-standard sql as possible.
> the auto_increment incompatibilities can be circumvented with a
> relatively simple
> function getMax($table, $field) {
This approach is guaranteed to run into race conditions. The only way to
positively ensure proper results is to let the DB engine take care of it
itself. The engines typically track incremental IDs by session, which
prevents you from getting an ID someone else has just used.
>
> in adodb, you'd loop through a huge dataset like this, ensuring proper
> comms & mem-usage betweeen the db server and php.
>
> $dbConn = adoEasyConnection(); //returns adodb connection object,
> initialized with values from config.php
> $sql = 'select * from data where bladiebla="yep"';
> $q = $dbConn->execute ($sql);
> if (!$q || $q->EOF) {
> $errorMsg = $dbConn->ErrorMsg();
> handleError ($errorMsg, $sql);
> } else {
> while (!$q->EOF) {
>
> //use $q->fields['field_name']; // from the currently loaded record
>
> $q->MoveNext();
> }
> }
>
> for short resultsets you could call $q->getRows() to get all the rows
> returned as 1 multilevel array.
>
> instead of difficult outer-join constructs and stored procedures,
> (that are not portable), i find it much easier to aim for small
> intermediate
> computation-result arrays in php, which are used to construct
> fetch-final-result-sql on the fly.
> itnermediate &/ result arrays can be stored on db / disk in json, too ;)
For MySQL I would agree. But I prefer the ability to use the full SQL
standard when manipulating a database; that is, all joins, foreign keys,
etc. For that same reason, I tend to avoid stored procedures as well. If
I have to do things like handle foreign key constraints in my PHP code
(instead of letting the DBMS handle them), I have to wonder why I'm even
using a "relational" DBMS.
Paul
--
Paul M. Foster
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
