UPDATE, JOIN and WHERE MySQL

Question

I'm currently working on my query. So I have two tables, tbl_room and tbl_reservation. I wanted to do an update query with the following conditions:

Decrement room counter by 1 in tbl_room if:

The first and last name of the customer matches the first and last name in the tbl_reservation
The tbl_reservation room ID matches with the tbl_room room ID

I'm currently stuck with this:

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

These are my tables:

----------
tbl_room
----------
roomID,
room_type,
capacity,
reserve <--- reservation counter


----------
tbl_reservation
----------
reserveID,
cus_fname,
cus_lname,

After recreating the table in SQLFiddle (http://sqlfiddle.com/#!9/4b8213/1/0), there seems to be no problem with the update statement. @Justine — WorkSmarter, Apr 16 '15 at 06:33
I actually had 6 queries. This is the last query I have and it doesn't work >_< I already update my question. Thanks for all your help! :) — Justine Win, Apr 16 '15 at 13:35
Please stop using MySQL and upgrade to MySQL*i* or PDO. Much more secure, much safer and more readable. MySQL is **deprecated** and should not be relied upon as it is. — Martin, Apr 16 '15 at 13:54
@JustineWin what is your `reserve` field in **tbl_room**? What do you mean for *"reservation counter"*? Is it just a flag to say that the room has been reserved and cannot be reserved twice at the same time? Or a counter of how many times the room has been reserved? @-@ — Cliff Burton, Apr 16 '15 at 15:04
reserve field is the counter (the one being decremented) :) @cliff — Justine Win, Apr 16 '15 at 15:09
@Martin I tried, but I seem to be more comfortable with MySQL >_> — Justine Win, Apr 16 '15 at 15:09
Hahahah @JustineWin, it is for you! Check for [MySql injections](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) XD — Cliff Burton, Apr 16 '15 at 15:13
Because the reserve counter displays the reservations made in a room (the room has bedspaces). So assuming the one who reserved the bedspace in the room checked-in in a different room instead of the original room he reserved, the reservation made will be deleted in the tbl_reservation and at the same time the reserve counter of the tbl_room will be decremented by 1. @CliffBurton thanks for ur response buddy! — Justine Win, Apr 16 '15 at 15:21
So **2** different customers can reserve **2** bedspaces in the same room at the same time? I see your project is a Hotel reservation sistem, or am I wrong? If a customer reserves a room with **4** bedspaces he reserves all the **4** bedspaces in that room, even if he is alone, with one, two or three other people. A counter in a database field is an error for data consistency (e.g. For a registry DB you can't declare a field as **Age** but **birth_date** and then calculate the age each time you need). **To be continued...** — Cliff Burton, Apr 16 '15 at 16:12
Why not just change the room number at check-in so the origina reserved room comes back vacancy? **Fin...** ;) — Cliff Burton, Apr 16 '15 at 16:17
Nope, it is for our school's transient dormitory :D A person can just rent for a bedspace. That is the confusing part of the system :( I'm planning to remove the reserve counter in the tbl_room and add a status column in the tbl_reservation instead so I could just do a COUNT instead @@ . That, if I won't still be able to find a query for my current plan >_> — Justine Win, Apr 16 '15 at 16:39
YEAH!! You are on the right way...somewhere! Here the protagonists are **person** and **bed**: 1 **person** can *reserve* N *bed*s (or am I wrong? I don't know how a dormitory works, sorry) and 1 **bed** can be *reserved* by N **person**s. So in the ER schema ther should be a **N:N** relationship and **reservation** becomes the join table. But if it is a homework I spoken too much!! u.u — Cliff Burton, Apr 16 '15 at 18:10
Nope, only 1 person for 1 bedspace :D My only problem is the query. It seem not to work in any other way >_> — Justine Win, Apr 17 '15 at 00:58
I don't think so, a person can reserve a bedspacecece for a period of time, then when the time ends (the next school year o semester) the bedspace comes back free and can be reserved by another person and the first person reserves another bedspace. Think to the time elapse what happens to the bed and the person, not at the single moment. What is your need? What is the action you have to perform? Think out to the query you need and if you still have problems, ask again. If the intuition come from you it is better XD — Cliff Burton, Apr 17 '15 at 08:15

score 2 · Answer 1 · edited Nov 04 '19 at 08:48

2

I already came up with the right query. Yehey! So I moved my query just after the while statement and used this syntax:

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

edited Nov 04 '19 at 08:48

halfer

19,824
17
99
186

answered Apr 18 '15 at 18:18

Justine Win

101
1
9

score 0 · Answer 2 · answered Apr 16 '15 at 13:48

0

Try this...

$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'";

answered Apr 16 '15 at 13:48

ronald8192

5,003
2
14
23

I tried using the same query. I think I need to use join instead since they belong in diff tables. – Justine Win Apr 16 '15 at 15:17

UPDATE, JOIN and WHERE MySQL

2 Answers2