Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    Regular Coder
    Join Date
    Sep 2004
    Posts
    713
    Thanks
    6
    Thanked 2 Times in 2 Posts

    php query question

    my database structure

    id name status
    1 abc 0
    2 ggg 0
    3 mimi 1

    now i want to update id=2 status to 1 and the rest to 0, how can i do it in one php query?

    now my steps are

    update all status to 0 then update the id=2 status to 1, two steps! anyway do in one step?
    flying dagger

  • #2
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by xiaodao View Post
    my database structure

    id name status
    1 abc 0
    2 ggg 0
    3 mimi 1

    now i want to update id=2 status to 1 and the rest to 0, how can i do it in one php query?

    now my steps are

    update all status to 0 then update the id=2 status to 1, two steps! anyway do in one step?
    Code:
    update tablename set status = if(id = 2, 1, 0)
    best regards

  • #3
    Regular Coder
    Join Date
    Sep 2004
    Posts
    713
    Thanks
    6
    Thanked 2 Times in 2 Posts
    id=2 is only one example, maybe id=1, or maybe id=2345, how to make it flexible and apply to all record?
    flying dagger

  • #4
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by xiaodao View Post
    id=2 is only one example, maybe id=1, or maybe id=2345, how to make it flexible and apply to all record?
    PHP Code:
    $query "update tablename set status = if(id = $id, 2, 1)"
    work for a single value.

    best regards

  • #5
    Regular Coder
    Join Date
    Sep 2004
    Posts
    713
    Thanks
    6
    Thanked 2 Times in 2 Posts
    no working...

    Code:
    		$id=intval($id);
    		$userid=intval($userid);
    
    		$sql=$this->db->query("UPDATE ".$prefix."address SET default = if(addressid='$id', 2, 1) WHERE userid='$userid'") or die($this->db->error());
    flying dagger

  • #6
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by xiaodao View Post
    no working...

    Code:
    		$id=intval($id);
    		$userid=intval($userid);
    
    		$sql=$this->db->query("UPDATE ".$prefix."address SET default = if(addressid='$id', 2, 1) WHERE userid='$userid'") or die($this->db->error());
    not same query.

    best regards

  • #7
    Regular Coder
    Join Date
    Sep 2004
    Posts
    713
    Thanks
    6
    Thanked 2 Times in 2 Posts
    my question is the example, the case i provided no working is the real case
    flying dagger

  • #8
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by xiaodao View Post
    my question is the example, the case i provided no working is the real case
    you changed the query and this is the reason why didn't work.

    Code:
    WHERE userid='$userid'"
    if you want something else then what you ask, explain what you want do.

    best regards


  •  

    Posting Permissions

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