PHP and mySQL - stop duplicate values

[Jova]

Hi there,

I'm completely stuck on a duplicate values problem.
I'm working with mySQL, PHP and Apache on my own PC.
I can use it offline, for testing.

What I'm trying to do in PHP is to print the directors' names in the same row in a HTML table as the dvd title.
Instead, it prints the same dvd title with a different director, depending on how many directors made the movie. Some movies are directed by more than one director such as Fargo by the Coen brothers.

Please ignore the data in the tables, as I know some of it is wrong e.g. spelling and year.

I have a sample dvd mySQL database which breaks down into the following tables: dvd, details, director.

The 'details' table has data on a dvd title: titleid (PK), title, year, region, subtitled.

The 'director' table has data on a director: directorid (PK), forename, surname.

The 'dvd' table holds data on from the above tables, it has foreign keys from 'details' and 'director' table : dvdid (PK), detailsid (FK), directorid(FK)


Here's the data from 'details'

titleid.......title...........region.......year.....subtitled... 01............Fargo...........02...........1990.....Yes......... 02............Ghostbusters....02...........1986.....Yes......... 03............Ocean's 11......01...........2001.....Yes.........

Here's the data from 'director'

directorid.......forename...........surname 01...............Joel...............Coen... 02...............Ethan..............Coen... 03...............Ivan...............Reitman 04...............Steven.............Soderberg

Here's the data from 'dvd'

dvdid.......detailsid...........directorid 01..........01..................02........ // Fargo directed by Ethan Coen 02..........01..................01........ // Fargo directed by Joel Coen 03..........02..................03........ // Ghostbusters directed by Ivan Reitman 04..........03..................04........ // Ocean's 11 directed by Steven Soderberg

The problem is I'm trying to place 2 directors in the same row of a HTML table as the dvd title. Instead what happens is the dvd title is printed twice with a different director e.g.

Title..........Director...........Year Fargo..........Ethan Coen.........1990 Fargo..........Joel Coen..........1990 Ghostbusters...Ivan Reitman.......1986 Ocean's 11.....Steven Soderberg...2001

I rather have this kind of output.

Title..........Director.......................Year Fargo..........Ethan Coen, Joel Coen..........1990 Ghostbusters...Ivan Reitman...................1986 Ocean's 11.....Steven Soderberg...............2001

Here's the select statement I have used in mysql.

PHP Code:

$Result = mysql_query ("Select distinct * from dvd, director, details 
where director.directorid=dvd.directorid 
and details.detailsid=dvd.detailsid") 
or die ('Invalid query : ' . mysql_Error());

print ("<table>\n");
while ($Row = mysql_fetch_array ($Result)) {
    print ("<tr align=\"center\" valign=\"top\">");
    print ("<td align=\"center\" valign=\"top\">$Row[title]</td>\n");
    print ("<td align=\"center\" valign=\"top\">$Row[forename] $Row[surname]</td>\n");
    print ("<td align=\"center\" valign=\"top\">$Row[year]</td>\n");
    print ("<td align=\"center\" valign=\"top\">$Row[subtitled]</td>\n");
    print ("</tr>");
}
print ("</table> \n"); 


This select statement prints the DVD title 'Fargo' twice when there were 2 directors. Any other titles has one director only printed the DVD title once.

To fix this duplicate value, I tried the 'GROUP BY' statement.

PHP Code:

$Result = mysql_query ("Select distinct * from dvd, director, details 
where director.directorid=dvd.directorid 
and details.detailsid=dvd.detailsid GROUP BY dvd.detailsid") 
or die ('Invalid query : ' . mysql_Error()); 


This works, to a point. It does print the dvd title once only BUT it left out the second director (Joel Coen) from the Fargo DVD title. e.g.

Title..........Director.......................Year Fargo..........Ethan Coen.....................1990 Ghostbusters...Ivan Reitman...................1986 Ocean's 11.....Steven Soderberg...............2001

I would like your help, please, to retrieve the directors' names and place them in the same row as the movie they have directed. It has been driving me crazy!

Cheers,
John

[mairving]

If a movie has two directors then you would need to use a sub-query to get the results properly. First select your DVD, then run a sub-query on those results. Something like:

"SELECT first query"
while {

"SELECT second query"
{

} //end second while
} //end first while statement

[Jova]

Thanks, mairving.

I got it working now. I did need a second query.

Now I can stop pulling my hair apart.