misomrk1 misomrk1 - 10 months ago 48
MySQL Question

How can I use a variable from one table as a SELECT parameter for another table in php

So I have a user who has registered themselves with an email address.
This is kept in a users table.
Now this user can book a class using a calendar booking system (where they have to manually enter the email address aagain) and this data is stored in a bookings table.
Now the question is, I would like to show all the bookings the specific user has made, Selecting by their email address (can

echo $userRow['email'];

which works fine and shows the right email address for the logged in user.
Now how can I select the bookings based on this email address? I have tried something like this:

$relevantEmail = $userRow['email'];

$get_booking = mysql_query("SELECT date, start
FROM bookings
JOIN users
ON bookings.email = '$relevantEmail' " );
while($row = mysql_fetch_row($get_booking))
echo $row[0] . " " . $row[1] ;
echo "\r\n";

But this somehow outputs really weird result - takes out only 3 classes booked and assigns them to 3 users - each user getting each class

something of the likes of:

joe - class 1
prick - class 1
josh - class 1
joe - class 2
prick - class 2
josh - class 2
joe - class 3
prick - class 3
josh - class 3

please help

Answer Source

Since you're not using Users for anything, drop the join, and just use WHERE:

$get_booking = mysql_query("SELECT date, start
    FROM bookings
    WHERE bookings.email = '$relevantEmail' " );

Please note that mysql_* functions are removed in PHP7 and deprecated in previous versions. Switch to PDO or mysqli, and take advantage of prepared statements and parameter binding. This will prevent you from being open to SQL injection.