updating issue

[spearchilduser]

HI there im having a problem with updating one table with anopther tables contents atm i have this code:

if($sql)
{
       $msg = "Taxi Accepted sucessfully.";
       mysql_query("INSERT INTO user_location ( User_ID,User_Longitude,User_Latitude) SELECT User_ID,User_Longitude,User_Latitude FROM quotes") or die (mysql_error());
}
       else
       {
$msg = "Taxi not Accepted sucssfully try again.";
echo "$msg";
       }

 

The error message im getting is :Duplicate entry '1' for key 1

 

:S any help at all

 

Thank You

[Muddy_Funster]

Your problem is that there is already an existing primary key value in user_location that matches the value you are trying to insert.  Here, this should help: http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

 

[spearchilduser]

THE table that the contents are going into doesnt have a primary key or a auto increment or is it abotu the table it is coming from ?

 

Im not great at SQL and the page is justr boggling my mind more

[Muddy_Funster]

can you provide the results of the following queries please?

 SHOW CREATE TABLE user_location; SHOW CREATE TABLE quotes;

either run them in phpMyAdmin, in command line or from script.

[spearchilduser]

Table Create Table

quotes CREATE TABLE `quotes` (\n `Quote_ID` int(11) NOT NULL auto_increment,\n `User_ID` int(11) NOT NULL,\n `Firm_By_Customer` int(11) NOT NULL,\n `Firm_ID` int(11) NOT NULL,\n `Customer_Location` varchar(255) NOT NULL,\n `User_Longitude` varchar(50) NOT NULL,\n `User_Latitude` varchar(50) NOT NULL,\n `Customer_Destination` varchar(255) NOT NULL,\n `How_Many` int(11) NOT NULL,\n `Wait_Time` int(11) NOT NULL,\n `Price` int(11) NOT NULL default '0',\n `accept_Decline` tinyint(1) NOT NULL default '5',\n `DeclienedByCustomer` tinyint(1) NOT NULL default '5',\n `Time` time NOT NULL,\n `Date` date NOT NULL,\n PRIMARY KEY (`Quote_ID`)\n) ENGINE=InnoDB DEFAULT CHARSET=latin1

 

 

user_location CREATE TABLE `user_location` (\n `User_ID` int(50) NOT NULL,\n `User_Longitude` varchar(50) NOT NULL,\n `User_Latitude` varchar(50) NOT NULL,\n `User_Time` varchar(50) NOT NULL,\n `User_Date` varchar(50) NOT NULL,\n PRIMARY KEY (`User_ID`)\n) ENGINE=InnoDB DEFAULT CHARSET=latin1

 

is the full message

 

thank you

[Muddy_Funster]

user_location CREATE TABLE `user_location` (\n `User_ID` int(50) NOT NULL,\n `User_Longitude` varchar(50) NOT NULL,\n `User_Latitude` varchar(50) NOT NULL,\n `User_Time` varchar(50) NOT NULL,\n `User_Date` varchar(50) NOT NULL,\n PRIMARY KEY (`User_ID`)\n) ENGINE=InnoDB DEFAULT CHARSET=latin1

You do have a primary key in your user_location table (as indeed you should) - I have highlighted it in red above -, please follow the link I provided previously to find out how to properly handle this issue.