Leaderboard
Popular Content
Showing content with the highest reputation on 03/16/2021 in all areas
-
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>1 point -
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 | +---------+----------------+---------------+---------------+------------+----------------+---------------+--------------+1 point
This leaderboard is set to New York/GMT-04:00