Foreach Loop for Jointed Tables
I am trying to list users inside a loop, but I want to keep the city_name outside of the loop. Do I need another request or instruction for this?
What exactly do I want as a result:
LONDON USERS: JAMES, BOB
NEW YORK USERS: DAVID
users table
====================
id user_name user_city_id user_type
1 Helen 2 NA
2 James 1 PREMIUM
3 David 1 NORMAL
4 Bob 1 NORMAL
cities table
=============
city_id city_name city_status
1 London 1
2 New York 1
3 Paris 0
<?php
$getcities = $db->get_rows("
SELECT *
FROM users
JOIN cities
ON cities.city_id = users.user_city_id
WHERE city_status = '1'
AND user_type IN ('NORMAL','PREMIUM')
");
?>
<h1><?php echo $row->city_name ?> USERS</h1>
<?php
foreach($getcities as $row) {
?>
<h2><?php echo $row->user_name ?></h2>
<?php
}
?>
+3
source to share
2 answers
You can do this with a SQL query.
SELECT cities.city_name, GROUP_CONCAT(users.user_name) AS names
FROM users INNER JOIN cities ON cities.city_id = users.user_city_id
WHERE city_status = '1' AND user_type IN ('NORMAL','PREMIUM')
GROUP BY cities.city_id
In your PHP
foreach($getcities as $row) {
echo $row->city_name . ": " . $row->names;
}
+2
source to share
If you want to list users by city, you need to switch your connection or you need to restructure your data at the application level, which is never good, but sometimes necessary, IMO.
Your example has quite a few problems, however, to do what you intend, change your SQL to
SELECT *
FROM cities
JOIN users
ON cities.city_id = users.user_city_id
WHERE city_status = '1'
AND user_type IN ('NORMAL','PREMIUM')
Then
<?php
$lastCity = null;
foreach($getcities as $row):
?>
<?php if($row->city_name !== $lastCity): $lastCity = $row->city_name; ?>
<h1><?php echo $row->city_name ?> USERS</h1>
<?php endif; ?>
<h2><?php echo $row->user_name ?></h2>
<?php
endforeach;
?>
+2
source to share