I have got a many-to-many relationship between Person and Interests. Which means that I needed to produce a middle table known as person_interests.

The Individual table was produced using:

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 produced 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 produced 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 am attempting to choose records in the person_interests table however with no luck. My PHP function for carrying this out is:

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 does not work! This is exactly what my template (user_detail.tpl) appears like:

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

which is what my user_detail.php appears like:

$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 am attempting to display the interests the user chose upon register. They're saved within the person_interests table. Each time I attempt to visit the consumer_detail.php page it provides me with this error:

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 anyone can assist me to then that might be much appreciated!

SQL syntax error. You should not possess a comma prior to the where clause. I recommend that you simply write your query like a join though:

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

Obviously, if you would like the interests of a person (Which your code indicates), you have to filter around the 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 answer your comment:

I believe you need to replace this line:

$person_interests = $person_interest;

With:

$person_interests[] = $person_interest;

Appears like a typo. You've "Interestid" inside your table, but "interestid" inside your query. Note the situation difference. And you've got a comma after your where. You need to rewrite this utilizing a join by doing:

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

You possess an error inside your SQL query that you simply should debug first.

Connect with your database having a commandline client (the command is known as mysql).

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

The commandline client will invariably let you know in which the error is. After that you can browse the SQL statement within the MySQL manual and appear where you have made a syntax error or something like that similar.

You'll be able to adopt the query inside your editor accordingly and check out again.

Repeat until your query doesn't have error any more and will get the expected results.

Then copy the significant query to your application and replace the invariants using the variables. Be mindful that you simply correctly escape the values.