PHP MySQL Combine Multiple Rows from query into one result with 1 similar column

Question

So we have a database that is used for history purposes to display businesses with their start and end dates, affiliated persons, historical notes, and associated newspaper clippings. Currently, when a user searches, for example (See Current Results), Arnold & Dunn, 2 results return, one with the affiliated person as 'Arnold', and the other with the affiliated person as 'Dunn". Group in the Sql Query on returns the first affiliated person. I was wondering how do you take x number of results for names and basically Concat them in PHP to show under one company result?

Current Results:

Arnold & Dunn Bicycle sales

Affiliated Person: Edwin L. Arnold

Year Established: 1886

Year Closed: 1888

Product/Service: Sporting Goods Stores

NAICS: 451110

Arnold & Dunn Bicycle sales

Affiliated Person: James R. Dunn

Year Established: 1886

Year Closed: 1888

Product/Service: Sporting Goods Stores

NAICS: 451110

Expected Results:

Arnold & Dunn Bicycle sales

Affiliated Person: Edwin L. Arnold, James R. Dunn

Year Established: 1886

Year Closed: 1888

Product/Service: Sporting Goods Stores

NAICS: 451110

PHP Search/Results Script:

<?php
require_once 'php/db.php';
$conn = dbConnect();
$OK = true;

if (isset($_GET['name'])) {
    $data = "%".$_GET['name']."%";
    $sql = 'SELECT *
                    FROM company
                    LEFT JOIN xref_ap_comp
                    ON company.CompId=xref_ap_comp.CompID
                    LEFT JOIN persons
                    ON xref_ap_comp.APID=persons.APID
                    LEFT JOIN xref_comp_prod2
                    ON company.CompId=xref_comp_prod2.CompID
                    LEFT JOIN product
                    ON xref_comp_prod2.NAICS=product.NAICS
                    LEFT JOIN product2
                    ON product.NAICSThreeDigitCode=product2.NAICSThreeDigitCode
                    LEFT JOIN product3
                    ON product2.NAICSRootCode=product3.NAICSRootCode
                    WHERE CompName like ? 
                    ORDER BY company.CompName';
    $stmt = $conn->prepare($sql);
    $results = $stmt->execute(array($data));
    $rows = $stmt->fetchAll();
    $error = $stmt->errorInfo();
}
if(empty($rows)) {
    echo "<h3>No Records Found</h3>";
}
else {  foreach ($rows as $row) {
        echo "<h3 class=\"ui-accordion-header ui-state-default ui-corner-all ui-accordion-icons\" role=\"tab\" id=\"ui-id-1\" aria-selected=\"false\" aria-expanded=\"false\" tabindex=\"0\">".$row['CompName']."<span class=\"ui-accordion-header-icon ui-icon ui-icon-triangle-1-e\"></span></h3>";
                    echo "<div>";
                    echo "<p>".$row['HistoricalNotes']."</p>";
                    echo "<ul>";
                    echo "<li>Affiliated Person: ".$row['APFirstName']." ".$row['APLastName']."</li>";
                    echo "<li>Year Established: ".$row['StartDate']."</li>";
                    echo "<li>Year Closed: ".$row['EndDate']."</li>";
                    echo "<li>Product/Service: ".$row['ProductCategory']."</li>";
                    echo "<li>NAICS: ".$row['NAICS']."</li>";
                    echo "</ul>";
                    echo "</div>";

    }
}

I am guessing in the echo "<li>Affiliated Person: ".$row['APFirstName']." ".$row['APLastName']."</li>";there would have to be some count object in order to recieve comma deliniated results (Affiliated Person: Edwin L. Arnold, James R. Dunn). Please let me know if there is any other required information needed to help on this issue.

function called [`GROUP_CONCAT`](https://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat) in mySQL.. You would have to replace `SELECT *` with each field and then use group_concat on a concat of the first and last names. You would also have to add a group by clause to your query for all the other fields otherwise you may get multiple records; which is what I believe you're trying to eliminate. Prior example on site: http://stackoverflow.com/questions/13451605/how-to-use-group-concat-in-a-concat-in-mysql — xQbert, Aug 19 '15 at 15:31

score 0 · Answer 1 · answered Aug 19 '15 at 16:21

One solution is to create a multi-dimensional array where persons are grouped by company.

$companies = array();
foreach ( $rows as $row ) {
    // Group persons by company name
    $companies[$row['CompName']][$row['CompId']] = $row;    
}
foreach ( $companies as $company => $persons ) {
    echo "<h3 class=\"ui-accordion-header ui-state-default ui-corner-all ui-accordion-icons\" role=\"tab\" id=\"ui-id-1\" aria-selected=\"false\" aria-expanded=\"false\" tabindex=\"0\">".$company."<span class=\"ui-accordion-header-icon ui-icon ui-icon-triangle-1-e\"></span></h3>";   
    foreach ( $persons as $person ) {
        echo "<div>";
        echo "<p>".$person['HistoricalNotes']."</p>";
        echo "<ul>";
        echo "<li>Affiliated Person: ".$person['APFirstName']." ".$person['APLastName']."</li>";
        echo "<li>Year Established: ".$person['StartDate']."</li>";
        echo "<li>Year Closed: ".$person['EndDate']."</li>";
        echo "<li>Product/Service: ".$person['ProductCategory']."</li>";
        echo "<li>NAICS: ".$person['NAICS']."</li>";
        echo "</ul>";
        echo "</div>";
    }
}

PHP MySQL Combine Multiple Rows from query into one result with 1 similar column

Current Results:

Expected Results:

PHP Search/Results Script:

1 Answers1