I've got a very small bit of code which I use to store unique values in separate database rows or count the values up if they are not unique, making it easy for me to retrieve this data and ORDER BY the count values to gather frequency statistics; however, I'm trying to make it work without storing anything in a DB (I assume a multidimensional array would be the best bet)... and I know php has some functions for sorting arrays (which I hope would preserve the "rows" in multidimensional arrays), but I really don't know what I'm doing...and I'm just looking for some tips on which functions to use, how to go about it, or anything else really.
Here's the code I was using:
Code: Select all
//this neat little statement tells us, assuming correct spelling and proper input, approximately how many unique albums have been played this week; we'll need this to calculate frequency of each album's play.
$total_plays = mysql_fetch_array(mysql_query("SELECT COUNT(`album`) FROM `airplay` WHERE `timestamp`>'$StatStartTime' "));
$unique_album_plays = mysql_fetch_array(mysql_query("SELECT COUNT(DISTINCT LOWER(`album`)) FROM `airplay` WHERE `timestamp`>'$StatStartTime' "));
$total_new_plays = mysql_fetch_array(mysql_query("SELECT COUNT(DISTINCT `cddbid`) FROM `airplay` WHERE `status`='2' AND `cddbid`!='' AND `timestamp`>'$StatStartTime' "));
//
// we have a special temporary table where we're going to store data we collect in the next statement
//
// because this table is temporary, we should make sure it's tabula rasa when we use it.
mysql_query("DELETE FROM `temp_stat` WHERE `id`>'-1'") or die(mysql_error());
//this statement will grab all the CDs that we care about, new ones... we wont be charting anything else, obviously
$stat_query = "SELECT * FROM `airplay` WHERE `status`<='2' AND `timestamp`>'$StartStatTime' AND `cddbid`!='' ";
$stat_result = mysql_query($stat_query);
while($stat_row = mysql_fetch_assoc($stat_result)) {
$temp_query = "SELECT * FROM `temp_stat` WHERE `cdid`='".$stat_row[cddbid]."' LIMIT 1";
$temp_result = mysql_query($temp_query) or die(mysql_error());
if ($temp_row = mysql_fetch_assoc($temp_result)) {
$spins = $temp_row[spins];
$spins++;
mysql_query("UPDATE `temp_stat` SET `spins`='".$spins."' WHERE `cdid`='".$stat_row[cddbid]."' ") or die(mysql_error());
} else {
mysql_query("INSERT INTO `temp_stat` SET `spins`='1', `cdid`='".$stat_row[cddbid]."' ") or die(mysql_error());
}
}
$rating = 0;
$statistics_query = "SELECT * FROM `temp_stat` ORDER BY `spins` DESC";
$statistics_result = mysql_query($statistics_query) or die(mysql_error());
while ($statistics_row = mysql_fetch_assoc($statistics_result)) {
++$rating;
if (($rating - 1) <= ($total_new_plays[0] / 3)) {
$heavy_spins = $statistics_row[spins];
} elseif (($rating - 1) <= ($total_new_plays[0] / 1.5)) {
$medium_spins = $statistics_row[spins];
}
}