1

My site acts like a search engine where people enter search queries on the main page. I wanted to make a trending / recent feature where each query gets recorded into a mysql database, then from that data, calculates which searches are being searched the most, and then displayed back on the page labeled as trending searches. Also, under that, I would like "recent searches" which simply displays the last 5 or so searches.

Honestly, I have no experience with mysql. I don't even know how to move data from my site to mysql. Any help would be appreciated. I searched and searched these questions and google, but didn't find anything. Thanks!

Michael
  • 55
  • 5
  • no experience with mysql? do you have php experience? – Galen Apr 29 '11 at 19:01
  • That must be some pretty bad Google-Fu if you couldn't find ANYTHING on using MySql with PHP. Start typing "mysql php" and the first autocomplete is for "mysql php tutorial" and the first link is a tutorial on doing exactly what you're trying to do. – Tom H Apr 29 '11 at 20:03

1 Answers1

1

First of all, you need to CREATE a DATABASE, in which you want a table with a timestamp and the keyword that's been searched. (CREATE TABLE)

Then you want to store each keyword access into this table (INSERT INTO ... VALUES ...)

Then you can select the top key words by creating a SELECT query with a "GROUP BY keyword", ORDER ing by COUNT(*) (the number of occurrences of a keyword)

This is a bit vague, but you'll need to go through a number of steps so I've uppercased the terms you'd need to google for each step. Do come back if you run into complications in any of those steps!

Nicolas78
  • 5,124
  • 1
  • 23
  • 41
  • For my table, how many fields should I have? I was thinking the id, userQuery, and count. – Michael May 01 '11 at 21:54
  • that's a good idea in principle because you need less space than storing each individual query. however, if you want to select the timewindow, you'll need to store query and datetime, ie multiple entries for each query (if you're just adding, how do you know you're only counting the last say 24 hours? you might reset at say midnight but that's not really what you want) in order to not waste too much space, you might make one table query_id, query (storing unique queries) and then for each access query_id, datetime (like this you don't have to store the same string over and over again) – Nicolas78 May 01 '11 at 21:58
  • first, im having a problem getting my data into my table. Right now, my site uses a google custom search using the google search apis. its from that search that i want to store the queries from. I found a way to grab them using html and php. However, it won't work because the searchform function is done in javascript. Here's what I have for the html. – Michael May 03 '11 at 04:22
  • ah ok that's a bit tricky. you need to capture the query in javascript and send it to the server via an ajax query (have the javascript call sth like storequery.php?query=... ). google "php ajax" for the latter, no idea about the further, delve into the code, google "intercept custom search" or sth or post a separate question – Nicolas78 May 04 '11 at 11:33
  • Ok, I've managed to get the queries into my database by grabbing the string with jquery and using ajax to call php to send it to mysql. Now i have to figure out how to add +1 to count whenever they search for a duplicate, and sort the searches by count from greatest to least based on the last 24 hours, and then return say, the first 5. – Michael May 12 '11 at 15:49
  • nice so you've already come quite a way! now, perform a select id from table where search="current search". if you find anything, update table set count=count+1 where id=id, otherwise insert into table (search) values ("current search"). make sure you put an index on the search field or this is going to get reaaallly slow after a while – Nicolas78 May 13 '11 at 22:36
  • right now im trying to do a "recent search" where it displays the last 5 searches made. Using this query, I get the last 5 searches, but when I try to display it on my site it only returns the the last id 5 times. $sql="SELECT * FROM trending WHERE id > (SELECT MAX(id) - 5 FROM trending)"; – Michael May 16 '11 at 22:34
  • I'd try SELECT * FROM trending ORDER BY id DESC LIMIT 0,5 That doesn't really explain why you get five times the same id with your query (you shouldn't) but is the more canonical way of doing it, maybe the problem simply vanishes ;) alternatively, your output code might be doing something wrong tbh – Nicolas78 May 18 '11 at 12:52
  • I ended up using "SELECT searchstring FROM trending ORDER BY id DESC LIMIT 5". Now I just have to figure out how to do the trending. – Michael May 20 '11 at 01:13