[PATCH] DB / autoPrepare()
| From: | MARTY Fabien | Date: | Wed, 05 Jun 2002 18:48:10 +0000 |
| Subject: | [PATCH] DB / autoPrepare() | ||
| References: | 1 | Groups: | php.pear.dev |
| Request: | Send a blank email to pear-dev+get-6739@lists.php.net to get a copy of this message | ||
Hello,
I made patches to add autoPrepare() method in PEAR_DB. If you don't know
what it is about, i have put the first mail of the thread at the end of this
one.
So to add it, we need a little patch to add two constants (DB_diff.txt) at
DB.php and a patch to add the autoPrepare() method at common.php
(common_diff.txt).
With it, you can use :
// --- INSER QUERIES ---
$data = array(
'field1' => $value1,
'field2' => $value2,
'field3' => $value3,
(...)
);
$prep = $db->autoPrepare('table', $data, DB_AUTOQUERY_INSERT);
// will make and prepare the sql query :
// INSERT INTO table (field1, field2, field3, ...) VALUES (?, ?, ?, ...)
$db->execute($prep, array_values($data));
// --- UPDATE QUERIES ---
$data = array(
'fieldx' = $newValueX,
'fieldy' = $newValueY
);
$prep = $db->autoPrepare('table', $data, DB_AUTOQUERY_UDPATE, 'id=4');
// will make and prepare the sql query :
// UPDATE table SET fieldx=?,fieldy=? WHERE id=4
$db->execute($prep, array_values($data));
Remarks, new ideas or opposite opinions are welcome !!!
Thanks,
Fabien
> Hello,
> One thing I feel borring is to write my sql queries to insert or update
> records in database.
> Like :
> INSERT INTO table (field1, field2, field3, ...) VALUES (value1, value2,
> value3, ...)
> DB / prepare() is really a good idea because you need only to write :
> INSERT INTO table (field1, field2, field3, ...) VALUES (?, ?, ?...)
> But, if you want to change the structure of the table or the number of the
> fields, you have to modify the query. So I think it would be to have a
> autoprepare() method which would take an array and would automaticaly deal
> with it. Like :
> $array = array(
> 'field1' -> value1,
> 'field2' -> value2,
> 'field3' -> value3,
> 'field4' -> value4,
> 'field5' -> value5,
> (...)
> );
> the method will write the query :
> INSERT INTO table (field1, field2, field3, ...) VALUES (?, ?, ?...)
> So, no query to write or to change if we modify fields names...
> For that, it would be a method like (non tested) :
> // it's an example for INSERT queries only.
> function insert_autoPrepare($table, $array)
> {
> $values = '';
> $names = '';
> $first = true;
> while (list($key, $value) = each($array)) {
> if ($first) {
> $first = false;
> } else {
> $names.=',';
> $values.=',';
> }
> $names.= $key;
> $values.= '?';
> }
> return "INSERT INTO $table ($names) VALUES ($values)";
> }
> Or something like it. The final method has to make INSERT and UPDATE
> queries and have to call prepare() instead of returning the query...
> NB : I use only keys of the assoc. But values will be used by execute()
> What do you think about this idea ? Any opposite opinions, remarks or
> questions are welcome.
> Thanks,
> Fabien
Attachment: [text/x-diff] DB_diff.txt
Attachment: [text/x-diff] common_diff.txt
Attachment: [text/x-diff] DB_diff.txt
Attachment: [text/x-diff] common_diff.txt