How to write where statement with INNER JOIN to view specific data PHP

I have two table table 1- user_table It have UserId, name ,emali .

and table 2

movies table it have movies_id ,name,image ,UserId.

for example what I need to do .. I see now (movie number-1) so with (movie number -1) I want show data of user from user_table. data of the user who posted this movie.

In table of movies I was create field of UserId that UserId same number in user_table. So there is a link between tables of movie field and user_table

I write code same that :

<?php
include ("connt.php");
$movie_id = strip_tags(trim($_GET["movie_id"]));
$sql="SELECT movies.movie_id, users_table.UserId ,users_table.name ,users_table.email 
FROM movies INNER JOIN users_table 
ON movies.UserId=users_table.UserId WHERE users_table.UserId = movies.UserId";
$result=mysqli_query($con,$sql);

$data=array();
while($row=mysqli_fetch_assoc($result)){
$data["data"][]=$row;

}


    echo json_encode($data);

?>

It works but displays all movies the user has written.I don’t need to view all the movies the user has written. I just need the data for one time, based on the movie ID or on the movie that I’m watching.

It works as follows now :

{"data":[{"movie_id":"103","UserId":"16","name":"z","email":"z"},{"movie_id":"202","UserId":"16","name":"z","email":"z"}]}

And now I watching movie number3 it’s have id 103 so what I need now like that:

{"data":[{"movie_id":"103","UserId":"16","name":"z","email":"z"}

Answer

You have to add a filter to the where statement.

Replace the where with this:

WHERE users_table.UserId = movies.UserId AND movie_id = $movie_id

I added the movie_id = $movie_id to tell the DB that filter rows by movie_id.

Note: To avoid MYSQL injection you must use the MYSQLI object to run the query instead the function.

The final code to avoid MySQL injection look like this:

$stmt = $con->prepare("SELECT movies.movie_id, users_table.UserId ,users_table.name ,users_table.email 
FROM movies INNER JOIN users_table 
ON movies.UserId=users_table.UserId WHERE users_table.UserId = movies.UserId AND movie_id = ? ");
$stmt->bind_param("i", $movie_id);
$result=$stmt->execute();

To read more about MYSQL injection this.