Proper syntax for if not exists, insert, else, update query?

[Fog Juice]

I have the following query but I'm not sure of the exact syntax to use, this is what I want:

 

IF NOT EXISTS(SELECT id FROM games_linked WHERE game_id = '$gid')
BEGIN
INSERT INTO `games_linked` (`id`,`server_id`,`game_id`) VALUES (NULL,'$serverid','$gid')
END
ELSE
BEGIN
UPDATE `games_linked` SET `server_id` = '$serverid' WHERE `game_id` = '$gid'
END

 

Does anyone have any suggestions for the correct syntax to make this work?

[Ken2k7]

Well if you set game_id to be the PK or UK, then try this -

INSERT INTO games_linked (id, server_id, game_id)
     VALUES (NULL, $serverid, $gid)
     ON DUPLICATE KEY UPDATE server_id = $serverid;

[Fog Juice]

Why was this moved to PHP help? Clearly this is not a PHP question.

[Zane]

$connect = mysql_connect($domain, $user, $pass);
mysql_select_db("yeoledatabase');

$serverid = 1;
$gid = 575;

$test = mysql_query("SELECT COUNT(*) as x, id FROM games_linked WHERE game_id = $gid") or die(mysql_error());

$result = mysql_fetch_array($test);

if($result[0] == 0) {
     echo "Appending to Database";
     $sql = "INSERT INTO `games_linked` (`server_id`,`game_id`) VALUES ($serverid,$gid)";
} else {
    echo "Updating";
    $sql = "UPDATE `games_linked` SET `server_id` = $serverid WHERE `game_id` = $gid";
}
$query = mysql_query($sql) or die(mysql_error());

[Fog Juice]

$connect = mysql_connect($domain, $user, $pass);
mysql_select_db("yeoledatabase');

$serverid = 1;
$gid = 575;

$test = mysql_query("SELECT COUNT(*) as x, id FROM games_linked WHERE game_id = $gid") or die(mysql_error());

$result = mysql_fetch_array($test);

if($result[0] == 0) {
     echo "Appending to Database";
     $sql = "INSERT INTO `games_linked` (`server_id`,`game_id`) VALUES ($serverid,$gid)";
} else {
    echo "Updating";
    $sql = "UPDATE `games_linked` SET `server_id` = $serverid WHERE `game_id` = $gid";
}
$query = mysql_query($sql) or die(mysql_error());

 

Thanks but I was looking for an answer native to MySQL, that is why it was in the MySQL section. I want to avoid having multiple SQL queries .

 

I'm already using PHP atm for this,

 

$return = mysql_query("UPDATE `games_linked` SET `server_id` = '$serverid' WHERE `game_id` = '$gid'");
		if(mysql_affected_rows() == 0)
		{
			mysql_query("INSERT INTO `games_linked` (`id`,`server_id`,`game_id`) VALUES (NULL,'$serverid','$gid')") or die(mysql_error());
		}

[Zane]

Thanks but I was looking for an answer native to MySQL, that is why it was in the MySQL section. I want to avoid having multiple SQL queries

 

Well pardon me, I guess I won't write you anymore free code examples either.

[Fog Juice]

Thanks but I was looking for an answer native to MySQL, that is why it was in the MySQL section. I want to avoid having multiple SQL queries

 

Well pardon me, I guess I won't write you anymore free code examples either.

 

I'm *edit* not *edit(my bad,forgot to say not.. lol)* looking for an argument but it just frustrated me to log in and see my post was moved with no explanation or anything. You can't expect users to know your intentions without warning them first.

 

I appreciate your help but my first knee jerk reaction was "wth was that moved for", now it makes sense but like I said, you can't expect users to to know your intentions unless you tell them.

 

Anyways thanks for trying to help in the first place, this forum is an excellent resource and is great for ppl like me who are out of college but still require a little bit of help every now and then.

[Zane]

well, back to your question then..

 

syntax of what...SQL?

I assumed what you wrote in the op post was pseudo code.  It didn't appear to me as any SQL I've dabbled in.  Perhaps someone else has used the functions BEGIN or END

 

http://forums.mysql.com/read.php?10,251666,251670#msg-251670

 

 

[corbin]

You could of course use UPDATE INTO, but there are downsides to that...  It's only native to MySQL is pretty much the downside.

 

 

 

 

You could technically do it in 1 function call if you used PDO to do multiple statements, but mysql_query can't do multiple statements.  The PDO way wouldn't be any better than two mysql_query calls though, and the mysql_query way would probably be better.

 

 

 

Any reason why you want to do it natively in MySQL?