I'm trying to show some stats from my database and count the number of routes submitted, total length of routes etc. etc. I've got the query below displaying on my page fine.
However, this gives me a list showing userids. What I want to do is substitute the submittedby field in the jos_downloads_files table with the username in the jos_users table, this table has a URN called id.
My working Code
PHP Code:
$sql = mysql_query("SELECT submittedby, SUM(routelengthmiles) as RoutesTotalLength, SUM(routeclimbfeet) as RoutesTotalClimb, COUNT(*) as HowMany
FROM jos_downloads_files
GROUP BY submittedby
ORDER BY routelengthmiles DESC
LIMIT 3"
) or die (mysql_error());
while($row=mysql_fetch_array($sql)){
echo ($row[submittedby]) .": " .($row[HowMany])." Routes, " . number_format($row[RoutesTotalLength],0)." miles and " . number_format($row[RoutesTotalClimb],0)." ft of ascent.<br />";
}
I know I need to use a (left?) join, but I've been trying to pull in the right columns and put the LEFT JOIN in the right place all afternoon and it's driving me bananas!
The linking fields are:
jos_users table: id
jos_downloads_files: submittedby
I'm trying to show some stats from my database and count the number of routes submitted, total length of routes etc. etc. I've got the query below displaying on my page fine.
However, this gives me a list showing userids. What I want to do is substitute the submittedby field in the jos_downloads_files table with the username in the jos_users table, this table has a URN called id.
My working Code
PHP Code:
$sql = mysql_query("SELECT submittedby, SUM(routelengthmiles) as RoutesTotalLength, SUM(routeclimbfeet) as RoutesTotalClimb, COUNT(*) as HowMany
FROM jos_downloads_files
GROUP BY submittedby
ORDER BY routelengthmiles DESC
LIMIT 3"
) or die (mysql_error());
while($row=mysql_fetch_array($sql)){
echo ($row[submittedby]) .": " .($row[HowMany])." Routes, " . number_format($row[RoutesTotalLength],0)." miles and " . number_format($row[RoutesTotalClimb],0)." ft of ascent.<br />";
}
I know I need to use a (left?) join, but I've been trying to pull in the right columns and put the LEFT JOIN in the right place all afternoon and it's driving me bananas!
The linking fields are:
jos_users table: id
jos_downloads_files: submittedby
Could anyone please put me out of my misery?
Thanks!
PHP Code:
$sql = "SELECT id, username, submittedby, SUM(routelengthmiles) as RoutesTotalLength, SUM(routeclimbfeet) as RoutesTotalClimb, COUNT(*) as HowMany
FROM jos_downloads_files, jos_users
where jos_users.id=jos_downloads.submittedby
GROUP BY submittedby
ORDER BY routelengthmiles DESC
LIMIT 3";
We suppose that you have in jos_users id and username fields. Now if you do: echo $row["username"]; you should get the username.
// Max Uploads by Distance
echo "<strong>Top 3 Uploaders By Distance</strong><br />";
$sql = mysql_query("SELECT submittedby, SUM(routelengthmiles) as RoutesTotalLength, SUM(routeclimbfeet) as RoutesTotalClimb, COUNT(*) as HowMany where jos_users.id=jos_downloads_files.submittedby
FROM jos_downloads_files, jos_users
GROUP BY submittedby
ORDER BY RoutesTotalLength DESC
LIMIT 3"
) or die (mysql_error());
You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'where jos_users.id=jos_downloads_files.submittedby FROM jos_do
I've also tried:
PHP Code:
$sql = mysql_query("SELECT submittedby, SUM(routelengthmiles) as RoutesTotalLength, SUM(routeclimbfeet) as RoutesTotalClimb, COUNT(*) as HowMany where jos_users.id=jos_downloads_files.submittedby
FROM jos_downloads_files, LEFT JOIN jos_users ON jos_downloads_files.submittedby=jos_users.id
GROUP BY submittedby
ORDER BY RoutesTotalLength DESC
LIMIT 3"
$sql="SELECT jos_users.id, jos_users.username, submittedby, SUM( routelengthmiles ) AS RoutesTotalLength, SUM( routeclimbfeet ) AS RoutesTotalClimb, COUNT( * ) AS HowMany
FROM jos_downloads_files, jos_users
WHERE jos_users.id = jos_downloads_files.submittedby
GROUP BY submittedby
ORDER BY routelengthmiles DESC
LIMIT 3 ";
// Max Uploads by Height
echo "<strong>Top 3 Uploaders By Height</strong><br />";
$sql = mysql_query("SELECT jos_users.id, jos_users.username, submittedby, SUM(routelengthmiles) AS RoutesTotalLength, SUM(routeclimbfeet) AS RoutesTotalClimb, COUNT(*) AS HowMany
FROM jos_downloads_files, jos_users
WHERE jos_users.id = jos_downloads_files.submittedby
GROUP BY submittedby
ORDER BY routelengthmiles DESC
LIMIT 3"
) or die (mysql_error());
while($row=mysql_fetch_array($sql)){
echo ($row[username]) .": ". number_format($row[RoutesTotalClimb],0)." ft of ascent.<br />";
}
I see loads of posts where people say "I fixed it.." or "I got it to work" with no further information and I always want to know what they did to get it to work or to fix it, so I try to remember to post details of the final working solution to any of my problems, so others can see what worked.
Left Join with Where & Count is eluding me
Linear Mode
