Solved

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

Posted on 2007-04-04
11
324 Views
Last Modified: 2013-12-12
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.
0
Comment
Question by:benwiggy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 2
11 Comments
 
LVL 24

Expert Comment

by:glcummins
ID: 18851454
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
 
LVL 6

Author Comment

by:benwiggy
ID: 18851530
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
 
LVL 6

Author Comment

by:benwiggy
ID: 18852335
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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 24

Expert Comment

by:glcummins
ID: 18852437
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
 
LVL 2

Expert Comment

by:taveirne
ID: 18854171
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
 
LVL 6

Author Comment

by:benwiggy
ID: 18854607
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
 
LVL 2

Accepted Solution

by:
taveirne earned 300 total points
ID: 18883899
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
 
LVL 6

Author Comment

by:benwiggy
ID: 18893947
I get home on saturday evening, will probs look at it sunday and post back thanks for the help!
0
 
LVL 2

Expert Comment

by:taveirne
ID: 18901150
any luck?
0
 
LVL 6

Author Comment

by:benwiggy
ID: 18902385
Its thursday so not yet read above comment! Will post back in 3 days!
0
 
LVL 6

Author Comment

by:benwiggy
ID: 18914753
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
PHP 7 issue seeing runtime MS SQL driver in PHP info page 5 36
Multi line FPDF footer: 3 25
JSON decode 5 18
Get data from two MySQL tables 6 23
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to dynamically set the form action using jQuery.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question