MySQL Update Query

MySQL database
#1

Hi,

I am building an update query which will update a record in a table, but I am getting an error which doesn’t make sense to me.

Here is my code:
$query = “UPDATE iknew
SET ID=’$id’,
SET Herbarium=’$herbarium’,
SET Taxon=’$taxon’,
SET Bar_Code=’$bar_code’,
SET GUID=’$guid’,
SET Private=’$private’,
SET Image=’$image’,
SET Kingdom=’$kingdom’,
SET Genus=’$genus’,
SET Species=’$species’,
SET Variety_Name=’$variety_name’,
SET Taxon_Author=’$taxon_author’,
SET Accession=’$accession’,
SET State=’$state’,
SET County=’$county’,
SET Habitat=’$habitat’,
SET Locality=’$locality’,
SET Latitude=’$latitude’,
SET Longitude=’$longitude’,
SET Quadrangle=’$quadrangle’,
SET Collector=’$collector’,
SET Collection_Number=’$collection_number’,
SET Country=’$country’,
SET Family=’$family’,
SET Annotation=’$annotation’,
SET Col_Date=’$col_date’,
SET Comments=’$comments’,
SET Natural_Area=’$natural_area’,
SET Col_datet=’$col_datet’,
SET Symbol=’$symbol’
WHERE ID=’$id’ LIMIT 1”;

Here is a sample update query:

UPDATE iknew SET ID=‘62547’, SET Herbarium=‘EKY’, SET Taxon=‘Salix exigua Nutt.’, SET Bar_Code=‘3288’, SET GUID=’’, SET Private=‘1’, SET Image=’’, SET Kingdom=‘Plantae’, SET Genus=‘Salix’, SET Species=‘exigua’, SET Variety_Name=’’, SET Taxon_Author=‘Nutt.’, SET Accession=‘EKY3288’, SET State=‘Kentucky’, SET County=‘Madison’, SET Habitat=‘stream bank’, SET Locality=‘Hagan Mill Road at bridge over Silver Creek.’, SET Latitude=’ 0’, SET Longitude=’ 0’, SET Quadrangle=’’, SET Collector=‘John Hornback’, SET Collection_Number=‘38’, SET Country=‘USA’, SET Family=‘Salicaceae’, SET Annotation=’’, SET Col_Date=‘1976-04-18’, SET Comments=‘RRP’, SET Natural_Area=’’, SET Col_datet=’’, SET Symbol=‘SAEX’ WHERE ID=‘62547’ LIMIT 1

And here is the error given:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘SET Herbarium=‘EKY’, SET Taxon=‘Salix exigua Nutt.’, SET Bar_Code=‘3288’, SET’ at line 3

Any help would be quite appreciated.

#2

hi,

there is a problem in your update query. In update query you have write SET more than one time so obviously it will saw you error. here is example of update query

[php]mysql_query(“UPDATE student SET ID = $b, FName = '”.$c."’, LName = ‘".$d."’, Age = $e, Sex = ‘".$f."’, Email = ‘".$g."’, City = ‘".$h."’ WHERE ID = $a ");[/php]

#3

And don’t forget to sanitize data you pulled out from form…characters can get you in trouble…

#4

I agree with sarthakpatel, you only need to type SET once, followed by commas.

#5

Also you can use this tiny piece of javascript code to create a Go Back link, instead of forcing the user to hit the back button on the browser (which is ver unpractical)…

[php]echo ‘

Please Go Back and complete the form
’;[/php]

#6

I don’t know if you still have this problem but I had to do this when changing multiple entries.

SET Variable="’.$variable.’" , and so on.

#7

Why do you need “’.$variable.’” around the variable? In some cases, ‘$variable’ works. What’s the differencce?

#8

when inserting a variable into a query it would only have to be

$variable

if you are using something like:
[php]
$_GET[‘variable’] or $_POST[‘variable’] or $_SESSION[‘variable’] and so on
[/php]
then those need to be in curly brackets/braces or between quotes and periods
[php]". variable ."[/php]
I have never run across a point where you have to put a regular $variable in braces

Sponsor our Newsletter | Privacy Policy | Terms of Service