Page 1 of 1
Count(*) as num_of.... can you then SORT this??
Posted: Fri May 06, 2011 9:10 am
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'] . " </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.
Re: Count(*) as num_of.... can you then SORT this??
Posted: Fri May 06, 2011 10:15 am
by incubi
Hi,
Its ORDER BY you need but when you say it "produced errors" what are the errors?
Lee
Re: Count(*) as num_of.... can you then SORT this??
Posted: Fri May 06, 2011 10:18 am
by simonmlewis
ORDER BY ... what tho? I am not sorting it by a field in the database, but by the num_of COUNT results.
Re: Count(*) as num_of.... can you then SORT this??
Posted: Fri May 06, 2011 10:35 am
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
Re: Count(*) as num_of.... can you then SORT this??
Posted: Fri May 06, 2011 10:40 am
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'] . " </td><td> searches for <b>" . $row['keywords'] . ".</b></td></tr>";}
}
mysql_free_result($result);
mysql_close($sqlconn);
echo "</table></div></div>";
Re: Count(*) as num_of.... can you then SORT this??
Posted: Fri May 06, 2011 11:19 am
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
Re: Count(*) as num_of.... can you then SORT this??
Posted: Fri May 06, 2011 11:37 am
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;
Re: Count(*) as num_of.... can you then SORT this??
Posted: Fri May 06, 2011 11:48 am
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
Re: Count(*) as num_of.... can you then SORT this??
Posted: Fri May 06, 2011 12:11 pm
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'] . " </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.
Re: Count(*) as num_of.... can you then SORT this??
Posted: Fri May 06, 2011 12:24 pm
by incubi
Change ORDER BY COUNT(*)
to
ORDER BY COUNT(*) DESC
Re: Count(*) as num_of.... can you then SORT this??
Posted: Fri May 06, 2011 12:27 pm
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.
Re: Count(*) as num_of.... can you then SORT this??
Posted: Fri May 06, 2011 12:30 pm
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");
Re: Count(*) as num_of.... can you then SORT this??
Posted: Fri May 06, 2011 12:37 pm
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'] . " </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.
Re: Count(*) as num_of.... can you then SORT this??
Posted: Fri May 06, 2011 12:47 pm
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
Re: Count(*) as num_of.... can you then SORT this??
Posted: Fri May 06, 2011 12:49 pm
by incubi
Good job mikosiko I learned something too, didn't know you can order by count.
Thanks
Lee