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);
}

Figure it out how use it. I change the SQL query to below

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
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.