Hello,
I have seen some "top searches" on www.kelkoo.co.uk . There are the frequently searched items. Could i add this feature too on my pt?
Thanks,
Gabriel
Hi,
Try this....
$url = $config_baseHREF."search/".urlencode($row["query"]);
print "<li><a href='".$url."'>".$row["query"]."</a></li>";Cheers,
David.
Hi,
Works preety good. But how can i show the most searched keywords in a bigger text size and the less searched keywords in a smaller text size like kelkoo does?
Thanks,
Gabriel
Hi Gabriel,
You would need to define some styles in your CSS to do this, perhaps something like this:
.search1 { font-size: 12px; }
.search2 { font-size: 10px; }Then, put a counter in your loop that displays the searches and decide which style to use, something like this:
<?php
print "<p>Top Searches</p>";
print "<ul>";
$sql = "SELECT * FROM querylog ORDER BY count DESC LIMIT 10";
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>";
?>
There would be plenty of other ways to do it but this should help point you in the right direction...
Cheer,
David.
hi david,
can you please make an easy php script that will auto include the sql statements on mysql database? similar to createQuerylog.php on this thread
i am having a little trouble making this specific mod work.
thank you in advance!
david,
or an integration of the topsearches and the list of other sitemap based on query logs on this thread.
http://www.pricetapestry.com/node/822
i would like to implement both but it seems like i am having issues making it work.
thank you very much.
Hi,
The full SQL script would be something like this:
<?php
require("includes/common.php");
$sql =
"
CREATE TABLE `querylog` (
`id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`count` INT ( 11 ) NOT NULL ,
`query` VARCHAR( 255 ) NOT NULL ,
UNIQUE (
`query`
)
) ENGINE = MYISAM ;
";
database_queryModify($sql,$rows);
print "<p>".mysql_error()."</p>";
print "<p>Done.</p>";
?>
I've added a MySQL error message print out to this incase there are any problems. This table is all you need to implement both top searches and the searches sitemap. Let me know if you need any other pointers...
Cheers,
David.
hi david,
i am getting an error
Parse error: syntax error, unexpected T_ELSE in /home/ACCOUNTUSERNAME/public_html/html/footer.php on line 166
the line 166 is
if ($i
Hi,
There is a missing semi-colon in that line (i've now corrected it in the post above) - correct code should be:
if ($i < 3) $style = "search1"; else $style = "search2";Cheers,
David.
hi david,
the error is gone now.
but i cant see anything on the top searches list.
is there a minimum number of search before a specific keyword could be visible on the top searches?
Hi atman,
You should see results straight away.
You mentioned that you have done both mods, is the sitemap showing links to search queries as this will prove that the database is being updated with each new query...
david,
there is no search queries on the top search .. I placed the top searhc script on the /html/footer.php but its not showing any result.
Hi atman,
What I need to know is whether the queries are getting into the database. Do you have phpMyAdmin or other MySQL administration tool that you can browse the querylog table to confirm that queries are being logged?
Cheers,
David.
david,
i have phpmyadmin
i tried to export the querylog database but all i got is this
"id","count","query"
it seems like it is not adding the search queries.
Hi,
If the table is not being filled, then the problem is down to this part of the modification (from above):
if ($q)
{
if ($_GET["log"])
{
$sql = "INSERT INTO querylog SET query='".database_safe($q)."'";
database_queryModify($sql,$result);
}
.. rest of code ..However, this could mean that the hidden form field "log" is not on your search form. First, double check that it is in place. The modification should have been made in html/searchform.php.
If you're happy that it is in place, change the above code in search.php as follows:
if ($q)
{
if ($_GET["log"])
{
$sql = "INSERT INTO querylog SET query='".database_safe($q)."'";
database_queryModify($sql,$result);
print "<p>MySQL Error: ".mysql_error()."</p>";
}
.. rest of code ..The, do a search, and see if an error message is displayed. If you do not even see the words "MySQL Error:" then again it points to the problem being the "log" parameter on the form, which must be present to inform the search script to log the query.
Hope this helps.
Cheers,
David.
hi david,
your correct.
the problem is in my form.
i am using search mod searchRedirect.php
and just added this code
i am using the default form and its working fine now except that the search result output URL would be
http://example.com/search.php?q=SEARCHEDKEYWORD&log=1
can it be modified to be compatible with
mod searchRedirect.php
thank you very much
i am also having trouble
MySQL Error: Duplicate entry 'christopher' for key 2
i can simply remove the
print "MySQL Error: ".mysql_error()."";
and its back to normal.
Hi atman,
To use this via searchRedirect.php I you should just need to make this change in your .htaccess
Existing (from the mods in this thread)
RewriteRule ^search/(.*)/$ search.php?q=$1&rewrite=1&%{QUERY_STRING} [L]Change to:
RewriteRule ^search/(.*)/$ search.php?q=$1&rewrite=1&log=1&%{QUERY_STRING} [L]Cheers,
David.
Don't worry about the error (it's supposed to be like that) - just delete the print statement and it will work fine...
Cheers,
David.
just added this and I get the following error code when I'm trying to show the results
Top Searches
Fatal error: Call to undefined function: database_queryselect() in /home/magsub/public_html/display.php on line 5
Any advice?
Hi,
This sounds like you are just using a snippet from code above in a standalone file (that you have called display.php), and I suspect that this file will not have had the database library include by the require("includes/common.php"); statement.
If you paste the content of display.php inside the following code it should work as expected:
<?php
require("includes/common.php");
require("html/header.php");
**** INSERT YOUR CODE HERE ***
require("html/footer.php");
?>
Hope this helps!
Cheers,
David.
I've tried and think suceeded to install this code on my Popular Magazines page, however instead of popular searches, is it possible to show popular products. By this I mean offers that people have selected to go to the merchant?
Mally
Hi Mal,
As it stands there is no clicks per product count, it is based on merchants. However, it would be relatively straight forward to add. If you have phpMyAdmin, simply add a "clicks" field of type "INT" to the products table. Then, in jump.php, look for the following code:
$sql = "UPDATE `".$config_databaseTablePrefix."feeds` SET clicks=clicks+1 WHERE merchant = '".$product["merchant"]."'";
database_queryModify($sql,$insertID);...and ADD the following new code immediately after this:
$sql = "UPDATE `".$config_databaseTablePrefix."products` SET clicks=clicks+1 WHERE id='".database_safe($_GET["id"])."'";
database_queryModify($sql,$insertID);That will give you a clicks per product, which could then be used to give a Top Products list as follows (include this anywhere on a page that has the standard Price Tapestry include files already included, just as with Top Searches):
<?php
print "<p>Top Products</p>";
print "<ul>";
$sql = "SELECT name FROM products ORDER BY clicks DESC LIMIT 10";
if (database_querySelect($sql,$rows))
{
foreach($rows as $row)
{
if ($config_useRewrite)
{
$url = $config_baseHREF."product/".tapestry_hyphenate($row["name"]).".html";
}
else
{
$url = $config_baseHREF."products.php?q=".urlencode($row["name"]);
}
print "<li><a href='".$url."'>".$row["name"]."</a></li>";
}
}
print "</ul>";
?>
Cheers,
David.
Hello David, that works well and I've added it to the popular
magazines page.
This I can imagine being a popular mod with other users.
As an enhancement, would it be possible for the following?
1) - Popular products in the last 24 hours
with the addition of a rss feed, so I could use it on blogs/forums
2) - popular products showing the name and best price available?
thanks
Mally
Hi Mal,
To select the best price for the most popular products, try changing the SQL within the Top Products section from:
$sql = "SELECT name FROM products ORDER BY clicks DESC LIMIT 10";to:
$sql = "SELECT name,MIN(price) as minPrice FROM products GROUP BY name ORDER BY clicks DESC LIMIT 10";...and then where you display the product, you can use the "minPrice" field - for example:
print "<li><a href='".$url."'>".$row["name"]."</a> - Best Price: ".$config_currencyHTML.$row["minPrice"]"</li>";Popular products within the last 24 hours is more tricky as you would require a second "last 24 hours" counter in the products table, and something like a CRON process to reset this value and generate the feed once each day. I would also recommend using the same CRON process to generate the RSS feed and write it to disk at the same time (rather than serving it dynamically). Are you able to setup this on your server?
Cheers,
David.
Hello david
I added the above code (there was a missing . at the end of print "<li><a href='".$url."'>".$row["name"]."</a> - Best Price: ".$config_currencyHTML.$row["minPrice"]."</li>"; )
It works hoever shows different products than the basic top products, these should be the same magazines shouldn't they, just without the prices at popular magazines
I have full cpanel access so should be able to setup a cron, not that I've done it before..
thanks
Mally
Ah yes. The problem here is that click count is based on a single product/merchant, whereas the same product may also be available from other merchants. A possible solution is to determine popular products not by the click count of a single product, but by the total click count for that product across all merchants. Instead of:
$sql = "SELECT name,MIN(price) as minPrice FROM products GROUP BY name ORDER BY clicks DESC LIMIT 10";...try this instead:
$sql = "SELECT name,MIN(price) as minPrice,SUM(clicks) as totalClicks FROM products GROUP BY name ORDER BY totalClicks DESC LIMIT 10";Now, this may still not mirror the previous results exactly, but because it is now summing the click counts it should be a more accurate result...
Cheers,
David.
David,
I am trying, largely unsuccessfully, to add top 10's to my homepage. I am trying to add products, merchants, brands and categories.
The code I am using on the homepage is:
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td><?php
print "<p><b class='u'>Top 10 products on Online Shopping For</b>";
$sql = "SELECT name FROM products ORDER BY clicks DESC LIMIT 10";
if (database_querySelect($sql,$rows))
{
foreach($rows as $row)
{
if ($config_useRewrite)
{
$url = $config_baseHREF."product/".tapestry_hyphenate($row["name"]).".html";
}
else
{
$url = $config_baseHREF."products.php?q=".urlencode($row["name"]);
}
print "<br><a href='".$url."'>".$row["name"]."</a>";
}
}
print "</p>";
?></td>
<td width="19" rowspan="3"></td>
<td><?php
print "<p><b class='u'>Top 10 merchants on Online Shopping For</b>";
$sql = "SELECT merchant FROM feeds ORDER BY clicks DESC LIMIT 10";
if (database_querySelect($sql,$rows))
{
foreach($rows as $row)
{
if ($config_useRewrite)
{
$url = $config_baseHREF."merchant/".tapestry_hyphenate($row["name"]).".html";
}
else
{
$url = $config_baseHREF."merchants.php?q=".urlencode($row["name"]);
}
print "<br><a href='".$url."'>".$row["name"]."</a>";
}
}
print "</p>";
?></td>
</tr>
<tr>
<td height="19" colspan="2"></td>
</tr>
<tr>
<td><?php
print "<p><b class='u'>Top 10 brands on Online Shopping For</b>";
$sql = "SELECT brand FROM brand_cache ORDER BY clicks DESC LIMIT 10";
if (database_querySelect($sql,$rows))
{
foreach($rows as $row)
{
if ($config_useRewrite)
{
$url = $config_baseHREF."brand/".tapestry_hyphenate($row["name"]).".html";
}
else
{
$url = $config_baseHREF."brands.php?q=".urlencode($row["name"]);
}
print "<br><a href='".$url."'>".$row["name"]."</a>";
}
}
print "</p>";
?></td>
<td><?php
print "<p><b class='u'>Top 10 categories on Online Shopping For</b>";
$sql = "SELECT category FROM category_cache ORDER BY clicks DESC LIMIT 10";
if (database_querySelect($sql,$rows))
{
foreach($rows as $row)
{
if ($config_useRewrite)
{
$url = $config_baseHREF."category/".tapestry_hyphenate($row["name"]).".html";
}
else
{
$url = $config_baseHREF."categories.php?q=".urlencode($row["name"]);
}
print "<br><a href='".$url."'>".$row["name"]."</a>";
}
}
print "</p>";
?></td>
</tr>
</table>The code I am using in jump.php is:
$sql = "UPDATE `".$config_databaseTablePrefix."feeds` SET clicks=clicks+1 WHERE merchant = '".$product["merchant"]."'";
database_queryModify($sql,$insertID);
$sql = "UPDATE `".$config_databaseTablePrefix."products` SET clicks=clicks+1 WHERE id='".database_safe($_GET["id"])."'";
database_queryModify($sql,$insertID);
$sql = "UPDATE `".$config_databaseTablePrefix."brand_cache` SET clicks=clicks+1 WHERE brand='".$product["brand"]."'";
database_queryModify($sql,$insertID);
$sql = "UPDATE `".$config_databaseTablePrefix."category_cache` SET clicks=clicks+1 WHERE category='".$product["category"]."'";
database_queryModify($sql,$insertID);Please let me know what amendments I need to make these all work correctly.
Kind regards
Hello Stephen,
The first place to look I think is the SQL in jump.php that selects the product based on the ID in the URL. In the distribution, it only selects ID merchant and buy_url with the following code:
$sql = "SELECT merchant,buy_url FROM `".$config_databaseTablePrefix."products` WHERE id=".database_safe($_GET["id"]);Because you are also updating new tables based on the category and brand fields, I would simply change the above line to select all using * instead, for example:
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE id=".database_safe($_GET["id"]);That might be all it is...
Cheers,
David.
David,
Thanks for that, it was exactly as you said.
Kind regards
Stephen
Online Shopping For
Hi David
I have successfully used your popular mods on a couple of sites ( e.g popular magazines )
Theres a problem though with the following site which the only difference with the install is that its located in a sub folder. Security Equipment Would that make any difference?
Hi Mally,
The error message "Unknown column 'count' in 'order clause'" indicates that the querylog table does not have the count field. The original mods from this thread:
http://www.pricetapestry.com/node/822
...don't include the count table; so you will need to add this - the easiest way would be with phpMyAdmin if you have that available - just add a new column called "count" of type INT.
However, this may imply that this site doens't have the code to increment the count value when a repeat query is entered, so check that as well - and perhaps copy the logging code in search.php from the working site to this site (after adding the count field)...
Cheers,
David.
Hi David, I spent over an hour last night trying to work it out, then check today after your post and saw the prob within 10 sec's. I had forgot to add the count field.
thanks again for your help!
Mally
Hi Dave,
I have use this code from above and i was trying to adjust the code to use 10 css tags for each keyword then loop again if dispalying 20 keywords?
Regards
Darren
your code
<?php
print "<p>Top Searches</p>";
print "<ul>";
$sql = "SELECT * FROM querylog ORDER BY count DESC LIMIT 10";
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>";
?>
css code
#cloud a.tag1 { font-size: 0.7em; font-weight: 100; }
#cloud a.tag2 { font-size: 0.8em; font-weight: 200; }
#cloud a.tag3 { font-size: 0.9em; font-weight: 300; }
#cloud a.tag4 { font-size: 1.0em; font-weight: 400; }
#cloud a.tag5 { font-size: 1.2em; font-weight: 500; }
#cloud a.tag6 { font-size: 1.4em; font-weight: 600; }
#cloud a.tag7 { font-size: 1.6em; font-weight: 700; }
#cloud a.tag8 { font-size: 1.8em; font-weight: 800; }
#cloud a.tag9 { font-size: 2.2em; font-weight: 900; }
#cloud a.tag10 { font-size: 2.5em; font-weight: 900; }
Hi Darren,
Try something like this:
<?php
print "<p>Top Searches</p>";
print "<ul>";
$sql = "SELECT * FROM querylog ORDER BY count DESC LIMIT 10";
if (database_querySelect($sql,$rows))
{
$i = 0;
foreach($rows as $row)
{
$i++;
if ($i > 10) $i = 1;
$style = "tag".$i;
$url = $config_baseHREF."search.php?q=".urlencode($row["query"]);
print "<li class='".$style."'><a href='".$url."'>".$row["query"]."</a></li>";
}
}
print "</ul>";
?>
I _think_ that's right (my CSS isn't brilliant) - don't forget to make sure that the code is output within the "cloud" div. If it's not quite right, you can see how I have constructed the class attribute ($style variable) using "tag".$i - which will result in "tag1" if $i=1 etc. etc.
Cheers,
David.
Is there any way to make the top searches count reset back to zero after every 24 hours, or after every week?
I would guess that a cron task could do this, but I'm not sure how to set this up?
I've set up my top searches count column as per this thread
Also - would it be difficult to make a backend whereby the admin could delete certain searches? maybe a little red cross could appear alongside the search terms if you are logged in as the admin, and by clicking on the red cross it would remove that term from the database table?
Hi Jim,
A simply script to reset the top search count would be as follows (to be run from the main Price Tapestry directory):
resetcount.php:
<?php
set_time_limit(0);
ignore_user_abort();
require("includes/common.php");
$sql = "UPDATE querylog SET count = 0";
database_queryModify($sql,$result);
print "Done.";
?>
You could then set this up as a cron task - on most server configurations you simply need to prefix the filename (with its full path) with the php command; for example:
php /path/to/pricetapestry/resetcount.phpIf you're not sure what /path/to/pricetapestry/ is; have a look in this thread (1st reply) for the whereami.php script, which you can run in the same directory and it will display the full path for you!
In terms of administration; there's quite a lot involved in creating an "admin" login with a delete function within the main body of the site; but what I could perhaps suggest as an alternative is to use something like phpMyAdmin if that is available on your host (or you are able to install it); which would allow you to browse the table, sort by clicks etc. and delete any fields required...
Cheers,
David.
Hello David
I've been using the popular searches and top products selected for a while now but I've noticed with the popular searches, the following are appearing in the top 10
* g
* O
* M
* P
* n
* in
Is there away of excluding single characters and words like 'in' ?
Cheers
MAlly
Hi Mally,
Sure - in your logging code near the top of search.php you should have the following IF statement to decide whether or not to log...
if ($_GET["log"])This test can be made more comprehensive to decide whether or not to log, for example:
if (
($_GET["log"])
&&
(strlen($q) > 1)
&&
($q != "in")
&&
($q != "to")
)...adding more lines as required (with && between each) to trap more words!
Cheers,
David.
hi,
I implemented the above mod a few years back, but now, cant seem to make it work on a new PT.
i followed the setup above but cant seem to make this work on a new version of PT.
is the above code still OK and working with the latest version?
Can anyone have a quick summary on how to implement these?
Hi atman,
Assuming that you are using the same querylog table, it should be fine. Did you remember to add the "log" hidden field to the search form (in html/searchform.php)? Alternatively, is it possible that the querylog table hasn't yet been created in the new installation?
Otherwise, feel free to email me your modified files and I'll take a look for you!
Cheers,
David.
Is there a way to get a complete list of the searches or top searches exported to a .txt file?
Hi,
Sure - if you've implemented the mod in this thread, so that you have a querylog table in your Price Tapestry database, the following script will export the entire log as a text file (for download through the browser)
export.php
<?php
set_time_limit(0);
require("includes/common.php");
header("Content-Type: application/octet-stream");
header("Content-Disposition: attachment; filename=querylog.txt");
$sql = "SELECT * FROM `".$config_databaseTablePrefix."querylog`";
$link = mysql_connect($config_databaseServer,$config_databaseUsername,$config_databasePassword);
mysql_select_db($config_databaseName,$link);
$result = mysql_unbuffered_query($sql,$link);
while($row = mysql_fetch_array($result,MYSQL_ASSOC))
{
print $row["query"]."\n";
}
?>
Cheers,
David.
Hi David
I would like to display the ten most popular products like a serach result with
title
description
image
price
Can you show me how to do this ?
Thank you
Guillaume
Hi Guillaume,
The initial part of this thread only concerns query logging, which doesn't necessarily map to products. If you have not already done so, in order to show the most popular products you would need to add a "clicks" column to the products table as described in this post from lower down this thread...
http://www.pricetapestry.com/node/892#comment-7042
With that in place, you could then actually use the existing html/searchresults.php HTML module to display the Top 10 products in the search results format. Try something like this:
<?php
print "<p>Top Products</p>";
$searchresults = array();
$sql = "SELECT * FROM products ORDER BY clicks DESC LIMIT 10";
if (database_querySelect($sql,$rows))
{
$searchresults["products"] = $rows;
foreach($searchresults["products"] as $k => $product)
{
$searchresults["products"][$k]["numMerchants"] = 1;
$searchresults["products"][$k]["minPrice"] = $searchresults["products"][$k]["price"];
$searchresults["products"][$k]["maxPrice"] = $searchresults["products"][$k]["price"];
if ($config_useRewrite)
{
$searchresults["products"][$k]["productHREF"] = "product/".tapestry_hyphenate($product["name"]).".html";
if ($rewrite) $searchresults["products"][$k]["productHREF"] = "../../".$searchresults["products"][$k]["productHREF"];
}
else
{
$searchresults["products"][$k]["productHREF"] = "products.php?q=".urlencode($product["name"]);
}
}
require("html/searchresults.php");
}
?>
Cheers,
David.
Is there any way the server could email me the query count data every night?
I modded your code so it shows the count followed by the query (tabbed)
<?php
set_time_limit(0);
require("includes/common.php");
header("Content-Type: application/octet-stream");
header("Content-Disposition: attachment; filename=querylog.txt");
$sql = "SELECT * FROM `".$config_databaseTablePrefix."querylog`";
$link = mysql_connect($config_databaseServer,$config_databaseUsername,$config_databasePassword);
mysql_select_db($config_databaseName,$link);
$result = mysql_unbuffered_query($sql,$link);
while($row = mysql_fetch_array($result,MYSQL_ASSOC))
{
print $row["count"]."\t";
print $row["query"]."\n";
}
?>I've also set up crontasks: dailycount.php, weeklycount.php, monthlycount.php and yearlycount.php with unique values like dailycount, weeklycount etc
I'm a stats junkie and I'd love to take my logging and statistics one step further...
Could every time a search is made (or product clicked) PT would record:
- date
- time
- ip address of visitor
- search query
- (entry page?)
- (exit page?)
What would be the easiest way to set this up?
Maybe at the end of every day (before dailycount.php resets) it could insert the days stats into the database?
I'd like to browse through my query stats day by day. Currently I've built a nice admin backend, but it only shows number of queries today (resets every 24 hours), weekly queries etc
Another feature suggestion I thought of - if you are logged in as 'admin' then do not record your own searches and clicks?
Hi,
What I've done is this, which means only valid products are logged so that if you link to the search queries, they'll all work, plus you avoid spam getting logged:
if ($_GET["log"] && $resultCount > 0)
{
$sql = "SELECT * FROM tags WHERE tag='".database_safe($q)."'";
if (database_querySelect($sql,$rows))
{
$sql = "UPDATE tags SET count=count+1 WHERE tag='".database_safe($q)."'";
}
else
{
$sql = "INSERT INTO tags SET tag='".database_safe($q)."',count='1', approved='0'";
}
database_queryModify($sql,$result);
}Just means terms are only logged that relate to actual products in your database.
Cheers.
Keeop
Hi David
I've tried adding this to my new installation with pricetapestry.
I get this error.
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/****/public_html/includes/database.php on line 27
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/*****/public_html/includes/database.php on line 32
Is there any error between the old and new config with the scripts?
Thanks
Hi Mally,
There shouldn't be, but with the new distribution you can enable database debug mode which will describe the error and show the SQL that caused it. In config.advanced.php change line 6 as follows:
$config_databaseDebugMode = TRUE;(don't forget to change it back shortly afterwards and certainly before importing as it will generate unwanted output during the import process)
If you're not sure of the problem based on the debug output let me know what is displayed and I'll take a look...
Cheers,
David.
agh, handy, used the debug mode and realised I'd not added the count clause, great stuff thanks
Hello,
I created a separate page with top searches. I'd like to show the searches in different order, I mean from last to oldest (now it show first the old searches)
Perfect (for me) should be:
a) a column with last 100 searches
b) a column with top 100 searches (product most searched)
Do you think it's possible?
Thank you as usual for "fanatic" support ;-)
Hi,
Sure - the $sql = ... line for both cases would be:
> a) a column with last 100 searches
$sql = "SELECT * FROM querylog ORDER BY id DESC LIMIT 100";> b) a column with top 100 searches (product most searched)
$sql = "SELECT * FROM querylog ORDER BY count DESC LIMIT 100";If you're not sure of the display code to go with those just let me know!
Cheers,
David.
Hi David,
I am using the code below to generate random search queries and attempting to create a tag cloud.
<?php
print "<p>Top Searches</p>";
print "<ul>";
$sql = "SELECT * FROM querylog ORDER BY RAND() LIMIT 20";
if (database_querySelect($sql,$rows))
{
$i = 0;
foreach($rows as $row)
{
$i++;
if ($i > 10) $i = 1;
$style = "tag".$i;
$url = $config_baseHREF."search.php?q=".urlencode($row["query"]);
print "<li class='".$style."'><a href='".$url."'>".$row["query"]."</a></li>";
}
}
print "</ul>";
?>
The css i am using is as follows
.tag1 a { font-size: 20px}
.tag2 a { font-size: 19px}
.tag3 a { font-size: 18px}
.tag4 a { font-size: 17px}
.tag5 a { font-size: 16px}
.tag6 a { font-size: 15px}
etc etc
This seems to create the cloud ok and increse the font size based on tag number generated above except it always makes the top line the biggest font working down to the smallest font at the bottom.
What I am trying to do is return random search queries (say 20) in alpabetical order and then be sized based on number of queries.
Perhaps this would need to be based on a percentage of the search term against the other 19.
Search Word 1 (5 hits)
Search Word 2 (1 hit)
Search Word 3 (10 hits)
Search Word 4 (3 hits)
Based on this search word 3 would be in a bigger font in comparision to the others, search Word 2 would be the smallest font.
Hope that makes sense
Richard
Hi Richard,
Looking at the above code first, the reason it would be cycling through the sizes is because the tagN is being created with $i, which increments in each iteration. For a random tag cloud, this could be reduced as follows:
<?php
print "<p>Top Searches</p>";
print "<ul>";
$sql = "SELECT * FROM querylog ORDER BY RAND() LIMIT 20";
if (database_querySelect($sql,$rows))
{
foreach($rows as $row)
{
$style = "tag".rand(1,10);
$url = $config_baseHREF."search.php?q=".urlencode($row["query"]);
print "<li class='".$style."'><a href='".$url."'>".$row["query"]."</a></li>";
}
}
print "</ul>";
?>
To make the tagN be proportional to the total number of hits based on the 20 selected random queries (sorted alphabetically), have a go with something like this:
<?php
print "<p>Top Searches</p>";
print "<ul>";
$sql = "SELECT * FROM querylog ORDER BY RAND() LIMIT 20";
if (database_querySelect($sql,$rows))
{
$queries = array();
$counts = array();
$total_count = 0;
foreach($rows as $row)
{
$total_count += $row["count"];
$queries[] = $row["query"];
$counts[$row["query"]] = $row["count"];
}
asort($queries);
foreach($queries as $query)
{
$percent = ceil((100/$total_count) * $counts[$query]);
$style = "tag".($percent/10);
$url = $config_baseHREF."search.php?q=".urlencode($query);
print "<li class='".$style."'><a href='".$url."'>".$query."</a></li>";
}
}
print "</ul>";
?>
This would use the same tag1 ... tag10 classes that you have already set-up, where tag1 would be used for 0-9%, tag2 would be used for 10-19% etc.
Hope this helps!
Cheers,
David.
Hello,
after modifying as stated in /node 3444 and modify the search.php as follows
/** $rewrite = isset($_GET["rewrite"]); **/
$rewrite = isset($_GET["rewrite"]);
if ($_GET["redir"])
{
$location = $config_baseHREF."".$sort."/".tapestry_hyphenate($q)."/";
header("Location: ".$location);
exit();
}require("html/header.php");
require("html/menu.php");
require("html/searchform.php");
require("html/banner.php");
print "<table><tr><td valign=top width=50%>";
print "<p><font color=#000000>Last searches<ul> ";
$sql = "SELECT * FROM querylog ORDER BY id DESC LIMIT 30";
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>";Thank you!
Hi,
What you would need to do is make the URL go straight to the clean version rather than search.php, so in your last searches code, instead of:
$url = $config_baseHREF."search.php?q=".urlencode($row["query"]);...use this:
$url = $config_baseHREF."relevance/".urlencode(tapestry_hyphenate($row["query"]))."/";Hope this helps!
Cheers,
David.
Thanks as usual.
I changed the code in topsearch page as suggested but it does not show last searches (only the ones generated before the change mentionned in in /node 3444 ). So, I tried to remove this hack, by removing the block after
$rewrite = isset($_GET["rewrite"]);
/** hack node 3444 **/
if ($_GET["redir"])
{
// $location = $config_baseHREF."".$sort."/".tapestry_hyphenate($q)."/";
$location = $config_baseHREF."search/".$sort."/".tapestry_hyphenate($q)."/";
header("Location: ".$location);
exit();
}
And last search works. So this two hacks are in conflict?
Hi,
Sorry - I misunderstood - although the fix described above to make the displayed top searches link to the clean URL is still valid (and worth doing) of course!
Otherwise, what's happening is that the logging code is taking place after the redirect - it needs to be moved before it. The original instructions above indicate inserting the code after the if($q) line as follows:
if ($q)
{
if ($_GET["log"])
{
$sql = "INSERT INTO querylog SET query='".database_safe($q)."'";
database_queryModify($sql,$result);
$sql = "UPDATE querylog SET count=count+1 WHERE query='".database_safe($q)."'";
database_queryModify($sql,$result);
}
.. rest of code ..To make this work in conjunction with rewriting to clean URLs, simply move the if ($_GET["log"]) { ... } section ABOVE the if ($_GET["redir"]) { ... } section; so you will end up with something like this; depending on exactly what code you are currently using for each mod:
$rewrite = isset($_GET["rewrite"]);
if ($_GET["log"])
{
$sql = "INSERT INTO querylog SET query='".database_safe($q)."'";
database_queryModify($sql,$result);
$sql = "UPDATE querylog SET count=count+1 WHERE query='".database_safe($q)."'";
database_queryModify($sql,$result);
}
if ($_GET["redir"])
{
$location = $config_baseHREF."".$sort."/".tapestry_hyphenate($q)."/";
header("Location: ".$location);
exit();
}If you're not sure, email me and I'll check it out for you...
Cheers,
David.
Work fine, thank for fast and kind support
Bit of a weird one - I'm trying to use the code you suggested in this thread on Mon, 2009-05-18 08:21 on a development installation of Price Tapestry and it's not working.
I've checked the database and the 'click' field is definitely populating, but the PHP in your post can't find the field. Specifically, it seems to be failing to apply $config_databaseTablePrefix from the config.php file.
Using debug, I get this error: [SELECT * FROM products ORDER BY clicks DESC LIMIT 10][Table 'onlineab_ptapfftb.products' doesn't exist]
Which is perfectly true, as the table is actually called ptap_products.
In all other respects Price Tapestry is running fine so I can't see it being a general problem with that field. Any idea what I need to do to get it sorted?
Hi Andy,
Welcome to the forum!
I see - referring back to the original posts in this thread, the query logging code in search.php where described as:
$sql = "INSERT INTO querylog SET query='".database_safe($q)."'";use:
$sql = "INSERT INTO `".$config_databaseTablePrefix."querylog` SET query='".database_safe($q)."'";And in Top Searches code, in place of:
$sql = "SELECT * FROM querylog ORDER BY count DESC LIMIT 10";use:
$sql = "SELECT * FROM `".$config_databaseTablePrefix."querylog` ORDER BY count DESC LIMIT 10";Hope this helps!
Cheers,
David.
--
PriceTapestry.com
Thanks for the welcome!
It wasn't exactly what I needed, but it was close enough for even my rudimentary scripting skills to figure it out. I now have a line that says $sql = "SELECT * FROM `".$config_databaseTablePrefix."products` ORDER BY clicks DESC LIMIT 3"; and all is well, so thank you very much.
A
I'd like to log only valid searches with $resultCount > 0
But i'm not able to script it correctly...
i have tried to put
if ($_GET["log"] && $resultCount > 0) ...Now my form is:
<div class='searchform'>
<form name='search' action='<?php print $config_baseHREF ?>search.php'>
<input type='text' name='q' id='q' size='35' value='<?php print (isset($q)?$q:""); ?>' />
<input type='hidden' name='log' value='1' />
<input type='submit' value='<?php print translate("Search"); ?>' />
</form>
</div>And search.php
if ($q)
{
if ($_GET["log"])
{
$sql = "INSERT INTO querylog SET query='".database_safe($q)."'";
database_queryModify($sql,$result);
$sql = "UPDATE querylog SET count=count+1 WHERE query='".database_safe($q)."'";
database_queryModify($sql,$result);
}How can i log queries only if $resultCount > 0??
Thanks a lot
Hi,
All you'll need to do is move the logging PHP down to where it is known that there are results. So, rather than inserting the above code after
if ($q)
{...instead, search for and insert the same code after:
if ($resultCount)
{(line 279 in 12/10A)
Hope this helps!
Cheers,
David.
--
PriceTapestry.com
Thanks again David, it works perfectly :)
Hi David,
I am wondering to show top search by period.
For the example top search:
- week:
- month:
- year:
Best Regards,
Martin
Hi Martin,
(I'm assuming that querylog with count is already implemented as per the above)
One statistical rather than 100% absolute way to do this would be to continually update 3 counts for each query (week, month and year) and then have a weekly CRON job run that reduces each value by the average over the period. This will keep track of trends without any complex date handling.
To have a go at this, first modify the querylog table to add the additional count fields as follows:
<?php
require("includes/common.php");
$sql = "ALTER TABLE `".$config_databaseTablePrefix."querylog`
CHANGE count count_w INT(11) NOT NULL default '0',
ADD count_m INT(11) NOT NULL default '0',
ADD count_y INT(11) NOT NULL default '0'
";
database_queryModify($sql,$result);
$sql = "UPDATE `".$config_databaseTablePrefix."querylog`
SET count_m = count_w, count_y = count_w
";
database_queryModify($sql,$result);
print "Done.";
?>
Next, in your modified search.php where you have the current UPDATE sql as follows:
$sql = "UPDATE querylog SET count=count+1 WHERE query='".database_safe($q)."'";...REPLACE this with:
$sql = "UPDATE querylog SET count_w=count_w+1,count_m=count_m+1,count_y=count_y+1 WHERE query='".database_safe($q)."'";Next create a new file scripts/querylog.php containing the following code:
<?php
set_time_limit(0);
require("../includes/common.php");
$sql = "UPDATE querylog SET
count_w=0,
count_m=count_m-FLOOR(count_m/4),
count_y=count_y-FLOOR(count_y/52)
";
database_queryModify($sql,$result);
print "Done.";
?>
...and schedule this script to run weekly by adding a new CRON job via your hosting control panel with a command line similar to the following:
cd /path/to/pricetapestry/scripts;/usr/local/bin/php querylog.php(The "Install Path" /path/to/pricetapestry/ can be found on the Support Info page of your /admin/ area)
That's basically it for the logging; finally to display the top 10 counts, the code is pretty much identical to the above, except that you would use the appropriate count_w (Weekly) count_m (Monthly) or count_y (Yearly) values as required, so for example the code to show the top 10 searches this month would be:
<?php
print "<ul>";
$sql = "SELECT * FROM querylog ORDER BY count_m DESC LIMIT 10";
if (database_querySelect($sql,$rows))
{
foreach($rows as $row)
{
$url = $config_baseHREF."search.php?q=".urlencode($row["query"]);
print "<li><a href='".$url."'>".$row["query"]."</a></li>";
}
}
print "</ul>";
?>
Hope this helps!
Cheers,
David.
--
PriceTapestry.com
Hi David,
I have this situation:
DB
pt_brands field "clicks" (mapping and not mapping in pt_feeld)
pt_categories field "clicks"
pt_categories_hierarchy field "clicks"
My jump.php is:
{code saved}
The query, categories, categories_hierarchy and brands, not tracking and increase value click.
not works.
Thanks for support
Giuseppe
Hello Giuseppe,
You need to update the non-products table `clicks` fields based on the associated values from the product record rather than by the product record ID. To do this, if you revert to the distribution jump.php and look for the following code at line 8:
$sql = "SELECT filename,buy_url FROM `".$config_databaseTablePrefix."products` WHERE id='".database_safe($id)."'";...and REPLACE with:
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE id='".database_safe($id)."'";That will make all fields available for use in your modification, for which you can then use:
$sql = "UPDATE `".$config_databaseTablePrefix."products` SET clicks=clicks+1 WHERE id='".database_safe($id)."'";
database_queryModify($sql,$insertID);
if ($product["category"])
{
$sql = "UPDATE `".$config_databaseTablePrefix."categories` SET clicks=clicks+1 WHERE name='".database_safe($product["category"])."'";
database_queryModify($sql,$insertID);
}
if ($product["categoryid"])
{
$sql = "UPDATE `".$config_databaseTablePrefix."categories_hierarchy` SET clicks=clicks+1 WHERE id='".database_safe($product["categoryid"])."'";
database_queryModify($sql,$insertID);
}
if ($product["brand"])
{
$sql = "UPDATE `".$config_databaseTablePrefix."brands` SET clicks=clicks+1 WHERE name='".database_safe($product["brand"])."'";
database_queryModify($sql,$insertID);
}Hope this helps!
Cheers,
David.
--
PriceTapestry.com
Hello Giuseppe,
You need to update the non-products table `clicks` fields based on the associated values from the product record rather than by the product record ID. To do this, if you revert to the distribution jump.php and look for the following code at line 8:
$sql = "SELECT filename,buy_url FROM `".$config_databaseTablePrefix."products` WHERE id='".database_safe($id)."'";...and REPLACE with:
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE id='".database_safe($id)."'";That will make all fields available for use in your modification, for which you can then use:
$sql = "UPDATE `".$config_databaseTablePrefix."products` SET clicks=clicks+1 WHERE id='".database_safe($id)."'";
database_queryModify($sql,$insertID);
if ($product["category"])
{
$sql = "UPDATE `".$config_databaseTablePrefix."categories` SET clicks=clicks+1 WHERE name='".database_safe($product["category"])."'";
database_queryModify($sql,$insertID);
}
if ($product["categoryid"])
{
$sql = "UPDATE `".$config_databaseTablePrefix."categories_hierarchy` SET clicks=clicks+1 WHERE id='".database_safe($product["categoryid"])."'";
database_queryModify($sql,$insertID);
}
if ($product["brand"])
{
$sql = "UPDATE `".$config_databaseTablePrefix."brands` SET clicks=clicks+1 WHERE name='".database_safe($product["brand"])."'";
database_queryModify($sql,$insertID);
}Hope this helps!
Cheers,
David.
--
PriceTapestry.com
Hello,
Is de top search mod still working for the newest distribution?
And is it possible to create a separate page with for example the 500 most searched words in four colums?
Thanks Jan Roel
Hello Jan,
I've updated line numbering in the Popular Searches Modification thread for 16/10A.
To make a Top 500 page, using Foundation block grid styles (2 columns for mobile, 4 columns for medium-up) have a go with the following top500.php script (based on exampl.php from the distribution)
<?php
require("includes/common.php");
$header["title"] = "Top 500 ";
require("html/header.php");
require("html/menu.php");
require("html/searchform.php");
$banner["breadcrumbs"] = array();
$banner["breadcrumbs"][] = array("title"=>"Top 500","href"=>$config_baseHREF."top500.php");
require("html/banner.php");
print "<div class='row'>";
print "<div class='small-12 columns'>";
$sql = "SELECT * FROM `".$config_databaseTablePrefix."querylog` ORDER BY count DESC LIMIT 500";
if (database_querySelect($sql,$rows))
{
print "<ul class='small-block-grid-2 medium-block-grid-4'>";
foreach($rows as $row)
{
print "<li><a href='".$config_baseHREF."search.php?q=".urlencode($row["query"])."'>".$row["query"]."</a></li>";
}
print "</ul>";
}
print "</div>";
print "</div>";
require("html/footer.php");
?>
Cheers,
David.
--
PriceTapestry.com
Hi Gabriel,
This should be quite easy as you have already done a lot of the work by logging queries on your site following on from this thread:
http://www.pricetapestry.com/node/822
What you need to do is modify this so that you are counting the searches as well as logging them. The new table structure you would need is this:
CREATE TABLE `querylog` (`id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`count` INT ( 11 ) NOT NULL ,
`query` VARCHAR( 255 ) NOT NULL ,
UNIQUE (
`query`
)
) ENGINE = MYISAM ;
(with phpMyAdmin, just add another field called count with type INT length 11)
Now, to update the count, modify this previous modification (in search.php:
if ($q){
if ($_GET["log"])
{
$sql = "INSERT INTO querylog SET query='".database_safe($q)."'";
database_queryModify($sql,$result);
}
.. rest of code ..
as follows...
if ($q){
if ($_GET["log"])
{
$sql = "INSERT INTO querylog SET query='".database_safe($q)."'";
database_queryModify($sql,$result);
$sql = "UPDATE querylog SET count=count+1 WHERE query='".database_safe($q)."'";
database_queryModify($sql,$result);
}
.. rest of code ..
Finally, to display the top 10 searches, do something like this:
<?phpprint "<p>Top Searches</p>";
print "<ul>";
$sql = "SELECT * FROM querylog ORDER BY count DESC LIMIT 10";
if (database_querySelect($sql,$rows))
{
foreach($rows as $row)
{
$url = $config_baseHREF."search.php?q=".urlencode($row["query"]);
print "<li><a href='".$url."'>".$row["query"]."</a></li>";
}
}
print "</ul>";
?>
That should do the trick!
Cheers,
David.