UPDATE table issues

[cocpg]

Hi there guys,

 

I have been lurking for a good couple of days, trawling for the answer, but to no avail! This is after a good day of despairingly staring at my code.

So before I scream and run out the door, stripping off my clothes and literally going insane, here is a question I hope someone could help me with! (and it would be much appreciated!)

 

It should be noted that I am not at all experienced, and have possibly made the most obvious mistake possible for all I know...

 

MySQL ver: 5.0.81-community

 

Show create table:

CREATE TABLE `lexpense` (

`pID` int(11) NOT NULL,

`expid` int(11) NOT NULL auto_increment,

`desc` varchar(32) character set latin2 NOT NULL,

`amount` decimal(6,2) NOT NULL,

`recipient` varchar(32) character set latin7 NOT NULL,

PRIMARY KEY  (`expid`)

) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

 

Explain table:

Field Type         Null Key Default Extra

pID        int(11)         NO   NULL

expid int(11)         NO PRI NULL auto_increment

desc varchar(32) NO   NULL

amount decimal(6,2) NO   NULL

recipient varchar(32) NO   NULL

 

raw MySQL statement:

"UPDATE lexpense SET desc='x', amount='y', recipient='z' WHERE expid='a'"

(i think that is what the forum rules mean by raw mysql, apologies if not)

 

Error Number: 1064

(this may have something to do with compatibility - however, there seems to be no clear message for my particular problem that I can find)

 

Error Statement:

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 'desc='repair roof', amount='122.00', recipient='roofmakers inc' WHERE expid=''' at line 1

 

So the update statement in question is correct in syntax  (I believe). In fact, I know it works in some cases, as shown below. I have several update functions. One works, the other doesn't:

 

WORKING:

  //##############EDIT INVENTORY ITEM###############

  function editInvItem($iName, $iDesc, $iStatus, $iComments){

    $iID = $_SESSION['iID'];

    $q = "UPDATE inventory SET name='$iName', description='$iDesc', status='$iStatus', comments='$iComments' WHERE ID='$iID'";

    return mysql_query($q, $this->connection) or die(mysql_error());

  }

 

(aim of above statement: edit the details of an item in the inventory for a property)

 

NOT WORKING:

  //##############EDIT LANDLORD EXPENSE###############

  function editLExp($amount, $desc, $recipient){

$eID = $_SESSION['eID'];

    $q = "UPDATE lexpense SET desc='$desc', amount='$amount', recipient='$recipient' WHERE expid='$eID'";

    return mysql_query($q, $this->connection) or die(mysql_error().mysql_errno());

  }

 

(aim of above statement: edit the details of a landlord's revenue item for a particular property)

 

As you can see, they each use different tables (working:inventory, not working:lexpense), so it is most likely that it is the table named 'lexpense' that is the faulty link here.

 

Once again, any help is much appreciated

Chris

[PFMaBiSmAd]

desc (the starting position in the query that is displayed in the error as the to use near '...) is a reserved keyword -

http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html

[cocpg]

fantastic. completely resolved with a one line reply.

Thank you so much PFMaBiSmAd!