How to retrieve records from mysql from different

Question

I am making a booking system using php and mysql database. The system has multiple users who can log in and make bookings so i want to know a way of selecting records based on users sessions. At the moment its only selecting all booking records which means users have access to all bookings.

I have a booking table which has booking records and a users table with users credentials.Is there also a way for selecting records from both tables

Answer 1

You need to create a unique ID for each user in your database. Use a Primary Key and set it to Auto Increment.

After checking for valid credentials on login, grab their ID from the database and store it in a session variable.

Create your bookings table with a user ID column. Assign a user ID to a booking. When you want to query their bookings, include their user ID in the query.

Answer 2

On your users table, you should have a unique id.

On your bookings table, you have a field user id. When you add a booking, you save the user id in to that field.

Then, when you want to retrieve bookings, you select records matching the specific user id.

Having a field for user id on the bookings table is called a foreign key. It's the basis for a lot of what you do with sql. Setting it up this way allows you to do what's called a join. This is where you get all the data from two or more tables, with the rows combined based on their key values. So in your case you could do something like this:

SELECT * FROM users,bookings WHERE bookings.id=users.id;

This will give you back records that have all the booking data, and all the user data for the user who made that booking. You could also do:

SELECT * FROM bookings where users.id=3;

Which would give you all the booking data for the user with id 3.

You can also match on other fields, but keep that foreign key as the join to guarantee that you always get the correct link between bookings and users eg.

SELECT * FROM users,bookings WHERE users.surname="Doe" AND bookings.id=users.id;

You can use it in reverse too - for example, if you want to get the user details for everyone who has a booking on a given date, you could do something like this:

SELECT * FROM users WHERE bookings.date='03/03/2015' AND users.id=bookings.id;

As you can see, adding that user id to the bookings table opens up a lot of possibilities because it links your data together, allowing you to get data as a single record even if it's split over multiple tables.