我有这个ff表叫做equipment:
+----------+----------+-----------+-------------+----------+---------+
| equip_id | chara_id | weapon_id | headgear_id | armor_id | ring_id |
+----------+----------+-----------+-------------+----------+---------+
| 3 | 1 | 3 | 5 | 9 | 8 |
| 5 | 3 | 3 | 5 | 3 | 8 |
| 6 | 4 | 7 | 5 | 3 | 8 |
| 7 | 5 | 4 | 5 | 3 | 8 |
| 8 | 6 | 3 | 5 | 2 | 8 |
| 10 | 8 | 3 | 5 | 2 | 8 |
+----------+----------+-----------+-------------+----------+---------+
我有这个长case语句:
switch ($equip->item_type) {
case '1':
# Weapon
$sql_equip = "UPDATE equipment SET weapon_id = :item_id WHERE chara_id = :chara_id";
break;
case '2':
# Armor
$sql_equip = "UPDATE equipment SET armor_id = :item_id WHERE chara_id = :chara_id";
break;
case '3':
# Ring
$sql_equip = "UPDATE equipment SET ring_id = :item_id WHERE chara_id = :chara_id";
break;
case '4':
# Headgear
$sql_equip = "UPDATE equipment SET headgear_id = :item_id WHERE chara_id = :chara_id";
break;
default:
# do nothing...
break;
}
我在网上看到一些查询,你可以在你的查询中使用case语句,我搜索了网络,我似乎找不到一个正确的使用
我想在1语句,因为我将包装它在一个事务函数,也将添加另一行mysql语句。所以,而不是做4x2(当前)我将只做2(如果这可以在1查询)mysql查询。
你可以有这样的语句
UPDATE equipment
SET weapon_id = CASE WHEN :itemtype = 1 THEN :item_id ELSE weapon_id END,
armor_id = CASE WHEN :itemtype = 2 THEN :item_id ELSE armor_id END,
ring_id = CASE WHEN :itemtype = 3 THEN :item_id ELSE ring_id END,
headgear_id = CASE WHEN :itemtype = 4 THEN :item_id ELSE headgear_id END
WHERE chara_id = :chara_id
但是你需要传递三个值,:itemtype
, :item_id
, :chara_id
$fieldnames = array(1=>'weapon', 'armor', 'ring', 'headgear');
if (isset($fieldnames[$equip->item_type])) {
$field = $fieldnames[$equip->item_type].'_id';
$sql_equip = "UPDATE equipment SET $field = :item_id WHERE chara_id = :chara_id";
}