Insert multiple actors on the same movie with MYSQL

Question

Guys i dont iveen know how to ask this, i just started to learn PHP and Mysql and i having a lot of trouble to do something that i think is pretty easy.

Lets just say i have two tables in Mysql Actors and Movies.

The Actors have ID and NAME And the Movies also have the same ID and NAME.

In this example lets say our actors are 1 Brad Pitt, 2 Edward Norton and 3 Jack Nicholson

The movies are 1 Fight Club and 2 Ocean's Thirteen.

Brad Pitt and Edward Norton are in the Fight Club, Brad Pitt is on Ocean's Thirteen, And Jack Nicholson arent in any of those movies.

How can i link the actors to the movies on my Mysql Database, also how can i display them using PHP.

I really appreciate any Help, iam really lost in this one! Thanks for the attention!

Answer 1

You can use JOINS for this.

In a brief intro, lets have another table, relations. In that, the movies and the actors are related. The PRIMARY KEY will be the combination of the both IDs.

Actors Table
+----+----------------+
| ID |           NAME |
+----+----------------+
|  1 |      Brad Pitt |
|  2 |  Edward Norton |
|  3 | Jack Nicholson |
+----+----------------+

Movies Table
+----+--------------------+
| ID |               NAME |
+----+--------------------+
|  1 |         Fight Club |
|  2 | Ocean''s Thirteen. |
+----+--------------------+

Now we can have a relationship table with these two IDs.

+-------+-------+
| MOVIE | ACTOR |
+-------+-------+
|     1 |     1 |
|     1 |     2 |
|     2 |     1 |
+-------+-------+

This way, Movie 1 will have both actors 1 and 2.

SQL Queries

CREATE TABLE Actors (`id` int, `name` varchar(255));

INSERT INTO Actors (`id`, `name`) VALUES
(1, 'Brad Pitt'),
(2, 'Edward Norton'),
(3, 'Jack Nicholson');

CREATE TABLE Movies (`id` int, `name` varchar(255));

INSERT INTO Movies (`id`, `name`) VALUES
(1, 'Fight Club'),
(2, 'Ocean''''s Thirteen.');

CREATE TABLE stars (`movie` int, `actor` int);

INSERT INTO RelationShip (`movie`, `actor`) VALUES
(1, 1),
(1, 2),
(2, 1);

Answer 2

in this case you have a many to many relationship, it means, one actor may be in many movies, and one movie may have many actors.

what you do in this cases is generate an intermediate table, let's say "MOVIE_ACTOR" which should have 2 columns MOVIE_ID and ACTOR_ID

this is a good point to start

so, for example i you want to know all the movies from an actor you would do your query something like this:

SELECT movies.name FROM movie_actor LEFT JOIN movies ON movie_actor.movie_id = movies.id
WHERE movie_actor.actor_id = (SELECT id FROM actors WHERE name = 'brad pitt')

here's how yo could create the table CREATE TABLE MOVIE_ACTOR (movie_id int, actor_id int);

INSERT INTO MOVIE_ACTOR (`movie`, `actor`) VALUES
(1, 1),
(1, 2),
(2, 1),
(2, 2);

good luck!