CODEWITHSUNDEEP
phpmysqlselect
Nitish
Nitish

Reputation: 2763

Selecting unique records from mysql with considering only one field

My question may be not reflecting my problem, sorry for that at first. I have a problem , I need to select distinct movie names from a table also selecting its id and other records. But since ids are different for each movies, all the movies are selected(with same name ). Here's the query :

    $sql = "
            SELECT DISTINCT
                movie_id,movie_name
            FROM
                tbl_current_movies as cm, tbl_movie_hall as mh
            WHERE
                movie_active = 'active'
            AND
                cm.hall_id = mh.hall_id
        ";

$res = $this->db->returnArrayOfObject($sql,$pgin = 'no');

    var_dump($res);

And var_dump($res) says :

array
  0 => 
    object(stdClass)[48]
      public 'movie_id' => string '1' (length=1)
      public 'movie_name' => string 'MIB' (length=12)
  1 => 
    object(stdClass)[49]
      public 'movie_id' => string '2' (length=1)
      public 'movie_name' => string 'Jetuka Pator Dare' (length=17)
  2 => 
    object(stdClass)[50]
      public 'movie_id' => string '3' (length=1)
      public 'movie_name' => string 'MIB' (length=12)

So as you can see the movie MIB is showing twice, but I want to get in the results the movies MIB only once !

Upvotes: 0

Views: 291

Answers (1)

wakooka
wakooka

Reputation: 1428

Change your query to this : 

 $sql = "SELECT movie_id , movie_name
         FROM tbl_current_movies as cm 
         LEFT JOIN  tbl_movie_hall mh ON cm.hall_id = mh.hall_id
         WHERE movie_active = 'active'
         GROUP BY movie_name
        ";

You shouldn't join your tables in the WHERE clause, if you're not confortable with the JOINs you can read some of the docs : http://dev.mysql.com/doc/refman/5.0/en/join.html That should help you understand what they are for.

Upvotes: 1

Related Questions