mySQL query question

Question

I have 1 table called urltracker that holds information about the number of times a specific url has been clicked. The table is structured into 4 columns id, urlclicked, referrer and timestamp. The urltracker creates another entry in the table every time a url has been clicked.

What i would like to do is display a specific url and the number of times it has been clicked, the user would then be able to drill down into the data by clicking to reveal all the specific times this url has been clicked.

I'm no sql expert but this sounds like i need to initially display all the distinct URL's followed by a sub query which displays all the entries for this specfic url.

Am i right in thinking this or is there an easier way?

Answer 1

One thing to consider is using two tables - one table that holds the unique urls and another that stores the click information (and either using a unique sequence number to join them, or the url (since it is unique))

But to answer your query question - yes I would have one query to display URLs and a second query to get the data for a specific URL (only if/when the user requests that info).

Answer 2

Something like that should do the trick: First:

SELECT urlclicked, 
       COUNT(*)
  FROM urltracker
GROUP BY urlclicked

And then when the user clicks on some url:

SELECT id, 
       urlclicked, 
       referrer,
       timestamp
FROM urltracker
WHERE urlclicked = ?clickedUrl

to display details.

Answer 3

You should be able to do something like this to get the totals for each URL:

SELECT urlclicked, COUNT(urlclicked) AS total
FROM urltracker
GROUP BY urlclicked

Is this what you were after?

Answer 4

Yes, in a way u r right :

SELECT id, 
       tb1.urlclicked, 
       referrer,
       timestamp, tb2.totaltimesclicked
FROM urltracker as tb1
inner join 
(SELECT urlclicked, 
       COUNT(urlclicked) as totaltimesclicked
  FROM urltracker
GROUP BY urlclicked) as tb2
on tb1.urlclicked = tb2.urlclicked

This will display all the information along with howmany times each url is clicked.