[SOLVED] Help with a query hitting several tables

[limitphp]

I'm not sure if I'll need to break this into two queries or if I can do it all in one query.

 

Goal: I'm trying to display all songs by an artist and sort them by popularity.

 

I'll have to goto the songs table to get all songIDs where artistID = $artistID

 

Then I'll have to go to the vote table and count the number of entries for each songID COUNT(vote.songID) and order them by descending order DESC.

 

Possible Problem: An artist might have a song in the songs table that has zero votes.  So, it might be in the song table, but not the vote table.  I would need those songs to be last in the results.

 

I thought I could do the query by myself, but it doesn't seem to work.

<?php
$querySongs = "SELECT songs.artistID, vote.songID, songs.songID FROM songs, vote WHERE songs.artistID=$artistID AND vote.songID=songs.songID ORDER BY COUNT(vote.songID) DESC";
$resultSongs = mysql_query($querySongs) or die (mysql_error());
$greckleVoteCount = 0;
while ($rowSongs = mysql_fetch_assoc($resultSongs))
{ 
$greckleVoteCount++;	

$queryTotalVote = "SELECT songID FROM vote WHERE songID = '$rowSongs[songID]'";
$resultTotalVote = mysql_query($queryTotalVote) or die (mysql_error());
$totalVoteCount=mysql_affected_rows();
//********* GET SONG COMMENTS ********************
$queryComments = "SELECT songID FROM songcomments WHERE songID = '$rowSongs[songID]'";
$resultComments = mysql_query($queryComments) or die (mysql_error());
$commentCount=mysql_affected_rows();
//************************************************************************************************	
?>

 

It doesn't seem to be getting the song that doesn't have any votes.

[limitphp]

hmmm....

I'm guessing its not pulling songs with zero votes because of the

WHERE songs.artistID=$artistID AND vote.songID=songs.songID

 

its saying AND .

In other words, only pull where the songID appears in the vote table.

 

I think I might need that vote.songID=songs.songID in there so it can count the votes for each song and put them in desc order.

 

But how do I make sure it grabs the songs in songs table that aren't in the vote table (have zero votes)?

[limitphp]

maybe its my policy that is flawed.

 

I know on digg.com when you add a news story, it automatically gives it one digg by the person who submits the story.

 

problem is, I'll be the one adding the songs to the database (I assume similar to itunes), not individual users.

 

 

[limitphp]

thinking aloud (please feel free to chime in, as I am just one guy who's very new to all this trying to build a pretty big website):

 

If I can change it so all songs are in the vote table, this would simplify the query needed greatly.

 

Maybe the procedure can be, whenever a new artist signs a contract and wants to put their music on my website, I will create a user for each of the band members.  They'll be like normal users, only they'll have access to stats abuot how their sales are doing and they'll be able to update any band info.

 

Then when I add songs for the band, I'll just automatically make each band member vote for the song.

 

 

[limitphp]

Ok, I decided to change the policy.  All songs will now start out with at least 1 vote.

 

Ok, so I added a vote to the test song that didn't have one before. 

I also changed the query.

 

So now all I need is to grab all songs from the vote table where ArtistID = $artistID.

Then I need to grab all the song info for each song from the songs table where songs.songID = vote.songID and ORDER it all by COUNT(vote.songID) DESC

 

here is the new code:

<?php
$querySongs = "SELECT * FROM vote, songs WHERE vote.ArtistID=$artistID AND 
songs.songID=vote.songID ORDER BY COUNT(vote.songID) DESC";
$resultSongs = mysql_query($querySongs) or die (mysql_error());
$greckleVoteCount = 0;
while ($rowSongs = mysql_fetch_assoc($resultSongs))
{ 
?>

Problem is, its still only showing one song.  Its showing the first song it hits in the vote table.

 

Does anyone know why its not going all the way down in the vote table and grabbing the next one?

 

Could it be that I have SELECT *?

Should I put SELECT vote.xxx, vote.xxx, songs.xxx, songs.xxx, etc instead?

 

[limitphp]

ok, I tried changing the select * to each column name....still no luck.

 

Maybe its the while....

will this grab all records until there are no more?

while ($recordVote = mysql_fetch_assoc($resultVote))

 

thanks

[limitphp]

fixed it....

 

I was missing the GROUP BY vote.songID

 

now it works!