im trying to select all data from my "events" table whenever the "event id" matches with the "user id". However i get an error 1242 Subquery returns more than 1 row.
$query = "SELECT * FROM events WHERE id = (SELECT event_id FROM booking_dates WHERE user_id = '{$user_id}')";
$event_set = mysqli_query($connection, $query);
I understand that my subquery will return multiple rows because a user can attend multiple events. So how can I make my query accept multiple rows?
You could use in:
SELECT * FROM events
WHERE id IN (SELECT event_id FROM booking_dates WHERE user_id = '{$user_id}')
Warning: injecting strings like that in your SQL makes them vulnerable to SQL injection. Please look into prepared statements to which you can bind arguments without this vulnerability.
Others have shown how to correct your syntax. However, MySQL generally performs better if you use JOIN rather than WHERE column IN (subquery).
SELECT DISTINCT e.*
FROM events AS e
JOIN booking_dates AS b ON e.id = b.event_id
WHERE b.user_id = '$user_id'
You query should be using IN operator with where.
$query = "SELECT * FROM events WHERE id IN (SELECT event_id FROM booking_dates WHERE user_id = '{$user_id}')";
$event_set = mysqli_query($connection, $query);
I'm not a SQL expert and as such my syntax may be a little off. However, I believe what you're looking for is an IN clause:
$query = "SELECT * FROM events WHERE id IN (SELECT event_id FROM booking_dates WHERE user_id = '{$user_id}')";
$event_set = mysqli_query($connection, $query);
"So how can I make my query accept multiple rows?"
USE a Select that has a right join to the booking_dates table. The reason for the RIGHT JOIN is that you want to only display the records in the events table when there is 1 or more valid booking_dates records for the eventid and given user. I believe to achieve multiple rows and only display the data the code will end up looking like this:
$query = "
SELECT
*
FROM
events e
RIGHT JOIN
booking_dates bd
ON e.id=bd.event_id
WHERE
bd.user_id = '{$user_id}'";
$event_set = mysqli_query($connection, $query);
As recommended above it also best practice to make sure your SQL queries are "cleansed" using parameters and binding. This stack overflow link provides the answer. parameters in MySQLi
mysqliyou should be using parameterized queries andbind_paramto add user data to your query. DO NOT use string interpolation or concatenation to accomplish this because you have created a severe SQL injection bug. NEVER put$_POSTor$_GETdata directly into a query, it can be very harmful if someone seeks to exploit your mistake.