Jump to content

Archived

This topic is now archived and is closed to further replies.

dagnasty

Finding "most searched for" in mysql table.

Recommended Posts

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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
[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 )

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
nope COUNT(DISTINCT searchterm) is counting the things in the row and then searchterm_count is just naming it....

Share this post


Link to post
Share on other sites
My bad sorry

This worked for me

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

Share this post


Link to post
Share on other sites
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..

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

$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?

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
I believe:
[code]
SELECT
DISTINCT searchterm
,searchterm as st
, (SELECT COUNT(*) FROM search WHERE search.searchterm=st)
FROM search
[/code]

[b]Edit: Tested.[/b]

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.