0

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);
}
1
  • 4
    Look into using mysql's GROUP_CONCAT() function. Commented Jan 19, 2012 at 20:33

3 Answers 3

4

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

4
  • Why he should use CAST … AS BINARY? Commented Jan 19, 2012 at 20:38
  • @kuboslav I took the code from one of my projects and I have found it on the internet a long time ago. Would GROUP_CONCAT(title_genere SEPARATOR ',') AS generes be correct? Commented Jan 19, 2012 at 20:40
  • I suppose it would be better – CAST is unnecessary. Commented Jan 20, 2012 at 6:47
  • This works fine if I dont use "ORDER BY RAND()" but as soon as I add this line in SQL query it gives me error "Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in" Commented Jan 20, 2012 at 7:39
0

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.

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.