Hi,
Is there a way (code) I can display "Most Recent Searches" on my index page?
I'd probably place just above the footer.
Thanks
A point to remember here is that you will have uncensored results. So it may be susceptible to spamming. A way to get round that is to add a field called active and set that to 0 be default. Simply change the MySQL to print only active rows and hey presto, you have a moderated list. Down side is that you will need to moderate every search. :(
Alternatively you could use a bit of php code to filter out particular words that may be offensive.
There's my 2 pennies worth! Hope it helps.
Dave
Thanks,
I made the new Table as per the other thread, added the file you posted above.
All I get though is at the bottom of my page it says "Latest Searhes" but nothing appears?
I must be missing something obviously, any pointers?
Thank You
Hi,
Did you make the changes in search.php to insert queries into the database...
If so - can you use phpMyAdmin to check that the table is being updated...
Cheers,
David.
Hi,
I do have it displaying now :)
I forgot the search.php changes.
Last question, How to make this show the results Horizontal instead of vertical?
Thanks
Cool - try something like this for a horizontal display:
<?php
print "<p>Latest Searches</p>";
$sql = "SELECT * FROM querylog ORDER BY id DESC LIMIT 5";
if (database_querySelect($sql,$rows))
{
foreach($rows as $row)
{
$url = $config_baseHREF."search.php?q=".urlencode($row["query"]);
print "<a href='".$url."'>".$row["query"]."</a> ";
}
}
?>
Cheers,
David.
Hi David,
Have added this to a couple of sites, before if I searched for PS3 it would bring up the ps3 console.
Now it seems to be bringing up a lot more results than it previously did before.
{link saved} is one example
Before when I done a search, it would be the PS3 that were up the top, but now it seems to list misc products, and the true search results are either bottom of the page, or on another site, the ps3 isn't listed until page 2.
Any ideas?
Regards
Mark
Hi Mark,
Has this co-incided with adding new merchants to the site? Because the query "ps3" is under 4 characters, search.php uses the bases search method, not the FULL TEXT index. The reason that you see "pack of two waistslips 30ins" at #1 is because this search method ignores spaces, so it is the ps 3 highlighted in bold that causes the result.
I ask this because the modifications described in this thread to build the query log table should not have any impact on the search - that section of code isn't affected...
Cheers,
David.
Hi David,
If I can, I shall remove the new codes and then you will hopefully see a difference, give me a few mins
Hi David,
I still got the same results even with the code removal, but at {link saved} this only displays the PS3, unless theres no products that have PS3 as their title.
Is there anyway of tighting this up, so the actual PS3 gets top billing, rather than PS 3 for example on product descriptions.
Hating this mod already, as got to add it to about 5 different sites and I'm not in a work mood, more like a muddle mood :)
Regards
Mark
Hi Mark,
What you could do is change the 3-letter and below search query to use actual name rather than the version that has spaces removed. In search.php, look for the following code on line 73:
$sql = "SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants FROM `".$config_databaseTablePrefix."products` WHERE search_name LIKE '%".database_safe($parts[0])."%' GROUP BY name";
$sqlResultCount = "SELECT COUNT(DISTINCT(name)) as resultcount FROM `".$config_databaseTablePrefix."products` WHERE search_name LIKE '%".database_safe($parts[0])."%'";...and change this as follows:
$sql = "SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants FROM `".$config_databaseTablePrefix."products` WHERE name LIKE '%".database_safe($parts[0])."%' GROUP BY name";
$sqlResultCount = "SELECT COUNT(DISTINCT(name)) as resultcount FROM `".$config_databaseTablePrefix."products` WHERE name LIKE '%".database_safe($parts[0])."%'";In other words, just do a search and replace of "search_name" for "name".
Cheers,
David.
Hi David,
Seems to have worked perfectly {link saved}
So massive difference
I have also applied this to {link saved}
Before PS3 was on page 2 of 50 results per page.
So works a lot more than before, I just got to change the rest now and add it to my modified script that I'm keeping for future installs.
Regards
Mark
Hi David,
Your sql query is using 'id' so it can be problem for most recent searches.
I added new column on sql table (pt_querylog) which is 'timestamp':
ALTER TABLE `pt_querylog` ADD `timestamp` INT(11) NOT NULL AFTER `query`;in search.php line 428:
change the following code
if (isset($_GET["log"]))
{
$sql = "INSERT IGNORE INTO `".$config_databaseTablePrefix."querylog` SET query='".database_safe($q)."'";
database_queryModify($sql,$result);
$sql = "UPDATE `".$config_databaseTablePrefix."querylog` SET count=count+1 WHERE query='".database_safe($q)."'";
database_queryModify($sql,$result);
} if (isset($_GET["log"]))
{
$sql = "INSERT IGNORE INTO `".$config_databaseTablePrefix."querylog` SET query='".database_safe($q)."'";
database_queryModify($sql,$result);
$sql = "UPDATE `".$config_databaseTablePrefix."querylog` SET count=count+1, timestamp='".time()."' WHERE query='".database_safe($q)."'";
database_queryModify($sql,$result);
}in user_footer_before.php
change the following code
$sql = "SELECT * FROM pt_querylog ORDER BY id DESC LIMIT 5";$sql = "SELECT * FROM pt_querylog ORDER BY timestamp DESC LIMIT 5";Hello Kenan and welcome to the forum!
Ah yes - well spotted; a timestamp would indeed be required for most recent searches when incrementing `count` for popular search functionality...
Cheers,
David.
--
PriceTapestry.com
Hi,
To do this, you need to create a database table to record queries. There are instructions for doing this in the following thread (read the entire thread before you begin because there are changes and helper scripts discussed to make it easier):
http://www.pricetapestry.com/node/822
The scripts at the end of that thread show how to make a dedicated page listing the top "n" queries. To show the latest queries in the footer of every page, create a new file in your site as follows:
html/user_footer_before.php:
<?phpprint "<p>Latest Searches</p>";
print "<ul>";
$sql = "SELECT * FROM querylog ORDER BY id DESC LIMIT 5";
if (database_querySelect($sql,$rows))
{
$i = 0;
foreach($rows as $row)
{
$i++;
if ($i < 3) $style = "search1"; else $style = "search2";
$url = $config_baseHREF."search.php?q=".urlencode($row["query"]);
print "<li class='".$style."'><a href='".$url."'>".$row["query"]."</a></li>";
}
}
print "</ul>";
?>
You can of course change the HTML to make those queries display however you would like; and change the number 5 in "LIMIT 5" (line 4) for more or less recent searches!
Hope this helps,
Cheers,
David.