how about a WHERE clause.
Maybe something like:
$query = "SELECT WEEK(date) as week, count(*) as hits, listingID as lidcount FROM invotestotal WHERE WEEK(date) = 8 ORDER BY hits DESC";
Ryan Schefke wrote:
I am trying to echo the ranking of votes (highest to lowest) that a product received in a given week. See example of desired output.
In the query below, It's working great and outputs ID, votes, and week; however, I can't figure out how to get the ranking for a given week. In the example below, how would I be able to output values in the Ranking column for week 8 so that ID 1 is ranked 1, ID 3 is ranked 2, etc. Help?
Example ranking:
ID Votes Ranking Week
1 29 1 8
3 23 2 8
7 2 3 8
1 2 1 10
-------------------- QUERY ------------------------------
$query = "SELECT WEEK(date) as week, count(*) as hits, listingID as lidcount
FROM
invotestotal GROUP BY week, lidcount ORDER BY week DESC, hits DESC";
$result = @mysql_query($query);
while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
$printweek = $row[0];
$countinvotes = $row[1];
$lidnumber = $row[2];
Then, echo values in table rows...
}
-------------------- QUERY ------------------------------
-------------------- DEFINITIONS IN QUERY ------------------------------
WEEK(date) as week :: week vote took place
count(*) as hits :: counts total votes
listingID as lidcount :: ID of the product that received the vote
-------------------- DEFINITIONS IN QUERY ------------------------------
Best regards,
Ryan
-- Respectfully, Ligaya Turmelle
"Life is a game.... so have fun"
-- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
