my_location1&my_location2&my_location3。CCD_ 1。location1=国家/地区列表。location2=静态列表。location3=联排别墅。在另一张表中，我有用户列表。现在我想列出所有位置（country-stats-town），使用树样式+每个country-statis town（count）中的aqual用户。（如何生成？？）
数据库注意事项：pid&pid1=国家id，pid2=统计的id
my_location1:
id ++++++ name ++++++ active
my_location2:
id ++++++ pid +++++++ name ++++++ active
my_location3:
id ++++++ pid1 +++++++ pid2 ++++++ name ++++++ active
my_user
id ++++++ name ++++++ location1 ++++++ location2 ++++++ location3 ++++++ active
e.x
Country (print count Of users in this country )
+++++++stats (print count Of users in this stats )
++++++++++++town (print count Of users in this town )
++++++++++++town ( ... )
++++++++++++town ( ... )
++++++++++++town ( ... )
+++++++stats ( ... )
++++++++++++town ( ... )
+++++++stats ( ... )
++++++++++++town ( ... )
++++++++++++town ( ... )
Country1 ( ... )
+++++++stats ( ... )
++++++++++++town ( ... )
++++++++++++town ( ... )
Country2 ( ... )
+++++++stats ( ... )
++++++++++++town ( ... )
++++++++++++town ( ... )
++++++++++++town ( ... )
++++++++++++town ( ... )
...more
谢谢你的帮助。
我没有检查代码，但应该是这样的：
function getCountries()
{
return mysql_query('SELECT c.*, (COUNT(*) from u WHERE u.location1 = c.id) AS user_count
from countrylist c
LEFT JOIN users u
ON u.location1 = c.id
WHERE c.active = 1'
);
}
function getStats($countryId)
{
return mysql_query('SELECT s.*, (COUNT(*) from u WHERE s.location2 = s.id) AS user_count
from statslist s
LEFT JOIN users u
ON u.location2 = s.id
WHERE s.pid = ' . $countryId
);
}
function getTowns($stateId)
{
return mysql_query('SELECT t.*, (COUNT(*) from u WHERE t.location3 = t.id) AS user_count
from townlist
LEFT JOIN users u
ON u.location3 = t.id
WHERE t.pid2 = ' . $stateId
);
}
echo "<ul>'n";
while ($country = mysql_fetch_assoc(getCountries())) {
echo "<li>" . $country["name"] . "(" . $country["user_count"] . ")'n";
echo "<ul>'n";
while ($state = mysql_fetch_assoc(getStats($country["id"]))) {
echo "<li>" . $state["name"] . "(" . $state["user_count"] . ")'n<ul>'n";
while ($town = mysql_fetch_assoc(getTowns($state["id"]))) {
echo "<li>" . $town["name"] . "(" . $town["user_count"] . ")</li>'n";
}
echo "</ul>'n</li>'n";
}
echo "<ul>'n</li>'n";
}
echo "</ul>'n";
它可以而且应该进一步优化，但希望你明白这个想法。还请注意，您的mysql结构是相当多余的。