Solved

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

Posted on 2007-04-04
11
310 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
  • 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

707 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now