user3452547 user3452547 - 7 months ago 5
SQL Question

Why the below query returns all the records instead of one?

I have written below query to fetch the records from DB that matches the id value. But this query return all the records instead of only one record whose id =1.

$movie_id=$_GET['id']; //assume movie_id=1
//$sql ='select * from tbl_movie where movie_id='.$_GET["id"];

$sql='SELECT M.movie_name, MC.on_screen_name, R.role, C.celebrity_name, C.celebrity_id, MI.production, MI.director,'
. ' MI.screenplay, MI.music, MI.bgm_score, Col.movie_running_time, L.language, CC.censor_certificate,'
. ' MR.movie_review_comment, A.award_name'
. ' FROM tbl_movie M INNER JOIN tbl_movie_awards MA INNER JOIN tbl_movie_details MD'
. ' INNER JOIN tbl_movie_cast MC on MC.movie_id='.$movie_id
. ' INNER JOIN tbl_actor_role R on MC.movie_role_id=R.role_id AND MC.movie_id='.$movie_id
. ' INNER JOIN tbl_celebrity C on MC.movie_celebrity_id=C.celebrity_id AND MC.movie_id='.$movie_id
. ' INNER JOIN tbl_awards A on MA.award_id=A.award_id AND MA.award_movie_id='.$movie_id
. ' INNER JOIN tbl_language L on MD.movie_language_id=L.language_id'
. ' INNER JOIN tbl_censor_certificate CC on MD.censor_id=CC.censor_id AND MD.movie_id='.$movie_id
. ' INNER JOIN tbl_movie_info MI on MI.movie_info_id='.$movie_id
. ' INNER JOIN tbl_movie_collection Col on Col.movie_id='.$movie_id
. ' INNER JOIN tbl_tt_movie_review MR on MR.movie_review_id='.$movie_id.';';

$result = $db->getData($sql);
if(!empty($result))
{
while($row=$result->fetch_assoc())
{
$movie_name=$row['movie_name'];
}

}


getData
function written in another file:

public function getData($query)
{
$result=$this->myconn->query($query);
if($result->num_rows>0)
{
return $result;
}
else
{
echo ' error in query execution'.$this->myconn->error;
}
}


I don't know what the problem is in the above query. Can any one suggest how to get correct answer?

Answer

Assuming the commented line ( //$sql ) at the top is working well, try this:

$sql='SELECT M.movie_name, MC.on_screen_name, R.role, C.celebrity_name, C.celebrity_id, MI.production, MI.director,'
        . ' MI.screenplay, MI.music, MI.bgm_score, Col.movie_running_time, L.language, CC.censor_certificate,'
        . ' MR.movie_review_comment, A.award_name'
        . ' FROM tbl_movie M INNER JOIN tbl_movie_awards MA INNER JOIN tbl_movie_details MD'
        . ' INNER JOIN tbl_movie_cast MC on MC.movie_id='.$movie_id
        . ' INNER JOIN tbl_actor_role R on MC.movie_role_id=R.role_id AND MC.movie_id='.$movie_id
        . ' INNER JOIN tbl_celebrity C on MC.movie_celebrity_id=C.celebrity_id AND MC.movie_id='.$movie_id
        . ' INNER JOIN tbl_awards A on MA.award_id=A.award_id AND MA.award_movie_id='.$movie_id
        . ' INNER JOIN tbl_language L on MD.movie_language_id=L.language_id'
        . ' INNER JOIN tbl_censor_certificate CC on MD.censor_id=CC.censor_id AND MD.movie_id='.$movie_id
        . ' INNER JOIN tbl_movie_info MI on MI.movie_info_id='.$movie_id
        . ' INNER JOIN tbl_movie_collection Col on Col.movie_id='.$movie_id
        . ' INNER JOIN tbl_tt_movie_review MR on MR.movie_review_id='.$movie_id
        . ' WHERE M.movie_id = '.$movie_id.';';

You're filtering the tables related with movies (INNER JOIN ... ON id = $movie_id), but the WHERE sentence filters the retrieved result.

Comments