www.webdeveloper.com
Results 1 to 5 of 5

Thread: multiple row UPDATE (mySQL)

  1. #1
    Join Date
    Sep 2008
    Posts
    61

    Smile multiple row UPDATE (mySQL)

    Hi!

    I want to update data on multiple rows in my table. Such as this :

    PHP Code:
    $mysql->query("UPDATE categories SET open=0");

    foreach(
    $p as $value) {
        
    $mysql->query("UPDATE categories SET open=1 WHERE id=" $value);

    So what I want to do is to set "open" to 1, on rows with specific id's, and all the others shall have open=0;
    So if I've got a table like this

    Code:
     _ID___OPEN_
    |  1  |  1  |
    |  2  |  0  |
    |  3  |  0  |
    |  4  |  1  |
    |  5  |  0  |
    And the ID's I want to update is 2 and 5, the table should look like below after the query.

    Code:
     _ID___OPEN_
    |  1  |  0  |
    |  2  |  1  |
    |  3  |  0  |
    |  4  |  0  |
    |  5  |  1  |
    Is this possible in one query?

    Cheers and Thanks,
    Artheus

  2. #2
    Join Date
    Mar 2007
    Posts
    946
    Use mysql handy IN()

    Look something like this
    PHP Code:
    UPDATE categories SET open=1 WHERE id IN (1,2,4,5
    "Hippies.They're everywhere. They wanna save the earth, but all they do is smoke pot and smell bad."-Cartman

  3. #3
    Join Date
    Jan 2010
    Posts
    13
    Here is:

    $ids=array();
    foreach($p as $value) {
    $ids[]=$value;
    }
    $mysql->query("UPDATE categories SET open=1 WHERE id IN (".implode(",",$ids).")");

  4. #4
    Join Date
    Mar 2007
    Posts
    946
    Quote Originally Posted by qforever View Post
    Here is:

    $ids=array();
    foreach($p as $value) {
    $ids[]=$value;
    }
    $mysql->query("UPDATE categories SET open=1 WHERE id IN (".implode(",",$ids).")");
    If $p is already an array then you don't need to loop through $p to store it in another array.
    "Hippies.They're everywhere. They wanna save the earth, but all they do is smoke pot and smell bad."-Cartman

  5. #5
    Join Date
    Jan 2010
    Posts
    13
    Quote Originally Posted by skywalker2208 View Post
    If $p is already an array then you don't need to loop through $p to store it in another array.
    It's obvious. Just an example. Nothing more.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles