[SOLVED] Query inside a query

[limitphp]

I have a query:

$queryVote = "SELECT vote.artistID, vote.songID, vote.Date, artist.artistName, songs.songName, songs.genre, COUNT(vote.songID) FROM vote, artist, songs WHERE vote.artistID=artist.artistID AND vote.songID=songs.songID AND vote.Date > NOW()-INTERVAL 7 DAY GROUP BY vote.songID ORDER BY COUNT(vote.songID) DESC";
$resultVote = mysql_query($queryVote) or die (mysql_error());

while ($recordVote = mysql_fetch_assoc($resultVote))
{ 

  
....display some stuff  in html

}

The query works great.  It counts all songID fields from table vote where the date field is greater than 7 days and returns them in descending order.

 

Thats what I want.  I want it to only get the records where the date field is greater than 7 days, BUT.....

When I display the number of votes, I want it to display ALL OF THE VOTES.

In other words, I want to somehow, inside of this query get a count of all of the votes (number of records where songID is the same) to display it.

 

Kind of like on digg.com.

You can select the top news stories for only the past 7 days.  But when it displays each news story, you see the total number of diggs it had, not just the number of diggs in the past 7 days.

 

Does this make sense?  Sorry, I'm not that good at explaining exactly what I need help with.  I don't know all the technical terms yet, and I'm not that great at php.

 

Thanks for any help....

[.josh]

perhaps you're looking to do a GROUP BY?

[limitphp]

perhaps you're looking to do a GROUP BY?

 

I'm not sure.  The query does have a GROUP BY in it, to group all the songID's together.

 

Actually, the query could be as simple as this:

$queryVote = "SELECT songID, Date, FROM vote WHERE Date > NOW()-INTERVAL 7 DAY";
$resultVote = mysql_query($queryVote) or die (mysql_error());
while ($recordVote = mysql_fetch_assoc($resultVote))
{ 

     inside here I will need another query to do 
    "SELECT * FROM vote WHERE songID = '$recordVote[songID]'";
   $totalVoteCount=mysql_affected_rows();

}

 

Can you do that?

That wouldn't work, would it?

Can it grab all of them, if its inside a query that is only grabbing some of them?

 

 

[.josh]

Well I mean, you could do it like that (using the correct php syntax, obviously), but I have a feeling you can probably do it in a single query. I guess if you already have a group by in there, maybe you could do it using a JOIN of some kind.  But I freely admit I'm no expert on sql.  I'll move your thread to the sql forum; you'll have a much better chance of getting a real answer there.

[limitphp]

nevermind.  Just putting this inside:

"SELECT * FROM vote WHERE songID = '$recordVote[songID]'";

  $totalVoteCount=mysql_affected_rows();

 

that worked!  Success!

 

So, even though, the query is inside another query that is only grabbing some of the songIDs where Date > whatever, it doesn't matter.

The query inside it can still grab as many records regardless.

I learned something new today!