I am creating a movie database. Right now I have each movie set up as its own table. Inside each table there are fields for title, summary, rating and so on.
On one of my web pages, feeding off this database, I want to show the movie title, summary and rating for every movie in the database. Will this be possible since each movie is it's own table? Can I select all database tables in a query? Should I set up my database differently? Any help will be much appreciated. Thanks.
i would set up the database differently. the way you have it organized but hard to deal with once you are trying to get the information.
make a table for movies with an id column so you can reference a movie itself.
make a reviews table with 2 id colums. one to identify the review itself incase you need to delete it and one to show which movie it belongs to.
LIKE THIS:
CREATE TABLE `movies` (
`m_id` INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
`m_name` VARCHAR(100) NOT NULL,
ect... you get the picture
);
CREATE TABLE `reviews` (
`r_id` INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
`r_m_id` INT NOT NULL, --- This is where the movie id goes
then add more fields
);then to get the reviews for a movie:
$sql = "SELECT * FROM `reviews` WHERE `r_m_id` = " . $mid;
//where mid is the movie id.hopefully you understand.
Yes that's right. I absolutely agree with kkeith29
Hy, As i read your description my suggestion is that you have to change your database design because your design is looking very tedious for making new table for all the new movies so it is very time consuming when you display this all record in front page.
I am professional in making database design if you want than i make full database design with minimum of amount if you interested than reply me in my email
Thnaks
I totally agree with Kkeith29. Having different tables for each movie is not a good idea. If you plan to implement something, for example, a "database search" for a movie name, would you go through each and every table ?
Anyway, If you want all the tables in the database, then "show tables" would be the query to list all the tables in that database.
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.