Kushal Gupta Kushal Gupta - 1 year ago 42
MySQL Question

Adding foreign key dynamically in a database via php

Ok I am new in php development so I did some mistakes, didnt planned my database just started because I was so excited about my first project, SO dont judg me.. :D :D

this is my Primary database
Primary Database

enter image description here

and this is my Movies database,

Movies database

enter image description here

so I created the songs database first and stored the data in it, after that i created the movies database and movie_id is the foreign key of the id of movies database, so there are too many songs to add foreign keys in primary database, I want a php script which can insert the foreign key for me in my primary database,

I created a php script ( actually tried for several hours) but didnt succseeded

I wanted to
get movie_name from songs database
match with the movie_name of movies databse
if both movies matches
insert the id of movies database into the movie_id(foreign key) of song database

require_once ('../inc/db.php') ?>


$lang_query = " SELECT * FROM songs";
$query = "UPDATE songs SET movie_id = '$mov_id'";
$lang_run = mysqli_query($conn, $lang_query);
$mov_query = " SELECT * FROM movies";
$mov_run = mysqli_query($conn, $mov_query);
$mov_row = 1;
$lang_row = 1;

while ($mov_row = mysqli_fetch_array($mov_run))
$mov_name = $mov_row['movie_name'];
$mov_id = $mov_row['id'];
while ($lang_row = mysqli_fetch_array($lang_run))
echo $movie_name = $lang_row['movie_name'];
$movie_id = $lang_row['id'];
if ($movie_name == $mov_name)
mysqli_query($conn, "UPDATE songs SET movie_id = '$mov_id' where id = '$movie_id'");
} ?>

Please Help me, Thanks :)

Answer Source

I think you can do it by running following sql query -

update *songs* inner join *movies* on songs.movie_name=movies.movie_name set songs.movie_id=movie.id