Version Error with UPDATE query

[bano6010]

Hello,

 

I am trying to use the update command to update my SQL database from PHP but get an error. My version is 5.0.67.

 

Here is the error I get:

 

Error: 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 '(firstname, lastname) VALUES ('S.','')' at line 1

 

I looked up the documentation for the version and can't really see how my code is any different.

 

Here is my code:

$sql = "UPDATE officers SET (firstname, lastname) VALUES ('$_POST[firstname]','$_POST[lastname]')";

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "1 record updated";

 

Here is my table structure:

CREATE TABLE `officers` (
  `id` INTEGER(11) NOT NULL AUTO_INCREMENT,
  `type` TINYTEXT NOT NULL,
  `firstname` TINYTEXT NOT NULL,
  `lastname` TINYTEXT NOT NULL,
  `archived` INTEGER(1) UNSIGNED ZEROFILL NOT NULL,
  PRIMARY KEY (`id`)
)ENGINE=MyISAM
AUTO_INCREMENT=13 CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci';

 

I'm new to SQL and PHP but I can't see where the syntax error is. Any help would be greatly appreciated.

 

Thanks!

[peddel]

VALUES ('$_POST[firstname]','$_POST[lastname]')

 

this is ur problem

finding php variables trough the post methods is generally done by

$_POST['firstname'] --> see the extra inside quotes

 

This wont word to use single quotes inside single quotes !

 

SOLUTION

 

$firstname = $_POST['firstname']

$lastname = $_POST['lastname']

 

then ur code will be

 

VALUES ('$firstname','$lastname')

 

 

[PFMaBiSmAd]

This is the syntax definition for an UPDATE query -

 

UPDATE [LOW_PRIORITY] [iGNORE] tbl_name

    SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...

    [WHERE where_condition]

    [ORDER BY ...]

    [LIMIT row_count]

 

The syntax you are using is that of an INSERT query. Are you attempting to do an UPDATE or an INSERT?

 

[peddel]

LoL now u mentioned it, i totally scrambeld them in between

But i guess he has more than 1 problem then

 

I think he is looking for this

 

$sql = "INSERT INTO officers (firstname,lastname) VALUES ($firstname,$lastname);"
$result = mysql_query($sql);

[bano6010]

I am trying to do an update.

 

I tried this code:

 

$sql = "INSERT INTO officers (firstname,lastname) VALUES ($firstname,$lastname);"
$result = mysql_query($sql);

 

and I got an error on the second line. I also tried this:

VALUES ('$firstname','$lastname')

and got an error.

 

I guess I don't know enough about it to understand how to read the syntax posted for an UPDATE query. But I am attempting to UPDATE, not INSERT.

[PFMaBiSmAd]

I recommend reading a basic mysql tutorial to get up to speed with the basics of what you are trying to do (link has a page showing what an UPDATE query looks like) - http://w3schools.com/sql/default.asp

[bano6010]

I have actually looked at that page and used that site to get where I am now. The problem with that page is that it shows how to use the UPDATE when putting the updates right into the code. Where I am trying to let users make changes in a text box, hit submit and have to record updated. I have it where the current record is being displayed in the text box, but when submit is hit, it gives the error I put in my first post. I am having trouble understanding how to take the information in the text box and post it through the UPDATE query.

[bano6010]

I can not see where the syntax error in this code is. I have looked at several different examples and tutorials. It looks correct to me and I just can't see where its going wrong.

 

$sql = "UPDATE officers SET (firstname, lastname) VALUES ('$_POST[firstname]','$_POST[lastname]')";

 

I don't need to define the $_POST[] for first and last name do I? Those should come from the page before correct? In the error is shows both of the values so I assume that its correct. I just have no idea how to proceed.

[fenway]

Stop right now... echo $sql.  We're going in circles.