Explode group_concat and create individual links?

[imxuk]

Thanks to R937 for helping out with the DB query, ive managed to add to the response slightly... however now im not too sure about how to approach this from a PHP angle

I have the following DB query:

SELECT movie.id
     , movie.title
     , genres
     , genreids
  FROM movie
INNER
  JOIN series
    ON series.id = movie.seriesid
INNER
  JOIN studio
    ON studio.id = movie.studioid
INNER
  JOIN (
       SELECT moviegenre.movieid
            , GROUP_CONCAT(genre.genrename ORDER BY genre.genrename SEPARATOR ', ') as genres
         FROM moviegenre
       INNER
         JOIN genre
           ON genre.id = moviegenre.genreid
       GROUP
           BY moviegenre.movieid
       ) as mg
    ON mg.movieid = movie.id
INNER
  JOIN (
       SELECT moviegenre.movieid
            , GROUP_CONCAT(genre.id ORDER BY genre.genrename SEPARATOR ', ') as genreids
         FROM moviegenre
       INNER
         JOIN genre
           ON genre.id = moviegenre.genreid
       GROUP
           BY moviegenre.movieid
       ) as mgid
    ON mgid.movieid = movie.id
ORDER BY title

Which returns the following:

+----+--------+----------------------------+------------+--------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------+
| id | xid | title | pubdate | genres | genreids |
+----+--------+----------------------------+------------+--------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------+
| 28 | xx101 | xx101 - test | 2007-01-10 | Action, Comedy, Thriller | 21, 23, 15

Now....the tricky bit For each movie that is returned, i need to display with its lists of genres. But i need these genre's to be links, so if a user clicks on it it redefines the search to only display movies that contain that genre.

Am ok with doing the post's etc, its just returning these as individual variables, that can then link to its corresponding 'genreid'...

Can anyone point me in the right direction? Would explode be the right way to go? Am using Smarty...not sure if theres any easy(er) way to do this...

Any help is appreciated, cheers

[imxuk]

Ok... retrieving arrays as follows:

$moviesearchresult = @mysql_query($searchsqlselect);

$i=0;
while ($r=@mysql_fetch_array($moviesearchresult)) {
$id[$i] = $r['id'];
$title[$i] = $r['title'];
$genres[$i] = $r['genres'];
$genreids[$i] = $r['genreids'];
$i++;
}

Am guessing i need something to explode the $genres and $genreids, may have to ditch Smarty... cant quite see how im going to keep everything seperate, ie code/templates :/

[Hypnos]

<?php
$movies = "Horror, Action, Drama";
$moviesarray = explode(", ", $movies);
var_dump($moviesarray);
// array(3) { [0]=> string(6) "Horror" [1]=> string(6) "Action" [2]=> string(5) "Drama" }
foreach($moviesarray as $arr) {
    $movieshtmlfriendly .= '<a href="movies.php?genre=' . $arr . '">' . $arr;
    $movieshtmlfriendly .= '</a>, ';
}
$movieshtmlfriendly = substr($movieshtmlfriendly, 0, strlen($movieshtmlfriendly) - 2);
var_dump($movieshtmlfriendly);
// Horror, Action, Drama as links

There should never be anything stoping you from abstracting your logic from your HTML. Just look at all the major PHP applications that do it.

Remeber, smarty has basic functions (like loops) that you can get away with using, without completely losing that abstraction.

[imxuk]

Cheers, ill have a play

[imxuk]

After assigning to smarty, the following works in the template..... more actions in the template file than id like... but works until i can figure something better:

<table width=100%>
<tr align="center">
<td>
{section name=searchresult loop=$id}
{$title[searchresult]}
<br />
{assign var=genre value=", "|explode:$genres[searchresult]}
{assign var=genreid value=", "|explode:$genreids[searchresult]}
{section name=genre loop=$genre}
<a href=search.php?genreid={$genreid[genre]}>{$genre[genre]}</a>
{/section}
<br />
<br />
{/section}
</td>
</tr>
</table>

[Hypnos]

Yeah, I agree. That's more logic than I would use in a template.

Remeber, you can do these operations before you feed the data in to the array.

$moviesearchresult = @mysql_query($searchsqlselect);

$i=0;
while ($r=@mysql_fetch_array($moviesearchresult)) {
$id[$i] = $r['id'];
$title[$i] = $r['title'];
$genreshtml[$i] = movielink($r['genres'], $r['genreids']);
$i++;
}



function movielink($movies, $id) {

    $moviesarray = explode(", ", $movies);
    $idarray = explode(", ", $id);

    foreach ($moviesarray as $key => $arr) {
        $movieshtmlfriendly .= '<a href="search.php?genreid=' . $idarray[$key] . '">' . $arr;
        $movieshtmlfriendly .= '</a>, ';
    }
    return ($movieshtmlfriendly);
}