CODEWITHSUNDEEP
phpmysqlarraysinner-join
Bhaskar Wankhede
Bhaskar Wankhede

Reputation: 69

PHP MYSQL array

I'm running the following code in PHP to generate the random list of movies from the database but since value of genere is an array it is just capturing the first value of the array.

How can I modify the query so that I can get all the values of genere in comma separated line. 

      $q = "SELECT *
            FROM
              title
              INNER JOIN title_genere ON (title.id = title_genere.id_title)
              INNER JOIN genere ON (title_genere.id_genere = genere.id)
            ORDER BY RAND()  
            LIMIT 8";

$result = mysql_query($q);
while ($row = mysql_fetch_array($result)) {

    $id = $row['id'];
    $title = $row['title'];
    $year = $row['year'];
    $poster = $row['poster'];
    $poster = str_replace("./", "lib/", $poster);
    $genere = $row['genere'];

    echo "<div id='a'>";
    echo "<div id='b'>".$title.'</div>';
    echo "<div id='c'>".$year.'</div>';
    echo "<div id='d'><a href='select.php?movieid=$id'><img src='$poster' alt='' border='1' align='center' width='214' height='314' /></a></div>";
    echo "<div id='e'>".$genere.'</div>';
    echo "</div>";
    //var_dump($genere);
}

Upvotes: 0

Views: 297

Answers (3)

Bhaskar Wankhede
Bhaskar Wankhede

Reputation: 69

Sorted out. I use below code to generate what I want. Thanks everyone for the help

SELECT 
      title.id,
      title.title,
      GROUP_CONCAT(genere SEPARATOR ' | ') AS genere,
      title.`year`,
      title.poster
    FROM
      title
      INNER JOIN title_genere ON (title.id = title_genere.id_title)
      INNER JOIN genere ON (title_genere.id_genere = genere.id)
    GROUP BY
      title.id,
      title.title,
      title.`year`,
      title.poster
    ORDER BY
      RAND()
    LIMIT 8

Upvotes: 0

Dmitry Ovsyanko
Dmitry Ovsyanko

Reputation: 1416

Don't use GROUP_CONCAT/GROUP BY, it causes overhead.

First, select your 8 random titles (from the titles table only) into an associative array indexed by title id. Let's name it $idx.

Next, prepare the id list, say, as $ids variable.

Then, run the query

SELECT 
 title_genere.id_title
 , genre.genre
FROM
 title_genre
 INNER JOIN genre ON title_genere.id_genere = genere.id
WHERE 
 title_genere.id_title IN ($ids)
ORDER BY
 genre

and harvest genre values in lists in $idx: 

$idx[$row['id_title']]['genres'][] = $row['genre']

Finally, generate HTML from $idx.

Upvotes: 0

TimWolla
TimWolla

Reputation: 32701

Try this one:

$q = "SELECT 
          *, 
          GROUP_CONCAT(CAST(title_genere AS BINARY) SEPARATOR ',') AS generes
      FROM
          title
          INNER JOIN title_genere ON (title.id = title_genere.id_title)
          INNER JOIN genere ON (title_genere.id_genere = genere.id)
      ORDER BY RAND()  
      GROUP BY title.id
      LIMIT 8";

This should select a comma-separated list on generes into the key generes. For more information see the manual at http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

Upvotes: 4

Related Questions