PHP SQL get data from 2 tables and output wanted information

Question

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

`select table1.user_id, table2.naam from table1 inner join table2 on table1.table2_id = table2.id` You join the two tables with the id information they both have in common and are related on — Julio Soares, Oct 15 '15 at 17:20
I would suggest that you learn about prepared statements. Otherwise you open up the doors to havoc being rained down on your database/application. You can learn about it here on SO: http://stackoverflow.com/a/60496/652519 — Michael, Oct 15 '15 at 17:25

score 1 · Answer 1 · answered Oct 15 '15 at 17:21

1

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

answered Oct 15 '15 at 17:21

odannyc

717
9
25

Jay Blanchard · Accepted Answer · 2015-10-15T17:23:10.583

1

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

edited Oct 15 '15 at 17:23

answered Oct 15 '15 at 17:21

Jay Blanchard

34,243
16
77
119

1

I guess `ON table1.Table2_id = table2.id` – Alex Oct 15 '15 at 17:22

score 1 · Answer 3 · answered Oct 15 '15 at 17:27

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

score 0 · Answer 4 · answered Oct 15 '15 at 17:20

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

score 0 · Answer 5 · answered Oct 15 '15 at 17:21

0

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

answered Oct 15 '15 at 17:21

Akshay

2,244
3
15
34

PHP SQL get data from 2 tables and output wanted information

5 Answers5