Seating Reservation System - PHP + MYSQL

  • (6 Pages)
  • +
  • 1
  • 2
  • 3
  • Last »

82 Replies - 19720 Views - Last Post: 23 March 2013 - 09:19 AM Rate Topic: -----

#1 carmex  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 44
  • Joined: 21-February 13

Seating Reservation System - PHP + MYSQL

Posted 21 February 2013 - 05:52 AM

Hi everyone, just a question regarding both php and mysql.
I am currently trying to create a seating reservation system where seats can be selected and booked.
I have been using these as guides :
http://forum.lowyat....pic/2349252/all
http://dayg.wordpres...rvation-system/

I'm looking for a way to loop through with php to insert into my sql database rowId's (A, B, C...) and columnId's(1,2,3..) to create each seat in the data base.
Below is an example of why i would like this to work. This snippet checks whether the seat has been booked or not by cross checking array values with rowId and columnId within the DB. But obviously there isn't anything to actually be checked as of now.

$sql = "SELECT * FROM seats WHERE columnId = '$seat' AND rowId = '$row'";
       $query = mysql_query($sql);
       $check = $row['status'];
 echo $sql.'<br>';
   if($check >0)
       {
           return 'disabled';
       }
       else{
           return 'available';
       }

   }


I realize i could go through one by one with INSERT INTO Seat (rowId, columnId) VALUES(A,1)....
But this would be bad programming and later when i design it so a admin can set up the seating plan (how many seats there are)it would create further problems.

I hope this makes sense, i have been trying to find a solution for ages now. Any help given will be appreciated.

Thanks.

This post has been edited by Dormilich: 21 February 2013 - 05:57 AM
Reason for edit:: please use [code] [/code] tags when posting code


Is This A Good Question/Topic? 0
  • +

Replies To: Seating Reservation System - PHP + MYSQL

#2 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3569
  • View blog
  • Posts: 10,404
  • Joined: 08-June 10

Re: Seating Reservation System - PHP + MYSQL

Posted 21 February 2013 - 07:59 AM

View Postcarmex, on 21 February 2013 - 01:52 PM, said:

I realize i could go through one by one with INSERT INTO Seat (rowId, columnId) VALUES(A,1)....
But this would be bad programming ...

why would that be bad programming? you have to insert that data eventually.

though for an initial build-up, I recommend Prepared Statements (create query once, execute as often as you like).

View Postcarmex, on 21 February 2013 - 01:52 PM, said:

... and later when i design it so a admin can set up the seating plan (how many seats there are)it would create further problems.

the database will not solve the problem, which row/column combination is valid and which is not. but it can make sure, that there are no duplicate seats (composite primary key).

if there is more than one place for seats (e.g. different rooms), you need to extend the composite primary key to the room ID as well.
Was This Post Helpful? 1
  • +
  • -

#3 carmex  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 44
  • Joined: 21-February 13

Re: Seating Reservation System - PHP + MYSQL

Posted 21 February 2013 - 08:15 AM

Big thanks for the reply !
Sorry I'm a little confused.

I was thinking that if I insert all the values and create all the seats myself when an admin comes to create a room they will not be able to do this the same way. Say they want 5 rows (A-E) and 20 columns (1-20) I need a way for them to change a variable which will loop and create this plan based on their input as they wont have access to the actual program code. Catch my drift? :S
Sorry trying my best to explain.

View PostDormilich, on 21 February 2013 - 07:59 AM, said:

I recommend Prepared Statements (create query once, execute as often as you like).


Sorry could you elaborate on this?
thanks
Was This Post Helpful? 0
  • +
  • -

#4 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3569
  • View blog
  • Posts: 10,404
  • Joined: 08-June 10

Re: Seating Reservation System - PHP + MYSQL

Posted 21 February 2013 - 08:24 AM

View Postcarmex, on 21 February 2013 - 04:15 PM, said:

Say they want 5 rows (A-E) and 20 columns (1-20) I need a way for them to change a variable which will loop and create this plan based on their input as they wont have access to the actual program code.

the problem is actually far worse. do you remember the seating plan given in the second tutorial? I cannot imagine a way to create that seating plan with just one or two variables.

the only way I can think of is passing the primary key data for each seat. e.g. if you have 2 rows / 3 columns, you could pass the array [A1, A2, A3, B1, B2, B3] and then you just insert that (and if row B only has 2 seats, then only use B1, B2). of course your programme needs to split that appropriately to insert it in the DB.
Was This Post Helpful? 1
  • +
  • -

#5 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3569
  • View blog
  • Posts: 10,404
  • Joined: 08-June 10

Re: Seating Reservation System - PHP + MYSQL

Posted 21 February 2013 - 08:29 AM

View Postcarmex, on 21 February 2013 - 04:15 PM, said:

View PostDormilich, on 21 February 2013 - 07:59 AM, said:

I recommend Prepared Statements (create query once, execute as often as you like).


Sorry could you elaborate on this?
thanks

http://www.dreaminco...base-recycling/ the preliminary code (the very first code box) pretty much does exactly what I meant. it inserts a handful (an array) of data into a DB.
Was This Post Helpful? 1
  • +
  • -

#6 carmex  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 44
  • Joined: 21-February 13

Re: Seating Reservation System - PHP + MYSQL

Posted 21 February 2013 - 09:08 AM

Nice tutorial, going to study it and try implement it.
Thanks for the help!
May re-open this topic in the future if that's okay with you? Think i may need some more advice :P
Was This Post Helpful? 0
  • +
  • -

#7 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3569
  • View blog
  • Posts: 10,404
  • Joined: 08-June 10

Re: Seating Reservation System - PHP + MYSQL

Posted 21 February 2013 - 10:01 AM

it wont be closed in the meantime.
Was This Post Helpful? 1
  • +
  • -

#8 carmex  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 44
  • Joined: 21-February 13

Re: Seating Reservation System - PHP + MYSQL

Posted 28 February 2013 - 06:42 AM

View PostDormilich, on 21 February 2013 - 10:01 AM, said:

it wont be closed in the meantime.


Hey there!
Managed to get everything going well, booking works etc.
However i know am trying to develop on initial ideas.

 "CREATE TABLE seats
(
  rowId varchar(1) not null,
  columnId int not null,
  status int,
  updatedby varchar(10),
  CONSTRAINT pk_seatId PRIMARY KEY (rowId, columnId)
)";



This is my current table. I would like to have the Primary /composite key as [rowId][columnId] e.g [A][1].

"CREATE TABLE bookings
(
bookingId not null AUTO_INCREMENT
seatId varchar(3) not null,
firstName varchar(15) not null,
secondName varchar(15) not null,
FOREIGN KEY (seatId) REFERENCES seats pk_seatId
)";



This is the second booking table as you can see seatId is being used as a foreign key. This is so that a booking can be searched based on the actual seatId row A column 1 [A][1] making it easy to find who has been booked for that seat.

However this is not working even with the first table, i cannot SELECT and echo pk_seatId to check it is working.
Is this possible or am i just doing something terribly wrong?

Thanks in advance ! :D
Was This Post Helpful? 0
  • +
  • -

#9 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3569
  • View blog
  • Posts: 10,404
  • Joined: 08-June 10

Re: Seating Reservation System - PHP + MYSQL

Posted 28 February 2013 - 09:52 AM

are that MyISAM or InnoDB tables?
Was This Post Helpful? 1
  • +
  • -

#10 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3569
  • View blog
  • Posts: 10,404
  • Joined: 08-June 10

Re: Seating Reservation System - PHP + MYSQL

Posted 28 February 2013 - 10:01 AM

as I google around, it seems to work differently:

Quote

an example from the MySQL docs
CREATE TABLE product (category INT NOT NULL, id INT NOT NULL,
                      price DECIMAL,
                      PRIMARY KEY(category, id)) ENGINE=INNODB;
CREATE TABLE customer (id INT NOT NULL,
                      PRIMARY KEY (id)) ENGINE=INNODB;
CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT,
                      product_category INT NOT NULL,
                      product_id INT NOT NULL,
                      customer_id INT NOT NULL,
                      PRIMARY KEY(no),
                      INDEX (product_category, product_id),
                      FOREIGN KEY (product_category, product_id)
                        REFERENCES product(category, id)
                        ON UPDATE CASCADE ON DELETE RESTRICT,
                      INDEX (customer_id),
                      FOREIGN KEY (customer_id)
                        REFERENCES customer(id)) ENGINE=INNODB;

it seems composite keys can only be referenced as composite keys (cf. this SO question))

This post has been edited by Dormilich: 28 February 2013 - 10:01 AM

Was This Post Helpful? 1
  • +
  • -

#11 carmex  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 44
  • Joined: 21-February 13

Re: Seating Reservation System - PHP + MYSQL

Posted 05 March 2013 - 02:53 AM

Hey Dormilich thanks for your previous suggestions my page is coming along nicely.
However could do with some further advice!

Currently i am submitting values 0/1 via a check box into a table that holds the seat information using a button and code on a following page.

<input type='button' value='Confirm Reservation' onclick='confirmReservation()'/> 
	
			function confirmReservation() {
			
			var selectedList = getSelectedList('Confirm Reservation');
			
			if (selectedList) {
				if (confirm('Do you want to CONFIRM this Reservation :  ' + selectedList + '?')) { 
					document.forms[0].statusA.value=0;
					document.forms[0].statusB.value=1;
					document.forms[0].action='bookingQueries.php';
					document.forms[0].submit();
				} else {
					clearSelection();
				}
			}
		}



I now want to add a text box that will allow the name of the person booking to be entered and sent to a separate "bookings" table. However i would like it to use the same button as the 'confirm reservation' button for the check boxes. Have looked around and cannot work out how to do this.

Any suggestions?
Thanks!
Was This Post Helpful? 0
  • +
  • -

#12 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3569
  • View blog
  • Posts: 10,404
  • Joined: 08-June 10

Re: Seating Reservation System - PHP + MYSQL

Posted 05 March 2013 - 03:20 AM

View Postcarmex, on 05 March 2013 - 10:53 AM, said:

Currently i am submitting values 0/1 via a check box [...]

you might be interested in PHPs Boolean Filter.


View Postcarmex, on 05 March 2013 - 10:53 AM, said:

i would like it to use the same button as the 'confirm reservation' button for the check boxes. Have looked around and cannot work out how to do this.

Im not sure I understand what you want. Do you want to use the same HTML/JS code to use for the text field?
Was This Post Helpful? 1
  • +
  • -

#13 carmex  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 44
  • Joined: 21-February 13

Re: Seating Reservation System - PHP + MYSQL

Posted 05 March 2013 - 03:27 AM

Quote

you might be interested in PHPs Boolean Filter.

Okay ill take a look thanks.

Quote

Im not sure I understand what you want. Do you want to use the same HTML/JS code to use for the text field?

Ermmm I'm not actually sure both perhaps, i would like the button that confirms and sends the check box values to also confirm and send the text box data?
Was This Post Helpful? 0
  • +
  • -

#14 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3569
  • View blog
  • Posts: 10,404
  • Joined: 08-June 10

Re: Seating Reservation System - PHP + MYSQL

Posted 05 March 2013 - 03:30 AM

OK, if both elements are in the same form, the button submits both values (a form submit always sends all concerned data).
Was This Post Helpful? 1
  • +
  • -

#15 carmex  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 44
  • Joined: 21-February 13

Re: Seating Reservation System - PHP + MYSQL

Posted 05 March 2013 - 03:36 AM

Quote

you might be interested in PHP’s Boolean Filter.

Had a look and I'm planning to use 1,2, and 3 eventually for available, reserved, confirmed. But thanks anyway always good to have a read :D/>



View PostDormilich, on 05 March 2013 - 03:30 AM, said:

OK, if both elements are in the same form, the button submits both values (a form submit always sends all concerned data).

So i can just use a normal post form? like :
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
First Name: <input type="text" name="fname">
Last Name: <input type="text" name="age">


and it should work?

Sorry really confused!

This post has been edited by Dormilich: 05 March 2013 - 03:38 AM
Reason for edit:: r

Was This Post Helpful? 0
  • +
  • -

  • (6 Pages)
  • +
  • 1
  • 2
  • 3
  • Last »