[SOLVED] ON DUPLICATE KEY???

[maztrin]

Hi, i am trying to insert a record into a table, but if the record exists i want it to update the record instead of creating a new one, so i tried to use the on duplicate key update

 

 

my php code:

$query3 = "INSERT INTO book(id, userid, datetime) VALUES ('$id', '$uid', NOW() )
ON DUPLICATE KEY UPDATE  counts= counts + 1";
$result3 = mysql_query($query3);

 

but instead of updating it just inserts a new record.

 

when i want it to update i just want the count field to update. could it be that because i havent initially inserted anything into the counts field it wont update?

i have not initialized count as a variable.

the default value for counts is 0 which is in the mysql.

 

my id is an auto incremenet value.

 

i tried reading the manual about the duplicate key but  i got a bit confused

 

 

any help is appreciated,

thanks

 

 

[shadiadiph]

i think you need to be using update instead of insert

 

$action = $_GET["action"];
$id = $_GET["id"];


if ($action=="add")
{
$query3 = "INSERT INTO book(id, userid, datetime) VALUES ('$id', '$uid', NOW() )
$result3 = mysql_query($query3);
}
if ($action=="update")
{
sql = "update book set count = count +1 where id=$id"; 
$result= mysql_query($sql);
}

 

you need to be using something along these lines

 

 

and you need to be able to diffeentiate as to if it is a new insertion or not.

[kickstart]

Hi

 

Not sure but I suspect MySQL sees an insert statement on a table with an autonumber field and ignores any specified value for that field. As such it doesn't trigger a duplicate key.

 

All the best

 

Keith

[Yesideez]

You're both right although if you try inserting data and the unique identifier already exists then you'll get this error.

[maztrin]

i think you need to be using update instead of insert

 

$action = $_GET["action"];
$id = $_GET["id"];


if ($action=="add")
{
$query3 = "INSERT INTO book(id, userid, datetime) VALUES ('$id', '$uid', NOW() )
$result3 = mysql_query($query3);
}
if ($action=="update")
{
sql = "update book set count = count +1 where id=$id"; 
$result= mysql_query($sql);
}

 

you need to be using something along these lines

 

 

and you need to be able to diffeentiate as to if it is a new insertion or not.

 

ok ive changed it so that it uses a function to check if an id exists,

 

so if the returned result is > than 0 then a record exists so update

otherwise insert

but now that doesnt work, is the function ok?

function check_debate_ID_exists($id)
{
#select the book id and see if it already exists in the table
$query2 = "SELECT id FROM book WHERE id like \"$id\" ";
$result2 = mysql_query($query2);

if (mysql_num_rows($result2) > 0)#if greater than 0, then $id exists
{
	#update row that is = to $id
	$query3 = "UPDATE book SET counts = counts+ 1 WHERE id = $id AND userid = $uid";
	$result3 = mysql_query($query3);
}
else #no id exists
{
	#insert a record by creting a vote
	$query4 = "INSERT INTO book(id, userid, datetime) VALUES ('$id', '$uid', NOW() )";
	$result4 = mysql_query($query4);
}
}

 

 

[maztrin]

You're both right although if you try inserting data and the unique identifier already exists then you'll get this error.

 

so then how would i fix this.

i need the id to auto_increment when it inserts a new record

[Yesideez]

You'd be better off using single quotes inside a query...

$query2 = "SELECT id FROM book WHERE id like '$id' ";

 

Also change this line:

$query4 = "INSERT INTO book(userid, datetime) VALUES ('$uid', NOW() )";

I'm presuming "id" is the unique identifier - if this is set to AUTO_INCREMENT there's no need to specify it in INSERT as MySQL will fill it in for you automatically.

[kickstart]

Hi

 

The ID field is numeric (being autonumber) so you don't want to do a like nor have inverted commas around the value.

 

function check_debate_ID_exists($id)
{
#select the book id and see if it already exists in the table
$query2 = "SELECT id FROM book WHERE id = $id ";
$result2 = mysql_query($query2);

if (mysql_num_rows($result2) > 0)#if greater than 0, then $id exists
{
	#update row that is = to $id
	$query3 = "UPDATE book SET counts = counts+ 1 WHERE id = $id AND userid = $uid";
	$result3 = mysql_query($query3);
}
else #no id exists
{
	#insert a record by creting a vote
	$query4 = "INSERT INTO book(userid, datetime) VALUES ('$uid', NOW() )";
	$result4 = mysql_query($query4);
}
}

 

All the best

 

Keith

[Yesideez]

Hi

 

The ID field is numeric (being autonumber) so you don't want to do a like nor have inverted commas around the value.

It doesn't matter whether they're there or not - for readability I always add them.

 

The proper way to write the query is this:

$query2 = "SELECT `id` FROM `book` WHERE `id`='$id'";

[shadiadiph]

looks ok but i usually use something like this

 

 

$checkbook = "select id from book where id='$id'";
$result	= mysql_query($checkbook);
$total	= mysql_num_rows($result);

if ($total>0)
{

[kickstart]

Hi

 

Also noticed that the userid field doesn't have surruonding inverted commas in the update statement, yet might well be a character field.

 

It doesn't matter whether they're there or not - for readability I always add them.

 

Depends on the flavour of SQL you are using. As such I would say it is not a good habit to get into.

 

All the best

 

Keith

[maztrin]

ok thanks everyone i have got it working now, i decided not to use the duplicate key as it just wasnt working. so i used an if else statement instead.

 

thanks all