MySQL subquery error: Subquery returns more than 1 row

Question

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?

Could you show us the booking_dates and events structure, or atleast their associations (if any). You have to solve with with an INNER JOIN: http://dev.mysql.com/doc/refman/5.7/en/join.html — Mees Kluivers, Jan 13 '17 at 21:07
**WARNING**: When using `mysqli` you should be using [parameterized queries](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php) to 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](http://bobby-tables.com/). **NEVER** put `$_POST` or `$_GET` data directly into a query, it can be very harmful if someone seeks to exploit your mistake. — tadman, Jan 13 '17 at 21:18

score 2 · Accepted Answer · answered Jan 13 '17 at 21:07

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.

score 1 · Answer 2 · answered Jan 13 '17 at 21:15

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'

score 0 · Answer 3 · answered Jan 13 '17 at 21:08

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);

http://www.w3schools.com/sql/sql_in.asp

score 0 · Answer 4 · answered Jan 13 '17 at 21:08

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);

score 0 · Answer 5 · edited May 23 '17 at 10:29

"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

MySQL subquery error: Subquery returns more than 1 row

5 Answers5