Using GROUP_CONCAT in while loop to output multiple database entries by same user in one row

Question

Would like to use GROUP_CONCAT in order to retrieve database info (images) from a userid with multiple image entries. These images are in the form of a file path. Would then like to have the images output in a single row per userid.

After extensive research on the matter, I find simple examples of what is needed, but I get lost with the query I currently have in place.

Here is my code as of now:

<table>
                <th></th>
                <th>Name</th>
                <th>Location</th>
                <th>High School</th>
                <th class="thLargest">Colleges</th>
            </table>
            <table>
                    <?php
                        session_start();

                        $conn = mysqli_connect("", "", "", "");


                        if (mysqli_connect_errno()) {
                            echo "Failed to connect to MySQL: " . mysqli_connect_error();
                        }

                        $sql = "SELECT college.id, college.title, GROUP_CONCAT(college.image) AS cImage, users.profileImage, users.fname, users.lname, users.level AS ulevel, users.city, users.state, users.highschool, users.primarysport, usercollege.id, usercollege.collegeid 
                                FROM college, users, usercollege 
                                WHERE usercollege.collegeid = college.id AND usercollege.userid = users.id 
                                ORDER BY usercollege.createdate ASC";


                        if ($result = mysqli_query($conn, $sql)) {
                            while ($row = mysqli_fetch_assoc($result)) {


                                if ($row['status'] == null) {
                                    echo "";
                                }

                                if ($row['ulevel'] == "A Profile") {
                                    echo '<tr>' .
                                            '<td class="small">' . '<a href="http://www.example.com/aprofile.php?user="'.$row["username"] . '>' . '<img src="'.$row['profileImage'].'" />' . '</a>' . '</td>' .
                                            '<td class="large">' . $row['fname']. ", " . $row['lname'] . '</td>' .
                                            '<td class="large">' . $row['city'] . ", " . $row['state'] . '</td>' .
                                            '<td class="large">' . $row['highschool'] . " (" . $row['primarysport'] . ") " . '</td>' .
                                            '<td class="largest">' .
                                                '<div class="Limage">' . '<img src="images/colleges/'.$cImage.'"/>' . '</div>' . 
                                            '</td>' .
                                        '</tr>';
                                }

                            }


                        mysqli_free_result($result);
                        }

                        mysqli_close($conn);
                    ?>
            </table>

The image portion in question is '<img src="images/colleges/'.$cImage.'"/>' . '</div>'.

A user could have up to 10 entries of college images, so i'd like to display these images side by side, rather than displayed in a different row with the same userid.

In other words, I'd like to group multiple rows of user data from the database in a single row with no duplicate userid in the table.

I've read about exploding the cImage and using foreach as well, but that's where my lack of experience gets me.

Sal · Accepted Answer · 2017-08-05 04:39:21Z

0

SELECT users.profileImage
     , users.fname
     , users.lname
     , users.city
     , users.state
     , users.highschool
     , users.primarysport
     , group_concat(college.image) AS cImage
  FROM usercollege
    INNER JOIN users
      ON users.id = usercollege.userid
    INNER JOIN college
      ON college.id = usercollege.collegeid
  GROUP BY users.id
  ORDER BY usercollege.createdate ASC

AND

foreach( explode(',', $row['cImage']) as $img )
  echo '<div class="Limage">' . '<img src="images/colleges/'.$img.'"/>' . '</div>';

answered Aug 5, 2017 at 4:39

Sal

1,3171 gold badge8 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Michael Philibin Over a year ago

receiving a syntax error with foreach. any suggestions.

Sal Over a year ago

Should work as in this question. Maybe you are missing a ; before foreach.

Michael Philibin Over a year ago

I changed the sql query to be "SELECT......ASC"; and that helped solve the syntax error with that. Should foreach require brackets?

Michael Philibin Over a year ago

i placed the foreach code between '<td class="largest">' div in the original code. That correct?

Sal Over a year ago

Yes, but keep in mind that each php instruction (as echo) must be terminated with a semicolon. In foreach curly brackets are not required since it has a single instruction.

|

Collectives™ on Stack Overflow

Using GROUP_CONCAT in while loop to output multiple database entries by same user in one row

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related