Top searches
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
Thanks again David.
But when i have the rewrite mod on what should i modify here:
$url = $config_baseHREF."search.php?q=".urlencode($row["query"]);
print "<li><a href='".$url."'>".$row["query"]."</a></li>";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.
thank you david.
the sitemapquery.php and the top search seems to be working fine now.
from the sitemapquery.php output, can we convert it to plain browsable output?
if possible, can we make an archive of all the search queries and layout it in a nice way similar to
http://shopping.kelkoo.co.uk/b/a/ssq_163601_0_100.html
nothing fancy like the kelkoo example (tag cloud) but a nice chronological archive would do.
with an option to view "top serches", chronological or alphabetical
regards,
atman
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.
--
Developer, Price Tapestry
For unrelated PHP, MySQL or Affiliate Marketing tech help please post your questions on my personal forum
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.
--
Developer, Price Tapestry
For unrelated PHP, MySQL or Affiliate Marketing tech help please post your questions on my personal forum
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.
--
Developer, Price Tapestry
For unrelated PHP, MySQL or Affiliate Marketing tech help please post your questions on my personal forum
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.
--
Developer, Price Tapestry
For unrelated PHP, MySQL or Affiliate Marketing tech help please post your questions on my personal forum
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.
--
Developer, Price Tapestry
For unrelated PHP, MySQL or Affiliate Marketing tech help please post your questions on my personal forum
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.
--
Developer, Price Tapestry
For unrelated PHP, MySQL or Affiliate Marketing tech help please post your questions on my personal forum
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.
--
Developer, Price Tapestry
General PHP, MySQL and Affiliate Marketing tech help for Price Tapestry customers at davidmorison.com
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.
--
Developer, Price Tapestry
General PHP, MySQL and Affiliate Marketing tech help for Price Tapestry customers at davidmorison.com
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.
--
Developer, Price Tapestry
General PHP, MySQL and Affiliate Marketing tech help for Price Tapestry customers at davidmorison.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.