PHP, MySQL Junction Table displays information incorrectly

Question

My tables are as follows:

person2: personID validPositions: positionID, positionDesc personPositions: personID, positionID

I want to be able to display a person with their multiple positions (some may only have one position) on one line. Example: Sierra's Positions: s1, s2

Currently it displays each position they have, however on different lines AND it repeats the last position in the database twice. Example:

Sierra's Positions: S1
Sierra's Positions: S2
Sierra's Positions: S2


    $sql = "SELECT * FROM person2";
    // LEFT JOIN validTitles ON personTitle.positionID=validTitles.positionID GROUP BY person2.personID"; 

    if ($result = mysqli_query($connection, $sql)) {     
       // loop through the data

       //create 4 columns for the table
       $columns=5;
       $i = 0;


        while($row = mysqli_fetch_array($result))
        {
            // the % operator gives the remainder of a division of two values
            //   in this case, 0/4, this tells the table row to jump to a new row
            //       if there are already 4 columns in one row
            if($i % $columns == 0){

                //begin table row
                 echo "<tr>";
             } //end if

             echo '<td class="staffImage badgeText frameImage displayInLine">
                     <a href=#openModal'.$row["personID"].'><img src="images/staff/'.$row["imgName"].'.jpg"></a><br> 
                     <strong>'.$row["firstName"].'</strong>
                     <strong>'.$row["lastName"].'</strong><br>'
                     .$row["position"].

        '<div id="openModal'.$row["personID"].'" class="modalDialog">
            <div>
                <a href="#close" title="Close" class="close">X</a>
                <h2>' . $row["firstName"] . " " .
                $row["lastName"].'</h2><br>
                <img class="floatLeft" src="images/staff/'.$row["imgName"] .'.jpg">
                <p><strong>Hire Date: </strong>'.$row["hireDate"].'<br>
                <p><strong>Major: </strong>'.$row["major"].'<br>';


                     //if the field "major2" (Double Major) is not null, display it
                     if($row["major2"] != NULL)
                     {
                        echo ' & '.$row["major2"].'<br>';
                     }

                     //if the field "minor" is not null, display it
                     if($row["minor"] != NULL)
                     {
                        echo '<p><strong>Minor: </strong>'.$row["minor"].'<br>';
                     }

                     //if the field "concentration" is not null, display it 
                     if($row["concentration"] != NULL)
                     {
                        echo '<p><strong>Concentration: </strong>'.$row["concentration"];
                     }


    **$sql2 = "SELECT * FROM personPositions LEFT JOIN validPositions ON personPositions.positionID=validPositions.positionID ";

    if ($result2 = mysqli_query($connection, $sql2)) {   
       // loop through the data

        while($row2 = mysqli_fetch_array($result2))
        {
            echo '<p><strong>Position(s): </strong>'.$row2["positionDesc"];
        }//end second while**

     } //end second if

            '</div>
        </div>

    </div> ';    
              echo '</td>';

Any help is greatly appreciated, I am new to PHP and MySQL and unsure what to do!

SAMPLE DATA:

personPositions table:

personID 1 | positionID 11
personID 1 | positionID 22
personID 2 | positionID 22
personID 2 | positionID 55

validPositions table:

positionID 11 | positionDesc S1
positionID 22 | positionDesc S2
positionID 55 | positionDesc S3

Giorgos Betsos · Answer 1 · 2016-04-10T16:22:02.693

0

Something like this should work for you:

SELECT p.personID, GROUP_CONCAT(DISTINCT positionDesc ORDER BY positionDesc) AS positions
FROM person AS p
LEFT JOIN personPositions AS pp ON p.personID = pp.personID 
LEFT JOIN validPositions AS vp ON pp.positionID = vp.positionID
GROUP BY p.personID

Output:

personID  | positions
----------+-----------
1         |  S1,S2
2         |  S2,S3

Demo here

edited Apr 10 '16 at 16:22

answered Apr 10 '16 at 15:28

Giorgos Betsos

71,379
9
63
98

That displays blank Position(s): ________ For each person, there are 4 blank positions, I am assuming because there are 2 people currently in my table, with 2 positions each??? – J Adams Apr 10 '16 at 15:32
@JAdams Can you post some sample data? – Giorgos Betsos Apr 10 '16 at 15:38
I added it up top - I could not figure out how to format it correctly, however, hope it is readable – J Adams Apr 10 '16 at 15:52
I think the issue is that it is not finding the personID from the first SQL statement to go off of – J Adams Apr 10 '16 at 16:03
@JAdams The `person` table is supposed to contain all persons, or maybe not? – Giorgos Betsos Apr 10 '16 at 16:12
Yes, the **person** table contains every person – J Adams Apr 10 '16 at 16:16
@JAdams In this case it should work as expected. Please check the demo. – Giorgos Betsos Apr 10 '16 at 16:22

PHP, MySQL Junction Table displays information incorrectly

1 Answers1