Calculating a top 10 chart from 2 tables

So here's the situation.

I have 2 tables, one named r2m and the other named plays.

r2m holds data such as the title, artist and url of the song.

plays has a date field and an id to associate it with the r2m table to link both tables.


What I am trying to do is compile a top 10 chart for the week.

The code would be along the lines of;

SELECT id, artist, title, type, pic FROM r2m WHERE genre='$genre'

Followed by the code to link in the plays table, not sure how! (( order by how many times each song has been played in the last 7 days, using data from the 'plays' table. ))

Hope this makes sense, if not please ask and I'll re-phrase. Any assistance to compile the query would be much appreciated!

Thanks.

 

Maybe this needs some tweaking, but you get the idea.

SELECT
	r2m.*,
	COUNT(*) AS total
FROM
	plays
LEFT JOIN
	r2m
ON
	plays.songid = r2m.songid
WHERE
	r2m.genre = '$genre' AND
	plays.date >= " . strtotime('-2 Weeks') ."
GROUP BY
	r2m.title
LIMIT
	10

Code (sql):

 

you can either use a JOIN statement or the original way..

select r.id, r.artist, r.title, r.type, r.pic, p.date from r2m r, plays p WHERE r.id=p.id ORDER BY r.totalplay DESC LIMIT 10

Code (markup):

r beside r2m is a way to rename the table, so we dont have to repeat a long name, matching those fields.
r.id=p.id is the relationship term of how both table are joined/matched.
ORDER BY r.totalplay DESC is how you might want to sort the table.
LIMIT 10 is to limit the number of rows retrieved.

 

Thanks guys.

Nico, I've implemented your code and now have the following function:

function displayChart($genre)
{

	/* Query for music items */

	$result2 = mysql_query("SELECT r2m.*, COUNT(*) AS total FROM plays LEFT JOIN r2m ON plays.multimedia_id = r2m.id WHERE r2m.genre = '$genre' AND plays.play_date >= " . strtotime('-2 Weeks') ." GROUP BY r2m.title LIMIT 5");

	/* END Query for music items */

	$counter = 1;

	echo'
		<!--begin chart //-->
		<p><strong>' . $genre . ' top 5 of the week</strong></p>
	';

while ($row = mysql_fetch_assoc($result2))
{
		$artist = $row['artist'];
		$title = $row['title'];
		$type = $row['type'];
		$pic = $row['pic'];
		$id = $row['id'];


		$artist_link = $row['artist'];
		$artist_link = str_replace(' ','-',$artist_link);

		$title_link = $row['title'];
		$title_link = str_replace(' ','-',$title_link);


	echo'
		<p class="chart"><strong>' . $counter . '</strong> <a href="/' . $genre . '-music/' . $artist_link . '/' . $title_link . '" title="view multimedia for ' . $artist . '">' . $artist . ' - ' . $title . '</a></p>
	';

	$counter ++;
}


	echo'
		<!--end chart //-->
	';

	mysql_free_result($result2);

}

Code (markup):

The chart does not display however. Any ideas?

 

What field type are you using for the date/time of the play? Can I have an example entry?

 

Date of play is just a date field. Here are some of the records within the plays table.

play_id(int)........... multimedia_id(int).......... play_date(date)
1 ............................ 1 ............................ 2007-09-16
2 ............................ 2............................. 2007-09-16
9 ............................ 1............................. 2007-09-17

 

Instead of:

plays.play_date >= " . strtotime('-2 Weeks') ." 

Code (sql):

Try:

plays.play_date >= (plays.play_date - INTERVAL 1 WEEK)

Code (sql):

I have to admit, I'm not too familiar with SQL date/time functions. I usually work with INT fields and UNIX timestamps. So it might not work...