SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Hybrid View

  1. #1
    SitePoint Enthusiast imxuk's Avatar
    Join Date
    Dec 2006
    Posts
    33
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Explode group_concat and create individual links?

    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:

    Code:
    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

  2. #2
    SitePoint Enthusiast imxuk's Avatar
    Join Date
    Dec 2006
    Posts
    33
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 :/

  3. #3
    SitePoint Member
    Join Date
    Dec 2006
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    <?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($movieshtmlfriendly0strlen($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.

  4. #4
    SitePoint Enthusiast imxuk's Avatar
    Join Date
    Dec 2006
    Posts
    33
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cheers, ill have a play

  5. #5
    SitePoint Enthusiast imxuk's Avatar
    Join Date
    Dec 2006
    Posts
    33
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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>

  6. #6
    SitePoint Member
    Join Date
    Dec 2006
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    PHP Code:
    $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);



Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •