Select and display records from 2 different tables

Question

I am making a booking system using php and mysql. The system will involve retrieving booking records of different users from mysql. I am currently having difficulties in selecting bookings made by different users. I want to select records based on user logged in. I have used the code below where it should select records from table bookings as well as the user session. But, this code does not display any bookings.

$sqlquery = "SELECT * FROM bookings,users where users.id=  ".$_SESSION['loggedInUser']."";

$results = $mysqli->query($sqlquery);

I have been facing this issue for a while and i really want to select bookings made by users login.

You should use LEFT JOIN between those 2 tables: http://www.tizag.com/mysqlTutorial/mysqlleftjoin.php — valicu2000, Feb 08 '15 at 18:25
would you be able to give me an example please and how i could use it — r93, Feb 08 '15 at 18:27
Does the logged in user have a single booking, or can a user have more than one? — junkystu, Feb 08 '15 at 18:30
A join will only return a single row in most cases, so I'm guessing you are looking to get all the bookings rather than the latest one? — junkystu, Feb 08 '15 at 18:31
You need something like: SELECT u.`username`, b.`booking` FROM `users` u LEFT JOIN `bookings` b ON u.`user_id`=b.`user_id` WHERE u.username='.$_SESSION['loggedInUser'].' ... I don't know exactly what are the names of your fields — valicu2000, Feb 08 '15 at 18:32
i have a bookings table which has an id,bookingdate,time and a users table which has id,username,password, i just need a way of being able to select bookings made by a user — r93, Feb 08 '15 at 18:35
In you case, where you can have more than one booking per user, I suggest using a pivot table that only has an index, a user_id and a booking_id. This table will store the relation between your user and his bookings. — junkystu, Feb 08 '15 at 18:38
echo $sqlquery; And you need some way of relating users to bookings — Strawberry, Feb 08 '15 at 23:35

score 0 · Answer 1 · edited May 23 '17 at 12:13

You need a pivot table to relate users to bookings:

CREATE TABLE user_bookings (id int PRIMARY KEY AUTO_INCREMENT, booking_id int, user_id int);

I usually end up using two queries as it allows for better structuring of data. Depending on the size of your website/application, you can use a Object Relation Mapper as it can handle the data mapping internally for you.

If you only need the bookings you can simply select from the pivot table and join bookings like this

$bookingsql = "SELECT * FROM user_bookings JOIN bookings on bookings.id =  user_bookings.booking_id where user_bookings.user_id =" . $_SESSION['loggedInUser'];

If you need the user details too, you can select the user first and then run the $bookingsql query to get the booking details.

$usersql = "SELECT * FROM users where id= " . $_SESSION['loggedInUser'];

And then, depending on the framework/database driver you're using, transform the $usersql query result into an array, called $user and assign the bookings to it with the result from $bookingsql query.

This can also be achieved with subqueries, see this question as an example but think of products as your user and categories as your bookings.

i have tried this way. $sqlquery = "SELECT * FROM bookings,users where users.username= " . $_SESSION['loggedInUser'].""; but i cannot still retrieve any records — r93, Feb 08 '15 at 19:11
Sorry, I've updated the query. The idea is that you get your user with `$usersql` and then get the bookings for that user with `$bookingsql`. For that to work, you of course need to store the user ID in `$_SESSION['loggedInUser']`, in case you were storing the username or something else. — junkystu, Feb 08 '15 at 22:56

JC Sama · Answer 2 · 2015-02-08T23:36:34.627

-1

To select bookings made by logged user : You should try a INNER JOIN :

$sqlquery = "SELECT b.* FROM bookings b
 INNER JOIN users u ON u.id_user = b.user_id
 WHERE u.id_user= ".$_SESSION['loggedInUser'];

edited Feb 08 '15 at 23:36

answered Feb 08 '15 at 18:31

JC Sama

2,214
1
13
13

Your WHERE condition negates your JOIN choice. – Strawberry Feb 08 '15 at 18:55
Are you sure about that, or just saying, you can write down some answer first. – JC Sama Feb 08 '15 at 21:31
I'm quite sure. `EXPLAIN EXTENDED...` followed by `SHOW WARNINGS;` will prove the point – Strawberry Feb 08 '15 at 22:57
I'm still waiting for you to post the correct answer for this topic so we can move on. – JC Sama Feb 08 '15 at 23:23
Correct answer to what? – Strawberry Feb 08 '15 at 23:28
im still having problems with selecting bookings made by specific users – r93 Feb 08 '15 at 23:31
@Rohan, do you have a user_id column in your bookings table ? – JC Sama Feb 08 '15 at 23:37
I have an id column in both users and bookings table – r93 Feb 08 '15 at 23:39
when you run this query in your phpAdmin what do you get as a result : `SELECT b.* FROM bookings b INNER JOIN users u ON u.id_user = b.user_id WHERE u.id_user= #id` => #id is the logged user ID – JC Sama Feb 08 '15 at 23:42
does not show any records, the user is logged in with a username which is stored in a session. I just want to display bookings for a specific user that has logged in. – r93 Feb 09 '15 at 00:10
I have : $username = $_SESSION['loggedin']; $sqlquery = "SELECT b.* FROM bookings b INNER JOIN users u ON u.id = b.id WHERE u.username= '".$_SESSION['loggedin']."'" ; – r93 Feb 09 '15 at 00:17
is there a way of assigning session to id rather than username since that is most likely the reason to why its not working. – r93 Feb 09 '15 at 00:19
use `u.id` = $_SESSION['loggedin'] instead of `u.username` – JC Sama Feb 09 '15 at 00:38

Select and display records from 2 different tables

2 Answers2