CODEWITHSUNDEEP
phpmysqlsql
Coman Paul
Coman Paul

Reputation: 301

Click counter via Mysql database

Table: links

id     link_ref             name          clicks   unique_clicks       url
1    external_link      EXTERNAL LINK        2         1            .index.php    


Table:links_clicks

id   link_id     ip     date    hour
1       2      an_ip  a_date  a_hour

These are the tables and below is the code I have tried:

$querym = mysql_query("SELECT * FROM `links` RIGHT JOIN `links_clicks` ON `links`.`id`=`links_clicks`.`link_id`");
while ($row = mysql_fetch_assoc($querym))
{
Echo $row['link_ref']."<br>";
}

What I want is to display an output with all the links from "links" table and how many clicks have been made on each of them in the last 24 hours.

In table "links_clicks" are stored all the clicks and in "links" are stored the informations about each external link.

links.id is the same with many link_clicks.link_id

So,

  1. How do I join tables it's the first question.
  2. Related to the 1: How do i count the clicks for each link?
  3. How do i count the clicks on a link in a period of time ?

Upvotes: 0

Views: 1175

Answers (2)

O. Jones
O. Jones

Reputation: 108651

First of all, use a TIMESTAMP or DATETIME column in your links_clicks table rather than separated DATE and INT columns to hold date and hour.

Second, go read up on JOIN operations. It's not clear to my why you're using a right outer join. An inner join will work fine.

Third, pro-tip: Don't use SELECT * in software. It usually returns too much information.

Fourth, you need some aggregate query capability (GROUP BY).

Try this query to get a listing of links that were clicked in the most recent 24 hours and the number of times they were clicked, ordered by most-clicked first.

SELECT COUNT(*) AS num,
       links.url
  FROM links
  JOIN links_clicks ON links.id = links_clicks.link_id
 WHERE links_clicks.click_timestamp >= NOW() - INTERVAL 1 DAY
 GROUP BY links.url
 ORDER BY COUNT(*) DESC

If a pure row count is not what you need, you can use something besides COUNT(*) for the aggregate query. For example, you could try

 COUNT(distinct links_clicks.ip)

if you only want to count each distinct ip value once.

Upvotes: 3

Limiter
Limiter

Reputation: 497

SELECT count(*) AS total_click FROM links_clicks WHERE link_id = ?;

Should get you started, do that for each link.

Or you could be more complex and do

SELECT count(DISTINCT c.link_id) FROM link_clicks c LEFT JOIN links l ON (c.link_id=l.link_id) GROUP BY l.link_ref

These get you started, you can narrow the results down with date ranges etc.

Upvotes: 0

Related Questions