CODEWITHSUNDEEP
phpmysqldatabase
troelsk
troelsk

Reputation: 35

mySQL: Showing data from one table using id from another

I want my users to be able to make a favourite list.

I have two tables in a database in mySQL. One stores information about businesses and the other stores the unique user ids as well as the ids from the first table that the user has marked a favourite.

Table 1

<pre>
ID   | NAME | EMAIL      | PHONE    |
1    | Joe  | [email protected] | 25634565 |
2    | John | [email protected] | 43634565 |
3    | Jack | [email protected] | 65634565 |
4    | James| [email protected] | 43634565 |
5    | Julie| [email protected] | 65634565 |
...
</pre>
Table 2 
<pre>
  USERID   |  FAV1  |  FAV2  |  FAV3  | FAV...
2565325489 | 1      | 3      | 5      |
8596854785 | 3      | 2      | NULL   |
2356256263 | 5      | NULL   | NULL   |
...
</pre>
The output I want for a user (in this example the first in table2): 
<pre>
Joe  | [email protected] | 25634565 |
Jack | [email protected] | 65634565 |
Julie| [email protected] | 65634565 |
</pre>

I have looked into JOIN LEFT and minus query calls, but I just can't make it work. I have a basic understanding of mySQL and PHP, but not a lot.

I would highly appreciate any help with what approach to take.

Ps. If there are better ways to structures my databases, I would love to know.

Upvotes: 2

Views: 2975

Answers (3)

Naveen Kumar Yadav
Naveen Kumar Yadav

Reputation: 432

$result = mysqli_query('SELECT name,email,phone FROM t1 table1 LEFT JOIN table2 t2 ON t1.ID = t2.fav1');  

//iterate the results
while ($row = mysqli_fetch_array($result))
{
    echo $row['name']." ".$row['email']." "$row['phone'];
}

Upvotes: 0

Sindhara
Sindhara

Reputation: 1473

I'd use a table with two fields - userID and fav - make one entry for each entry. And then...

SELECT table1.name, table1.email, table1.phone FROM table1,table2 WHERE table2.fav = table1.id AND table2.userid = 2565325489

Upvotes: 2

user1432124
user1432124

Reputation: 

Select * from table1 InnerJoin (Select * from table2) as t4 on table1.ID=t4.FAV1

Upvotes: 0

Related Questions