我目前正在处理我的查询。所以我有两张桌子,tbl_room和tbl_reservation。我想在以下条件下进行更新查询:
如果出现以下情况,将房间柜台减少 1 分tbl_room:
- 客户的名字和姓氏
- 与tbl_reservation中的名字和姓氏匹配
- tbl_reservation房间 ID 与tbl_room房间 ID 匹配
我目前被困在这个问题上:
$result = mysql_query("UPDATE
tbl_room
JOIN tbl_reservation
ON tbl_room.roomID = tbl_reservation.roomID
AND tbl_reservation.cus_fname = '$cusFN'
AND tbl_reservation.cus_lname = '$cusLN' SET tbl_room.reserve = reserve - 1 ");
这些是我的表格:
----------
tbl_room
----------
roomID,
room_type,
capacity,
reserve <--- reservation counter
----------
tbl_reservation
----------
reserveID,
cus_fname,
cus_lname,
我已经想出了正确的查询。嘿嘿!所以我在 while 语句之后移动了我的查询并使用以下语法:
$result6 = mysql_query("UPDATE tbl_room
JOIN tbl_reservation ON tbl_room.roomID=tbl_reservation.roomID
AND tbl_reservation.cus_fname='$cusFN'
AND tbl_reservation.cus_lname='$cusLN'
SET tbl_room.reserve=tbl_room.reserve-1");
试试这个...
$sql = "UPDATE `tbl_room`
SET `tbl_room`.`reserve` = `tbl_room`.`reserve` - 1
WHERE `tbl_room`.`roomID` = `tbl_reservation`.`roomID`
AND `tbl_reservation`.`cus_fname` = '$cusFN'
AND `tbl_reservation`.`cus_lname` = '$cusLN'";