Help with mysql query please

Dolemite50

Hello,

I have a site with songs that are rated from 1-10 such as
6.57, 9.02, 7.75.

I would like to setup a statement where I determine the songs ranking compared to the others such as:

This song is ranked #155 out of 4009 items.

I can use this to find the total song count:

$getGenSongCount = "SELECT COUNT(*) AS count FROM songs WHERE sgenre = '$sgenre'";
$rs5 = mysql_query($getSongCount, $conn);
$songCount = mysql_result($rs5, 0);

in the "songs" table there is a column called srating. I think I need to do something like:

Select * from songs ORDER BY srating DESC

but am still uncertain how to determine its Ranking compared to the other songs.

Thanks for any help. 🙂

Shrike

You've already answered the question really.

$songCount // Found in previous query
$q = "Select * from songs ORDER BY srating DESC";
$r = mysql_query($q);
$i = 0;
while($song = mysql_fetch_assoc($r))
{
  ++$i;
  echo $song['sname'] . " is ranked " . $i . " out of " . $songCount . "<br>";
}

Dolemite50

Just to confirm,

The songs are located on individual pages and I only need the ranking for that individual song. So if you go to mysite.com?songid=55, I'm only finding the ranking for that song on that page. How would I alter that?

And thank you very much. 🙂

Dolemite50

VROOOOM!

Way over my head with that one. 🙂

Isnt there a way that I could just adjust his loop to say something like

$this_song = 55;

and somewhere within the loop something like:

if $song == $this_song)

Sorry, I'm still pretty dang green. Thanks for the help. 🙂

Dolemite50

Hey,..what happened to Kudose's reply above my last one? Now I'm looking schizo here. 🙁

Kudose

This may not be bug free, or even work for that matter, it should be used to convey a point. You could set your entire ranked table into an array, then figure out the key, and add 1 to it to find the rank. I just used $current_rank and started it at one so I didn't have to add one to the key each time.

<?php
$sql = mysql_query("SELECT title FROM table ORDER BY rank ASC") or die(mysql_error());
$results = mysql_fetch_array($sql);
$current_rank = 1;
$rank_array = array();

foreach($results as $key => $val){
	$rank_array[$key]['rank'] = $current_rank;
	$rank_array[$key]['title'] = $results['title'];
	$current_rank++;
}

//print it all to make sure it's right
foreach($rank_array as $ranked){
	echo 'Title: '.$ranked['title'].' Rank: '.$ranked['rank'].'<br>';
}
?>

Kudose

Reading your addition posts, brings me to another solution as well.

If you know what song you're looking for, as you do since you're putting it in your URL, then you can just use another SQL query to find that particular song's rank. Then, yes, you can use something like:

if($this_song == 55){
//do stuff
}

If you have some code, you should post it up. It'd probably save some time and confusion.