#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2006
    Posts
    199
    Rep Power
    28

    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); 
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,911
    Rep Power
    1045
    Hi,

    you need to start indenting your code properly. I can barely read this.

    There's two options: You can concatenate names in the query itself. This is the most convenient approach in this case as it doesn't require any special logic in the application.

    sql Code:
    SELECT
    	films.title
    	, films.release
    	, films.poster
    	, films.synopsis
    	, films.STATUS
    	, GROUP_CONCAT(CAST.name SEPARATOR ', ') AS castlist	-- you may wanna ORDER the names
    FROM
    	films
    	LEFT JOIN CAST
    		ON CAST.filmID = films.id
    GROUP BY
    	films.id
    ;

    However, this only gives back a plain list. If you plan to do anything fancy with the cast list (like turning it into links), don't use this approach.

    The alternative would be to generate the list in the application like you kinda began. However, that's rather ugly, because you need to save all values of the current row in additional variables so that you can output the last film when it has ended.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2006
    Posts
    199
    Rep Power
    28
    Yeah, I really need the second option.


    Here's the current result: http://www.andrewmccarrick.com/kam/?p=films

    It's close but all the names after the first are being placed outside of the ending <div> tags.

    PHP Code:

    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)
                        {
                            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 Film Synopsis Film Synopsis Film SynopsisFilm Synopsis Film Synopsis Film Synopsis Film Synopsis Film Synopsis Film SynopsisFilm Synopsis Film Synopsis Film Synopsis Film Synopsis Film Synopsis Film SynopsisFilm Synopsis Film Synopsis Film Synopsis Film Synopsis Film Synopsis Film SynopsisFilm Synopsis Film Synopsis Film Synopsis Film Synopsis Film Synopsis Film SynopsisFilm Synopsis Film Synopsis Film Synopsis Film Synopsis Film Synopsis Film SynopsisFilm Synopsis Film Synopsis Film Synopsis Film Synopsis Film Synopsis Film SynopsisFilm Synopsis Film Synopsis Film Synopsis Film Synopsis Film Synopsis Film SynopsisFilm Synopsis Film Synopsis Film Synopsis Film Synopsis Film Synopsis Film SynopsisFilm Synopsis Film Synopsis Film Synopsis Film Synopsis Film Synopsis Film SynopsisFilm Synopsis Film Synopsis Film Synopsis Film Synopsis Film Synopsis Film SynopsisFilm Synopsis Film Synopsis Film Synopsis Film Synopsis Film Synopsis Film SynopsisFilm Synopsis Film Synopsis Film Synopsis Film Synopsis Film Synopsis Film SynopsisFilm Synopsis Film Synopsis Film Synopsis Film Synopsis Film Synopsis Film SynopsisFilm Synopsis Film Synopsis Film Synopsis Film Synopsis Film Synopsis Film SynopsisFilm Synopsis Film Synopsis Film Synopsis Film Synopsis Film Synopsis Film SynopsisFilm </div>
                            </div>
                            <div id="filmLinksCast">
                            <div class="filmLeft" id="filmLinks">Website | IMDB | Facebook</div>
                            <div class="filmRight" id="filmCast"><span class="bold">Cast:</span> '
    ;
                        }
                            
    $content.=$cast.', ';
                        if(
    $titleCheck!=$title)
                        {
                            
    $titleCheck=$title;
                            
    $content.='</div>
                            </div>
                            </div>'
    ;
                        }
                
    $i++;
                }
            }
            else
            {
            
    $content.='<div>Could not find any films.</div>';
            }
        }
        else
        {
        
    $content.='<div id="queryFailed">Query failed to run correctly.</div>';
        } 
    Last edited by HDFilmMaker2112; July 7th, 2013 at 11:08 AM.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2009
    Posts
    667
    Rep Power
    6
    I'd say build your $cast independantly via a loop on a row count. And as far as the commas go, don't add them after the cast names. The first one will just be a cast name, and if there is more than one, the added name(s) will START with a comma space.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2006
    Posts
    199
    Rep Power
    28
    Originally Posted by Triple_Nothing
    I'd say build your $cast independantly via a loop on a row count. And as far as the commas go, don't add them after the cast names. The first one will just be a cast name, and if there is more than one, the added name(s) will START with a comma space.
    I'll have to admit... I have no idea how to do that.

    How do I row count a specific title from the query?



    I get what you're saying about the commas:
    PHP Code:
    $castlist=$cast;
    $castlist.=', '.$cast;

    Last edited by HDFilmMaker2112; July 7th, 2013 at 12:57 PM.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2006
    Posts
    199
    Rep Power
    28
    Originally Posted by Jacques1
    Hi,

    you need to start indenting your code properly. I can barely read this.

    There's two options: You can concatenate names in the query itself. This is the most convenient approach in this case as it doesn't require any special logic in the application.

    sql Code:
    SELECT
    	films.title
    	, films.release
    	, films.poster
    	, films.synopsis
    	, films.STATUS
    	, GROUP_CONCAT(CAST.name SEPARATOR ', ') AS castlist	-- you may wanna ORDER the names
    FROM
    	films
    	LEFT JOIN CAST
    		ON CAST.filmID = films.id
    GROUP BY
    	films.id
    ;

    However, this only gives back a plain list. If you plan to do anything fancy with the cast list (like turning it into links), don't use this approach.

    The alternative would be to generate the list in the application like you kinda began. However, that's rather ugly, because you need to save all values of the current row in additional variables so that you can output the last film when it has ended.
    You know what... I'm just going to go with the Concat method, and then if I need to do anything with the data afterwards, I'll just explode out the variable into an array.

    Works like a charm this way. Thanks a ton.
    Last edited by HDFilmMaker2112; July 7th, 2013 at 09:17 PM.

IMN logo majestic logo threadwatch logo seochat tools logo