big select problem please help

simonfroude

hi.

im quite new to php so if this is basic then im sorry. I have been given the task of building a booking system for a fictional theme park. i have a separate table for each ride and they are already populated as follows...

time row_number a b c d
1000 1 0 0 0 0
1000 2 0 0 0 0
1000 3 0 0 0 0
1000 4 0 0 0 0
1000 5 0 0 0 0
1030 1 0 0 0 0
1030 2 0 0 0 0
...etc, for the times the ride is run.

A booking form asks the customer what time they want, how many seats, and if they want all the seats on the same row or not.

thats the background, now for the bit im stuck with 🙂 ...

firstly i need to find the seats to be booked i started but have soon become stuck. here is how far i got...

if ($samerow == 1) { they want all the seat on the same row }

else {
for ($i=0; $i<$seatsreq; $i++){
select the row number and seat letter for those that are available, ie are set to null
}
}

so basically it looks to see if they want all the seats on the same row (if the do ill get that working later).

so i dont want to actually make the booking, just search my table and find the first seats, looping for the number of seats they want. and prevent any double booking or overbooking.

sorry if ive made no sense but can anyone help??

thanks

lth2h

I don't really understand where your problem is. What is wrong? What kind of error message do you get? What did you do to fix the error and what were the results? I'm just confused. Sorry🙁

simonfroude

i think ive been going at it wrong. ill try a new approach and post again if i have any more probs

so expect to hear from me 😃

Roger_Ramjet

It was your table structure threw me for ages. Could not understand why I could not get it. Ended up creating a table in Access to query, that's when I got it.

Your table structure should be changed as follows:
Instead of each row on the ride getting it's own record, each Seat should get it's own record.

time row seat booked name
10:00 1 a yes smith
10:00 1 b yes smith
10:00 1 c yes jones
10:00 1 d no

10:00 2 a yes brown

etc

This would in any case be what you need so that you can record who booked each seat. But it also makes the query you need really quite easy.

$query1 = "SELECT time, row, Count(seat) AS Count, booked FROM bookings 
				GROUP BY time, row, booked 
				HAVING (((time)='$time') AND ((Count(seat))>=$seat) AND ((booked)=0)) 
				ORDER BY time, row 
				LIMIT 1";
	//returns the first row with enough seats on the ride specified by time
	//you can use it as you wish
	$result = mysql_query($query1);
	$record = mysql_fetch_array($result);
	$query2 = "SELECT * FROM bookings WHERE time='$time' AND row=$record['row'] LIMIT $seats"
	//returns the seats you need

simonfroude

hi. thanks for your reply. it has really help me get the problem sorted in my head.

ive changed my tables as you suggested and then implemented your code but am getting an error and really cant find out why.

the error message i get is : Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource

and its in the $record = mysql_fetch_array($result); line

ive spend the last couple of hours going through every line to find the problem but cant work it out.

do you have any suggestions?

thanks again.

simonfroude

ive solved the problem 🙂 it must have been the version of php, my variables needed to be '".variable."'

Roger_Ramjet

Looking over my code I see one flaw: The seats returned may not be next to each other. I'd take out the LIMIT and return all rows with enough seats. Let whomever is doing the input choose which seats make most sense. Hopefullly, that will be someone with an orderly mind who fills the rows from left to right and top to bottom.

simonfroude

ive got the code working fine roger but have found one problem

what if they want to book more than 4 seats?? so it goes over more than 1 row

thanks

simon

Roger_Ramjet

$seat = $_POST['seat'];
$time = $POST['time'];
$samerow = $_POST['samerow'];
$rowcount =1;

while ($seat>4) {
	$seat = $seat-4;
	$rowcount = $rowcount+1;
	}
//will give the number of rows required when more than 4 seats
//you need to trap for 0 remainder before running next query

$query1 = "SELECT time, row, Count(seat) AS Count, booked FROM bookings 
                GROUP BY time, row, booked 
                HAVING (((time)='$time') AND ((Count(seat))>=$seat) AND ((booked)=0))
                ORDER BY time, row 
                LIMIT 1";
    //returns the first row with enough seats on the ride specified by time
    //you can use it as you wish
    $result = mysql_query($query1);
    $record = mysql_fetch_array($result);
    $query2 = "SELECT * FROM bookings WHERE time='$time' AND row=$record['row'] LIMIT $seats"
    //returns the seats you need

$query2 = "SELECT time, row, Count(seat) AS Count, booked FROM bookings 
                GROUP BY time, row, booked 
                HAVING (((time)='$time') AND ((Count(seat))=4) AND ((booked)=0))
                ORDER BY time, row
                LIMIT $rowcount";
    //returns a list of the required empty rows

Elliott

i am also working with simon on this project and am equally as stuck.

Using your first code you posted, it worked a treat.

ie, if i had 1 person booked on row 1, and wanted to book 4, it would return row 2, seats a, b, c and d. Which it should.

But....

If i wanted to book 2, or 3 more, ontop of the 1 person already booked in, it would return row 1, seats a, b, c, etc.

It doesnt seem to be missing out seat a for some reason.

This is what i have:

<?php
$seat = $_POST['seat'];
$time = $_POST['time'];

$query1 = "SELECT time, row_number, Count(seat) AS Count, booked FROM rollercoaster  

                GROUP BY time, row_number, booked  

                HAVING (((time)='".$time."') AND ((Count(seat))>='".$seat."') AND ((booked)=0)) 
                ORDER BY time, row_number  

                LIMIT 1"; 

$result = mysql_query($query1); 
    $record = mysql_fetch_array($result); 
	$num_rows = mysql_num_rows($result);

$query2 = "SELECT * FROM rollercoaster WHERE time='".$time."' AND row_number='".$record['row_number']."' LIMIT $seat" ;
	$result2 = mysql_query($query2);

 echo $record['row_number'];

while ($record2 = mysql_fetch_array($result2))
				{
				$seat = $record2['seat'];

?>

Any ideas why its doing this?

Roger_Ramjet

Originally posted by Elliott

<?php
$seat = $_POST['seat'];
$time = $_POST['time'];

$query1 = "SELECT time, row_number, Count(seat) AS Count, booked FROM rollercoaster  

                GROUP BY time, row_number, booked  

                HAVING (((time)='".$time."') AND ((Count(seat))>='".$seat."') AND ((booked)=0)) 
                ORDER BY time, row_number  

                LIMIT 1"; 

$result = mysql_query($query1); 
    $record = mysql_fetch_array($result); 
	$num_rows = mysql_num_rows($result);

$query2 = "SELECT * FROM rollercoaster WHERE time='".$time."' AND row_number='".$record['row_number']."' AND booked=0 LIMIT $seat" ;
	$result2 = mysql_query($query2);

 echo $record['row_number'];

while ($record2 = mysql_fetch_array($result2))
				{
				$seat = $record2['seat'];

?>

[/B]

You forgot to ask for only empty seats in query 2

I take it you both understand that booked is a boolean filed, 0 = false/no, 1 = true/yes. In MySQL you have to use tinyint and code the 1 or 0 as required.

Elliott

Thanks Roger.

Dont ya just hate it, 2 hours of messing about with stuff, and its somthing really simple!

Damn my novice php skills 🙂

Now to try the booking for over 4 pple 🙂

Elliott

Hi again Roger.

I understand the bottom query to show how many empty rows there are needed, but it dont seem to be working.

Ive managed to so far display the row, and seat numbers, using a while loop as u knw.

The main stumbling block is making it put a booking over 2 rows.

EG> if 1 seat is already booked, then another person books 4 or 5 seats, then it wont use the remaining ones on row 1 and then fill in the rest on row2.

Thanks again for your time.

Elliott

Roger_Ramjet

What it does is to return a list of rows where all 4 seats are empty. So if you want 6 seats it will return the first row number with 4 empty seats and if you want 9 seats it will return 2 row numbers. All returned row numbers will have 4 empty seats.

Now. I'm not going to do any more of your project for you. I can see obvious holes that need plugging (like what if the first row returned by query 1 has 4 empty seats, it will also be in the full row results). You need to work them out for yourselves, or put my name at the top of the source code you hand in.

I can see a very slick change to my code that will generalise the solution and I will give you that: everything else you must work out for yourselves

//replace
while ($seat>4) {
    $seat = $seat-4;
    $rowcount = $rowcount+1;
    }

//with
$rowcount = $rowcount + (($seat-1)/4);
//8 seats = (8-1=7); 7 / 4 = 1; 1 + 1 = 2 rows
//5 seats = (5-1=4); 4 / 4 = 1; 1 + 1 = 2 rows
//3 seats = (3-1=3); 1 / 4 = 0; 1 + 0 = 1 rows
$seat = mod($seat/4);
//= remainder

//give you the no of rows absolutely required
//and the no of seats in any partial row


//work out the most efficient while/if/for loop 
//to construct a UNION query using the above results ie $rowcount & $seat
//that will list all the seats you need in 1 query
// IT CAN BE DONE

Elliott

heh, ok, thanks for all your help so far Roger, it has been very much appreciated.

Truth be known, yours is the only solution that has worked at all. I suppose its all in the way you approach the problem.

The tutors we have probably know less than us, and have offered no constructive help or tuition in this subject.

In E Commerce lectures we get told about the current business situations of various internet companies etc, then they throw a programming assignment at us.

If you get chance to check this form between now and hand in on friday, we'd be greatful if you could offer any more professional tips , if we need them, even in the form of your last post. Its all been a tremendous help

Thanks again,
Elliott

PS. can u believe this is only worth 25% of the module marks!!!!
http://www.passecdl.co.uk/ecommerce/

tekky

You could do this with ONE column (db) PER row (seats) using a SET

SET('a', 'b', 'c', 'd')

uses binary bits,

0 = all empty
1 = a seated
2 = b seated
4 = c seated
8 = d seated

15 = all seated

1 query would tell you about EACH row then, and simple PHP could easily tell you if that row had X number of seats open 😉

Roger_Ramjet

A bit mask in fact. Yes, thought of that last night when I got home: it's the way I would have done it back coding 370 Assembler.

I think this was more a table design and smart query exercise though.

The really smart solution though is to just query for all empty seats on the chosen ride and bung them into an array. 1 query does all, including check to see if there are enough seats in the first palce.

The table design part comes when I normalise the data. It is the seat that pans out as the entity, not the row or the ride.

tekky

I would use the bit mask, looks cleaner and simpler to me (for this purpose anyways)

It seems most people overlook their usefulness these days

Elliott

😕

No idea what bit masks are 😃

Gonna stay with the query Roger posted, and try make that work. Spent 2 days on that so far, only got 1 more day left, so cant really change anything that major now.

Thanks for feedback tho guys

Elliott

Hello again.

wonder if u can see what im doing wrong here.....

im booking some seats. but instead of booking the remainder of row 1, its booking seat a, on each row, until its booked the amount required.

eg:

expected outcome:
1b
1c
1d
2a

actual outcome:
1b
2a
3a
4a

<?php
$query3 = "SELECT time, row_number, seat, booked FROM rollercoaster
			   GROUP BY time, row_number, booked
			   HAVING (((time)='".$time."') AND ((booked)=0))
			   ORDER BY time, row_number
			   LIMIT $seat";
	$result3 = mysql_query($query3);



	while ($record3 = mysql_fetch_array($result3))
	{
			?>
    <tr> 
      <td><?php echo $record3['seat']; ?></td>
      <td><?php echo $record3['row_number']; ?></td>
      <td colspan="2" align="left">&nbsp;</td>
    </tr>
	<?php
	}
	?>

im on the right road, as its doing what i want it to and keeping it simple. but i think its the use of my ORDER and GROUP thats making it put things in the wrong rows.