Finding "most searched for" in mysql table.

[dagnasty]

My brain is running in circles and I'm going nuts over this one.

I have a MySQL database with the row "searchterm" in it and I am looking for a way to pull the 10 most common search terms. I know.. I should have had a query that looked for a duplicate search result when it was put into the database, and added onto a counter. It would have made things a LOT easier at this point.

Here's an example if my explanation wasn't too good:

searchterm row:

cat
dog
dog
cat
cat
mouse
mouse
cat
dog


What I am looking to output:

cat = 4
dog = 3
mouse 2

Thanks to anyone who can enlighten me.

Database also contains a row id auto increment if that can help any.

[brown2005]

WELL UR BEST BET, AS FAR AS I KNOW, BUT IM NO EXPERT, IS TO CREATE A TABLE AND A SCRIPT THAT INSERTS EACH RESULT IN IT, AND DO IT THAT WAY

[dagnasty]

I have the script, and the database (with 40,000 rows)

[GingerRobot]

[code]
<?
<?php
$sql = mysql_query("SELECT * FROM `your table`");
while($row = mysql_fetch_array($sql))
{
 $search[$row[searchterm]]++;
}
print_r($search);
?>
[/code]

With the data i chucked in to test this, outputted
Array ( [dog] => 3 [cat] => 2 [rabbit] => 1 )

[dagnasty]

you got lucky that it was sorted correctly. It's 7 Am and i've been up all night working on this project.

Is there a quick way to sort the data?

sort(); doesn't seem to do exactly what I want. Maybe I'm doing it wrong... It seems to print out only the number and not the value, and it also sorts from 1 and up.

If you guys are willing to save me some time please feel free to help me out, if not, I'll wake up tomorrow and tackle the rest.

My brain isn't working right. I'm going to bed.

[brown2005]

SELECT *, COUNT(*) AS num FROM table ORDER BY num DESC LIMIT 10;

[bowenbowen]

SELECT COUNT(DISTINCT searchterm) AS `searchterm_count`, searchterm FROM table GROUP BY searchterm ORDER BY 'searchterm_count' DESC LIMIT 10

Give that a go, that should give you your top ten search terms descending

[dagnasty]

No count is set for each search term in the database.

or does "AS `searchterm_count`" not actually refer to a row?

And wouldn't that just set you up to count the rows and not actually contain the actual row information from the database?

Oh god i'm so tired.

[brown2005]

nope COUNT(DISTINCT searchterm) is counting the things in the row and then searchterm_count is just naming it....

[bowenbowen]

My bad sorry

This worked for me

SELECT DISTINCT searchterm, COUNT(*) AS `Count` FROM search GROUP BY searchterm ORDER BY `Count` DESC LIMIT 10

[brown2005]

yeap like i said in my original post.. but if u have a group by i dont think u need the DISTINCT, well i dont know, maybe u do. lol

glad it worked..

[bowenbowen]

Yeah you are right, don't need the distinct --> tired mind here as well.....I am now lost on what you want though, I thought you needed the top ten search terms? Surely that SQL achieves this with the example you provided?

Perhaps I getting wrong end of the stick or I am incredibly dum.....sorry if I am, because I have been wasting your time.

[brown2005]

i dunno, im confused.. lol

[dagnasty]

$result = @mysql_query("SELECT DISTINCT searchquery, COUNT(*) AS `Count` FROM tscrapestats GROUP BY searchquery ORDER BY `Count` DESC LIMIT 10");



while($row = mysql_fetch_array($result))
{
  $search[$row[searchquery]]++;
  $amount[$row[Count]]++;
}

This works! (tested with print_r) However, I don't know how to print each item out correctly. :) I tried to$search[0] and search[0][0] and got nothing? What method do I use?

[akitchin]

your array notation/usage is a little fudged up for your purposes.  it should work fine if you go:

[code]<?php
while (stuff = stuff)
{
  $search["{$row['searchterm']}"]++;
}
?>[/code]

this should yield an array of format $search[search_term] = searchterm_count.

[ShogunWarrior]

I believe:
[code]
SELECT
DISTINCT searchterm
,searchterm as st
, (SELECT COUNT(*) FROM search WHERE search.searchterm=st)
FROM search
[/code]

[b]Edit: Tested.[/b]