dorarishome
asked on
sql help needed for booking system
Hi,
I am building a web app for booking and tourist company
I have the following table
summary_pnr
ID | PNR_ID | PNR | SEATS
1 | 123 | ABC1 | 50
2 | 123 | ABC2 | 100
3 | 124 | XYZ | 200
and another table
booking
ID | PNR_ID
1 | 123
2 | 124
What I am trying to achieve is as following:
When a booking done the app must deduct seats from first table (summary_pnr)
but you notice there is for same booking there is some times two pnr (Passenger name list)
so system will deduct from first pnr and if it reaches 0 it start deduct from second pnr
in another word
DO Booking ----> Check which package is this ----> 1 -->if it has two PNR ID ---> Deduct from first until its zero ---> Then start deducting from the other pnr , else alert no more seats
a previous questions about the issue is:
https://www.experts-exchange.com/questions/28283840/Simple-SQL-Query-Help-Needed-500-Urgent.html
https://www.experts-exchange.com/questions/28288478/Simple-SQL-Query-Help.html
I am building a web app for booking and tourist company
I have the following table
summary_pnr
ID | PNR_ID | PNR | SEATS
1 | 123 | ABC1 | 50
2 | 123 | ABC2 | 100
3 | 124 | XYZ | 200
and another table
booking
ID | PNR_ID
1 | 123
2 | 124
What I am trying to achieve is as following:
When a booking done the app must deduct seats from first table (summary_pnr)
but you notice there is for same booking there is some times two pnr (Passenger name list)
so system will deduct from first pnr and if it reaches 0 it start deduct from second pnr
in another word
DO Booking ----> Check which package is this ----> 1 -->if it has two PNR ID ---> Deduct from first until its zero ---> Then start deducting from the other pnr , else alert no more seats
a previous questions about the issue is:
https://www.experts-exchange.com/questions/28283840/Simple-SQL-Query-Help-Needed-500-Urgent.html
https://www.experts-exchange.com/questions/28288478/Simple-SQL-Query-Help.html
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Would you also please guide me how to save this procedure into mysql database using navicatmysql or cpanel sql
Thank you
Thank you
ASKER
ASKER
Thank you very much
please help in the next question
https://www.experts-exchange.com/questions/28295857/transaction-rolback-mysql-function.html
please help in the next question
https://www.experts-exchange.com/questions/28295857/transaction-rolback-mysql-function.html
ASKER
I really need as well an alert where no more seats or seats less than booking
Also I really need a rollback function to return seats after they expire
I appreciate your great help.
Thanks you