Jump to content

[SOLVED] Automatic INSERT vs. UPDATE


F1Fan

Recommended Posts

OK, so I have a frequent need to insert or update data into a table. But the problem is that I have to do a select to determine if it needs to be an update or an insert. This works perfectly, but it's a little bulky.

 

So, my question is this: Is there a way to have ONE query run that inserts if the where statement in the update doesn't return any rows to update?

 

Here's my working code:

<?php
	$query = "SELECT c FROM feature_count WHERE f = '$f' AND a = '$a'";
	$count = $db->getOne($query);
	if ($count>0){
		$count++;
		$query = "UPDATE feature_count SET c = $count WHERE f = '$f' AND a = '$a'";
	}
	else{
		$query = "INSERT INTO feature_count (f, a, c) VALUES ('$f', '$a', 1)";
	}
	$db->query($query);
?>

 

Basically, this function runs on every page someone on my site hits. It counts the features that are used. I really just want something like this:

 

<?php
$query = "UPDATE feature_count SET c = (c+1) WHERE f = '$f' AND a = '$a'";
?>

 

And if there are no rows where f = '$f' AND a = '$a', I want it to insert a row with f = '$f' and a = '$a' and c = 1.

 

Any ideas?

Link to comment
https://forums.phpfreaks.com/topic/124810-solved-automatic-insert-vs-update/
Share on other sites

Nope, I can't find anything like that in PosgreSQL, however, I did manage to simplify it to this:

 

<?php
	$query = "UPDATE feature_count SET c = (c+1) WHERE f = '{$c['f']}' AND a = '{$c['a']}'";
	$result = $db->query($query);
	if ($db->affectedRows()==0){
		$query = "INSERT INTO feature_count (f, a, c) VALUES ('{$c['f']}', '{$c['a']}', 1)";
		$result = $db->query($query);
	}
?>

 

Thanks for your reply. It led me in the right direction.

"UPSERT" (update if exists, insert if doesn't exist) is on the todo list for postgresql, but it doesn't seem to be high priority.

 

You can use triggers to simulate it for specific tables (eg, have your trigger check if the row exists and then insert or update accordingly)

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.