Jump to content

Finding "most searched for" in mysql table.


dagnasty

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.

Link to comment
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 )
Link to comment
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.
Link to comment
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.

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.