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.
我在mySQL的数据库中有两个表。一个存储有关企业的信息,另一个存储唯一用户ID以及用户标记为收藏夹的第一个表中的ID。
Table 1
<pre>
ID | NAME | EMAIL | PHONE |
1 | Joe | a@mail.com | 25634565 |
2 | John | b@mail.com | 43634565 |
3 | Jack | c@mail.com | 65634565 |
4 | James| d@mail.com | 43634565 |
5 | Julie| e@mail.com | 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 | a@mail.com | 25634565 |
Jack | c@mail.com | 65634565 |
Julie| e@mail.com | 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.
我已经查看了JOIN LEFT和minus查询调用,但我无法使其工作。我对mySQL和PHP有基本的了解,但不是很多。
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.
PS。如果有更好的方法来构建我的数据库,我很想知道。
3 个解决方案
#1
2
I'd use a table with two fields - userID
and fav
- make one entry for each entry. And then...
我使用一个包含两个字段的表 - userID和fav - 为每个条目创建一个条目。接着...
SELECT table1.name, table1.email, table1.phone FROM table1,table2 WHERE table2.fav = table1.id AND table2.userid = 2565325489
SELECT table1.name,table1.email,table1.phone FROM table1,table2 WHERE table2.fav = table1.id AND table2.userid = 2565325489
#2
0
Select * from table1 InnerJoin (Select * from table2) as t4 on table1.ID=t4.FAV1
#3
0
$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'];
}
#1
2
I'd use a table with two fields - userID
and fav
- make one entry for each entry. And then...
我使用一个包含两个字段的表 - userID和fav - 为每个条目创建一个条目。接着...
SELECT table1.name, table1.email, table1.phone FROM table1,table2 WHERE table2.fav = table1.id AND table2.userid = 2565325489
SELECT table1.name,table1.email,table1.phone FROM table1,table2 WHERE table2.fav = table1.id AND table2.userid = 2565325489
#2
0
Select * from table1 InnerJoin (Select * from table2) as t4 on table1.ID=t4.FAV1
#3
0
$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'];
}