CODEWITHSUNDEEP
phpjavascriptmysqlhtml
user1110409
user1110409

Reputation:

Getting information from multiple tables to a div (HTML,PHP and MySQL)

I am working on a movie booking/viewing website for a particular archive. There are multiple movies in both same and different formats(vcd,dvd,vhs).There are two tables in the database, one is just for the general information like title, director etc. of the movie and the other table is special for the disk like subtitles, language, number of disks.

I created a div, when showing search results. Only the movie names came as the search result, and there is an expand button near them, when the button is clicked an information div appears on the right of the page showing all information on the movie. But, there is a problem with the multiple movies I mentioned above. I couldn’t manage to do it for multiple movies.

I want the page to show only one title for duplicate movies and when I click on expand button, I want it to show all of the details for all copies for duplicates(may be there could be a dropdown box, and the user selects the format then according to choice, the information is showed)

How can I do this? What will be the database functions and php code for this ?

I am open to all sorts of ideas or pieces of codes

Thanks

Upvotes: 0

Views: 319

Answers (1)

Philip Whitehouse
Philip Whitehouse

Reputation: 4157

The short answer is that $mysqli or $mysql returns multiple rows and you need to loop through them.

A decent initial approach might be. Note that this isn't optimised (you'd probably want to get all the disks for all the movies in a single WHERE IN request and then assign them. But this is a first go to get you started.

Movie Table:

id (PRIMARY KEY)
title
director

Disk Table

id (PRIMARY KEY)
film_id
subtitle
language

PHP:

class DB {
    private static $mysqli;
    public static function getDB() {
        if(!isset($this->mysqli)) {
            $this->mysqli = new mysqli('localhost','user','password','db');
        }
        return $this->mysqli();
    }
    public static function closeDB() {
        $mysqli->close();
        unset($mysqli);
    }
}
class Disk {
    private $id;
    private $film_id;
    private $subtitle;
    private $language;
    public static function GetByFilmId($id) {
        $id= DB::getDB()->real_escape_string($id);
        $query = sprintf("SELECT * FROM movie where title = '%i1'",$id);
        $result = DB::getDB()->query($query);
        $disks = array();
        while ($disk = $result->fetch_object("Disk")) {
            $disks[] = $disk;
        }
        $result->close();
        return $disks;
    }
    public render() {
?>
<div class="disk">
    <div><?php echo $this->subtitle; ?></div>
</div>
<?php
    }
}
class Movie {
    private $id;
    private $title;
    private $director;
    public static function GetByTitle($title) {
        $title = DB::getDB()->real_escape_string($title);
        $query = sprintf("SELECT * FROM movie where title = '%s1'",$title);
        $result = DB::getDB()->query($query);
        $movies = array();
        while ($movie = $result->fetch_object("Movie")) {
            $movies[] = $movie;
        }
        $result->close();
        return $movies;
    }
    public __construct() {
        $this->getDisks();
    }
    public getDisks() {
        $disks = Disks::GetByFilmId($this->id);
    }
    public render() {
?>
<div class="movie">
<div><?php echo $this->title; ?></div>
<div class="disks">       
<?php 
foreach($disks as $disk) {
   $disk->render();   
}
?>
</div>
<?php
    }
}
$movies = Movie::GetByTitle($_GET['title']);
DB::closeDB();
?>
<html>
<head>
    <title>MovieLand :: Movies Named <?php echo $_GET['title']; ?></title>
</head>
<body>
<h1>A List of Movies</h1>
<div class="movies">
<?php
foreach($movies as $movie) {
    $movie->render();
}
?>
</div>
</body>
</html>

Upvotes: 1

Related Questions