MySQL,PHP多对多关系

I've got a many-to-many relationship between Person and Interests. This means that I had to create a middle table called person_interests.

我在人和兴趣之间有多对多的关系。这意味着我必须创建一个名为person_interests的中间表。

The Person table was created using:

Person表的创建使用:

create table if not exists Person
(
  id int not null auto_increment primary key,
  username varchar(10) not null,
  name varchar(40) not null,
  gender varchar(6) not null,
  dateOfBirth timestamp not null,
  signUpDate timestamp not null,
  email varchar(40) not null
);

The interests table was created using:

兴趣表是用以下方法建立的:

create table if not exists interests
(
    id int not null auto_increment primary key,
    interestName varchar(10) not null
);

insert into interests(id, interestName)
values
(1, 'sport'),
(2, 'computer'),
(3, 'dancing'),
(4, 'boating'),
(5, 'car');

and person_interests was created using:

个人兴趣是通过:

create table if not exists person_interests
(
    id int not null auto_increment primary key,
    UserID int not null,
    foreign key (UserID) references Person(id),
    Interestid int not null,
    foreign key (Interestid) references interests(id)
);

I'm trying to select entries from the person_interests table but with no luck. My PHP function for doing this is:

我正在尝试从person_interests表中选择条目，但是运气不好。为此，我的PHP函数是:

function get_person_interests($id)
{
    $connection = mysql_open();
    $query = "select pi.UserID, i.interestName from interests as i, person_interests as pi, where i.id = pi.interestid";

    $result = mysql_query($query, $connection) or show_error();

    $person_interests = array();

    while($person_interest = mysql_fetch_array($result))
    {
        $person_interests = $person_interest;
    }

    mysql_close($connection) or show_error();
    return $person_interests;
}

But this doesn't work! This is what my template (user_detail.tpl) looks like:

但这并不工作!这就是我的模板(user_detail.tpl)的样子:

{foreach $interests as $interest}
    <li>{$interest.interestName}</li>
{/foreach}

and this is what my user_detail.php looks like:

这就是user_detail。php的样子:

$id = $_GET['id'];

$Person = get_person($id);

$interests = get_person_interests($id);

$smarty = new Smarty;
$smarty->assign("Person", $Person);
$smarty->assign("interests", $interests);
$smarty->display("user_detail.tpl")

I'm trying to display the interests that the user chose upon signup. They are stored in the person_interests table. Every time I try to go to the user_detail.php page it gives me this error:

我试图显示用户在注册时选择的兴趣。它们存储在person_interest表中。每次我尝试到user_detail时。php页面给了我这个错误:

Error 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where i.id = pi.interestid' at line 1

If anybody can help me then that would be much appreciated!

如果有人能帮助我，那将不胜感激!

3 个解决方案

#1

SQL syntax error. You shouldn't have a comma before the where clause. I would suggest that you write your query as a join though:

SQL语法错误。在where子句前面不应该有逗号。我建议您将您的查询写成一个连接:

$query = "select pi.UserID, i.interestName from person_interests as pi join interests as i on i.id = pi.interestid";

Of course, if you want the interests of a particular person (Which your code suggests), you need to filter on the user_id:

当然，如果您想要一个特定的人的兴趣(您的代码建议)，您需要过滤user_id:

$query = "select pi.UserID, i.interestName from person_interests as pi join interests as i on i.id = pi.interestid where pi.UserID =" . mysql_escape_string($id);

In reply to your comment:

I think you want to replace this line:

我想你想替换这句话:

$person_interests = $person_interest;

With:

:

$person_interests[] = $person_interest;

#2

Looks like a typo. You have "Interestid" in your table, but "interestid" in your query. Note the case difference. And you have a comma after your where. You should rewrite this using a join by doing:

看起来像一个错字。您的表中有“Interestid”，但查询中有“Interestid”。注意区别。在where后面有一个逗号。您应该使用join来重写此操作:

select
    pi.UserId,
    i.interestname
from
    person_interests pi
left join
    interests i
on
    pi.Interestid = i.id

#3

You have an error in your SQL query which you should debug first.

在SQL查询中有一个错误，您应该首先调试它。

Connect to your database with a commandline client (the command is named mysql).

使用命令行客户端连接到数据库(该命令名为mysql)。

Then write the SQL query in a text editor. Then copy the query and paste it into the commandline client to run it.

然后在文本编辑器中编写SQL查询。然后复制查询并将其粘贴到命令行客户端以运行它。

The commandline client will always tell you where the error is. You can then read the SQL statement in the MySQL manual and look where you've made a syntax error or something similar.

命令行客户端将始终告诉您错误在哪里。然后，您可以阅读MySQL手册中的SQL语句，并查看您在哪里犯了语法错误或类似的错误。

Then you can adopt the query in your editor accordingly and try again.

然后，您可以在编辑器中相应地采用该查询并再次尝试。

Repeat until your query has no error any longer and gets you the expected results.

重复，直到查询不再出现错误并得到预期的结果。

Then copy the working query into your application and replace the invariants with the variables. Take care that you properly escape the values.

然后将工作查询复制到应用程序中，并用变量替换不变量。注意你要正确地避开这些值。