PHP, mysql - get number of rows with most of a collumn and sort descending

Mysql, PHP

I am writing my own CMS - involved is a part where users can review different 'affiliates'.
The table called 'comments' has the following structure:
commentid affiliateid name affiliate rating comment email date ip status

Example row:
1 1 Bob Allposters 5 Posters,etc,etc x@y.com 2007-04-01 22:08:02 192.0.0.1 L

status can be either L (listed) or W (waiting)

I need the code so that I can generate a table of 'most reviewed' affiliates. The affiliateid with the most reviews which have status 'L' are at the top, descending to the least, limited to 10. This table will have 2 collumns, which are: the affiliate name (e.g. allposters) and the number of reviews.

I hope I have made what I am looking for clear.

Please ask if you need more clarification.
LVL 6
benwiggyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

glcumminsCommented:
SELECT count(*) as count FROM comments GROUP BY affiliateid WHERE status='L' ORDER BY count LIMIT 10;

This query will give you the number of reviews for each affiliate, ordered most to least, no more than ten results.
0
benwiggyAuthor Commented:
Ok, so could you help me finish this code? (The integration part)

<table style="width: 100%">
<tr class="title">
<td>Position</td>
<td>Name</td>
<td>Description</td>
<td>Reviews</td>
</tr>
<?php
$sql = "SELECT count(*) as count FROM comments GROUP BY affiliateid WHERE status='L' ORDER BY count LIMIT 10;";
$query = mysql_query($sql) or die(mysql_error());
$pos = '0';
while ($row = mysql_fetch_array($query)) {
$pos++;
echo "
<tr>
<td>".$pos."</td>
<td><a href='affiliate.php?affiliate=".$row[name]."'>".$row[name]."</a></td>
<td>".$row[description]."</td>
<td>".???."</td>
</tr>
";
}
?>
</table>
0
benwiggyAuthor Commented:
I tried your code:
$sql1 = "SELECT count(*) as count FROM comments GROUP BY affiliateid WHERE status='L' ORDER BY count LIMIT 10;";
$query1 = mysql_query($sql1) or die(mysql_error());
I get the error:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE status='L' ORDER BY count LIMIT 10' at line 1
Thanks!
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

glcumminsCommented:
Sorry, I had some of the elements out of order. Try it like this:

$sql1 = "SELECT count(*) as count FROM comments WHERE status='L' GROUP BY affiliateid ORDER BY count LIMIT 10;";
0
taveirneCommented:
select * from (
select name, count(name) as cnt from comments where status = 'L' group by name) order by cnt desc, name asc limit 10;

assuming that you have a 1:1 relationship of those affiliateid's to the name (affiliate name) column (which btw, i don't like the data model being set up like this) - i'm also assuming the above query works for you on mysql.  would on sql server/oracle.
0
benwiggyAuthor Commented:
taveirne, with your statement I get the error 'Every derived table must have its own alias'
glcummins, your statement works fine without errors, however why doesnt this work?

glcummins, I need to be able to complete this table, and so need to know the current affiliateid:

<table style="width: 100%">
<tr class="title">
<td>AffID</td>
<td>Position</td>
<td>Name</td>
<td>Description</td>
<td>Reviews</td>
</tr>
<?php
require 'config.php';
$sql1 = "SELECT count(*) as count FROM comments WHERE status='L' GROUP BY affiliateid ORDER BY count LIMIT 10;";
$query1 = mysql_query($sql1) or die(mysql_error());
$pos = '0';
while ($row = mysql_fetch_array($query1)) {
$pos++;
echo "
<tr>
<td>".$row['affiliateid']."</td>
<td>".$pos."</td>
<td><a href='affiliate.php?affiliate=".$row['affiliate']."'>".$row['affiliate']."</a></td>
<td>Description</td>
<td>".$row[0]."</td>
</tr>
";
}
?>
</table>

I have a feeling taveirne is a little closer here to what I'm looking for, but all help appreciated in finding a solution!
0
taveirneCommented:
yah that's where i don't have a mysql box to play on or a whole lot of coding time on it so you may have to correct my syntax errors and play with my code...  try

select cntTable.name, cntTable.cnt from (
select name, count(name) as cnt from comments where status = 'L' group by name) cntTable
order by cnt desc, name asc limit 10;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
benwiggyAuthor Commented:
I get home on saturday evening, will probs look at it sunday and post back thanks for the help!
0
taveirneCommented:
any luck?
0
benwiggyAuthor Commented:
Its thursday so not yet read above comment! Will post back in 3 days!
0
benwiggyAuthor Commented:
Thanks for your help, below is my final code, if you can see any obvious improvements/simplifications to it please let me know.
Thanks again!

The final code:

<table style="width: 100%">
<tr class="title">
<td>Position</td>
<td>Name</td>
<td>Description</td>
<td>Reviews</td>
</tr>
<?php
require 'config.php';
$sql1 = "SELECT *, cntTable.cnt from (
select *, count(name) as cnt from comments where status = 'L' group by affiliateid) cntTable
order by cnt desc, name asc limit 10;";
$query1 = mysql_query($sql1) or die(mysql_error());
$pos = '0';
while ($row = mysql_fetch_array($query1)) {
$pos++;
//Get info
$affiliate = $row['affiliate'];
$rtotal = $row['cnt'];
$sql2 = "SELECT * from affiliates WHERE name = '$affiliate'";
$query = mysql_query($sql2) or die(mysql_error());
while ($row = mysql_fetch_array($query)) {
$affiliateid = $row[id];
$name = $row[name];
$url = $row[url];
$description = $row[description];
}
echo "
<tr>
<td>".$pos."</td>
<td><a href='affiliate.php?affiliate=".$affiliate."'>".$affiliate."</a></td>
<td>".$description."</td>
<td>".$rtotal."</td>
</tr>
";
}
?>
</table>
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.