MySQL Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
User Name:
Password:
Remember me
 
Go Back   Dev Articles Community Forums > Databases > MySQL Development
Reload this Page

update multiple columns using mysql UPDATE

Discuss update multiple columns using mysql UPDATE in the MySQL Development forum on Dev Articles.
update multiple columns using mysql UPDATE MySQL Development forum to discuss administration, SQL syntax, and other MySQL-related topics. Find help with installing, configuring, and maintaining your MySQL databases.

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Display Modes
 
Unread Dev Articles Community Forums Sponsor:
Stay one step ahead of the competition. Evaluate and give feedback on some of the hottest web development tools on the market today. Make your opinion heard! Click Here
  #1  
Old July 1st, 2003, 06:39 AM
jben.net jben.net is offline
Contributing User
Dev Articles Newbie (0 - 499 posts)
  
Join Date: Apr 2003
Posts: 51 jben.net User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 19 m 41 sec
Reputation Power: 6
Send a message via AIM to jben.net
update multiple columns using mysql UPDATE
Hi,

I'm trying to update multiple columns in my db but I keep getting errors with the SQL. I'm using stripslashes and urlencode before I update the columns but it only seems to work if I try to update 1 column at a time ?

PHP Code:
 $title = stripslashes(urlencode($_POST['title']));
    $date = stripslashes(urlencode($_POST['date']));
    $body =stripslashes(urlencode($_POST['body']));
    
    // Attempt to connect to MySQL server
    $link = mysql_connect($dbHost ,$dbUser ,$dbPass ) or die( "An error has ocured: " .mysql_error (). ":" .mysql_errno ());
        
    $dbconnect = mysql_select_db($dbName);
        
    // setup MySQL query
    $query = "UPDATE news SET title = '$title', SET date = '$date', SET body = '$body' WHERE newsID = '$ID'";

    // Execute Query
    $result = mysql_query($query) or die( "An error has ocured: " .mysql_error (). ":" .mysql_errno ()); 


This throws this error ?

PHP Code:
 An error has ocured: You have an error in your SQL syntax near 'SET body = 'this+is+some+news+body+text+%21this+is+some+news+  body+text+%21this+i' at line 1:1064 


This a real pain in the butt, do I have to use 3 seperate querries to alter the data in a single row ???? surely not.

Thanks in advance,

Jon
Reply With Quote
  #2  
Old July 1st, 2003, 06:47 AM
jben.net jben.net is offline
Contributing User
Dev Articles Newbie (0 - 499 posts)
  
Join Date: Apr 2003
Posts: 51 jben.net User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 19 m 41 sec
Reputation Power: 6
Send a message via AIM to jben.net
ok, I've tried just updating 1 column (title, date, body) and they all work fine that way so why doesn't it work when Itry to update 3 at once ??? I've changed urlencode to htmlentities but that hasn't helped, any ideas ?

Thanks,

Jon
Reply With Quote
  #3  
Old July 1st, 2003, 06:53 AM
jben.net jben.net is offline
Contributing User
Dev Articles Newbie (0 - 499 posts)
  
Join Date: Apr 2003
Posts: 51 jben.net User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 19 m 41 sec
Reputation Power: 6
Send a message via AIM to jben.net
Right, I;ve got it to work like this:

PHP Code:
// setup MySQL query
    $query = "UPDATE news SET title = '$title' WHERE newsID = '$ID'"; //  

    // Execute Query
    $result = mysql_query($query) or die( "An error has ocured: " .mysql_error (). ":" .mysql_errno ());
    
    // setup MySQL query
    $query = "UPDATE news SET date = '$date' WHERE newsID = '$ID'"; //  

    // Execute Query
    $result = mysql_query($query) or die( "An error has ocured: " .mysql_error (). ":" .mysql_errno ());
    
    // setup MySQL query
    $query = "UPDATE news SET body = '$body' WHERE newsID = '$ID'"; // 

    // Execute Query
    $result = mysql_query($query) or die( "An error has ocured: " .mysql_error (). ":" .mysql_errno ()); 


so I know my data is in a correct format but to do it that way seems total overkill.

bummer,

Jon
Reply With Quote
  #4  
Old July 1st, 2003, 07:08 AM
dhouston's Avatar
dhouston dhouston is offline
Contributing User
Dev Articles Beginner (1000 - 1499 posts)
  
Join Date: May 2003
Location: Tennessee
Posts: 1,355 dhouston User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 7
Send a message via ICQ to dhouston
Try:

PHP Code:
 $query="UPDATE news SET title='$title', date='$date', body='$body' WHERE newsID=$ID 


If your ID is an integer, you shouldn't need quotes around $ID. (And your ID should probably be an auto-incremented ingeter unless you've got another primary key set that fills this role.) Note also that you don't need a "SET" for each column. Use SET and then give a comma-delimited name=value listing of updates. Also, putting stuff into the database, you probably want to add slashes rather than stripping them. PHP should automatically decode the query string into plain text (removing %20 and other such special characters and converting them back into spaces, quotes, etc.). And if somebody happens to put a single quote in the form blanks they've filled out, your query will be hosed up unless you add slashes. You strip slashes when pulling the data back out for display.
Reply With Quote
  #5  
Old November 29th, 2007, 11:23 AM
stuartnico stuartnico is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
  
Join Date: Nov 2007
Posts: 2 stuartnico User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 23 m 57 sec
Reputation Power: 0
Question
I've been working on a script for a few days 50% of it works in terms of installing the data from one table to another. The part that has me stumped is getting it to update the data.

So I took the query I used to generate the rows and tried an update script, even though I'm not getting any failure messages it not updating the required table.....

Would greatly appreciate some help here.

PHP Code:
 $query = "SELECT `table1`.`userid`,`table1`.`username`,`table1`.`pa  ssword`,`table1`.`email` FROM `table1` ORDER BY `table1`.`userid` ASC";

$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($result)) {

mysql_query ("UPDATE `table2` SET email='$row[3]', username='$row[1]', pwd='$row[2]' WHERE UID='$row[0]'"); 
    

} 


Stuart
Reply With Quote
  #6  
Old November 30th, 2007, 04:32 AM
stuartnico stuartnico is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
  
Join Date: Nov 2007
Posts: 2 stuartnico User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 23 m 57 sec
Reputation Power: 0
Any idea's anyone.
Reply With Quote
Reply

Viewing: Dev Articles Community Forums > Databases > MySQL Development > update multiple columns using mysql UPDATE

« Previous Thread | Next Thread »

Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Linear Mode Linear Mode
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 6 hosted by Hostway.