PHP - data from SQL db repeats itself

Question

I'm so sorry if this doesn't make any sense at all. I've created a PHP file called "filmdetails.php" that will list all details of a given film:

    <?php

    require_once 'dbcon_sakila.php';

    $film_id = $_GET['filmid'];

    $sql = 'SELECT title, description, release_year, rental_duration, rental_rate, length, replacement_cost, rating, special_features, film.last_update, category.name, actor.first_name, actor.last_name
    FROM film, film_category, category, film_actor, actor
    WHERE film.film_id = film_category.film_id
    AND category.category_id = film_category.category_id
    AND film.film_id = film_actor.film_id
    AND film_actor.actor_id = actor.actor_id
    AND film.film_id = ?';
    $stmt = $link->prepare($sql);
    $stmt->bind_param('i', $film_id);
    $stmt->bind_result($title, $description, $release_year, $rental_duration, $rental_rate, $length, $replacement_cost, $rating, $special_features, $last_update, $category_name, $first_name, $last_name);

    $stmt->execute();

    while ($stmt->fetch()) {
    echo '<p>Title: ' . $title . '</p>';
    echo '<p>Description: ' . $description . '</p>';
    echo '<p>Release year: ' . $release_year . '</p>';
    echo '<p>Rental duration: ' . $rental_duration . '</p>';
    echo '<p>Rental rate: ' . $rental_rate . '</p>';
    echo '<p>Length: ' . $length . '</p>';
    echo '<p>Replacement cost: ' . $replacement_cost . '</p>';
    echo '<p>Rating: ' . $rating . '</p>';
    echo '<p>Special features: ' . $special_features . '</p>';
    echo '<p>Last updated: ' . $last_update . '</p>';
    echo '<p>Categories: ' . $category_name . '</p>';
    echo '<p>First name: ' . $first_name . '</p>';
    echo '<p>Last name: ' . $last_name . '</p>';
    }

    ?>

The problem is when I choose a specific film (in my browser) it repeats itself but with the different actors in the film.
Example:

Title: ALLEY EVOLUTION
Description: A Fast-Paced Drama of a Robot And a Composer who must Battle a Astronaut in New Orleans
Release year: 2006
Rental duration: 6
Rental rate: 2.99
Length: 180
Replacement cost: 23.99
Rating: NC-17
Special features: Trailers,Commentaries
Last updated: 2006-02-15 05:03:42
Categories: Foreign
First name: KARL
Last name: BERRY

Title: ALLEY EVOLUTION
Description: A Fast-Paced Drama of a Robot And a Composer who must Battle a Astronaut in New Orleans
Release year: 2006
Rental duration: 6
Rental rate: 2.99
Length: 180
Replacement cost: 23.99
Rating: NC-17
Special features: Trailers,Commentaries
Last updated: 2006-02-15 05:03:42
Categories: Foreign
First name: JUDE
Last name: CRUISE

and so on...

How do I prevent this? Do I really have to make more SELECT statements?

How do you anticipate that MySQL will return a films information if the film has more than one actor? It's a One-to-Many relationship and so the Many must be returned causing the One to repeat. — JNevill
– JNevill, Commented Oct 8, 2014 at 13:25
Rossco posted the answer but I'd advise you to look into how databases fetch and return results. Make sure you understand aggregate functions because they're very common. — Alternatex
– Alternatex, Commented Oct 8, 2014 at 13:27

JNevill · Accepted Answer · 2014-10-08 13:49:44Z

1

Because you have a One-to-Many relationship between Films and Actors, you are going to get multiple results. One record for each Actor. This is the nature of an RDBMS.

A solution would be to take the actor table out of your query, and in your While loop query the actor table alone with the film_id (which you would need to return in your initial query). You can then have a separate loop through the results of the actor query and add them into your page.

I'm rusty on PHP, but it would look something like:

<?php

    require_once 'dbcon_sakila.php';

    $film_id = $_GET['filmid'];

    $sql = 'SELECT film_id, title, description, release_year, rental_duration, rental_rate, length, replacement_cost, rating, special_features, film.last_update, category.name 
        FROM film, film_category, category
        WHERE film.film_id = film_category.film_id
            AND category.category_id = film_category.category_id            
            AND film.film_id = ?';

    $stmt = $link->prepare($sql);
    $stmt->bind_param('i', $film_id);
    $stmt->bind_result($title, $description, $release_year, $rental_duration, $rental_rate, $length, $replacement_cost, $rating, $special_features, $last_update, $category_name);

    $stmt->execute();

    while ($stmt->fetch()) {
        echo '<p>Title: ' . $title . '</p>';
        echo '<p>Description: ' . $description . '</p>';
        echo '<p>Release year: ' . $release_year . '</p>';
        echo '<p>Rental duration: ' . $rental_duration . '</p>';
        echo '<p>Rental rate: ' . $rental_rate . '</p>';
        echo '<p>Length: ' . $length . '</p>';
        echo '<p>Replacement cost: ' . $replacement_cost . '</p>';
        echo '<p>Rating: ' . $rating . '</p>';
        echo '<p>Special features: ' . $special_features . '</p>';
        echo '<p>Last updated: ' . $last_update . '</p>';
        echo '<p>Categories: ' . $category_name . '</p>';


        $actorsql = 'SELECT actor_id, first_name, last_name
            FROM film_actor, film
            WHERE film_actor.actor_id = actor.actor_id                 
                AND film_actor.film_id = '.$film_id.';';

        $actorstmt = $link->prepare($actorsql);
        $actorstmt->bind_param('i', $actor_id);
        $actorstmt->bind_result($actor_id, $first_name, $last_name);

        while ($actorstmt->fetch()) {
            echo '<p>Actor: ' . $first_name . ' ' . $last_name . '</p>';
        }
    }

?>

Again... rusty with php, but that's the basic idea. This will print the movie details and then run through the actors in the film and print those one by one beneath the movie info.

edited Oct 8, 2014 at 13:49

answered Oct 8, 2014 at 13:29

JNevill

50.6k4 gold badges46 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Lula Over a year ago

Can you please explain me the loop part or give me an example?

Lula Over a year ago

Thanks but it's not working. It says this line: $stmt->bind_param('i', $film_id); is preventing it to run but I can't tell why though. Heck, I'll just send my assignment now.

JNevill Over a year ago

Yea... php... bind_param... I added film_id into the results of the initial query and apparently that is a problem for bind_param. Take film_id out of the initial query and then figure out how to get the film_id from the $stmt (I assume it's the index of the array) and use that in the second query to get the actors for the film. The important part is the separate queries to keep dupes out.

Rossco · Accepted Answer · 2014-10-08 13:31:15Z

0

Use GROUP BY film_actor.actor_id at the end of your SQL statement

edited Oct 8, 2014 at 13:31

answered Oct 8, 2014 at 13:25

Rossco

3,7833 gold badges27 silver badges37 bronze badges

Collectives™ on Stack Overflow

PHP - data from SQL db repeats itself

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related