RE: [PEAR-DEV] DB / autoprepare() ?

Hi, Metabase and therefore MDB has support for replace This sort of does what you want and then some ... Currently this is one big old method in the common.php file of MDB, but could (should?) get chunked up to better match what you are talking about. Anyways this sort of stuff goes more in the direction of a query builder. I saw something like that somewhere in the PEAR DB directory. Here is an excerpt from the Metabase docs: MetabaseReplace Synopsis $success=MetabaseReplace($database, $table, $fields) Purpose Execute a SQL REPLACE query. A REPLACE query is identical to a INSERT query, except that if there is already a row in the table with the same key field values, the REPLACE query just updates its values instead of inserting a new row. The REPLACE type of query does not make part of the SQL standards. Since pratically only MySQL implements it natively, this type of query is emulated through this Metabase function for other DBMS using standard types of queries inside a transaction to assure the atomicity of the operation. Use the MetabaseSupport function to figure if the current driver class object implements the REPLACE query even if it is emulated. Usage The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function. The $table argument is the name of the table on which the REPLACE query will be executed. The $fields argument is an associative array that describes the fields and the values that will be inserted or updated in the specified table. The indexes of the array are the names of all the fields of the table. The values of the array are also associative arrays that describe the values and other properties of the table fields. Here follows a list of field properties that need to be specified: Value Value to be assigned to the specified field. This value may be of specified in database independent type format as this function can perform the necessary datatype conversions. Default: this property is required unless the Null property is set to 1. Type Name of the type of the field. Currently, all types Metabase are supported except for clob and blob. Default: text Null Boolean property that indicates that the value for this field should be set to NULL. The default value for fields missing in INSERT queries may be specified the definition of a table. Often, the default value is already NULL, but since the REPLACE may be emulated using an UPDATE query, make sure that all fields of the table are listed in this function argument array. Default: 0 Key Boolean property that indicates that this field should be handled as a primary key or at least as part of the compound unique index of the table that will determine the row that will updated if it exists or inserted a new row otherwise. This function will fail if no key field is specified or if the value of a key field is set to NULL because fields that are part of unique index they may not be NULL. Default: 0 The $success return value determines if this function succeeded. A value of 0 indicates that the query failed. Lukas Smith smith@dybnet.de _______________________________ DybNet Internet Solutions GbR Reuchlinstr. 10-11 Gebäude 4 1.OG Raum 6 (4.1.6) 10553 Berlin Germany Tel. : +49 30 83 22 50 00 Fax : +49 30 83 22 50 07 www.dybnet.de info@dybnet.de > -----Original Message----- > From: Fabien MARTY [mailto:mpma712@andante.meteo.fr] > Sent: Tuesday, June 04, 2002 1:52 PM > To: pear-dev@lists.php.net > Subject: [PEAR-DEV] DB / autoprepare() ? > > 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 > > > -- > PEAR Development Mailing List (http://pear.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php