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

    Join Date
    May 2015
    Posts
    38
    Rep Power
    2

    Database Joins, Loops and Flags, Flags, Flags


    I got a large project that I'm working on and I'm getting lost on how to implement this last bit of code to flag off duplicate data from the database because of JOIN statements.



    Everything works except for this section:
    PHP Code:
    if($currentLocation!=$dbChartProjectLocation){
                                
    $currentLocation=$dbChartProjectLocation;
                                
    $charts.=$dbChartProjectLocation;
                                
    $charts.=',';
                            }
                            elseif(
    $dbChartRole!=$currentRole && $dbChartPersonName!=$currentPerson){
                                
    $charts.=';';

    This is supposed to loop the location column in the database and display a list of locations for each project. Instead I get this:



    So the proper end result should look something like this:

    (Start: 6/26/2016) Filming in Paris, France, Test, AAsd, Testing, 123, Testing, 1234; Aspyr Entertainment (AJinNYC2112@yahoo.com) - Address, Egg Harbor Township, NJ 08234; Phone: phone#; Producer: Names; Associate Producer: Names

    PHP Code:
    //Order By Project Name, Job Title, and by Updated Date on All Pages
                
    $alteredOrder='ORDER BY '.$Ordered.'`projects`.`updated` DESC, `projects`.`name`, `project_roles`.`role` ASC, `project_roles`.`name` ASC, `project_locations`.`location` ASC';
                
                
    //Addition columns to get from Database
                
    $columns='`roles`.`name` as `role`, `project_roles`.`name`, `project_status`.`statusname`, `project_details`.`genre`, `project_details`.`startdate`,
                `project_locations`.`location`, `project_details`.`email`, `production_companies`.`name` as `companyname`, `production_companies`.`phone`, `production_companies`.`zip`, 
                `production_companies`.`address`, `production_companies`.`city`, `production_companies`.`state`'
    ;
                
                
    //Query Chunk to Join the Necessary Tables
                
    $chartsJoinClause='LEFT JOIN `project_details` ON `project_details`.`projectid` = `projects`.`id` 
                LEFT JOIN `project_roles` ON `project_roles`.`projectid` = `projects`.`id`
                LEFT JOIN `roles` ON `roles`.`id` = `project_roles`.`role`
                LEFT JOIN `project_status` ON `project_details`.`status` = `project_status`.`id`
                LEFT JOIN `production_companies` ON `production_companies`.`id` = `project_details`.`companyid`
                LEFT JOIN `project_locations` ON `projects`.`id` = `project_locations`.`projectid`'
    ;
                
                
    //Combine Query into One Query.
                
    $chartsCompiledQuery trim($chartsJoinClause).' '.trim($alteredOrder);

                
    //Do the Heavy Lifting of Getting the Project Data
                
    $chartsQueryExecuted projectsSQL($conn$numOfProjectsReturned$chartsPrepared$chartsCompiledQuerynull$columns);
                
                
    //Bind Results from projectsSQL - $projectsPrepared and $numOfProjectResults are passed back by Reference
                
    mysqli_stmt_bind_result($chartsPrepared$dbChartProjectName$dbChartID$dbChartDate$dbChartUpdated
                
    $dbChartRole$dbChartPersonName$dbChartStatus$dbChartGenre$dbChartStartDate$dbChartProjectLocation$dbChartProdCompEmail
                
    $dbChartProductionCompany$dbChartProdCompPhone$dbChartProdCompZip$dbChartProdCompAdd$dbChartProdCompCity$dbChartProdCompState);
                
                
    //Generate the Charts
                
    $loopCounter=0;
                
    $charts=null;
                
    $currentTitle=null;
                
    $currentRole=null;
                
    $currentPerson=null;
                
    $currentLocation=null;
                if(
    $numOfProjectsReturned >= && $chartsQueryExecuted===true){
                    
    $charts.='<table id="chartsTable"><tbody>';
                        while(
    mysqli_stmt_fetch($chartsPrepared)){
                            
                            
    //Determine which stylized row to show
                            
    $firstRow = ($loopCounter=='FirstRow' null);
                            
                            if(
    $dbChartProjectName!=$currentTitle){
                                
    //Reset Current Role to Null When We Start Looping through a New Project
                                
    $currentRole=null;

                                
    //Close the Table Row from the Prior Project If there is More than One Project
                                
    if($currentTitle!=null){
                                
    $charts.='</td>';
                                
    $charts.='</tr>';
                                }
                            
    $charts.='<tr class="projectRowTop">';
                            
    $charts.='<td class="chartsProjectName'.$firstRow.'">'.$dbChartProjectName;
                            
    $charts.=(isset($dbChartGenre) && ($dbChartGenre!=null || $dbChartGenre!='') ? ' ('.$dbChartGenre.')' null);
                            
    $charts.=(isset($dbChartStatus) && ($dbChartStatus!=null || $dbChartStatus!='') ? ' - '.$dbChartStatus null);
                            
    $charts.='</td>';
                            
    $charts.='<td class="chartsProjectUpdated'.$firstRow.'">Last Updated: '.date("F jS, Y"strtotime($dbChartUpdated)).'</td>';
                            
    $charts.='</tr>';
                            
    $charts.='<tr class="projectRowBottom">';
                            
    $charts.='<td class="chartsProjectDetails'.$firstRow.'" colspan="2">';
                            
    $charts.='(Start: '.date("n/j/Y"strtotime($dbChartStartDate)).') ';
                            
    $charts.=(isset($dbChartProjectLocation) && ($dbChartProjectLocation!=null || $dbChartProjectLocation!='') ? 'Filming in ' null);
                            }
                            if(
    $currentLocation!=$dbChartProjectLocation){
                                
    $currentLocation=$dbChartProjectLocation;
                                
    $charts.=$dbChartProjectLocation;
                                
    $charts.=',';
                            }
                            elseif(
    $dbChartRole!=$currentRole && $dbChartPersonName!=$currentPerson){
                                
    $charts.=';';
                            }
                            if(
    $dbChartProjectName!=$currentTitle){
                            
    $charts.=(isset($dbChartProductionCompany) && ($dbChartProductionCompany!=null || $dbChartProductionCompany!='') ? $dbChartProductionCompany null);
                            
    $charts.=(isset($dbChartProdCompEmail) && ($dbChartProdCompEmail!=null || $dbChartProdCompEmail!='') ? ' ('.$dbChartProdCompEmail.') ' null);
                            
    $charts.=(isset($dbChartProdCompAdd) && ($dbChartProdCompAdd!=null || $dbChartProdCompAdd!='') ? '- '.$dbChartProdCompAdd.', ' null);
                            
    $charts.=(isset($dbChartProdCompCity) && ($dbChartProdCompCity!=null || $dbChartProdCompCity!='') ? ''.$dbChartProdCompCity.', ' null);
                            
    $charts.=(isset($dbChartProdCompState) && ($dbChartProdCompState!=null || $dbChartProdCompState!='') ? ''.$dbChartProdCompState.' ' null);
                            
    $charts.=(isset($dbChartProdCompZip) && ($dbChartProdCompZip!=null || $dbChartProdCompZip!='') ? ''.$dbChartProdCompZip.';' null);
                            
    $charts.=(isset($dbChartProdCompPhone) && ($dbChartProdCompPhone!=null || $dbChartProdCompPhone!='') ? ' Phone: '.$dbChartProdCompPhone.'; ' null);
                            
                            
    //Assign the Current Project Name from the Database to $currentTitle So We Only Show the Project Name Once
                            
    $currentTitle=$dbChartProjectName;
                            }
                            
                            
    //Determine If We need a Semi-Colon or a Comma based on Entering Another Job Title or Not
                            
    if($dbChartRole!=$currentRole){
                                
    //If $currentRole Does Not Equal Null It's the End of that Job Title's List of Individuals, so Add a Semi-Colon 
                                
    if($currentRole!=null){
                                
    $charts.='; ';
                                
    $currentLocation=null;
                                }
                                
    //Assign the Current Job Title to $currentRole, so We Can Prevent the Title Being Shown More than Once, and Prevent a Semi-Colon From Showing
                                
    $currentRole=$dbChartRole;
                                
    //Since This Is the First Time Seeing This Job Title for This Project, Show it
                                
    $charts.=$dbChartRole.': ';
                                
    $currentPerson=null;
                            }
                            elseif(
    $currentPerson!=$dbChartPersonName){
                                
    $charts.=', ';
                            }
                            
                            
    //Finish Off the Chart Row with the names of people for each job position; If They're Not Redundant
                            
    if($currentPerson!=$dbChartPersonName){
                            
    $charts.=$dbChartPersonName;
                            
    $currentPerson=$dbChartPersonName;
                            }
                        
                        
    //Increment Counter for Table Row Number
                        
    $loopCounter++;
                        }
                        
                        
    //Close the Table Row from the Last Project on the Page
                        
    $charts.='</td>';
                        
    $charts.='</tr>';
                
    $charts.='</tbody></table>';
                }
                else{
                    
    $charts.='<p class="center">No Projects Found.</p>';
                }
                
    //End Chart Generation 
    Here's what the database returns:



    I've been going crazy on how to implement this last bit, so I'm looking for some help on this last bit. Ideas?

    Note: The images were altered to remove personal information like the address and phone, that why the database image is lacking a few columns compared to the query in the code.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2015
    Posts
    38
    Rep Power
    2
    Here's the projectsSQL function to give some further context:

    PHP Code:
        //Function to Query Projects Table
        
    function projectsSQL($conn, &$numOfProjectResults, &$projectsPrepared$query=null$bindList=array(), $additional=null){
            
    //Add spacing to query if neccessary
            
    if(isset($query) && ($query!='' || $query!=null)){
                
    $query=' '.trim($query);
            }
            if(isset(
    $additional) && ($additional!='' || $additional!=null)){
                
    $additional=', '.trim($additional);
            }
                
                
    //Generate Final Query
                
    $finalQuery='SELECT `projects`.`name` as `projectname`, `projects`.`id`, `projects`.`date`, `projects`.`updated`'.$additional.' FROM `projects`'.$query;
                if(
    $projectsPrepared=mysqli_prepare($conn$finalQuery)){
                    
                    
    //If Array is Empty, Don't Bother Running Bind Param, as There is Nothing to Bind
                    
    if(!empty($bindList)){
                    
    //Implode Bind Variables
                    
    $bindVariables.=implode("', '",$bindList[1]);
                    
    //Bind Parameters
                    
    mysqli_stmt_bind_param($projectsPrepared$bindList[0], $bindVariables);
                    }

                    
    //Execute Query */
                    
    mysqli_stmt_execute($projectsPrepared);
                    
                    
    //Store Result
                    
    $successfulResult mysqli_stmt_store_result($projectsPrepared);
                    
                    
    //Get Total Number of Projects Returned
                    
    $numOfProjectResults mysqli_stmt_num_rows($projectsPrepared);
                }
                
                if(
    $successfulResult!==false){
                    return 
    true;
                }
                return 
    false;
        } 
    I abstracted this part of the query out because it's used in multiple areas of the site.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2015
    Posts
    38
    Rep Power
    2
    I just pulled the Locations table out from the query and dropped another query inside the if($dbChartProjectName!=$currentTitle){} block that specifically does the locations. It only runs once per project that way; so it's not a ton more overhead.

    I know the preferred method is to do everything in one query, but I just couldn't see a viable way to do it any other way. If anybody comes up with a solution on how to do it with the left join and one query, I am open to it.
  6. #4
  7. Lord of the Dance
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Oct 2003
    Posts
    4,092
    Rep Power
    1965
    What makes it complex is that you want the values from project_roles to be their own column.

    In short version, you have this table data
    Code:
    Role			Name
    Producer		Name1
    Producer		Name2
    Producer		Name3
    Secondary Producer	Name2
    Secondary Producer	Name4
    Which you want to display like this:
    Code:
    Producer		Secondary Producer
    Name1,Name2,Name3	Name2,Name4
    To accomplish this, you have to look at what is called pivot table, which is described further here:
    Pivot table with dynamic columns in MySQL - Stratos Blog

    Comments on this post

    • AJinNYC2112 agrees : Got me on the right track.
  8. #5
  9. No Profile Picture
    Super Moderator
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2009
    Location
    Hartford, WI
    Posts
    1,406
    Rep Power
    24
    It is simply because the only LOOP you are running is the entire row it is on. You have nothing grouped in any manner. If you are aiming to build a single item with multiple rows, having each column grouped on the output, I would suggest a multi-dimensional array. You can build its structure in a static manner to make referencing easy, as well as check inner arrays for duplicate items to help avoid saving/posting such. The outer array would simply hold the overall projects. Each of which would have perhaps its own id, projectname, date, updated, and so on to reference. (Comma separated to include non-duplicate items within 1 category)

    EDIT: Nice visual MrFujin. I gotta refresh my pages a bit more often before submitting my posts...

    Comments on this post

    • MrFujin agrees : I know the feeling :) But sometimes it can be nice with different type of explanation.
    Last edited by Triple_Nothing; June 12th, 2016 at 07:23 AM.
    He who knows not that he knows not is a fool, ignore him. He who knows that he knows not is ignorant, teach him. He who knows not that he knows is asleep, awaken him. He who knows that he knows is a leader, follow him.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2015
    Posts
    38
    Rep Power
    2
    Might not be getting what you're saying but anytime I use GROUP BY I lose all rows but one for each project role. So all of the other people under each project role are not returned.
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2015
    Posts
    38
    Rep Power
    2
    Well Sh!t I managed to get it work by just using GROUP_CONCAT on the locations and the roles/positions:


    Code:
    SELECT `projects`.`name` as `projectname`, `projects`.`id`, `projects`.`date`, `projects`.`updated`, `roles`.`name` as `role`,  GROUP_CONCAT(DISTINCT `project_roles`.`name` ORDER BY `project_roles`.`name` ASC SEPARATOR ',') as `rolenames`, `project_status`.`statusname`, `project_details`.`genre`, `project_details`.`startdate`, `project_details`.`email`, `production_companies`.`name` as `companyname`, `production_companies`.`phone`, `production_companies`.`zip`, `production_companies`.`address`, `production_companies`.`city`, `production_companies`.`state`, `project_details`.`website`, GROUP_CONCAT(DISTINCT `project_locations`.`location` ORDER BY `projects`.`id` ASC SEPARATOR ',') as `locations` FROM `projects` 
    LEFT JOIN `project_details` ON `project_details`.`projectid` = `projects`.`id` 
    LEFT JOIN `project_roles` ON `project_roles`.`projectid` = `projects`.`id` LEFT JOIN `roles` ON `roles`.`id` = `project_roles`.`role` 
    LEFT JOIN `project_status` ON `project_details`.`status` = `project_status`.`id` 
    LEFT JOIN `production_companies` ON `production_companies`.`id` = `project_details`.`companyid` 
    LEFT JOIN `project_locations` ON `project_locations`.`projectid` = `projects`.`id`GROUP BY `project_roles`.`role`, `projects`.`id` 
    ORDER BY `projects`.`updated` DESC, `projects`.`name`, `project_roles`.`role` ASC, `project_roles`.`name` ASC
    This got me on the right track:
    Pivot table with dynamic columns in MySQL - Stratos Blog
  14. #8
  15. Lord of the Dance
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Oct 2003
    Posts
    4,092
    Rep Power
    1965
    Nice to see you got it working.

    Just be careful when using the lazy-syntax for GROUP BY, where you omit fields which are selected but not part of an aggregate function:
    MySQL :: MySQL 5.7 Reference Manual :: 13.20.3 MySQL Handling of GROUP BY

IMN logo majestic logo threadwatch logo seochat tools logo