ON DUPLICATE KEY UPDATE wont work?

[Dusaro]

I have this but it will not update, it inserts the entry still!

<?php
$member=$_POST['memberid'];
$status=$_POST['Status'];

$con = mysql_connect("host","user","pass");
if (!$con)
{
	die('Could not connect: ' . mysql_error());
}
mysql_select_db("a2186214_hbclan",$con);
$sql="UPDATE application SET Status = '$status' WHERE ID = '$member'";
$sql1="INSERT INTO table_members(name) SELECT application.Name FROM application WHERE application.ID = '$member' ON DUPLICATE KEY UPDATE table_members.name = application.Name";
if ($status == 'ACCEPTED')
{
	if(mysql_query($sql, $con) or die(mysql_error()))
	{	
		if(mysql_query($sql1, $con) or die(mysql_error()))
		{	
			echo 'Status Changed.<br /><a href="../applications.php">Return To Members List</a>';
		}
	}
	else
	{
		die('Could not submit: ' . mysql_error());
		}
}
else
{
	if(mysql_query($sql, $con) or die(mysql_error()))
	{
	echo 'Status Changed.<br /><a href="../applications.php">Return To Members List</a>';
	}
	else
	{
		die('Could not submit: ' . mysql_error());
	}
}
mysql_close($con);

?>

[The Little Guy]

Try this:

 

INSERT INTO table_members(name) SELECT application.Name FROM application WHERE application.ID = '$member' ON DUPLICATE KEY UPDATE table_members.name = values(application.Name)

[Dusaro]

This gives me:

Unknown column 'application.Name' in 'field list'

Not sure because my table is called application and the field is called Name...

[The Little Guy]

I just realized that you have a select in the query, give this a try (I didn't try it, I don't even know if it is a valid query):

 

INSERT INTO table_members (name) values ((SELECT application.Name FROM application WHERE application.ID = '$member')) ON DUPLICATE KEY UPDATE table_members.name = values(table_members.name)

[Dusaro]

no, that still adds the entry...

[Pikachu2000]

Post your table structure and index definitions.

[Dusaro]

In table_members I have "id","name". "id" being the unique index.

In application I have "ID","Name","Email","Clans","Xfire","Invited","Skills","Status","Date","Rules","Ip",Old","fuser".  "ID" being the unique index.

Is that what you wanted?

[Pikachu2000]

Yes, and it isn't working as you expect it because you don't create a duplicate by inserting into the name field. You would need to create a UNIQUE index on that field if that's the field you want it to trigger the duplicate key conflict.

[Dusaro]

Well, when inserting I want it to check if the name exists, as each member will have their own unique id, not the same id as the applications.

[Pikachu2000]

Right, but if you want an attempt to insert a duplicate name to trigger an ON DUPLICATE KEY UPDATE, then you need to have a UNIQUE index on the name field.

[Dusaro]

After setting table_members.name and application.Name as Unique, i'm still getting error "Unknown column 'application.Name' in 'field list'"?

[Dusaro]

well, i had a thread bout 1 or 2 weeks ago about this but people stopped answering...

 

INSERT INTO table_members(name) SELECT application.Name FROM application WHERE application.ID = '$member' ON DUPLICATE KEY UPDATE table_members.name = application.Name

 

Will not update, still makes a new entry...

 

Table Structures

In table_members I have "id","name". "id" being the unique index. and name being unique.

In application I have "ID","Name","Email","Clans","Xfire","Invited","Skills","Status","Date","Rules","Ip",Old","fuser".  "ID" being the unique index. and Name being unique.

[fenway]

well, i had a thread bout 1 or 2 weeks ago about this but people stopped answering...

That's not a reason to start a new thread -- now merged.

 

INSERT INTO table_members(name) SELECT application.Name FROM application WHERE application.ID = '$member' ON DUPLICATE KEY UPDATE table_members.name = application.Name

 

Will not update, still makes a new entry...

 

Table Structures

In table_members I have "id","name". "id" being the unique index. and name being unique.

In application I have "ID","Name","Email","Clans","Xfire","Invited","Skills","Status","Date","Rules","Ip",Old","fuser".  "ID" being the unique index. and Name being unique.

If you want to share your table structures, post the CREATE TABLE syntax.

 

If you want to test your "unique" index, try to insert a hard-coded value twice -- if you don't get an error, then something else is wrong, and ON DUPLICATE KEY UPDATE won't work either.

[Dusaro]

You Mean these?

 

CREATE TABLE  `a2186214_hbclan`.`table_members` (
`id` INT( 11 ) NOT NULL AUTO_INCREMENT ,
`name` VARCHAR( 15 ) NOT NULL ,
UNIQUE KEY  `id` (  `id` ) ,
UNIQUE KEY  `name` (  `name` )
) ENGINE = MYISAM DEFAULT CHARSET = latin1;

 

CREATE TABLE  `a2186214_hbclan`.`application` (
`ID` INT( 11 ) NOT NULL AUTO_INCREMENT ,
`Name` VARCHAR( 30 ) NOT NULL ,
`Email` VARCHAR( 30 ) NOT NULL ,
`Clans` VARCHAR( 30 ) NOT NULL ,
`Xfire` VARCHAR( 30 ) NOT NULL ,
`Invited` VARCHAR( 30 ) NOT NULL ,
`Skills` VARCHAR( 255 ) NOT NULL ,
`Status` VARCHAR( 30 ) NOT NULL ,
`Date` DATE NOT NULL ,
`Rules` VARCHAR( 30 ) NOT NULL ,
`Ip` VARCHAR( 50 ) NOT NULL ,
`Old` INT( 30 ) NOT NULL DEFAULT  '0',
`fuser` VARCHAR( 50 ) NOT NULL ,
UNIQUE KEY  `ID` (  `ID` )
) ENGINE = MYISAM DEFAULT CHARSET = latin1;

[fenway]

Yes, that's what I meant -- the `ID` key should be PRIMARY, especially with AUTO_INCREMENT.

 

You're telling me that in your members tables you have two entries with the same name? I don't believe you.

[Dusaro]

well, I just copied the table structure and used the query it gave me.

[fenway]

well, I just copied the table structure and used the query it gave me.

What's "it"?

[Dusaro]

the query

[fenway]

the query

I don't believe you that you're getting duplicates in any table with a UNIQUE key.

 

And if "something" gave you that create table structure, it's wong.

[Dusaro]

This is what I did.

I went into phpmyadmin, went into my tables, operations, copy tables. It then gave me those.

[fenway]

This is what I did.

I went into phpmyadmin, went into my tables, operations, copy tables. It then gave me those.

Then you created them incorrectly.

 

But that wasn't your problem -- your problem was that you think that you're getting duplicates.