Update multiple fields at same time

[PeterPan]

("UPDATE Yahoogroup SET `Status`='2', `date`='12 Apr 2006 15:25:03',
`email`='jeremy.frankton@domain.co.nz', `subject`='Re: [Groupname]
Rail Use Surges', `modemail`='',
`webread`='0', `IP`='123.4.56.789' WHERE (`month`='200604' and
`group`='group' and `file`='1')")



Hi, (Ive changed the email / name of the Yahoogroup). but I'm updating
a MYSQL database, & get an error with the MYSQL query..

Is there any website tutorial withmultiple field names to update with
multiple fields at the same time - with multiple WHERE clauses ?

(I can only find tutorials on single items one at a time...)

Any advise on where my mistake can be - Thank you.

- Here is my TABLE STRUCTURE


Yahoogroup
Field Type Null Default
Status tinyint(1) No 0
group varchar(22) No
month int(6) No 0
file int(4) No 0
date int(14) No 0
email varchar(25) No
modemail varchar(25) No
Subject varchar(25) No
webread char(1) No
IP varchar(15) No


Indexes:
Keyname Type Cardinality Field
group INDEX None group


Space usage:
Type Usage
Data 587,176 Bytes
Index 191,488 Bytes
Total 778,664 Bytes
Row Statistics:
Statements Value
Format dynamic
Rows 19,178
Row length ø 30
Row size ø 41 Bytes
Creation Sep 23, 2006 at 09:38 AM
Last update Oct 03, 2006 at 06:26 AM

[entrecon]

What MySql error are you getting?

[entrecon]

Just looked at the Data you had here a little closer and noticed that the Update code has 'subject' while the table structure has 'Subject'. After going cross-eyed looking at code I have learned to check my quotes and my case.

[PeterPan]

Ive fixed it....

Apparently its the 'backticks' that were the problem...
Ive also updated the 'date' to VARCHAR...


$query="UPDATE `Yahoogroup` SET `Status`='2', `date`='$date', `email`='$from', `Subject`='$subject', `modemail`='$approved', `webread`='$webread', `IP`='$IP' WHERE (`month`='$month' and `group`='$grp' and `file`='$file');";


Ive also removed the extra quote / Brackets from the edge.

[PeterPan]

Hmm

it was fixed -0 But now I get the same errors :-


UPDATE Yahoogroup SET `Status`='2', `date`='15 May 2006 22:15:07', `email`='emailaddress@gmail.com', `Subject`='subject here', `modemail`='', `webread`='0', `IP`='64.233.162.203' WHERE (`month`='200605' and `group`='new' and `file`='168');

Unable to add data
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 Subject', `modemail`='', `webread`='0', `IP`='64.233.16' at line 1
Check the IP address at the end of the error message - that's not the normal full IP address (unless of course you intended to name a full range from 0-255).

[satis]

can you try this?


UPDATE Yahoogroup
SET Status=2,
`date`='15 May 2006 22:15:07',
email='emailaddress@gmail.com',
Subject='subject here',
modemail='',
webread='0',
IP='64.233.162.203'
WHERE
`month`='200605' and
`group`='new' and
`file`=168;


You sure are using alot of reserved names for column names. I try to avoid that if at all possible.... date, group, and I think month and file are all reserved. Also, is your email field still varchar (25)? You may want to expand that...the email you had in your first post was more than 25 characters.

[PeterPan]

Ive added 'a' to the end of the MYSQL table field names (to not conflict with reserved names)..

I've updated my script - to show the new (correct) field names - & still getting an error :-


UPDATE Yahoogroup SET `Statusa`='2', `datea`='10 Jun 2006 07:35:04', `emaila`='email@yahoo.com', `Subjecta`='[name] Re: Ahhh, i'm so confused!! :-)', `modemaila`='', `webreada`='1', `IPA`='66.94.237.52' WHERE (`montha`='200606' and `groupa`='schizoid' and `filea`='185');

Unable to add data
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 'm so confused!! :-)', `modemaila`='', `webreada`='1', `IPA`='66.94.237.52' WHERE' at line 1


(Ive removed the previous data from the table, & re-added a few rows - now trying tomodify them... )

However same data.

PS - Im not sure what the IP problem is - because the computer doesnt know its supposed to be an IP address - All it knows is I want to add a few numbers - So that shouldnt matter..

[PeterPan]

I've found my error

It was trying to update my 'subject' field =- & sometimes it contains their own quote marks.

Ive added a preg_replace - to swap quote marks with # marks.

(i'll to another preg replace - when Im 'reading' the field)


Thanks...

[mixerson]

You can store single quotes in a database field - all you have to do is escape them with a backslash in the SQL query string so they don't get confused with the delimeters.

PHP provides the function addslashes() to do this for you.$subject = addslashes($subject);
$result = mysql_query("UPDATE Yahoogroup SET ... `Subjecta`='$subject', ...")Then you don't have to do anything special when you read the data - the single quotes are already there.