tips and tricks for movable type
top searches using PHP and MySQL
November 23, 2003
I've already shown you how I display recent searches on my blog. Here's how to display the top searches.
//connection info
include ("/path/to/your/connection/file/connect.php");
// config
$cgi_path = "<MTCGIPath>mt-search.cgi";
$show = 10;
$getlogs = "SELECT log_message, log_created_on, COUNT(*) as log_count FROM mt_log GROUP BY log_message ORDER BY log_count DESC";
$result = mysql_query($getlogs);
$i = 0;
while ($row = mysql_fetch_array($result)) {
$pos = strpos(($row['log_message']), "Search: ");
if ($pos !== false) {
$logs[$i] = str_replace("Search: query for ", "", ($row['log_message']));
$logs[$i] = substr($logs[$i], 1, -1);
$count[$i] = $row['log_count'];
$i++; if ($i == $show) {break;}
}
}
echo "<div class=\"sidetitle\">Top Searches</div>";
echo "<div class=\"side\">";
for ($i=0; $i<$show; $i++) {
echo "• <a href=\"$cgi_path?search=$logs[$i]&Template=tips&IncludeBlogs=8\">$logs[$i]</a> ($count[$i])<br />";
}
echo "</div>\n";
?>
As before, you must change /path/to/your/connection/file/connect.php to point to your connect file; and modify &Template=tips&IncludeBlogs=8 or remove it if it doesn't fit your needs.
I put both of these together for display on my site search page.
Related Links:
• recent searches using PHP and MySQL
• MT Searches plugin
TrackBack: 9
(URL: http://www.thegirliematters.com/sf/mt-track.cgi/163)
» http://www.mariwood.com/sideblog/previously/002220.html
Excerpt: I've wanted to include a list of searches on my blog!...
Weblog: Sideblog
Tracked: 11.24.03 08:09 PM
» Bloggie Scripty Reverb
Excerpt: I like to keep an up-to-date listing of all the...
Weblog: Bloggie Broad
Tracked: 12.16.03 06:07 PM
» 20 things on My 2 Do list
Excerpt: 1. Add smilies in the 2. | Category | 3. Counting for (x) Recent entries 4. Statistics: " x entries", "x comments", "x users online", "x on this page" script 5. Last 50 referrers 6. 10 most commented entriescomments 7....
Weblog: .::faithjean.com::.
Tracked: 01.08.04 02:25 PM
» 20 things on My 2 Do list
Excerpt: 1. Add smilies in the comments 2. | Category | 3. Counting for (x) Recent entries 4. Statistics: " x entries", "x comments", "x users online", "x on this page" script 5. Last 50 referrers 6. 10 most commented entries7....
Weblog: .::faithjean.com::.
Tracked: 01.08.04 02:29 PM
» Bloggie Scripty Reverb
Excerpt: BB's Script, Plugin and Hack Archive as of 3/1/04: Collapse...
Weblog: Bloggie Broad
Tracked: 03.01.04 05:42 PM
» Recent / Top Search Script Modification
Excerpt: 1/13/04 REVISION: I caught an error in the following code...
Weblog: Bloggie Broad
Tracked: 04.02.04 06:55 PM
» Erotic postcard
Excerpt: I got many referers from Google using keywords "erotic postcard". Of course, since I have posted about this before. Hehe,...
Weblog: Warnadunia.NET: Gath Nasional 2 Blogbugs - Jogjakarta
Tracked: 04.09.04 03:52 PM
» Recent / Top Search Modifications
Excerpt: I made an easy modification to Girlie's top and recent...
Weblog: Code Novice
Tracked: 04.14.04 11:35 PM
» Tweaking Searches
Excerpt: The search for that comes with default MT templates is very limited in what it lets you do - basically it only lets you search the current blog and allows you to not specify any oother options. Here are some...
Weblog: Movalog
Tracked: 08.10.04 01:06 AM
Comments
If you add a WHERE left( log_message, 6 ) = "Search" before your GROUP BY clause, you eliminate the need to search through your query results for Search. Then all you need to do is pick out the log_count column and the search strings.
Another good tip. Thx.
is it possible to have an MT entry with php that evaluates so I can use that entry page as a ui to a mysql db?
Just thought I'd pass on something I learned the hard way over the past few days. Evidently, a lot of commercial sites and some idiots are using sites with these recent search pages to boost their Google rankings.
My site, for example, had 5 different IPs performing 700+ searches a piece just seconds apart. It effectively overloaded my server (leading to a nasty note from my hosting company) and nearly got me shut down.
So I strongly recommend that if you use this kind of page, update your robots.txt file to prevent the search engines from indexing it - that way there's no incentive for people to take advantage of your page.
My top searches is not working anymore. It keeps displaying the recent searches and giving a warning on line 49 which is while ($row = mysql_fetch_array($result)) {
now why all of sudden is it not working?
Based on the discussion here I generated a php script for searching MT entries that you might find interesting. That is until Tim Appnel releases his XSearch/Plus plugin. It also spits out the probability of the result matching the searched terms based on a simple rubric for scoring words based on title, text, etc.
I posted the code up on the MT forums here:
http://www.movabletype.org/support/index.php?act=ST&f=14&t=45349
You can see it in action here:
http://depository.unfoldedorigami.com/cupboard/
Thanks!
Thank you so very much for these.... happen to run across it while exhausting all searches for MTSearch help. As you note, MTSearches doesn't work dynamically so is kinda useless for a site like mine. This worked perfectly along with your recent searches code.
I added the following line right after your title so the list is truncated for new sites:
if ($i
that way... the blank lines do not show.
Thx again!@