Top 30 songs list - need help

[EP]

I'm trying to build a PHP script that displays the top 30 (or whatever number) played songs.

 

My mysql database is configured so that each time a song plays, an entry is made in the DB.  So if a song shows has 8 entries then that means it has been played 8 times.

 

I have no problem selecting the necessary data from the DB.  The problem is I'm not sure how to manipulate it to get the desired result.

 

Here is what I have so far:

<?php

mysql_connect("localhost", "root", "password");
mysql_select_db("songhistory");

$numbertop = $_GET["numbertop"];
$timeperiod = $_GET["timeperiod"];

# Grab entries for last 7 days only
$now = date('Y-m-d H:i:s');
$now2 = strtotime($now);

$date2 = strtotime("+1 week",$now);

$timefinal = $now2-$date2;

$timeplayedsearch = " > ' " . date('Y-m-d H:i:s',$timefinal) . " ' ";


$result = mysql_query("SELECT title, artist, timeplayed FROM `songhistory` WHERE timeplayed " . $timeplayedsearch . " ORDER BY title");

echo "<TABLE align='center' cellpadding='2' cellspacing='0' border='0'>";
echo "<TR><TD align='center' colspan='4'><FONT FACE='ARIAL' SIZE='3'>TOP 30 SONGS</FONT></TD></TR>";
echo "<TR><TD align='center'><FONT FACE='ARIAL' SIZE='2'><B>No.</B></FONT></TD><TD align='center'><FONT FACE='ARIAL' SIZE='2'><B>TITLE</B></FONT></TD><TD align='center'><FONT FACE='ARIAL' SIZE='2'><B>ARTIST</B></FONT></TD><TD align='center'><FONT FACE='ARIAL' SIZE='2'><B>TIME PLAYED</B></FONT></TD></TR>";


$i=1;

while($row = mysql_fetch_array($result))
  {
  echo "<TR>";
  echo "<TD align='center'><FONT FACE='ARIAL' SIZE='1'>" . $i++ . "</FONT></TD>";
  echo "<TD><FONT FACE='ARIAL' SIZE='1'>" . strtoupper($row['title']) . "</FONT></TD>" ;
  echo "<TD><FONT FACE='ARIAL' SIZE='1'><B>" . strtoupper($row['artist']) . "</B></FONT></TD>" ;
  echo "<TD align='center'><FONT FACE='ARIAL' SIZE='1'>" . $row['timeplayed'] . "</FONT></TD>" ;
  echo "</TR>";
  }

echo "</TABLE>";


mysql_close();


?>

 

Any help would be appreciated.

[waynewex]

Manipulate what exactly?

[waynewex]

For one thing, you're not validating external data. You should really get into the habit of validating input as you code. Otherwise you'll forget and you'll have a nightmare on your hands.

 

Your code:

 

$numbertop = $_GET["numbertop"];
$timeperiod = $_GET["timeperiod"];

 

Fixed code:

$numbertop = mysql_real_escape_string(strip_tags($_GET["numbertop"]));
$timeperiod = mysql_real_escape_string(strip_tags($_GET["timeperiod"]));

 

You could also make sure that the ids are numeric:

 

if(is_numeric($_GET["numbertop"]) != 1){
    
    //input is invalid - do error handling
}

[EP]

Oh ok thanks.  I'll be sure to keep that in mind and make the appropriate changes.

 

Manipulate what exactly?

 

The data that is pulled from the database.

 

Ok, here is an example.  Say these entries are pulled out of the DB:

 

Song 2, Blur 3PM

Iris, Goo Goo Dolls 2:57PM

Minerva, Deftones 2:53PM

Song 2, Blur 1PM

 

 

I want to combine the entries that have the same title and artist values because that is just the same song being played multiple times.

 

So if I was using the data above, I'd want the result to look like..

 

Title, Artist (Hits)

 

Song 2, Blur (2)

Iris, Goo Goo Dolls (1)

Minerva, Deftones (1)

[waynewex]

Maybe....

 

$result = mysql_query("SELECT DISTINCT title, artist, timeplayed FROM `songhistory` WHERE timeplayed " . $timeplayedsearch . " ORDER BY title");

 

Or, you're going to have to pluck the DISTINCT title and artist, and then use the SQL function SUM to count up all the times played....

 

Not sure if that's any help

[DeanWhitehouse]

you may be able to use GROUP BY, in the query to group all the same songs?

[EP]

Would the best way be to put the results from mysql_fetch_array($result) into a new array and combine, count, and sort within that new array?

 

How would I do that?  Does anybody have any sample code?