Mysql query returns wrong rows

Question

I have a simple mysql database where user has its own events table and invited events table.

User-->>Own events (user_id is foreign key )
User and Own events-->>Invited Events(user_id is foreign key, event id is foreign key)

I want to get OWN EVENT details from Invited Events where user_id is equal to some number

Example:

enter image description here

I try:

$user_id = $_POST["user_id"];
$stmt = $mysqli->query("SELECT A1.event_id,A1.root_folder,A1.event_name,A1.date,A1.location,A2.user_id FROM OWN_EVENTS A1, INVITED_EVENTS A2 WHERE A2.user_id=$user_id");

$rows = array();
while($r = mysqli_fetch_assoc($stmt)) {
    $rows[]=$r;
}

This supposed give me (one row) event details of event_id=3 where user_id=58 but this returns all the rows in the OWN_EVENTS table.

What am I doing wrong ?

John Woo · Accepted Answer · 2013-03-08 15:32:15Z

3

Currently, you query returns cartesian product.

You need to tell the optimizer how the two tables are connected with each other.

SELECT  A1.event_id,A1.root_folder,A1.event_name,A1.date,A1.location,A2.user_id 
FROM    OWN_EVENTS A1 
        INNER JOIN INVITED_EVENTS A2 
           ON A1.columnName = A2.columnsName  // columns that defines their relationship
WHERE   A2.user_id=$user_id

To further gain more knowledge about joins, kindly visit the link below:

Visual Representation of SQL Joins

edited Mar 8, 2013 at 15:32

answered Mar 8, 2013 at 15:26

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mord Fustang Over a year ago

thanks for quick answer that works fine, I guess I need to learn more about joins in sql.

Collectives™ on Stack Overflow

Mysql query returns wrong rows

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related