Jump to content


Photo

mysql 2 tables and count


  • Please log in to reply
1 reply to this topic

#1 poe

poe
  • Members
  • PipPipPip
  • Advanced Member
  • 143 posts

Posted 14 October 2006 - 11:46 PM

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 Barand

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

Posted 15 October 2006 - 08:58 AM

You need to specify the join condition for the tables otherwise, as you have noticed, each record in one table is joined with every record in the other table
SELECT clicks.link_id, COUNT(clicks.link_id) AS id_count,
    links.url, links.name
FROM clicks INNER JOIN links ON links.id = clicks.link_id
GROUP BY link_id
ORDER BY id_count DESC

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