Tracking Clicks by Day

[Bean Boy]

Hey guys,

 

I'm new here and just getting into PHP.

 

I've been coding a program that tracks click-throughs from emails to a website.

 

And though I can get this to work with 1 table, I suspect I'll need 2 tables if I want to record the data by days.

 

With just table, I have...

 

id,

subject, // name of email

clicks, // increments by +1 when a reader clicks-through for that email

 

But if I add a DATE value, it will obviously just hold one for each subject instead of recording by days.

 

Ideally, I want a user to have the ability to look back at the data from previous days as well as the total history of data combined.

 

Any ideas?

 

I've been trying to implement another table, but I'm not sure how to contact the appropriate days with the right subjects.

[Maq]

It's fine if you store each record with the same days because when you extract it from the database you take it out by day.  Pseudo example:

 

SELECT * FROM click_counter WHERE day = $today;

 

Then you just get the total by counting how many records were returned.

[discomatt]

Why not use a multi-column index

http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html

 

Then in theory, if you create an index with date,subject as a single unique index... you should be able to use REPLACE INTO and save yourself an extra query

 

REPLACE INTO `table` ( `subject`, `date`, `clicks` ) VALUES ( '$subject', CURDATE(), `clicks`+1 )

 

I'm not entirely sure if this will work as expected, but it's worth a try

[Bean Boy]

It's fine if you store each record with the same days because when you extract it from the database you take it out by day.  Pseudo example:

 

SELECT * FROM click_counter WHERE day = $today;

 

I was thinking I would use the subject as an "anchor". But are you saying I should use the date that way and just repeat the title as many times as I need rows for my dates?

 

For example if this is my table:

 

id,

date,

subject,

clicks

 

This would be how my MySQL rows would look:

 

1 | 2008-09-24 | subject_1 | 5

2 | 2008-09-24 | subject_2 | 2

3 | 2008-09-25 | subject_1 | 9

4 | 2008-09-25 | subject_2 | 8

5 | 2008-09-26 | subject_3 | 4

 

And here's my output display:

 

1) subject_1 | 14

2) subject_2 | 10

3) subject_3 | 04

 

Makes sense... but what if I pass an id in the email's link, where the number corresponds to the right subject...

 

<a href="index.php?id=1">Click</ a>

 

I'm just not sure how to make this connection???

 

If I don't anchor the id with the subject, is it still possible to pass a value that targets the right subject?

[Bean Boy]

bump