Insert multiple actors on the same movie with MYSQL

mysql, php, sql

Solution

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 `ID`s.

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

Problem

I don't know how to ask this, I just started to learn PHP and Mysql, and I'm having a lot of trouble doing something that is easy. Let's 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, let's 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 Fight Club, Brad Pitt is on Ocean's Thirteen, And Jack Nicholson isn't 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 appreciate any help, I am lost in this one! Thanks for your attention!

Original source