Table Issue - Multiple location values for user pushes values out of row instead of wrapping in cell

[Zserene]

Hi,

My company has 240+ locations and as such some users (general managers) cover multiple sites.  When I run a query to pull user information, when the user has multiple sites to his or her name, its adds the second / third sites to the next columns, rather than wrapping it inside the same table cell.  It also works the opposite way, if a piece of data is missing in the database and is blank, its pull the following columns in.  Both cases mess up the table and formatting.  I'm extremely new to any kind of programming and maybe this isn't the forum for this question but figured I'd give it a chance since I'm stuck.

The HTML/PHP code is below:

<table id="datatables-column-search-select-inputs" class="table table-striped" style="width:100%">

                                        <thead>

                                            <tr>

                                                <th>ID</th>

                                                <th>FirstName</th>

                                                <th>LastName</th>

                                                <th>Username</th>

                                                <th>Phone #</th>

                                                <th>Location</th>

                                                <th>Title</th>

                                                <th>Role</th>

                                                <th>Actions</th>

                                            </tr>

                                        </thead>

                                        <tbody>

                                            <?php

                                            //QUERY TO SELECT ALL USERS FROM DATABASE

                                            $query = "SELECT * FROM users";

                                            $select_users = mysqli_query($connection,$query);

                                            

                                            // SET VARIABLE TO ARRAY FROM QUERY

                                            while($row = mysqli_fetch_assoc($select_users)) {

                                                $user_id = $row['user_id'];

                                                $user_firstname = $row['user_firstname'];

                                                $user_lastname = $row['user_lastname'];

                                                $username = $row['username'];

                                                $user_phone = $row['user_phone'];

                                                $user_image = $row['user_image'];

                                                $user_title_id = $row['user_title_id'];

                                                $user_role_id = $row['user_role_id'];

                                            

                                                // POPULATES DATA INTO THE TABLE

                                                echo "<tr>";

                                                echo "<td>{$user_id}</td>";

                                                echo "<td>{$user_firstname}</td>";

                                                echo "<td>{$user_lastname}</td>";

                                                echo "<td>{$username}</td>";

                                                echo "<td>{$user_phone}</td>";

                                            

                                                //PULL SITE STATUS BASED ON SITE STATUS ID

                                                $query = "SELECT * FROM sites WHERE site_manager_id = {$user_id} ";

                                                $select_site = mysqli_query($connection, $query);

                                                while($row = mysqli_fetch_assoc($select_site)) {

                                                    $site_name = $row['site_name'];

                                                    echo "<td>{$site_name}</td>";

                                                }

                                                echo "<td>{$user_title_id}</td>";

                                                echo "<td>{$user_role_id}</td>";

                                                echo "<td class='table-action'>

                                                    <a href='#'><i class='align-middle' data-feather='edit-2'></i></a>

                                                    <a href='#'><i class='align-middle' data-feather='trash'></i></a>

                                                    </td>";

                                                //echo "<td><a href='users.php?source=edit_user&p_id={$user_id}'>Edit</a></td>";

                                                echo "</tr>";

                                            }   

                                            ?>

 

                                            <tr>

                                                <td>ID</td>

                                                <td>FirstName</td>

                                                <td>LastName</td>

                                                <td>Username</td>

                                                <td>Phone #</td>

                                                <td>Location</td>

                                                <td>Title</td>

                                                <td>Role</td>

                                                <td class="table-action">

                                                <a href="#"><i class="align-middle" data-feather="edit-2"></i></a>

                                                <a href="#"><i class="align-middle" data-feather="trash"></i></a>

                                                </td>

                                            </tr>

                                        </tbody>

                                        <tfoot>

                                            <tr>

                                                <th>ID</th>

                                                <th>FirstName</th>

                                                <th>LastName</th>

                                                <th>Username</th>

                                                <th>Phone #</th>

                                                <th>Location</th>

                                                <th>Title</th>

                                                <th>Role</th>

                                            </tr>

                                        </tfoot>

                                    </table>

[Barand]

Don't run queries inside loops. Use a single with join/s to get all the data in one go.

Don't use "SELECT * ". Specify the fields you need

TEST DATA

TABLE: users
+---------+----------------+---------------+---------------+------------+---------------+--------------+
| user_id | user_firstname | user_lastname | user_username | user_phone | user_title_id | user_role_id |
+---------+----------------+---------------+---------------+------------+---------------+--------------+
|       1 | Peter          | Dowt          | peterd        | 1234       | 1             | 1            |
|       2 | Laura          | Norder        | lauran        | 2345       | 2             | 2            |
|       3 | Tom            | DiCanari      | tomd          | 3456       | 1             | 1            |
|       4 | Scott          | Chegg         | cheggs        | 4567       | 2             | 2            |
|       5 | Polly          | Vinyl         | pollyv        | 5678       | 3             | 1            |
+---------+----------------+---------------+---------------+------------+---------------+--------------+


TABLE: site
+---------+-----------+-----------------+
| site_id | site_name | site_manager_id |
+---------+-----------+-----------------+
|       1 | Site A    |               2 |
|       2 | Site B    |               2 |
|       3 | Site C    |               4 |
|       4 | Site D    |               4 |
|       5 | Site E    |               5 |
+---------+-----------+-----------------+

QUERY

SELECT user_id
     , user_firstname
     , user_lastname
     , user_username
     , user_phone
     , GROUP_CONCAT(site_name SEPARATOR ', ') as site
     , user_title_id
     , user_role_id
FROM users u 
     LEFT JOIN 
     site s ON u.user_id = s.site_manager_id
GROUP BY user_id;

RESULTS

+---------+----------------+---------------+---------------+------------+----------------+---------------+--------------+
| user_id | user_firstname | user_lastname | user_username | user_phone | site           | user_title_id | user_role_id |
+---------+----------------+---------------+---------------+------------+----------------+---------------+--------------+
|       1 | Peter          | Dowt          | peterd        | 1234       |                | 1             | 1            |
|       2 | Laura          | Norder        | lauran        | 2345       | Site B, Site A | 2             | 2            |
|       3 | Tom            | DiCanari      | tomd          | 3456       |                | 1             | 1            |
|       4 | Scott          | Chegg         | cheggs        | 4567       | Site D, Site C | 2             | 2            |
|       5 | Polly          | Vinyl         | pollyv        | 5678       | Site E         | 3             | 1            |
+---------+----------------+---------------+---------------+------------+----------------+---------------+--------------+

 

[Barand]

Here's my version of your page, keeping the PHP separated from the HTML a far as is possible.

<?php
$res = $con->query("SELECT user_id
                         , user_firstname
                         , user_lastname
                         , user_username
                         , user_phone
                         , GROUP_CONCAT(site_name SEPARATOR ', ') as site
                         , user_title_id
                         , user_role_id
                    FROM users u 
                         LEFT JOIN 
                         site s ON u.user_id = s.site_manager_id
                    GROUP BY user_id;
                    ");
$data = '';
foreach ($res as $row) {
    $data .= "<tr><td>" . join('</td><td>', $row) . "</td><td class='table-action'>
                                                    <a href='#'><i class='align-middle' data-feather='edit-2'></i></a>
                                                    <a href='#'><i class='align-middle' data-feather='trash'></i></a>
                                                    </td>
                                                    </tr>\n";
}
?>
<!DOCTYPE html>
<html>
<head>
<title>Example Multiple Locations</title>
<meta http-equiv="content-language" content="en">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
</head>
<body>
    <table id="datatables-column-search-select-inputs" class="table table-striped" style="width:100%">
        <thead>
            <tr>
                <th>ID</th>
                <th>FirstName</th>
                <th>LastName</th>
                <th>Username</th>
                <th>Phone #</th>
                <th>Location</th>
                <th>Title</th>
                <th>Role</th>
                <th>Actions</th>
            </tr>
        </thead>
        <tbody>
               <?=$data?>
        </tbody>
    </table>
</body>
</html>

 

[Zserene]

Barand,

Thanks so much!  That is amazingly helpful and work great.  I'll start using this format on the rest of the tables with multi line responses.  And thanks for the pointers on placement (or lack thereof) of code within the while loop, easier to fix this stuff now than when I get everything finished up.