Update IF * ELSE Insert

[tinker]

Hi, i'm trying to update a field if two fields equal certain values, but if they don't I want to insert a new entry... any ideas?

 

Here's a little demo to use (i've commented where the test bit is!):

$host = 'localhost';
$user = 'user';
$pass = 'pass';
$db = 'db';

$conn = mysql_connect($host, $user, $pass) or die(mysql_error());
mysql_select_db($db, $conn) or die(mysql_error());

$s = "DROP TABLE test";
mysql_query($s, $conn);

$s = "CREATE TABLE test (id int not null primary key auto_increment, u_id int not null, last bigint not null, topic_id int not null )";
if(mysql_query($s, $conn))
{
print "Created table<br>";
$s = "INSERT INTO test VALUES ('', 1, 0, 1), ('', 2, 0, 1), ('', 1, 0, 2), ('', 2, 0, 2) ";
$res = mysql_query($s, $conn) or die(mysql_error());
}
else
{
print "Table creation failed<br>";
}

print "<br><br>";

$s = "UPDATE test 
	SET last= IF(u_id=1, 999, last)
	WHERE topic_id = 1 ";
$res = mysql_query($s, $conn) or die(mysql_error());


// !!!!!!! THIS IS WHAT I'M TRYING !!!!!!!
$s = "UPDATE test 
	SET last= IF((u_id=3 && topic_id = 3), 555, last) 
	ELSE (INSERT INTO test VALUES ('', 3, 333, 3)) ";
$res = mysql_query($s, $conn) or die(mysql_error());


print "<br><br>PRINT OUT<br>";

$s = "SELECT * FROM test
$res = mysql_query($s, $conn) or die(mysql_error());
while ($a = mysql_fetch_array($res))
{
print $a['id'].": (u_id) ".$a['u_id'].", (topic_id) ".$a['topic_id'].", (last) ".$a['last']."<br>";
}

 

Cheers!

[fenway]

You may want to use the CASE statement... a little bit easier than IF.

[tinker]

Still struggling!

 

//$s = "UPDATE test_chat_where SET last = CASE WHEN u_id=1 && topic_id=1 THEN 345 END";	//	works fine
//$s = "UPDATE test_chat_where SET last = CASE WHEN u_id=3 && topic_id=3 THEN 345 END | INSERT INTO test_chat_where VALUES ('', 3, 333, 3)";	//	
$s = "UPDATE test_chat_where SET last = CASE WHEN u_id=3 && topic_id=3 THEN 345 END, ELSE NULL THEN INSERT INTO test_chat_where VALUES ('', 3, 333, 3) END";	//	
$res = mysql_query($s, $conn) or die(mysql_error());

 

I'm not sure if it's trying to change into and INSERT, however all errors seem to stem from the ELSE area... any ideas or examples?

[fenway]

Whoa... you can't put an INSERT into an update like that.  and END goes after the else.  what are you trying to do again?

[tinker]

update if two fields match values, otherwise insert a new record.

 

I don't believe I can use the duplicates catch because of depending upon two fields?

 

Basically it's a bit of a log thing for a chat room, however you may be present in multiple chat rooms at once. The table looks a bit like this:

tn (id, u_id, room_id, last_time)

If u_id and room_id match then update time, else insert new. Every now and again everyone one in a room within x amount of time will be harvested and sent back as a buddy list. Also set up in cron is a function which clears any out over a certain age...

 

I've got a solution that i'm currently using, but it makes a sql call, then depending upon the result it decides upon 1 of 2 other sql calls, it just seems a bit wasteful and I like learning something new (to me anyway).

 

Cheers for any consideration... 

[fenway]

If you have a multi-column unique index, then it will trigger dupe key.

[tinker]

no i wasn't using any uniques, but i've just had an idea which does, if i combine the two keys into a single, then that will be unique, e.g.

tn (id, user_n_room, last)

$user_n_room = $u_id.":".$room;

this solves my abuse of sql networks but not my understanding of sql logic... sigh!

 

merci

 

[fenway]

You don't have to physicallycombine the columns...

[tinker]

so how would you do it?

[fenway]

I'm confused about your example... did you not suggest and index with 3 keys?

[tinker]

ok

[gluck]

Check out the REPLACE stmt:

http://dev.mysql.com/doc/refman/5.0/en/replace.html

This does what you need.

Your IF ELSE stmts are confusing.