Count(*) as num_of.... can you then SORT this??

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Count(*) as num_of.... can you then SORT this??

Post by simonmlewis »

Code: Select all

$result = mysql_query ("SELECT keywords, COUNT(*) as num_of FROM searchlog GROUP BY keywords");
while ($row = mysql_fetch_array($result)){
echo "<tr><td width='70'>
<form method='post' action='index.php?page=a_searchlog&menu=blank&head=search log'>
<input type='hidden' name='keyword' value='" . $row['keywords'] ."'>
<input type='hidden' name='update' value='deletekeyword'>
<input type='submit' value='Delete'>
</form>
</td><td width='15' align='right'>" .$row['num_of'] . "&nbsp;</td><td> searches for <b>" . $row['keywords'] . ".</b></td></tr>";
		}			
	mysql_free_result($result);
for our web site, this currently produces over 200,000 rows. I can page number it, but I would like to sort it so the highest amount of "num_of's" are at the top.

I tried what I thought it would be, but it failed:
"WHERE num_of != 50" or "ORDER BY num_of DESC", but that produced errors.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
incubi
Forum Contributor
Posts: 119
Joined: Mon Dec 07, 2009 1:47 pm

Re: Count(*) as num_of.... can you then SORT this??

Post by incubi »

Hi,

Its ORDER BY you need but when you say it "produced errors" what are the errors?

Lee
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Count(*) as num_of.... can you then SORT this??

Post by simonmlewis »

ORDER BY ... what tho? I am not sorting it by a field in the database, but by the num_of COUNT results.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
incubi
Forum Contributor
Posts: 119
Joined: Mon Dec 07, 2009 1:47 pm

Re: Count(*) as num_of.... can you then SORT this??

Post by incubi »

Hmm, well you're selecting keywords and count * as num_of then trying to group by keywords. What are you counting? You should group by another field, not keywords if you're trying to count keywords. Otherwise you will need to use PHP Array Sort on num_of.
Also, still need to know what the error says.

Lee
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Count(*) as num_of.... can you then SORT this??

Post by simonmlewis »

I've actually altered the code now, so I cannot tell you the error any more - sorry.
'keywords' is a fieldname, so I am grouping by that field, but the problem I get is I have the values of the "num_of" all over the place.

It's a search log.
So I need to see it in order of how many "counts" of each keyword it has found.

This is the code now - note the '$limit', which I run from a form so we can restrict it on showing only those where the COUNT produce X amount of counts.
But still need ti extract it in an order.

Code: Select all

<div class='createbox'><div class='searchresults'>
<table cellpadding='0' cellspacing='0' width='100%' class='table'>";

$result = mysql_query ("SELECT keywords, COUNT(*) as num_of FROM searchlog GROUP BY keywords");
while ($row = mysql_fetch_array($result)){
if ($row['num_of'] >= $limit) {
echo "<tr><td width='70'>
<form method='post' action='index.php?page=a_searchlog&menu=blank&head=search log'>
<input type='hidden' name='keyword' value='" . $row['keywords'] ."'>
<input type='hidden' name='update' value='deletekeyword'>
<input type='submit' value='Delete' style='font-size: 10px'>
</form>
</td><td width='15' align='right'>" .$row['num_of'] . "&nbsp;</td><td> searches for <b>" . $row['keywords'] . ".</b></td></tr>";}
		}			
	mysql_free_result($result);
	mysql_close($sqlconn);
echo "</table></div></div>"; 
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
incubi
Forum Contributor
Posts: 119
Joined: Mon Dec 07, 2009 1:47 pm

Re: Count(*) as num_of.... can you then SORT this??

Post by incubi »

I would guess the error would have been "Can't group on num_of". In any case with the query as it is you I don't think you can get where you're going. You will need to sort it in php perhaps with ksort(mysql_fetch_array($result) but that depends on your key type. I would just load the results into an array then sort them.

Lee
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Count(*) as num_of.... can you then SORT this??

Post by mikosiko »

these doesn't work for you?:

Code: Select all

SELECT keywords, COUNT(*) as num_of 
  FROM searchlog 
  GROUP BY keywords
  ORDER BY num_of DESC;
or

Code: Select all

SELECT keywords, COUNT(*) as num_of 
  FROM searchlog 
  GROUP BY keywords
  ORDER BY COUNT(*) DESC;
incubi
Forum Contributor
Posts: 119
Joined: Mon Dec 07, 2009 1:47 pm

Re: Count(*) as num_of.... can you then SORT this??

Post by incubi »

I don't think they will give you the values/ordering you're looking for in num_of. I could be wrong but....

*EDIT*

okay ran the query and it does seem to order correctly for me but I only tested with 2 fields I'm guessing you have more.


Lee
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Count(*) as num_of.... can you then SORT this??

Post by simonmlewis »

Code: Select all

$result = mysql_query ("SELECT keywords, COUNT(*) as num_of FROM searchlog GROUP BY keywords ORDER BY COUNT(*) DESC");
while ($row = mysql_fetch_array($result)){
if ($row['num_of'] >= $limit) {
echo "<tr><td width='70'>
<form method='post' action='index.php?page=a_searchlog&menu=blank&head=search log'>
<input type='hidden' name='keyword' value='" . $row['keywords'] ."'>
<input type='hidden' name='update' value='deletekeyword'>
<input type='submit' value='Delete' style='font-size: 10px'>
</form>
</td><td width='15' align='right'>" .$row['num_of'] . "&nbsp;</td><td> searches for <b>" . $row['keywords'] . ".</b></td></tr>";}
		}			
	mysql_free_result($result);
Produces no results at all.

I want to do it from within the query, so that it doesn't always look at all 200,000 entries. If ORDER BY, then LIMIT..... I am essentially only dragging out a limited number of entries.

The majory of COUNT results will be 1, but there are a great many with high values. Hence the request.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
incubi
Forum Contributor
Posts: 119
Joined: Mon Dec 07, 2009 1:47 pm

Re: Count(*) as num_of.... can you then SORT this??

Post by incubi »

Change ORDER BY COUNT(*)
to

ORDER BY COUNT(*) DESC
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Count(*) as num_of.... can you then SORT this??

Post by mikosiko »

@simmon: Both queries are absolutely correct (and equivalents) and they should produce the results that you want based on your problem description. if you want to LIMIT the results just incorporate the LIMIT clause in the select... if you are no getting results most likely your problem is display and no the select. another equivalent query that allow you to overcome the fact that you can't use the alias in the WHERE clause is:

Code: Select all

SELECT keywords, COUNT(*) as num_of 
  FROM searchlog 
  GROUP BY keywords
  HAVING COUNT(*) > 50   // or HAVING num_of > 50 
  ORDER BY COUNT(*) DESC
//AND HERE you can add the LIMIT clause  like... LIMIT 100
my suggestion for you is to test just the query outside of PHP (PHPmyAdmin or whatever you use) first and check if it produce results and then concentrate in the display.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Count(*) as num_of.... can you then SORT this??

Post by mikosiko »

incubi wrote:Change ORDER BY COUNT(*)
to

ORDER BY COUNT(*) DESC
please read carefully.... what you are suggesting is already in the OP code

Code: Select all

$result = mysql_query ("SELECT keywords, COUNT(*) as num_of FROM searchlog GROUP BY keywords ORDER BY COUNT(*) DESC");
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Count(*) as num_of.... can you then SORT this??

Post by simonmlewis »

Code: Select all

$result = mysql_query ("SELECT keywords, COUNT(*) as num_of FROM searchlog GROUP BY keywords HAVING COUNT(*) >= $limit ORDER BY COUNT(*) DESC");
while ($row = mysql_fetch_array($result)){
echo "<tr><td width='70'>
<form method='post' action='index.php?page=a_searchlog&menu=blank&head=search log'>
<input type='hidden' name='keyword' value='" . $row['keywords'] ."'>
<input type='hidden' name='update' value='deletekeyword'>
<input type='submit' value='Delete' style='font-size: 10px'>
</form>
</td><td width='15' align='right'>" .$row['num_of'] . "&nbsp;</td><td> searches for <b>" . $row['keywords'] . ".</b></td></tr>";
		}			
	mysql_free_result($result);
This works a treat, and gives the administrator control over how many they limit it by - and reduces the amount of burden on the database, before it produces the results.

Many thanks. Also - never knew about 'HAVING' - that's a new little gem to my knowledge.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Count(*) as num_of.... can you then SORT this??

Post by mikosiko »

good....
.... and reduces the amount of burden on the database, before it produces the results
no true... but that is a challenge for you to find out why... hint: EXPLAIN
incubi
Forum Contributor
Posts: 119
Joined: Mon Dec 07, 2009 1:47 pm

Re: Count(*) as num_of.... can you then SORT this??

Post by incubi »

Good job mikosiko I learned something too, didn't know you can order by count. :)

Thanks
Lee
Post Reply