MySQL Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
User Name:
Password:
Remember me
 



Go Back   Dev Articles Community ForumsDatabasesMySQL Development

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Display Modes
 
Unread Dev Articles Community Forums Sponsor:
  #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: 12
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 ocuredYou 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: 12
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: 12
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: 13
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
  #7  
Old July 8th, 2008, 06:54 AM
killdude69 killdude69 is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Jul 2008
Posts: 1 killdude69 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 23 m 43 sec
Reputation Power: 0
I know this post is very old, but I know exactly how you can update multiple columns in a row.

you cant use commas with the update function. you must use the keyword AND.

Example:

PHP Code:
 $sql "UPDATE table SET row1 = 'value' AND SET row2 = 'value' WHERE row3 = 'value'";
$result mysql_query($sql); 


Or, if the above throws an error, try this:

PHP Code:
 $sql "UPDATE table SET row1 = 'value' AND row2 = 'value' WHERE row3 = 'value'";
$result mysql_query($sql); 


Hope it works.

Reply With Quote
  #8  
Old August 7th, 2008, 05:37 AM
stongia stongia is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Aug 2008
Posts: 1 stongia User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 12 m 19 sec
Reputation Power: 0
SET infront of every field to update is not required. All the fields need update can be seperated by comma.

Reply With Quote
  #9  
Old August 4th, 2010, 01:19 PM
assinschoigma assinschoigma is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Jul 2010
Location: China
Posts: 2 assinschoigma User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 h 12 m 2 sec
Reputation Power: 0
Hello Everyone - Great Forum! Let me know if you need any help!

Hi People, i would just like to make an say hi to everyone at forums.devarticles.comYour forum is great! Generally when I visit forums, I just come across crap, but this time I was very surprised, finding a informative forum containing good information. Thanks people and keep the good effort up.

Reply With Quote
  #10  
Old December 19th, 2011, 06:36 AM
WackoZacho WackoZacho is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Dec 2011
Posts: 3 WackoZacho User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 26 m 56 sec
Reputation Power: 0
Hi there. I'm having a problem where my script is supposed to update two columns in my MySQL database and it only updates one and continues on without an error.

The background behind my script-
Pretty much, I just made a login system. I completed most parts of it, but I'm down to the confirmation of the e-mail. I have the e-mail letter being sent out, them clicking the link, the script goes ahead and checks it against the database and if it matches, it is supposed to do 4 things:

1. Change the confirmcode field from the confirm code to 'y'
2. Change the accountstatus field from 'Grey' to 'Brown'
indicating they are no longer in the Validating user group.
3. Send the user their Registration Is Complete e-mail.
4. Send the admincp a push notification.

Steps 1, 3, and 4 work. Along the way I've told the script to report errors and it still reports no problems with the SQL Update. Additionally, when I use the MySQL Query Browser tool, and run the same query, both fields get updated, but when I run it through PHP only the first field does.

Here are the relevant functions of my code.

Code:
function ConfirmUser()
    {
        if(empty($_GET['code'])||strlen($_GET['code'])<=10)
        {
            $this->HandleError("Please provide the confirm code");
            return false;
        }
        $user_rec = array();
        if(!$this->UpdateDBRecForConfirmation($user_rec))
        {
            return false;
        }
        
        $this->SendUserWelcomeEmail($user_rec);
        $this->SendAdminIntimationOnRegComplete($user_rec);
        return true;
    }


Code:
    function UpdateDBRecForConfirmation(&$user_rec)
    {
        if(!$this->DBLogin())
        {
            $this->HandleError("Database login failed!");
            return false;
        }   
        $confirmcode = $this->SanitizeForSQL($_GET['code']);
        
        $result = mysql_query("SELECT name, email, accountstatus FROM $this->tablename WHERE confirmcode='$confirmcode'",$this->connection);   
        if(!$result || mysql_num_rows($result) <= 0)
        {
            $this->HandleError("Wrong confirm code.");
            return false;
        }
        $row = mysql_fetch_assoc($result);
        $user_rec['name'] = $row['name'];
        $user_rec['email']= $row['email'];
        $user_rec['accountstatus']= $row['accountstatus'];        
        $qry = "UPDATE $this->tablename SET confirmcode='y', accountstatus='Brown' WHERE confirmcode='$confirmcode'";
        if(!mysql_query( $qry ,$this->connection))
        {
            $this->HandleDBError("Error inserting data to the table\nquery:$qry");
            return false;
        }      
    }



I've also tried UPDATE %TBLNAME% SET X=x AND SET Y=y WHERE Z=z and the same result happens, only the first value (confirmcode) gets set to y but accountstatus wont get set to brown. And yeah, I figured maybe it was a silly mistake with the field name or something but the column is indeed named accountstatus, and its the same field type and length as confirmcode, and has no special properties or anything aside from a default value. I just find it odd that if I run it through QueryBrowser it works fine, yet misbehaves solely in PHP.

Any help would be great. If you need to see my other functions, I can post them too but as there are no errors reported, and since the value in the database does change, I have to assume there's no connection problems or what not.

Nevertheless, all my resources are available to you should you need to see other parts of the module.

Thanks in advance,

~Zach Glubka

Reply With Quote
  #11  
Old December 19th, 2011, 06:39 AM
WackoZacho WackoZacho is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Dec 2011
Posts: 3 WackoZacho User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 26 m 56 sec
Reputation Power: 0
btw one last thing

ConfirmUser() returns true when it is run. I also receive the final e-mail notifications from the functions that follow the UpdateDatabase function. So I know that it has to be returning true, otherwise there'd be an error somewhere or I wouldn't receive the e-mails. Don't know if that helps. Anyway, again, thanks for any help.

Reply With Quote
  #12  
Old June 13th, 2012, 10:37 AM
juacala juacala is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Jun 2012
Posts: 5 juacala User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 h 55 m 42 sec
Reputation Power: 0
Just in case you haven't solved this, my guess is that it is working and then you have something going back and reoverwriting that one field.

Right after the query runs, in PHP, I would make another select query that redumps the row and check the value there. My guess is it will show it was changed.

Then all you have to do is find where it's setting it back to the old value.

Reply With Quote
  #13  
Old November 19th, 2012, 01:15 AM
lugocottewd lugocottewd is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Nov 2012
Posts: 6 lugocottewd User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 18 m 31 sec
Reputation Power: 0
I know exactly how you can update multiple columns in a row.




Reply With Quote
  #14  
Old November 19th, 2012, 06:45 PM
Rhon3da Rhon3da is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Nov 2012
Posts: 3 Rhon3da User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 6 m 8 sec
Reputation Power: 0
I know exactly how you can update multiple columns in a row.
URLURLURLURL
URL

Reply With Quote
  #15  
Old November 20th, 2012, 12:44 AM
alexasthoma alexasthoma is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Nov 2012
Posts: 2 alexasthoma User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 10 m 57 sec
Reputation Power: 0
So I took the query I used to generate the rows and tried an update scriptURLURLURLURLURLURL

Reply With Quote
  #16  
Old January 7th, 2013, 02:03 AM
giliakareo giliakareo is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Jan 2013
Posts: 9 giliakareo User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 16 m 45 sec
Reputation Power: 0
and if somebody happens to put a single quote in the form blanks they've filled out

Reply With Quote
  #17  
Old January 7th, 2013, 07:13 AM
Rkjobdft Rkjobdft is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Jan 2013
Posts: 5 Rkjobdft User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 13 m 15 sec
Reputation Power: 0
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 ?


Reply With Quote
Reply

Viewing: Dev Articles Community ForumsDatabasesMySQL Development > update multiple columns using mysql UPDATE


Developer Shed Advertisers and Affiliates


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
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 | 
  
 


Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.

© 2003-2014 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap