CODEWITHSUNDEEP
phpmysql
Wouter
Wouter

Reputation: 137

PHP SQL get data from 2 tables and output wanted information

i am trying to achieve the following result. (i am sorry for the horrible explanation but i find it very hard to explain :P)

I need data from 2 tables.

Table1 id, table2_id, user_id

Table2 id, Name

Table1 example information
ID 1   Table2_id 1 user_id 3
ID 2   Table2_id 2 user_id 3
ID 3   Table2_id 5 user_id 3

Table2 Example Information
ID 1   Name TEST
ID 2   Name Hello
ID 3   Name Helpme
ID 4   Name Please
ID 5   Name IamLost

i Would like to output everything tied user_id 3. This would be my ideal end result

TEST Hello IamLost

I have this as code

$id = "3";

    $sth = $db->prepare("SELECT table2_id, user_id, naam FROM table1, table2 where user_id = $id  ");
$sth->execute();

$result = $sth->fetchAll();



foreach( $result as $row ) {

    echo $row['table2_id']  . ' ';
     echo $row['naam'] . '<br>';
}

But this just outputs everything but then twice. like this

TEST
TEST
Hello
Hello
Helpme
Helpme
Please
Please
IamLost
IamLost

Upvotes: 0

Views: 67

Answers (5)

Nuriddin Rashidov
Nuriddin Rashidov

Reputation: 966

these two table must be related to each other. When you select , returned rows should be equal this two tables. This why we use table joins e.g

SELECT a.user_id,a.table_id,b.name FROM table1 as a, table2 as b
RIGHT OUTER JOIN table 1
ON b.ID = a.table2_id
AND table1.user_id = 3

Upvotes: 1

Jay Blanchard
Jay Blanchard

Reputation: 34426

A LEFT JOIN should do the trick:

SELECT `table1`.`table2_id`, `table1`.`user_id`, `table2`.`name` 
FROM `table1`
LEFT JOIN `table2` 
ON `table1`.`Table2_id` = `table2`.`id`
WHERE `table1`.`id` = $id

MySQL JOIN Syntax

Upvotes: 1

Akshay
Akshay

Reputation: 2229

A simple right outer join will help you here.

SELECT * FROM table2
RIGHT OUTER JOIN table 1
ON table2.ID = table1.table2_id
AND table1.user_id = 3

Upvotes: 0

odannyc
odannyc

Reputation: 718

Use Joins in SQL.

The SQL Query should look like this:

SELECT T1.USER_ID, T2.NAME
FROM TABLE1 AS T1
JOIN TABLE2 AS T2
ON T1.TABLE2_ID = T2.ID
WHERE T1.USER_ID = 3

Upvotes: 1

Alex
Alex

Reputation: 17289

I believe you just need to be more precise:

 $sth = $db->prepare("SELECT table2_id, user_id, name 
                 FROM table1
                 LEFT JOIN table2 
                 ON table1.Table2_id = table2.id
                 WHERE user_id = $id  ");

Upvotes: 0

Related Questions