Jump to content


Photo

count & multiple tables


  • Please log in to reply
2 replies to this topic

#1 poe

poe
  • Members
  • PipPipPip
  • Advanced Member
  • 143 posts

Posted 15 October 2006 - 02:07 AM

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

#2 poe

poe
  • Members
  • PipPipPip
  • Advanced Member
  • 143 posts

Posted 15 October 2006 - 02:32 AM

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 ";


#3 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 15 October 2006 - 09:51 AM

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  >:(
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users