PDA

View Full Version : stop user seeing data they already have in their database.


jaymo
03-22-2009, 07:32 PM
Hi Guys,

I have three database tables. One with flights available, one with user data and one with bookings which is relational so it holds users booked flights.

I am displaying all the flights to a html table using the flights database. This is fine and it adds a new row matching the database. However, I dont want them to see flights they are already booked on, so they cant re-book it etc. So these rows should not show up, but the others should.

My queries are:

$query = sprintf("SELECT * FROM flights WHERE spaces >0");
$rows = @mysql_fetch_array($result);

$queryflights = sprintf("SELECT flightID FROM bookings WHERE userID = '$userID' ");
$booking = @mysql_fetch_array($resultFlight);
Then my html table goes:

<?php do { ?>
<tr>
<td><?php echo $rows['flight_id']; ?></td>
<td><?php echo $rows['destination']; ?></td>
Etc....
<td><input name="chkSelect" type="checkbox" id="chkSelect" value="<?php echo $rows['flight_id']; ?>" /></td>
<?php } while($rows = mysql_fetch_array($result)) ?>
This displays all the rows from the flights table that have spaces left.
My question is this: Should I combine the queries somehow to achieve this result or throw an IF statement in the do while loop? I tried the IF statement using the second query but the best I can get is it checks if the flightID is in the bookings table and if it is, it displays no rows. Really hope someone has an idea because ive been looking at it for too long and i think im losing it :(

Cheers all

davidj
03-22-2009, 09:31 PM
if your wanting to see available booking and not the ones booked cant you just do...

"SELECT flightID FROM bookings WHERE userID != '$userID' "

jaymo
03-22-2009, 10:24 PM
Cheers David. The bookings table looks like this:

bookingID | userID | flightID
1 1 2

This says that userID 1 has booked a flight with an ID of 2. These are inherited from the user and flights tables. I dont think that query would work as the rest of the data in the bookings table is not the other flights available.

This got me thinking something like this...

"SELECT flights.flight_id
FROM flights
LEFT OUTER JOIN
bookings ON users.user_id = bookings.userID
LEFT OUTER JOIN
bookings ON flights.flight_id = bookings.flight_id
WHERE flights.flight_id != bookings.flightID"

This gives a mysql error but am I on the right track with this? Not really sure when it comes to JOINS. Thanks

davidj
03-22-2009, 10:33 PM
if your using a recent version of MYSQL then you can create a view

the view is a virtual table which holds the joins in the database

you dont have to use a WHERE clause in a view as its just a link between your tables

you name your view as you would a table and reference it in your PHP like wise

if you send me a image of your db (tables included) ill write the view so you can add this on your server

you need to export your db to a file and post it here. You may have to zip it up or just use a txt extension

jaymo
03-22-2009, 10:52 PM
Thats a nice off David thanks. Thats the first I have heard of using views. I'll have a bash at it see if I can get it working. If not i may just take you up on your offer :) Take care

jaymo
03-23-2009, 06:43 PM
Well after a lot of trying and failing I managed to use a left join to query the flights table and join the bookings table to it. I then used an if statement to see if the id from flights was present in bookings. If it wasn't THEN draw the rows with php. Works well. Dont think there are any design problems with this, not that I can spot anyway