Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Regular Coder
    Join Date
    May 2011
    Posts
    124
    Thanks
    12
    Thanked 6 Times in 6 Posts

    Removing duplicate MySQL data with a Flag?

    I have a MySQL query that gets data from two tables via a Join query.

    The resulting dataset is this:



    See how I have two rows of the title "Coming Out for Christmas". The only difference is the values in the "name" column. I need to essentially use the data from the first "Coming Out for Christmas" row for most of the page and then piece to together a comma separated list from the data in the "name" column.

    So I would get a list like "Test A, Test B".

    I know I need some kind of PHP flag to do this, but I don't know how or where ... any help?

    PHP Code:
    $posterurl="noposter.png";
    $titleCheck=null;
    $cast=null;
    $connected=Connect();
    if(
    $filmsQuery=mysqli_prepare($connected"SELECT `films`.`title`, `films`.`release`, `films`.`poster`, `films`.`synopsis`, `films`.`status`, `cast`.`name` FROM `films` LEFT JOIN `cast` ON `cast`.`filmID`=`films`.`id`")){
    mysqli_stmt_execute($filmsQuery);
    mysqli_stmt_bind_result($filmsQuery$title$release$poster$synopsis$status$cast);
    mysqli_stmt_store_result($filmsQuery);
    $numofFilmsReturned=mysqli_stmt_num_rows($filmsQuery);
        if(
    $numofFilmsReturned>0){
        
    $i=1;
            while(
    mysqli_stmt_fetch($filmsQuery)){
            if(
    $titleCheck!=$title){
            
    $titleCheck=$title;
            
    $castlist=$cast.", ";
            
    $castlist=rtrim($castlist",");
                if(
    $i==$numofFilmsReturned){$filmWrapper='Last';}else{}
                    if(
    $poster==null || $poster==''){$posterurl=$posterurl;}else{$posterurl=$poster;}
                    if(
    $status==0){$status="In Development";}else{$status="Completed";}
                    
    $releasedate=strtotime($release);
                    
    $releaseYear=date("Y"$releasedate);
                    
    $content.='<div id="filmWrapper'.$filmWrapper.'">
                    <div id="filmTitle">
                    <div class="filmLeft"><span class="bold">'
    .$title.'</span><br /><span class="filmStatus">('.$releaseYear.')</span></div>
                    <div class="filmRight"><span class="filmStatus">Status: '
    .$status.'</span></div>
                    </div>
                    <div id="filmPosterSynopsis">
                    <div class="filmLeft" id="filmPoster"><img src="'
    .$posterurl.'" /></div>
                    <div class="filmRight" id="filmSynopsis">Film Synopsis</div>
                    </div>
                    <div id="filmLinksCast">
                    <div class="filmLeft" id="filmLinks">Website | IMDB | Facebook</div>
                    <div class="filmRight" id="filmCast"><span class="bold">Cast:</span> '
    .$castlist.'</div>
                    </div>
                    </div>'
    ;
                    
    $i++;
                }
            }
        }
        else{
        
    $content.='<div>Could not find any films.</div>';
        }
    }
    else{
    $content.='<div id="queryFailed">Query failed to run correctly.</div>';
    }
    mysqli_close($connected); 
    Last edited by HDRebel88; 07-07-2013 at 08:05 AM.

  • #2
    Senior Coder whizard's Avatar
    Join Date
    Jan 2005
    Location
    Philadelphia, PA, USA
    Posts
    1,662
    Thanks
    14
    Thanked 76 Times in 76 Posts
    I would change how the DB was designed. You may not have this luxury, but I would set up another table which simply had two fields: movieIDs and your "name" field from above.

    Then you could just SELECT name FROM linkTable WHERE movieID = :id

    Edit:
    If you don't have the ability to set up your tables differently you could do
    SELECT name FROM table WHERE title = "Coming Out For Christmas"
    (Query simplified for purposes of simplicity, you should still use bindParam etc)


    HTH
    Dan
    Last edited by whizard; 07-07-2013 at 08:11 AM.
    PHP Tip: If you want to use short tags (<? or <?=$var) then make sure short_open_tag is set to "1". It really helps.

    Don't forget to save everyone time and mark your thread as Resolved :)

    "Also note that it is your responsibility to die() if necessary."

    DON'T USE THE MYSQL_ EXTENSION

  • #3
    Regular Coder
    Join Date
    May 2011
    Posts
    124
    Thanks
    12
    Thanked 6 Times in 6 Posts
    Quote Originally Posted by whizard View Post
    I would change how the DB was designed. You may not have this luxury, but I would set up another table which simply had two fields: movieIDs and your "name" field from above.

    Then you could just SELECT name FROM linkTable WHERE movieID = :id

    HTH
    Dan
    That's exactly how it is set-up...

    The result I posted is generated by the query. I have a films table:


    and a cast table:


    The query pulls them together into one result set. And I've always been told I shouldn't be running two individual select queries.

  • #4
    Senior Coder whizard's Avatar
    Join Date
    Jan 2005
    Location
    Philadelphia, PA, USA
    Posts
    1,662
    Thanks
    14
    Thanked 76 Times in 76 Posts
    Ah...my bad. I'll admit, I skimmed. :P

    I would build up an array first in the code, then loop through that array and put it in divs

    PHP Code:
    movies = array()
    while(
    fetching results)
    {
    movies[resultTitle]['cast'] .= ",resultCast"

    //(do this with all the other fields, they will overwrite with movie duplicates)
    movies[resultTitle]['poster'] = resultPoster 

    }

    foreach(
    movies as movie)
    {
    movie['cast'] = ltrim(movie['cast'], ",");
    }


    //NOW go through $movies and build DIV 
    Last edited by whizard; 07-07-2013 at 08:41 AM.
    PHP Tip: If you want to use short tags (<? or <?=$var) then make sure short_open_tag is set to "1". It really helps.

    Don't forget to save everyone time and mark your thread as Resolved :)

    "Also note that it is your responsibility to die() if necessary."

    DON'T USE THE MYSQL_ EXTENSION

  • #5
    Regular Coder
    Join Date
    May 2011
    Posts
    124
    Thanks
    12
    Thanked 6 Times in 6 Posts
    Yeah... I still can't get this.

  • #6
    Regular Coder
    Join Date
    May 2011
    Posts
    124
    Thanks
    12
    Thanked 6 Times in 6 Posts
    I just modified the query to use Group_concat.

    Code:
    SELECT `films`.`title`, `films`.`release`, `films`.`poster`, `films`.`synopsis`, `films`.`status`, GROUP_CONCAT(`cast`.`name` SEPARATOR ', ') AS castlist FROM `films` LEFT JOIN `cast` ON `cast`.`filmID`=`films`.`id` GROUP BY `films`.`id`


  •  

    Posting Permissions

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