php - 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 tables , below code have tried:

$querym = mysql_query("select * `links` right join `links_clicks` on `links`.`id`=`links_clicks`.`link_id`"); while ($row = mysql_fetch_assoc($querym)) { echo $row['link_ref']."<br>"; } 

what want display output links "links" table , how many clicks have been made on each of them in last 24 hours.

in table "links_clicks" stored clicks , in "links" stored informations each external link.

links.id same many link_clicks.link_id

so,

1. how join tables it's first question.
2. related 1: how count clicks each link?
3. how count clicks on link in period of time ?

first of all, use timestamp or datetime column in links_clicks table rather separated date , int columns hold date , hour.

second, go read on join operations. it's not clear why you're using right outer join. inner join work fine.

third, pro-tip: don't use select * in software. returns information.

fourth, need aggregate query capability (group by).

try query listing of links clicked in recent 24 hours , number of times clicked, ordered most-clicked first.

select count(*) num,        links.url   links   join links_clicks on links.id = links_clicks.link_id  links_clicks.click_timestamp >= now() - interval 1 day  group links.url  order count(*) desc 

if pure row count not need, can use besides count(*) aggregate query. example, try

 count(distinct links_clicks.ip)  

if want count each distinct ip value once.