Selecting id and order from list of votes.

[starvinmarvin14]

I have two tables. One is a table with information about a certain id number. The other table is a list of votes (either up or down) with the id from the first table. I want to be able to list the id from the first table descending in the order of the highest number of upvotes from the second table. I want to do this with one mysql query. Or any way I can get the $statement variable out of the while loop.

 

This is what the two tables look like...

 

Rants - id | name | rant

Votes - rantid | vote

 

id from Rants should match the rantid from votes

 

Here is what I currently have which I would like to accomplish in only one mysql_query instead of two...

 

$votes = mysql_query("SELECT *, COUNT(*) AS nrRatings FROM votes WHERE vote = 'up' GROUP by rantid ORDER BY nrRatings DESC");

 

while ($row2 = mysql_fetch_array($votes)){

$rantid = $row2['rantid'];

$statement = "rants WHERE id = '$rantid'";

$query = mysql_query("SELECT * FROM {$statement}");

 

while ($row = mysql_fetch_array($query)){

// DISPLAY ROW INFO

}

}

 

How can I do this with one query? I would need to call both tables in the same query. Let me know if you need more clarification. Please help. Thanks!

[cpd]

"SELECT votes.*, rants.*, COUNT(votes.*) AS nrRatings FROM votes LEFT JOIN rants ON votes.rantid = rants.id WHERE vote = 'up' GROUP by rantid ORDER BY nrRatings DESC"

 

That should get everything for you .

 

Research joins and you'll understand how that query works.

[fenway]

Yes, except you can't merge * and GROUP BY.

[starvinmarvin14]

I have this...

 

$statement = "votes LEFT JOIN rants ON votes.rantid = rants.id WHERE vote = 'up' GROUP by rantid ORDER BY nrRatings DESC";

$query = mysql_query("SELECT votes.*, rants.*, COUNT(votes.*) AS nrRatings, DATE_FORMAT(rants.date, '%M %e, %Y, %l:%i%p') as newdate FROM {$statement} LIMIT {$startpoint} , {$limit}");

 

while ($row = mysql_fetch_array($query)){

/// ROW INFO

}

 

And I am getting this error...

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/...../letsrant/top.php on line 34

 

[fenway]

Then ask mysql for the error.

[starvinmarvin14]

Sorry for being a newb  . How exactly do I do that?

[Muddy_Funster]

lets tidy this up a little:

$statement = "votes LEFT JOIN rants ON votes.rantid = rants.id WHERE vote = 'up' GROUP by rantid ORDER BY nrRatings DESC";
$query = mysql_query("SELECT votes.*, rants.*, COUNT(votes.*) AS nrRatings, DATE_FORMAT(rants.date, '%M %e, %Y, %l:%i%p') as newdate FROM {$statement} LIMIT {$startpoint} , {$limit}");

turns into:

$sql = <<<SQL_BLOCK
SELECT votes.*, rants.*, COUNT(votes.*) AS nrRatings, DATE_FORMAT(rants.date, '%M %e, %Y, %l:%i%p') as newdate 
FROM votes LEFT JOIN rants ON votes.rantid = rants.id 
WHERE vote = 'up' 
GROUP by rantid 
ORDER BY nrRatings DESC
LIMIT $startpoint , $limit";
SQL_BLOCK;
$query = mysql_query($sql) or die(mysql_error()."<br><br>Returned by Server when atempting to run the following query:<br>$sql");

 

not the use of the or die to capture the SQL error and return it to the screen.  Also note I havn't actualy changed any of your SQL, only formated things a little differently so that when the error comes back you can see the SQL statement that was sent to the server as it was at that point.