Selecting multiple tables for a Query MySQLi PHP

Question

I am trying to create a query, but I don't know how to select multiple tables with the way I have my database set up.

In my query I am trying to display what movies have what actor, but each movie has multiple actors so I have to select from three tables. This is my movies table:

This is my actor_information table

My last table is the actor_movie table. This table correlates the movie_id with and actor_id

I have the following php to connect to the database, but I am not sure how to write the query portion.

<?php
$databaseName = 'movie_database';
$databaseUser = 'root';
$databasePassword = 'root';
$databaseHost = '127.0.0.1';

$conn = new mysqli($databaseHost, $databaseUser, $databasePassword, $databaseName);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

echo "Connected successfully <br>";

// this code is wrong, but i get the general idea of how to select from my db.
$sql = "SELECT actor_movie, movies, actor_information FROM actor_movie INNER 
JOIN movies ON actor_movie.movie_id = movies.movie_id 
INNER JOIN actor_information ON actor_movie.actor_id =  
actor_information.actor_id"
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
    // output data of each row
    while($row = mysqli_fetch_assoc($result)) {
        echo " Title " . $row["title"]. "". $row["actor_id"]. "<br>";
    }
} else {
    echo "0 results";
}


mysqli_close($conn);
?>

Here is my uml if it helps:

Note: The object-oriented interface to `mysqli` is significantly less verbose, making code easier to read and audit, and is not easily confused with the obsolete `mysql_query` interface. Before you get too invested in the procedural style it’s worth switching over. Example: `$db = new mysqli(…)` and `$db->prepare("…")` The procedural interface is an artifact from the PHP 4 era when `mysqli` API was introduced and should not be used in new code. — tadman, Nov 17 '17 at 21:08
@MohammedAkhtarZuberi Didn't see that that was available to use. I will try it out! Thanks — the_crouton, Nov 17 '17 at 21:28
[Check this answer](https://stackoverflow.com/questions/46998346/return-data-from-two-tables-based-on-identical-value/46998503#46998503) in which I explained use of `INNER JOIN`. — Mohammed Akhtar Zuberi, Nov 17 '17 at 21:31
@MohammedAkhtarZuberi I like the explanation but I am having trouble following along with it since I have three tables I need to work with — the_crouton, Nov 17 '17 at 21:48
I will post only the SQL Query for three tables in the answer. — Mohammed Akhtar Zuberi, Nov 17 '17 at 21:49

score 4 · Accepted Answer · 2017-11-20T04:53:47.540

As @tadman suggested, the object-oriented mysqli is better. My recommendation, though, is to use PDO instead of mysqli. Read this.
Read here and here to properly handle the errors/exceptions.
Note the use of the prepared statement (see here too) in order to avoid SQL injection.
You can also fetch just one movie.
The select uses LEFT JOINs: fetches all movies and "attaches" the actor details to each of them, by using actor_movie. There are also the movies fetched, that doesn't have any actors assigned yet (e.g. actor_name = NULL in result set).
After the result set is fetched, I built a 2nd array ($movies) to prepare the data to be properly displayed in the html table. Note that this step could be replaced by fetching data from db using multiple queries: one for fetching the movies list, and a query for each movie for fetching the actors.
Note the separation of data fetching code (php, upper page part) from the displaying code (html, lower page part). E.g: all data is fetched from db in arrays (php, upper page part) and only these arrays are then iterated in order to display the data (html, lower page part). E.g: no data fetching code is mixed with the data displaying code.
The db structure corresponds to the one kindly presented by you.
Notice the "use" of actor "Robert de Niro" (actor_id = 1) in two movies (see actor_movie table).

Good luck.

Note: all three solutions are identical, starting with the line: `$movies = [];`

Solution 1: mysqli using `get_result()` + `fetch_all()`

Works only if the mysqlnd driver ("MySQL Native Driver") is installed!

<?php
// Db configs.
define('HOST', 'localhost');
define('PORT', 3306);
define('DATABASE', 'db');
define('USERNAME', 'user');
define('PASSWORD', 'pass');

// Error reporting.
error_reporting(E_ALL);
ini_set('display_errors', 1); // Set it to 0 on a live server!

/**
 * Enable internal report functions. This enables the exception handling, 
 * e.g. mysqli will not throw PHP warnings anymore, but mysqli exceptions 
 * (mysqli_sql_exception). They are catched in the try-catch block.
 * 
 * MYSQLI_REPORT_ERROR: Report errors from mysqli function calls.
 * MYSQLI_REPORT_STRICT: Throw a mysqli_sql_exception for errors instead of warnings. 
 * 
 * See: http://php.net/manual/en/class.mysqli-driver.php
 * See: http://php.net/manual/en/mysqli-driver.report-mode.php
 * See: http://php.net/manual/en/mysqli.constants.php
 */
$mysqliDriver = new mysqli_driver();
$mysqliDriver->report_mode = (MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

/**
 * Create a new db connection.
 * 
 * @see http://php.net/manual/en/mysqli.construct.php
 */
$connection = new mysqli(HOST, USERNAME, PASSWORD, DATABASE, PORT);

// Get movie id. If NULL, display all movies. Read from $_POST for example.
$movieId = NULL;

/*
 * The SQL statement to be prepared. Notice the so-called markers, 
 * e.g. the "?" signs. They will be replaced later with the 
 * corresponding values when using mysqli_stmt::bind_param.
 * 
 * See: http://php.net/manual/en/mysqli.prepare.php
 */
$sql = sprintf(
        'SELECT 
                mv.movie_id,
                mv.title,
                mv.rating,
                mv.Runtime,
                mv.movie_rating,
                mv.release_date,
                acin.actor_name 
            FROM movies AS mv 
            LEFT JOIN actor_movie AS acmv ON acmv.movie_id = mv.movie_id 
            LEFT JOIN actor_information AS acin ON acin.actor_id = acmv.actor_id 
            %s'
        , isset($movieId) ? 'WHERE mv.movie_id = ?' : ''
);

/*
 * Prepare the SQL statement for execution - ONLY ONCE.
 * 
 * See: http://php.net/manual/en/mysqli.prepare.php
 */
$statement = $connection->prepare($sql);

/*
 * Bind variables for the parameter markers (?) in the 
 * SQL statement that was passed to prepare(). The first 
 * argument of bind_param() is a string that contains one 
 * or more characters which specify the types for the 
 * corresponding bind variables.
 * 
 * See: http://php.net/manual/en/mysqli-stmt.bind-param.php
 */
if (isset($movieId)) {
    $statement->bind_param('i', $movieId);
}

/*
 * Execute the prepared SQL statement.
 * When executed any parameter markers which exist will 
 * automatically be replaced with the appropriate data.
 * 
 * See: http://php.net/manual/en/mysqli-stmt.execute.php
 */
$executed = $statement->execute();

/*
 * Get the result set from the prepared statement.
 * 
 * NOTA BENE:
 * Available only with mysqlnd ("MySQL Native Driver")! If this 
 * is not installed, then uncomment "extension=php_mysqli_mysqlnd.dll" in 
 * PHP config file (php.ini) and restart web server (I assume Apache) and 
 * mysql service. Or use the following functions instead:
 * mysqli_stmt::store_result + mysqli_stmt::bind_result + mysqli_stmt::fetch.
 * 
 * See:
 *      http://php.net/manual/en/mysqli-stmt.get-result.php
 *      https://stackoverflow.com/questions/8321096/call-to-undefined-method-mysqli-stmtget-result
 */
$result = $statement->get_result();

/*
 * Fetch data and save it into $fetchedData array.
 * 
 * See: http://php.net/manual/en/mysqli-result.fetch-all.php
 */
// Fetch all rows at once...
$fetchedData = $result->fetch_all(MYSQLI_ASSOC);

// ... OR fetch one row at a time.
// while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
//     $fetchedData[] = $row;
// }
// 
// Just for testing. Display fetched data.
//echo '<pre>' . print_r($fetchedData, TRUE) . '</pre>';

/*
 * Free the memory associated with the result. You should 
 * always free your result when it is not needed anymore.
 * 
 * See: http://php.net/manual/en/mysqli-result.free.php
 */
$result->close();

/*
 * Close the prepared statement. It also deallocates the statement handle.
 * If the statement has pending or unread results, it cancels them 
 * so that the next query can be executed.
 * 
 * See: http://php.net/manual/en/mysqli-stmt.close.php
 */
$statementClosed = $statement->close();

// Prepare a list with each movie and its corresponding actors - for display in a html table.
$movies = [];
foreach ($fetchedData as $item) {
    $movieId = $item['movie_id'];
    $title = $item['title'];
    $rating = $item['rating'];
    $runtime = $item['Runtime'];
    $movieRating = $item['movie_rating'];
    $releaseDate = $item['release_date'];
    $actorName = $item['actor_name'];

    if (!array_key_exists($movieId, $movies)) {
        $movies[$movieId] = [
            'title' => $title,
            'rating' => $rating,
            'Runtime' => $runtime,
            'movie_rating' => $movieRating,
            'release_date' => $releaseDate,
        ];
    }

    if (isset($actorName)) {
        $movies[$movieId]['actors'][] = $actorName;
    } else {
        $movies[$movieId]['actors'] = [];
    }
}

// Just for testing. Display movies list.
//echo '<pre>' . print_r($movies, TRUE) . '</pre>';
?>

<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1" />
        <meta name="viewport" content="width=device-width, initial-scale=1, user-scalable=yes" />
        <meta charset="UTF-8" />
        <!-- The above 3 meta tags must come first in the head -->

        <title>Demo</title>

        <style type="text/css">
            body { padding: 10px; font-family: "Verdana", Arial, sans-serif; }
            .movies { border-collapse: collapse; border: 1px solid #ccc; }
            .movies td, th { padding: 5px; }
            .movie-record { color: white; background-color: #00b3ee; }
            .actor-record { background-color: #f3f3f3; }
        </style>
    </head>
    <body>

        <h3>
            Movies list
        </h3>

        <table class="movies">
            <thead>
                <tr>
                    <th>Movie ID</th>
                    <th>Title</th>
                    <th>Rating</th>
                    <th>Runtime</th>
                    <th>Movie Rating</th>
                    <th>Release Date</th>
                </tr>
            </thead>
            <tbody>
                <?php
                if ($movies) {
                    foreach ($movies as $movieId => $movie) {
                        $title = $movie['title'];
                        $rating = $movie['rating'];
                        $runtime = $movie['Runtime'];
                        $movieRating = $movie['movie_rating'];
                        $releaseDate = $movie['release_date'];
                        ?>
                        <tr class="movie-record">
                            <td><?php echo $movieId; ?></td>
                            <td><?php echo $title; ?></td>
                            <td><?php echo $rating; ?></td>
                            <td><?php echo $runtime; ?></td>
                            <td><?php echo $movieRating; ?></td>
                            <td><?php echo $releaseDate; ?></td>
                        </tr>
                        <?php
                        foreach ($movie['actors'] as $actorName) {
                            ?>
                            <tr class="actor-record">
                                <td colspan="6"><?php echo $actorName; ?></td>
                            </tr>
                            <?php
                        }
                    }
                } else {
                    ?>
                    <tr>
                        <td colspan="6">
                            <?php echo 'No movies found'; ?>
                        </td>
                    </tr>
                    <?php
                }
                ?>
            </tbody>
        </table>

    </body>
</html>

Solution 2: mysqli using `store_result()` + `bind_result()` + `fetch()`

Hard to work with. But it works even if the mysqlnd driver ("MySQL Native Driver") is not installed.

<?php
// Db configs.
define('HOST', 'localhost');
define('PORT', 3306);
define('DATABASE', 'db');
define('USERNAME', 'user');
define('PASSWORD', 'pass');

// Error reporting.
error_reporting(E_ALL);
ini_set('display_errors', 1); // Set it to 0 on a live server!

/**
 * Enable internal report functions. This enables the exception handling, 
 * e.g. mysqli will not throw PHP warnings anymore, but mysqli exceptions 
 * (mysqli_sql_exception). They are catched in the try-catch block.
 * 
 * MYSQLI_REPORT_ERROR: Report errors from mysqli function calls.
 * MYSQLI_REPORT_STRICT: Throw a mysqli_sql_exception for errors instead of warnings. 
 * 
 * See: http://php.net/manual/en/class.mysqli-driver.php
 * See: http://php.net/manual/en/mysqli-driver.report-mode.php
 * See: http://php.net/manual/en/mysqli.constants.php
 */
$mysqliDriver = new mysqli_driver();
$mysqliDriver->report_mode = (MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

/**
 * Create a new db connection.
 * 
 * @see http://php.net/manual/en/mysqli.construct.php
 */
$connection = new mysqli(HOST, USERNAME, PASSWORD, DATABASE, PORT);

// Get movie id. If NULL, display all movies. Read from $_POST for example.
$movieId = NULL;

/*
 * The SQL statement to be prepared. Notice the so-called markers, 
 * e.g. the "?" signs. They will be replaced later with the 
 * corresponding values when using mysqli_stmt::bind_param.
 * 
 * See: http://php.net/manual/en/mysqli.prepare.php
 */
$sql = sprintf(
        'SELECT 
                mv.movie_id,
                mv.title,
                mv.rating,
                mv.Runtime,
                mv.movie_rating,
                mv.release_date,
                acin.actor_name 
            FROM movies AS mv 
            LEFT JOIN actor_movie AS acmv ON acmv.movie_id = mv.movie_id 
            LEFT JOIN actor_information AS acin ON acin.actor_id = acmv.actor_id 
            %s'
        , isset($movieId) ? 'WHERE mv.movie_id = ?' : ''
);

/*
 * Prepare the SQL statement for execution - ONLY ONCE.
 * 
 * See: http://php.net/manual/en/mysqli.prepare.php
 */
$statement = $connection->prepare($sql);

/*
 * Bind variables for the parameter markers (?) in the 
 * SQL statement that was passed to prepare(). The first 
 * argument of bind_param() is a string that contains one 
 * or more characters which specify the types for the 
 * corresponding bind variables.
 * 
 * See: http://php.net/manual/en/mysqli-stmt.bind-param.php
 */
if (isset($movieId)) {
    $statement->bind_param('i', $movieId);
}

/*
 * Execute the prepared SQL statement.
 * When executed any parameter markers which exist will 
 * automatically be replaced with the appropriate data.
 * 
 * See: http://php.net/manual/en/mysqli-stmt.execute.php
 */
$executed = $statement->execute();

/*
 * Transfer the result set resulted from executing the prepared statement.
 * E.g. store, e.g. buffer the result set into the (same) prepared statement.
 * 
 * See:
 *      http://php.net/manual/en/mysqli-stmt.store-result.php
 *      https://stackoverflow.com/questions/8321096/call-to-undefined-method-mysqli-stmtget-result
 */
$resultStored = $statement->store_result();

/*
 * Bind the result set columns to corresponding variables.
 * E.g. these variables will hold the column values after fetching.
 * 
 * See: http://php.net/manual/en/mysqli-stmt.bind-result.php
 */
$varsBound = $statement->bind_result(
        $boundMovieId
        , $boundTitle
        , $boundRating
        , $boundRuntime
        , $boundMovieRating
        , $boundReleaseDate
        , $boundActorName
);

/*
 * Fetch results from the result set (of the prepared statement) into the bound variables.
 * 
 * See: http://php.net/manual/en/mysqli-stmt.fetch.php
 */
$fetchedData = [];
while ($row = $statement->fetch()) {
    $fetchedData[] = [
        'movie_id' => $boundMovieId,
        'title' => $boundTitle,
        'rating' => $boundRating,
        'Runtime' => $boundRuntime,
        'movie_rating' => $boundMovieRating,
        'release_date' => $boundReleaseDate,
        'actor_name' => $boundActorName,
    ];
}

// Just for testing. Display fetched data.
//echo '<pre>' . print_r($fetchedData, TRUE) . '</pre>';

/*
 * Frees the result memory associated with the statement,
 * which was allocated by mysqli_stmt::store_result.
 * 
 * See: http://php.net/manual/en/mysqli-stmt.store-result.php
 */
$statement->free_result();

/*
 * Close the prepared statement. It also deallocates the statement handle.
 * If the statement has pending or unread results, it cancels them 
 * so that the next query can be executed.
 * 
 * See: http://php.net/manual/en/mysqli-stmt.close.php
 */
$statementClosed = $statement->close();

// Prepare a list with each movie and its corresponding actors - for display in a html table.
$movies = [];
foreach ($fetchedData as $item) {
    $movieId = $item['movie_id'];
    $title = $item['title'];
    $rating = $item['rating'];
    $runtime = $item['Runtime'];
    $movieRating = $item['movie_rating'];
    $releaseDate = $item['release_date'];
    $actorName = $item['actor_name'];

    if (!array_key_exists($movieId, $movies)) {
        $movies[$movieId] = [
            'title' => $title,
            'rating' => $rating,
            'Runtime' => $runtime,
            'movie_rating' => $movieRating,
            'release_date' => $releaseDate,
        ];
    }

    if (isset($actorName)) {
        $movies[$movieId]['actors'][] = $actorName;
    } else {
        $movies[$movieId]['actors'] = [];
    }
}

// Just for testing. Display movies list.
//echo '<pre>' . print_r($movies, TRUE) . '</pre>';
?>

<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1" />
        <meta name="viewport" content="width=device-width, initial-scale=1, user-scalable=yes" />
        <meta charset="UTF-8" />
        <!-- The above 3 meta tags must come first in the head -->

        <title>Demo</title>

        <style type="text/css">
            body { padding: 10px; font-family: "Verdana", Arial, sans-serif; }
            .movies { border-collapse: collapse; border: 1px solid #ccc; }
            .movies td, th { padding: 5px; }
            .movie-record { color: white; background-color: #00b3ee; }
            .actor-record { background-color: #f3f3f3; }
        </style>
    </head>
    <body>

        <h3>
            Movies list
        </h3>

        <table class="movies">
            <thead>
                <tr>
                    <th>Movie ID</th>
                    <th>Title</th>
                    <th>Rating</th>
                    <th>Runtime</th>
                    <th>Movie Rating</th>
                    <th>Release Date</th>
                </tr>
            </thead>
            <tbody>
                <?php
                if ($movies) {
                    foreach ($movies as $movieId => $movie) {
                        $title = $movie['title'];
                        $rating = $movie['rating'];
                        $runtime = $movie['Runtime'];
                        $movieRating = $movie['movie_rating'];
                        $releaseDate = $movie['release_date'];
                        ?>
                        <tr class="movie-record">
                            <td><?php echo $movieId; ?></td>
                            <td><?php echo $title; ?></td>
                            <td><?php echo $rating; ?></td>
                            <td><?php echo $runtime; ?></td>
                            <td><?php echo $movieRating; ?></td>
                            <td><?php echo $releaseDate; ?></td>
                        </tr>
                        <?php
                        foreach ($movie['actors'] as $actorName) {
                            ?>
                            <tr class="actor-record">
                                <td colspan="6"><?php echo $actorName; ?></td>
                            </tr>
                            <?php
                        }
                    }
                } else {
                    ?>
                    <tr>
                        <td colspan="6">
                            <?php echo 'No movies found'; ?>
                        </td>
                    </tr>
                    <?php
                }
                ?>
            </tbody>
        </table>

    </body>
</html>

Solution 3 (recommended): PDO

The absolute winner.

<?php
// Db configs.
define('HOST', 'localhost');
define('PORT', 3306);
define('DATABASE', 'db');
define('USERNAME', 'user');
define('PASSWORD', 'pass');
define('CHARSET', 'utf8');

// Error reporting.
error_reporting(E_ALL);
ini_set('display_errors', 1); // Set it to 0 on a live server!

/*
 * Create a PDO instance as db connection to db.
 * 
 * See: http://php.net/manual/en/class.pdo.php
 * See: http://php.net/manual/en/pdo.constants.php
 * See: http://php.net/manual/en/pdo.error-handling.php
 * See: http://php.net/manual/en/pdo.connections.php
 */
$connection = new PDO(
        sprintf('mysql:host=%s;port=%s;dbname=%s;charset=%s', HOST, PORT, DATABASE, CHARSET)
        , USERNAME
        , PASSWORD
        , [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES => FALSE,
    PDO::ATTR_PERSISTENT => TRUE
        ]
);

// Get movie id. If NULL, display all movies. Read from $_POST for example.
$movieId = NULL;

/*
 * The SQL statement to be prepared. Notice the so-called named markers.
 * They will be replaced later with the corresponding values from the
 * bindings array when using PDOStatement::bindValue.
 * 
 * When using named markers, the bindings array will be an associative
 * array, with the key names corresponding to the named markers from
 * the sql statement.
 * 
 * You can also use question mark markers. In this case, the bindings 
 * array will be an indexed array, with keys beginning from 1 (not 0).
 * Each array key corresponds to the position of the marker in the sql 
 * statement.
 * 
 * See: http://php.net/manual/en/mysqli.prepare.php
 */
$sql = sprintf(
        'SELECT 
                mv.movie_id,
                mv.title,
                mv.rating,
                mv.Runtime,
                mv.movie_rating,
                mv.release_date,
                acin.actor_name 
            FROM movies AS mv 
            LEFT JOIN actor_movie AS acmv ON acmv.movie_id = mv.movie_id 
            LEFT JOIN actor_information AS acin ON acin.actor_id = acmv.actor_id 
            %s'
        , isset($movieId) ? 'WHERE mv.movie_id = :movie_id' : ''
);

/**
 * The bindings array, mapping the named markers from the sql
 * statement to the corresponding values. It will be directly 
 * passed as argument to the PDOStatement::execute method.
 * 
 * See: http://php.net/manual/en/pdostatement.execute.php
 */
$bindings = [];
if (isset($movieId)) {
    $bindings[':movie_id'] = $movieId;
}

/*
 * Prepare the sql statement for execution and return a statement object.
 * 
 * See: http://php.net/manual/en/pdo.prepare.php
 */
$statement = $connection->prepare($sql);

/*
 * Execute the prepared statement. Because the bindings array
 * is directly passed as argument, there is no need to use any
 * binding method for each sql statement's marker (like
 * PDOStatement::bindParam or PDOStatement::bindValue).
 * 
 * See: http://php.net/manual/en/pdostatement.execute.php
 */
$executed = $statement->execute($bindings);

/*
 * Fetch data (all at once) and save it into $fetchedData array.
 * 
 * See: http://php.net/manual/en/pdostatement.fetchall.php
 */
$fetchedData = $statement->fetchAll(PDO::FETCH_ASSOC);

// Just for testing. Display fetched data.
echo '<pre>' . print_r($fetchedData, TRUE) . '</pre>';

// Prepare a list with each movie and its corresponding actors - for display in a html table.
$movies = [];
foreach ($fetchedData as $item) {
    $movieId = $item['movie_id'];
    $title = $item['title'];
    $rating = $item['rating'];
    $runtime = $item['Runtime'];
    $movieRating = $item['movie_rating'];
    $releaseDate = $item['release_date'];
    $actorName = $item['actor_name'];

    if (!array_key_exists($movieId, $movies)) {
        $movies[$movieId] = [
            'title' => $title,
            'rating' => $rating,
            'Runtime' => $runtime,
            'movie_rating' => $movieRating,
            'release_date' => $releaseDate,
        ];
    }

    if (isset($actorName)) {
        $movies[$movieId]['actors'][] = $actorName;
    } else {
        $movies[$movieId]['actors'] = [];
    }
}

// Just for testing. Display movies list.
//echo '<pre>' . print_r($movies, TRUE) . '</pre>';
?>

<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1" />
        <meta name="viewport" content="width=device-width, initial-scale=1, user-scalable=yes" />
        <meta charset="UTF-8" />
        <!-- The above 3 meta tags must come first in the head -->

        <title>Demo</title>

        <style type="text/css">
            body { padding: 10px; font-family: "Verdana", Arial, sans-serif; }
            .movies { border-collapse: collapse; border: 1px solid #ccc; }
            .movies td, th { padding: 5px; }
            .movie-record { color: white; background-color: #00b3ee; }
            .actor-record { background-color: #f3f3f3; }
        </style>
    </head>
    <body>

        <h3>
            Movies list
        </h3>

        <table class="movies">
            <thead>
                <tr>
                    <th>Movie ID</th>
                    <th>Title</th>
                    <th>Rating</th>
                    <th>Runtime</th>
                    <th>Movie Rating</th>
                    <th>Release Date</th>
                </tr>
            </thead>
            <tbody>
                <?php
                if ($movies) {
                    foreach ($movies as $movieId => $movie) {
                        $title = $movie['title'];
                        $rating = $movie['rating'];
                        $runtime = $movie['Runtime'];
                        $movieRating = $movie['movie_rating'];
                        $releaseDate = $movie['release_date'];
                        ?>
                        <tr class="movie-record">
                            <td><?php echo $movieId; ?></td>
                            <td><?php echo $title; ?></td>
                            <td><?php echo $rating; ?></td>
                            <td><?php echo $runtime; ?></td>
                            <td><?php echo $movieRating; ?></td>
                            <td><?php echo $releaseDate; ?></td>
                        </tr>
                        <?php
                        foreach ($movie['actors'] as $actorName) {
                            ?>
                            <tr class="actor-record">
                                <td colspan="6"><?php echo $actorName; ?></td>
                            </tr>
                            <?php
                        }
                    }
                } else {
                    ?>
                    <tr>
                        <td colspan="6">
                            <?php echo 'No movies found'; ?>
                        </td>
                    </tr>
                    <?php
                }
                ?>
            </tbody>
        </table>

    </body>
</html>

Used data for testing:

Table movies:

movie_id    title               rating  Runtime movie_rating    release_date
----------------------------------------------------------------------------
1           Blade Runner 2049   R       164     8.5             2017-10-06
2           The Godfather       R       178     9.2             1972-04-24
3           Pulp Fiction        R       178     8.9             1994-10-14


Table actor_information:

actor_id    actor_name
--------------------------
1           Robert de Niro
2           Ryan Gosling
3           Ana de Armas
4           Marlon Brando
5           John Travolta
6           Julia Roberts


Table actor_movie:

movie_id    actor_id
--------------------
1           1
1           2
1           3
2           4
2           1
2           6

Results:

This helps out alot! Im only having one issue, I cant find the extension=php_mysqli_mysqlnd.dll in my php.ini. Im using php 5.5 and also switched to 7, and it isn't there. If I try the mysqli_stmt::store_result + mysqli_stmt::bind_result + mysqli_stmt::fetch, it doesn't work and I get the error of Call to undefined method mysqli_stmt::get_result() — the_crouton, Nov 20 '17 at 00:27
@the_crouton I'm glad it helps. I reedited my answer. Feel free to ask anything. — , Nov 20 '17 at 03:57

score 2 · Answer 2 · answered Nov 18 '17 at 04:26

Use this =>

select movie.movie_name AS Movie, GROUP_CONCAT(actor_name.actor_name) AS Actor FROM movie INNER JOIN relation_table ON movie.id = relation_table.movie_id INNER JOIN actor_name ON actor_name.id = relation_table.actor_id GROUP BY movie.id

score 1 · Answer 3 · answered Nov 17 '17 at 21:55

As you have three tables and want to fetch relevant data, use the below SQL Query.

SELECT actor_movie.*, movies.*, actor_information.* FROM actor_movie INNER JOIN movies ON actor_movie.movie_id = movies.movie_id INNER JOIN actor_information ON actor_movie.actor_id = actor_information.actor_id"

Consider using an Auto Increment value (which will be PRIMARY KEY) in your DB with all the tables. This should be your ID.

Hope this helps.

score 0 · Answer 4 · answered Nov 17 '17 at 21:28

I think your query should look something like this:

 "SELECT mov.title,act.actor_name FROM movies AS mov JOIN actor_information AS act ON act.actor_id=mov.movie_id"

If you want to look for a particular movie or actor, simply append WHERE mov.title='Pulp Fiction', etc

Selecting multiple tables for a Query MySQLi PHP

4 Answers4

Note: all three solutions are identical, starting with the line: $movies = [];

Solution 1: mysqli using get_result() + fetch_all()

Solution 2: mysqli using store_result() + bind_result() + fetch()