Solved

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

Posted on 2007-04-04
11
336 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
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 count occurrences of each item in an array.

636 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