[wp-hackers] Better insert and update

[Dougal Campbell]

Matt Mullenweg wrote:
> Proposal:
> 
> function insert($tbl, $hash){
>     $fields = array_keys($hash);
>     $this->query("INSERT INTO $tbl (`".implode('`,`',$fields)."`) VALUES 
> ('".implode("','",$hash)."')");
>     return $this->insert_id;
> }
> 
> function update($tbl, $hash, $where){
>     $bits = array();
>     foreach( array_keys($hash) as $k )
>         $bits[] = "`$k`='$hash[$k]'";
>     return $this->query("UPDATE $tbl SET ".implode(', ',$bits)." WHERE 
> $where");
> }
> 
> In the $wpdb object. We could also move all escaping to this level, but 
> that's a separate issue.
> 
> It's a bit of code I got from Flickr Cal on something we collaborated 
> on, I've been using it in another project and it feels clean.
> 
> 
> $wpdb->insert( 'table', array( 'this' => 'that', 'and => 'another' ) );
> 
> $wpdb->update( $wpdb->posts, array( 'comment_count' => 0 ), "ID = $id");

You'll probably also need a way to specify a table field filter to get 
rid of alien keys before the actual SQL insert or update statements are 
generated. For example, if a plugin tried something like this, you'd 
generate an error, unless you get rid of the bogus key first:

   $wpdb->update ( $wpdb->posts,
     array(post_title => "foo", kickme => "bar"),
     "ID = $id" );

We'd probably want a core function that could be used to register the 
canonical list of valid fields for a table. Off the top of my head, 
something like:

   $wpdb->def_table_fields( $wpdb->usermeta,
     array('umeta_id', 'user_id', 'meta_key', 'meta_value') );

If you really wanted to get fancy-schmancy, you could make it a hash 
where the values represent some basic data types (string, int, datetime, 
etc), for automagical validation. And we'd want to have filters that 
allow plugins to modify this info, as well.

-- 
Dougal Campbell <dougal at gunters.org>
http://dougal.gunters.org/