Jump to content

Archived

This topic is now archived and is closed to further replies.

poe

count & multiple tables

Recommended Posts

i have 2 tables


links (3 fields)
- id - | - name - | - url -
1 | msn | www.msn.com
2 | google web page | www.google.com
3 | cnn news | www.cnn.com
4 | yahoo home page | www.yahoo.com
etc...


clicks (2 fields)
- id - | - link_id -
1 | 2
2 | 4
3 | 4
4 | 1
5 | 3
6 | 3
7 | 4
etc...


as someone clicks a link, the link _id is recorded in my clicks table.

i now want to show the results by most popular link to least.

ie.
- rank - | - id - | - count - | - url - | - name -
1 | 4 | 73 | www.yahoo.com |  yahoo home page
2 | 3 | 24 | www.cnn.com | cnn news
3 | 2 | 18 | www.google.com | google web page
.... etc....


$query = " SELECT link_id, COUNT(link_id) AS id_count FROM clicks GROUP BY link_id ORDER BY id_count DESC ";


this will give me ...
- rank - | - id - | - count -
1 | 4 | 73
2 | 3 | 24
3 | 2 | 18
.... etc....


but, how do i get my results to include the data from links table (url, name)



$query = " SELECT clicks.link_id, COUNT(clicks.link_id) AS id_count, links.url, links.name FROM clicks, links GROUP BY link_id ORDER BY id_count DESC ";

if i use this query, my count is way off as if it takes the count multiplied by number of records in the links table

Share this post


Link to post
Share on other sites
update.

i read some other posts and i think i figured it out.

$query .= " SELECT links.url, id_link, COUNT(*) AS cnt ";
$query .= " FROM clicks ";
$query .= " INNER JOIN links ON clicks.id_link = links.id ";
$query .= " GROUP BY id_link ";
$query .= " ORDER BY cnt ASC ";
$query .= " LIMIT 5 ";

Share this post


Link to post
Share on other sites
Read the forum guidelines. Don't double-post.

I see you had this solved about 8 hours ago so I've just been wasting my time giving you an answer to your post in the other forum  >:(

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.