[wp-hackers] DB Queries--which way to go?

[Jeff Minard]

Elliott Bäck wrote:
> Another option is something like oracle's MERGE function, where I can 
> check a condition and then base my query off subqueries if it exists to 
> update the row or else insert, but I don't think MYSQL can do that...
> 

See REPLACE (I know, it was new to me as well.)

  - http://dev.mysql.com/doc/mysql/en/replace.html

Otherwise, go with "update/if fail/insert" which is what I tend to do. I 
like update/insert better because a single update is faster than what 
replace does. Replace will delete a record entirely (if duplicate keys), 
then insert a new record. This takes longer than an update.

So I usually run something like....

-------------------------------

$set = "`col1`=('joe'), `col2`=('jane'), `col3`=('susy'), `id`=($id)";

$update = "UPDATE $this->stats_table SET $set WHERE `id`=$id LIMIT 1";
$u_results = mysql_query($update_query);
			
if( mysql_affected_rows() != 1  ){
	$this->log[] = "Updating failed: " . mysql_error();
			
	$insert_query = "INSERT INTO $this->stats_table SET $set";
	$i_results = mysql_query($insert_query);
	if( mysql_affected_rows() != 1  ){
		$this->log[] = "Inserting failed: " . mysql_error();
		return false;
	}
}

return true;

----------------------------


EXAMPLE CODE ONLY (probably missing a ; or something stupid...)


Jeff