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
– tadman, Commented Nov 17, 2017 at 21:08
@MohammedAkhtarZuberi Didn't see that that was available to use. I will try it out! Thanks — the_crouton
– the_crouton, Commented Nov 17, 2017 at 21:28
@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
– the_crouton, Commented Nov 17, 2017 at 21:48

score 4 · Accepted Answer · 2017-11-20 04:53:47Z

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 I'm glad it helps. I reedited my answer. Feel free to ask anything.

S. M. Imtiaz Hasan · Accepted Answer · 2017-11-18 04:26:17Z

2

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

answered Nov 18, 2017 at 4:26

S. M. Imtiaz Hasan

731 gold badge1 silver badge10 bronze badges

Comments

Mohammed Akhtar Zuberi · Accepted Answer · 2017-11-17 21:55:56Z

1

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.

answered Nov 17, 2017 at 21:55

Mohammed Akhtar Zuberi

6588 silver badges26 bronze badges

Comments

adski_ · Accepted Answer · 2017-11-17 21:28:18Z

0

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

answered Nov 17, 2017 at 21:28

adski_

113 bronze badges

Collectives™ on Stack Overflow

Selecting multiple tables for a Query MySQLi PHP

4 Answers 4

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()`

Solution 3 (recommended): PDO

Used data for testing:

Results:

3 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

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()

Solution 3 (recommended): PDO

Used data for testing:

Results:

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related

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()`