Posted by J.O. Aho on 04/23/06 18:05

Mark D. Smith wrote:
> Hi
>
> i am trying to update a group of values in 1 table all at once.
>
> my sql string is
>
> $sql = "UPDATE job_adverts (Posted_ID, Date_Posted, Job_Title, Job_Code,
> Job_Location, Job_Salary, Job_Description, Company_image, Contact_Name,
> Contact_Email, Contact_Web, Contact_Company, Contact_Address,
> Contact_Telephone, Contact_Fax, company_id, views) SET ('$Posted_ID',
> '$Date_Posted', '$Job_Title', '$Job_Code', '$Job_Location', '$Job_Salary',
> '$Job_Description', '$Nom_Image', '$Contact_Name', '$Contact_Email',
> '$Contact_Web', '$Contact_Company', '$Contact_Address',
> '$Contact_Telephone', '$Contact_Fax', '$company_id', '$views') WHERE
> ID='$ID'";

Updates works in a bit different way than insert

$sql = "UPDATE job_adverts SET Posted_ID='$Posted_ID',
Date_Posted='$Date_Posted', Job_Title='$Job_Title', Job_Code='$Job_Code',
Job_Location='$Job_Location', Job_Salary='$Job_Salary',
Job_Description='$Job_Description', Company_image='$Nom_Image',
Contact_Name='$Contact_Name', Contact_Email='$Contact_Email',
Contact_Web='$Contact_Web', Contact_Company='$Contact_Company',
Contact_Address='$Contact_Address', Contact_Telephone='$Contact_Telephone',
Contact_Fax='$Contact_Fax', company_id='$company_id', views='$views' WHERE
ID='$ID'";

For info about UPDATE see: http://dev.mysql.com/doc/refman/5.0/en/update.html


> but this fails, how should i re-write it? its based on the INSERT INTO code
> below which works.

If the ID column is a primary key/unique then this will fail, but if the
column isn't a primary key, then you would have both the updated and the old
entry in the database, which of course ain't a good thing.


//Aho

[Back to original message]