php query question

[xiaodao]

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?

[oesxyl]

Originally Posted by xiaodao

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?

update tablename set status = if(id = 2, 1, 0)

best regards

[xiaodao]

id=2 is only one example, maybe id=1, or maybe id=2345, how to make it flexible and apply to all record?

[oesxyl]

Originally Posted by xiaodao

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

[xiaodao]

no working...

		$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());

[oesxyl]

Originally Posted by xiaodao

no working...

		$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

[xiaodao]

my question is the example, the case i provided no working is the real case

[oesxyl]

Originally Posted by xiaodao

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.

WHERE userid='$userid'"

if you want something else then what you ask, explain what you want do.

best regards