Filtering Data by Different Time Lengths

[sdsportsfanatic]

I have a database table storing users on my site. I want to be able to filter the users in 5 different ways (amt of favs, amt of views, amt of plays, amt of reviews, highest rating).

 

I have a table storing all favs on the site and all the reviews on the site; and I have fields for all users storing amt of views, amt of plays, and rating. I do not have trouble filtering by views, plays, and rating. I was unsure how to filter by favs and reviews, so I added those fields to the users table and they are updated accordingly.

 

Now I do not have trouble filtering any of those parameters. But I would like to take the filters further, and filter the same data, but only account for numbers accumulated over the past day, the past week, and the past month, in addition to all time. ie: filtering all time may display that a user has 102 views, but filtering by today would give them only 3.

 

I am unsure if this is possible. My one idea involves making a table for every parameter and storing each statistic with a timestamp. If I had to do that I would have multiple tables with over 1,000,000 entries. This would mean storing every view, every play, etc. If this is the only way to execute my goal, I would still need assistance. How could I filter the users based on the amount of entries they have in another table? I am a little new to MySQL so I am unfamiliar with how this is done.

 

Is there any easier way to do this? Here is the page:

 

http://www.sdsportsfanatic.com/temp/iamcrack/browse_artists.php

 

As you can see, I am new to the forum. I don't really like to register on a new forum and ask for help before contributing anything; but I've really hit the wall on this one and I couldn't find any solutions by searching online. I appreciate your help.

[dreamwest]

How could I filter the users based on the amount of entries they have in another table? I am a little new to MySQL so I am unfamiliar with how this is done.

 

Join the two tables like so:

 

<?php
// Make a MySQL Connection
// Construct our join query
$query = "SELECT table2.column, table2.column ".
"FROM table1, table2 ".
"WHERE table2.column = table2.column";

$result = mysql_query($query) or die(mysql_error());


// Print out the contents of each row into a table 
while($row = mysql_fetch_array($result)){
echo $row['column1']. " - ". $row['colum2'];
echo "<br />";
}
?>

[dreamwest]

Sorry made typos in code:

 

<?php
// Make a MySQL Connection
// Construct our join query
$query = "SELECT table2.column, table1.column ".
"FROM table1, table2 ".
   "WHERE table2.column = table1.column";
    
$result = mysql_query($query) or die(mysql_error());


// Print out the contents of each row into a table 
while($row = mysql_fetch_array($result)){
   echo $row['column1']. " - ". $row['colum2'];
   echo "<br />";
}
?>

[sdsportsfanatic]

Thank you for the explanation. I think I understand and I'm going to test it out tonight.

 

One question I still have is in regards to creating tables for views and plays. Can it be bad to store all of this data? Every time a user's page is visited, I will need to add an entry to the views table. Same with every time a song is played. I'm guessing this will result in over 1,000,000 entries after 6-10 months that will only grow. Is this inefficient or could it be problematic for my database/hosting?

 

Thanks again.

[dreamwest]

Thank you for the explanation. I think I understand and I'm going to test it out tonight.

 

One question I still have is in regards to creating tables for views and plays. Can it be bad to store all of this data? Every time a user's page is visited, I will need to add an entry to the views table. Same with every time a song is played. I'm guessing this will result in over 1,000,000 entries after 6-10 months that will only grow. Is this inefficient or could it be problematic for my database/hosting?

 

Thanks again.

 

Forums update view counts every time a post is viewed

 

If your creating a column for views:

 

mysql_query("ALTER TABLE table_name ADD COLUMN views INT") 
or die(mysql_error());

 

If you look at the data size of this column after a few weeks youll find its very minimal.

[sdsportsfanatic]

Thank you for the explanation. I think I understand and I'm going to test it out tonight.

 

One question I still have is in regards to creating tables for views and plays. Can it be bad to store all of this data? Every time a user's page is visited, I will need to add an entry to the views table. Same with every time a song is played. I'm guessing this will result in over 1,000,000 entries after 6-10 months that will only grow. Is this inefficient or could it be problematic for my database/hosting?

 

Thanks again.

 

Forums update view counts every time a post is viewed

 

If your creating a column for views:

 

mysql_query("ALTER TABLE table_name ADD COLUMN views INT") 
or die(mysql_error());

 

If you look at the data size of this column after a few weeks youll find its very minimal.

 

I wouldn't be creating just a column for views, I would need a whole table to timestamp each one. I would need this to know how many views a user has received that occurred that day, that week, that month, and all time. Storing one number for each user doesn't allow me to do this.

[dreamwest]

I wouldn't be creating just a column for views, I would need a whole table to timestamp each one. I would need this to know how many views a user has received that occurred that day, that week, that month, and all time. Storing one number for each user doesn't allow me to do this.

 

INT is an intreger, meaning it will count up from 0.

 

You will need at least two columns to cross refernce so you can split the output into days , hours second, etc...

 

`date_content_added` DATETIME NOT NULL,
`date_displayed_last` DATETIME

 

Then use php to subtract the last view time with the intial date or whatever timeframe you want