Need some serious suggestions and/or help here. -- track hits by time/date

[Jax2]

Hello all.

 

Please forgive me in advance, because this will be a rather long post. I am going to try and explain what I am trying to do in detail as well as explain how things are currently set up on my site. In return, I would kindly ask for your opinions/ideas/suggestions/HELP! I would consider myself as medium proficiency when it comes to PHP. Not great, but not horrible. Please keep that in mind when answering. Thank you!!

 

Okay, on to my situation:

 

I am designing a new site (http://www.funnyhq.com). The site will have 4 different types of media for people to browse through, namely videos, pictures, games and odd / funny news. I have one database set up to hold all of the different media. The database is set up as such:

 

CONTENT_ID - int 11, not null auto-increment , unique, primary

type - varchar 12, not null (This is one of the four types, either videos, pictures, games or news)

title - varchar 255, not null

description - text

coding - text (This is where the relevant media code goes, such as embed codes for video, or the img src for pics ...etc)

date_added - date (It just inserts YYYY-DD-MM stamp when the file is added)

hits - int 11 (This is updated using $hits=$row['hits']+1 every time the media is displayed.)

comments - int 11 (Keeps track of the number of comments the media has had)

rating - text (Keeps track of the current rating and is updated when a new rating is given)

 

So that's what I am currently working with. I have one page to display all the different media types by type and content_id. When the media is displayed, again, it add's +1 to the 'hits' row, which keeps a running tally of total hits.

 

Here is where I am running into trouble. I would like to be able to display all of the media (each type) that has been viewed in X amount of time. For example, most viewed video in the last 24 hours, or most viewed picture in the last week, month, or even ALL TIME (all time is simple, got that down pat)

 

To be quite honest, I am clueless as to how I should go about doing this. I don't really even have a general idea. I know a few things like:

 

I need to somehow keep track of when each hit when made

I need to somehow find all the media that has been viewed within x amount of time and display links to each sorted by most hits during x period of time.

 

That's it. I feel completely stupid not being able to do this on my own, but I'm really at a loss on how to continue. Please, if you have done something like this before, help me out, and if there's important info I left out that would help you figure this out, let me know and I'll be glad to tell you!

 

Thank you guys!

 

[manwhoeatsrats]

just an idea.  create another table that is a transaction history.

each time an item is clicked, it injects into this table creating a new transaction with the date.

 

now for the display.  you will likely only want to display the top hits

do a sum query something like $query = "SELECT type, SUM(hits) FROM products ORDER BY hits ASC"

 

I hope this is a workable idea

[Jax2]

Ok, here is my concern with that, hopefully, you'll check back on the thread...

 

If I had a second database and every time media was displayed it would create a record with the timestamp, when I have say 10,000 media files and (gosh, I sure hope) many many hits per day, wouldn't that database become absolutely massive? I mean, like if I had 1000 visitors in one day and they each looked at an average of 10 - 15 different things, that would be 10,000 - 15,000 new entries each day.

 

Is there a way I could only have 1 record for each media file and use that?

 

Like I said, I've got no idea even where to start on this, but that's my view on it. Thanks for the reply!

[MatthewJ]

As manwhoeatsrats suggested, you will need to store the hits in a separate table. It will make the detailed sorting/display much easier to accomplish.

 

To answer your last post, the size of the second table (not database) would get large, but that is what databases are made for.

[manwhoeatsrats]

if you want to store the time of each hit, then I don't see any other way besides doing another table.  if your majorly concerned about the table getting to large then, you can always setup a script that will clear out the oldest records.

 

To be honest though, I don't think it is really a major concern.  you are talking about a table that stores very little information.

 

Last I checked myISAM had a limit of 4gigs of info per table.  I have a database with over 10,000 records in one table, with 40 different fields, and it is only using 20MB.....  If you are using InnoDB it is much higher....

 

oh and on that 10,0000 records, I can search a blob and still get the results back in under a second.  So really this should not be a major issue.

[Jax2]

Okay, I'm learning, slowly... I have done as suggested and created a new tabled called hits. It has 3 fields: hit_id (primary key) CONTENT_ID (stores the content ID number of the media file the hit is recorded for) and time (which shows up as: 2010-01-25 09:54:24).

 

I made a couple of hits on a bunch of different media files to test it out and the hits are being recorded as they should.

 

Now I need some more help, and I apologize for not quite getting this and appreciate your patience with me.

 

So I created a new test file called popular.php, just to mess around with. So far, I added this code to it:

 

<?php
include('db.php');
$sql = "select * from hits order by time desc" or die(mysql_error()); 
	$query = mysql_query($sql)or die(mysql_error());
	while ($row = mysql_fetch_array($query)) 
{
?>

Content ID = <?php echo $row['CONTENT_ID'];?><br>
Time Stamp = <?php echo $row['time'];?><br>

<?php
};
?>

 

When I execute this file, I get the following results:

 

Content ID = 1

Time Stamp = 2010-01-25 10:28:15

Content ID = 1

Time Stamp = 2010-01-25 09:54:42

Content ID = 3

Time Stamp = 2010-01-25 09:54:35

Content ID = 4

Time Stamp = 2010-01-25 09:54:30

Content ID = 4

Time Stamp = 2010-01-25 09:54:29

Content ID = 5

Time Stamp = 2010-01-25 09:54:24

Content ID = 6

Time Stamp = 2010-01-25 09:53:17

 

So you can see, media id #1 got 2 hits, 3 got 1 hit, 4 got 2 hits ...etc.

 

I tried changing the code to:

 

$sql = "select sum(CONTENT_ID) from hits order by time desc" or die(mysql_error());

 

This statement resulted in nothing.

 

I need to first figure out how to get a total number of records where, for example if I am using the within 24 hours, the hit was anytime between now and -3600 minutes ago (24 hours). I need the output to be like content_id  1 had 3 hits total; content_id 2 had 1 hit ...etc, not 1 = 1 hit, 2 = 1 hit, 1 = 1 hit, 1 = 1 hit...

 

Once I figure out how to do that, I need to put all of these into a while loop and then for each one returned, connect to the "content" table and using the content ID number from the HITS table, pull up the correct media file.

 

Am I correct on my thinking thus far?

 

[Jax2]

Please ignore previous post. Updated info here, now I know exactly what I need help with!

 

Before I begin, here is my current HITS table data:

 

HIT_ID CONTENT_ID time

1             6               1264453858

2             5               1264453877

3             6               1264453882

4             1               1264453885

5             3               1264453890

6             6               1264453894

7             1               1264453901

 

As you can see, MEDIA #6 has gotten 3 hits total, so, the highest, followed by MEDIA 1 with 2 hits, and 3 & 5 tied for last place with 1 hit each.

 

My code on the page currently looks like this:

<?php
include('db.php');
$current_time=mktime();
$cutoff_time=mktime()-24*3600;
$sql = "select *,sum('CONTENT_ID') from hits where time > $cutoff_time order by time desc" or die(mysql_error()); 
	$query = mysql_query($sql)or die(mysql_error());
	if (!mysql_num_rows($query)) {
	echo "nothing there<br>";
	}
	else {
	while ($row = mysql_fetch_array($query)) 
{
$CONTENT_ID=$row['CONTENT_ID'];
$sql = "select * from content where CONTENT_ID=$CONTENT_ID order by $HITS";
echo $CONTENT_ID;?> | <?php echo $num_rows;?><br>
<?php
};
echo "<br>cutoff date:".$cutoff_time."<br>";
echo "Now date:".$current_time."<br>";
};
?>

 

When I run that code, I get the following results:

 

6 | 1

 

cutoff date:1264368330

Now date:1264454730

 

So, not what I was expecting, I was hoping for:

6 | 3

1 | 2

3 | 1

5 | 1

 

I do not understand why it counted the record with the highest amount of hits ( Media 6, 3 hits) but did not continue on to the other records and display those as well as their hits.

 

If I can get this last little bit sorted out, I'll have it working, but I'm stumped at this point.

 

Thank you!

 

[MatthewJ]

SELECT CONTENT_ID, count(CONTENT_ID) as totals FROM hits where time > $cutoff_time GROUP BY CONTENT_ID ORDER BY time DESC

[Jax2]

SELECT CONTENT_ID, count(CONTENT_ID) as totals FROM hits where time > $cutoff_time GROUP BY CONTENT_ID ORDER BY time DESC

 

I tried that, my code looks like:

 

$sql = "SELECT CONTENT_ID, count(CONTENT_ID) as totals FROM hits where time > $cutoff_time GROUP BY CONTENT_ID ORDER BY time DESC";

 

but now, instead of only showing video#6 with 3 hits, it's showing video#3 which only has 1 hit, and nothing else. 

 

Thank you though, I was hoping that would solve it!

[MatthewJ]

I'm about to head home from work, I will take a look when I get situated. Probably an issue somewhere else in the code. I duped the table data you had and ran just the query via command line and the ouput was right, it just might need some tweaking in the rest of the code to display it how you want.

[Jax2]

Mathew, thank you.

 

It's a screwy code so far. As there are two different tables I have to access, I have a while loop reading the hits table and another while loop taking the info from the hits table and reading from the content table, which has all of the important stuff like the video title, description, embed code ...etc.

 

So yea, I probably messed something up bad in there. Looking forward to your reply!!

 

As a few things changed, here's the php page again. The table data remained the same.

 

<?php
include('db.php');
$current_time=mktime();
$cutoff_time=mktime()-24*3600;
$sql = "SELECT CONTENT_ID, count(CONTENT_ID) as totals FROM hits where time > $cutoff_time GROUP BY CONTENT_ID ORDER BY time DESC";
	$query = mysql_query($sql);
	if (!mysql_num_rows($query)) {
	echo "nothing there<br>";
	}
	else {
	while ($row = mysql_fetch_array($query)) 
{
$CONTENT_ID=$row['CONTENT_ID'];
$sql = "select * from content where CONTENT_ID=$CONTENT_ID";
$query = mysql_query($sql)or die(mysql_error());
while ($row = mysql_fetch_array($query)) 
{
echo $row['CONTENT_ID'];
};
};
echo "<br>cutoff date:".$cutoff_time."<br>";
echo "Now date:".$current_time."<br>";
};
?>

[MatthewJ]

This should be about what you're after. It looks like from your first post that you call the url filed in the content table "coding" so that is what I used in the link output. If that is wrong, that should be the only thing that needs to be changed.

 

include('db.php');

$cutoff_time = time() - 86400; //Now minus on day
$current_time = time();

$sql = "SELECT content.*, hits.CONTENT_ID, count(hits.CONTENT_ID) as totals 
	FROM hits
	JOIN content ON content.CONTENT_ID = hits.CONTENT_ID
	WHERE time > $cutoff_time 
	GROUP BY hits.CONTENT_ID 
	ORDER BY totals DESC";

$query = mysql_query($sql);

if (!mysql_num_rows($query)) {

echo "nothing there<br>";

} else {

while ($row = mysql_fetch_array($query)) {

	echo "Content ID: ".$row['CONTENT_ID']." had ".$row['totals']." hits between ".date('Y-m-d H:i:s', $cutoff_time)." and ".date('Y-m-d H:i:s', $current_time)."<br>";
	echo "<a href='".$row['coding']."'>Click here to view the content</a><br /><br />";

}

}

[Jax2]

Mat, you're a genius! Thank you so much for the help, it's exactly what I needed to the T. I've worked the code around a bit and added a few things and it works exactly as I'd planned. I didn't know you could access two separate tables in one SQL statement, that was one of my biggest problems. Shows how little I really do know!

 

You can check out the final version at http://www.funnyhq.com and clicking on the Popular link.

 

Now I just need to really work on making it all look nice instead of how it looks now

 

Take care and thanks again for all the help!