How to relate values from different tables in php mysql and display those values?

Question

Below is the code for connecting my php code to mysql. I want to display all the movie of an actor for example actor_name will display all the movie it is related to.. Can you guys help me with this problem.The output for this is when i search Actor not found..Can you check if my code in variable q4 is correct thanks.

enter image description here

actor table

enter image description here

movie table

enter image description here

movie_actor table

enter image description here

What i want is to display all the movies where actor_no 1 and 2 starred in.. thanks

<?php
$dbc = @mysqli_connect('localhost', 'root', 'black98765', 'activity_7b')
OR die("Could not connect to MySQL: " . mysqli_connect_error());
if(isset($_POST['submit'])){
$actor_query = "SELECT * FROM actor where name='$actor'";

$actor_result = mysqli_query($dbc, $actor_query);

$actor_data = mysqli_fetch_array($actor_result);

$actor_no = null;

// Check if actor already exists
if (isset($actor_data['actor_no'])) {
    $actor_no = (int)$actor_data['actor_no'];
} else {
    // Actor does not exist in the table, so we will create a new entry
    //insert into actor table
    $q = "INSERT INTO actor (name) VALUES ('$actor')";
    //execute the query to get primary key value
    $r = mysqli_query($dbc, $q);

    if ($r) {
        echo "Inserted actor successfully!";
        //assign to variable below
        $actor_no = mysqli_insert_id($dbc);
    } else {
        echo "Failed to insert actor data!";
        mysqli_error($dbc);
    }
}
//insert into movie table
$movie_query = "SELECT * FROM movie where movie_name='$movie'";

$movie_result = mysqli_query($dbc, $movie_query);

$movie_data = mysqli_fetch_array($movie_result);

$movie_no = null;
// Check if movie already exists
if (isset($movie_data['movie_no'])) {
    $movie_no = $movie_data['movie_no'];
} else {
    //movie does not exist in the table, so we will create a new entry
    //insert into actor table
    $q2 = "INSERT INTO movie (movie_name, release_year) VALUES ('$movie',DATE_FORMAT('$year','%Y-%m-%d'))";
    //execute the query to get primary key value
    $r2 = mysqli_query($dbc, $q2);

    if ($r2) {
        echo "Inserted move successfully!";
        //assign to variable below
        $movie_no = mysqli_insert_id($dbc);
    } else {
        echo "Failed to Insert Data!";
        mysqli_error($dbc);
    }


}
if (null !== $movie_no && null !== $actor_no) {
    $q3 = "INSERT INTO movie_actor (movie_no, actor_no, rate) VALUES ($movie_no, $actor_no, '$rate')";

//connect and insert $q
    $r3 = mysqli_query($dbc, $q3);
    if ($r3) {
        echo "Inserted Successfully!";
    } else {
        echo "Failed to Insert Data!";
        mysqli_error($dbc);
    }

} else {
    echo "Failed to Insert Data!";
}
}
if(isset($_POST['search'])){
     $q4 = "SELECT movie.movie_name,actor.name
            FROM movie 
            INNER JOIN actor

            WHERE movie_name = '$search_actor'";
     $r4 = mysqli_query($dbc,$q4);
     if($r4){
           while($row = mysqli_fetch_assoc($r4){
              echo "<p>Movies of Actor which he Starred In:</p>"    
              echo $row['movie_name']."<b/>";
           }
     }else{
          echo "actor not found!";
     }
}
mysqli_close($dbc);
?>

MD SHAHIDUL ISLAM · Answer 1 · 2015-03-10T06:40:34.537

0

In query

$q4 = "SELECT movie.movie_name,actor.name
        FROM movie     
        WHERE movie_name = '$search_actor'";

Change

WHERE movie_name = '$search_actor'

to

WHERE movie_name = '$_POST[search_actor]'

If global value is not set in your php.ini it should not work.

UPDATE

Need a relation between movie and actor table ex. actor_id in both table

SELECT m.movie_name, a.name
        FROM movie m, actor a     
        WHERE a.actor_id=m.actor_id AND m.movie_name = '$search_actor'

edited Mar 10 '15 at 06:40

answered Mar 09 '15 at 06:07

MD SHAHIDUL ISLAM

14,325
6
82
89

it doesnt work kindly kindly check the code in $q4 actor.name is not form movie table..i include inner join actor table – Jay Gorio Mar 09 '15 at 06:16
Never, *ever* include strings from `$_POST` directly in an SQL query! See [How can I prevent SQL injection in PHP?](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) for more information. Even if you're just writing code for your own use, and even if you're sure that it will never be publicly accessible, it's still good practice to use parametrized queries. You'll thank yourself for doing that the first time you try to search for an actor named, say, O'Brien. ;) – Ilmari Karonen Mar 09 '15 at 10:37
@salim okay thanks for the tip. But can you help me out with my problem?Thanks – Jay Gorio Mar 09 '15 at 10:45

Kamran · Answer 2 · 2015-03-09T11:14:34.977

0

You need to use join as data is coming from more than one table

$q4 = "SELECT movie.movie_name,actor.name FROM actor join movie_actor on actor.actor_no = movie_actor.actor_no join movie on movie.movie_no = movie_actor.movie_no where actor.name like '%$search_actor%';

edited Mar 09 '15 at 11:14

answered Mar 09 '15 at 06:22

Kamran

2,711
2
17
24

i will try your code and will let you know if it's working. Thanks in advanced – Jay Gorio Mar 09 '15 at 06:28
where did you get the movie_id and actor_id? – Jay Gorio Mar 09 '15 at 06:33
`movie_id` and `actor_id` is the primary key of your movie and actor table.Use your primary keys, that you are inserting in the movie_actor table. – Kamran Mar 09 '15 at 06:38
no my primary key for actor is actor_no and for movie is movie_no. For movie_actor table theres no primary key – Jay Gorio Mar 09 '15 at 06:49
I have edited my answer according to your table fields. give it a try. – Kamran Mar 09 '15 at 06:52
Run the above query in your mysql without `where` clause and see if it is working correctly or not. – Kamran Mar 09 '15 at 07:49
I will not work and i tried it..i will attach pictures for you to see what i mean.thanks – Jay Gorio Mar 09 '15 at 09:50
@JayGorio I have tried the second query posted in my answer according to your table structure and its working fine try it in your database and let me know. Thanks – Kamran Mar 09 '15 at 10:27
1

Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/72562/discussion-between-kamran-adil-and-jay-gorio). – Kamran Mar 09 '15 at 10:29
i want to search the actor and it will display all the movies they starred in okay i will display my initial form. output actor name cannot find still – Jay Gorio Mar 09 '15 at 10:32

How to relate values from different tables in php mysql and display those values?

2 Answers2