Jump to content


Photo

Finding "most searched for" in mysql table.


  • Please log in to reply
15 replies to this topic

#1 dagnasty

dagnasty
  • Members
  • PipPipPip
  • Advanced Member
  • 66 posts

Posted 13 July 2006 - 09:43 AM

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.



#2 brown2005

brown2005
  • Members
  • PipPipPip
  • Advanced Member
  • 943 posts

Posted 13 July 2006 - 09:48 AM

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

#3 dagnasty

dagnasty
  • Members
  • PipPipPip
  • Advanced Member
  • 66 posts

Posted 13 July 2006 - 09:53 AM

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

#4 GingerRobot

GingerRobot
  • Staff Alumni
  • Advanced Member
  • 4,086 posts
  • LocationUK

Posted 13 July 2006 - 09:55 AM

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

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

#5 dagnasty

dagnasty
  • Members
  • PipPipPip
  • Advanced Member
  • 66 posts

Posted 13 July 2006 - 10:50 AM

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.

#6 brown2005

brown2005
  • Members
  • PipPipPip
  • Advanced Member
  • 943 posts

Posted 13 July 2006 - 10:56 AM

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

#7 bowenbowen

bowenbowen
  • Members
  • Pip
  • Newbie
  • 8 posts
  • LocationIpswich, Suffolk, UK

Posted 13 July 2006 - 10:57 AM

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

#8 dagnasty

dagnasty
  • Members
  • PipPipPip
  • Advanced Member
  • 66 posts

Posted 13 July 2006 - 10:58 AM

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.



#9 brown2005

brown2005
  • Members
  • PipPipPip
  • Advanced Member
  • 943 posts

Posted 13 July 2006 - 11:11 AM

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

#10 bowenbowen

bowenbowen
  • Members
  • Pip
  • Newbie
  • 8 posts
  • LocationIpswich, Suffolk, UK

Posted 13 July 2006 - 11:20 AM

My bad sorry

This worked for me

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


#11 brown2005

brown2005
  • Members
  • PipPipPip
  • Advanced Member
  • 943 posts

Posted 13 July 2006 - 11:26 AM

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..

#12 bowenbowen

bowenbowen
  • Members
  • Pip
  • Newbie
  • 8 posts
  • LocationIpswich, Suffolk, UK

Posted 13 July 2006 - 11:31 AM

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.

#13 brown2005

brown2005
  • Members
  • PipPipPip
  • Advanced Member
  • 943 posts

Posted 13 July 2006 - 11:41 AM

i dunno, im confused.. lol

#14 dagnasty

dagnasty
  • Members
  • PipPipPip
  • Advanced Member
  • 66 posts

Posted 13 July 2006 - 06:23 PM


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

#15 akitchin

akitchin
  • Staff Alumni
  • Advanced Member
  • 2,516 posts
  • LocationCalgary, AB, Canada

Posted 13 July 2006 - 06:35 PM

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

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

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

#16 ShogunWarrior

ShogunWarrior
  • Members
  • PipPipPip
  • Advanced Member
  • 528 posts
  • LocationIreland

Posted 13 July 2006 - 10:30 PM

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

Edit: Tested.
<a href="http://www.daviddora...nmedia.com/">My New Site/Blog</a> | <a href="http://www.daviddora...m/check/">Check your page for broken links/images/scripts</a>

Zend Certified Engineer
Follow me on Twitter: http://twitter.com/davidd




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users