Hy,
How could i be able to index in a "sitemap" (for ex) all the pages witch were searched by users? This is for google optimization.
Thanks,
Gabriel
Hi,
Well, yeah, i need a little help to do this. I will need to create a new field in database that will stock all the search queries and then to grab them somewhere?
Thanks,
Gabriel
Hi Gabriel,
What MySQL tools do you have available? Have you got phpMyAdmin installed, or does your hosting control panel let you manage databases and create tables?
Cheers,
David.
Ok,
first thing you need to do is create a new table in your Price Tapestry database. I would call it "querylog".
Add the following fields:
id, INT length:11, auto-increment, primary key
query, VARCHAR length:255
You might find it easier to just paste the SQL. If you open your Price Tapestry database in phpMyAdmin, click on the SQL tab, then enter the following code:
CREATE TABLE `querylog` (
`id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`query` VARCHAR( 255 ) NOT NULL
) ENGINE = MYISAM ;Let me know when you've got the table setup and i'll help with the code...
Cheers,
David.
Update - the query field needs to be UNIQUE; sorry about that! If you drop the table then rebuild it with the following SQL:
CREATE TABLE `querylog` (
`id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`query` VARCHAR( 255 ) NOT NULL ,
UNIQUE (
`query`
)
) ENGINE = MYISAM ;Alternatively, just click the "U" icon alongside the query column...
Hiya,
Did you catch my update about making the query field unique?
Hi,
Yes i`ve done it from the first place. Thanks
Thanks,
Gabriel
Ok,
Bear with me now; i'll write up the modifications and test them on my dev server - it's not much code - but will be limited to 50,000 queries as this is the maximum permitted in a sitemap file.
I'll be able to do the code shortly - check back in a couple of hours for the mods...
Cheers,
David.
Thanks a lot. I wont leave the forum. Also i`m writing you an email right now.
Thanks,
Gabriel
Hello Gabriel,
I've written the code - it's not much to do.
Firstly, in html/searchform.php we need to add a hidden field to tell the search script to log the query. This is because we don't want all the other system based calls to search.php to be logged. Change the search form as follows:
<div class='searchform'>
<form name='search' action='<?php print $config_baseHREF ?>search.php'>
<input type='text' name='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>If you have already modified your search form, just add that new line containing the hidden form field "log".
Next, in search.php we need write the query to the database if log=1 in the URL. Starting at line 13 you will see the following code:
if ($q)
{
.. rest of code ..Add the code below to this to write the query into the database, so you will have the following:
if ($q)
{
if ($_GET["log"])
{
$sql = "INSERT INTO querylog SET query='".database_safe($q)."'";
database_queryModify($sql,$result);
}
.. rest of code ..Finally, you need a new script to generate the sitemap. I've called it sitemapquery.php. This is the file that you would register with Google Sitemaps:
<?php
require("includes/common.php");
header("Content-Type: text/xml");
print "<?xml version='1.0' encoding='UTF-8'?>";
print "<urlset xmlns='http://www.google.com/schemas/sitemap/0.84' xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xsi:schemaLocation='http://www.google.com/schemas/sitemap/0.84 http://www.google.com/schemas/sitemap/0.84/sitemap.xsd'>";
$sql = "SELECT * FROM querylog ORDER BY id LIMIT 50000";
if (database_querySelect($sql,$rows))
{
$sitemapBaseHREF = "http://".$_SERVER["HTTP_HOST"].$config_baseHREF;
foreach($rows as $row)
{
print "<url>";
$sitemapHREF = "search.php?q=".urlencode($row["query"]);
print "<loc>".$sitemapBaseHREF.$sitemapHREF."</loc>";
print "</url>";
}
}
print "</urlset>";
?>That lot should do the trick!
Cheers,
David.
Thanks for the code,
i get this error in searchquery.php:
<b>Warning</b>
: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in
<b>...\includes\database.php</b>
on line
<b>21</b>Thanks,
Gabriel
and this one too:
<b>Warning</b>
: mysql_num_rows(): supplied argument is not a valid MySQL result resource in
<b>...\includes\database.php</b>
on line
<b>26</b>Thanks,
Gabriel
I`m sorry it was my mistake. I copy/paste the code you wrote. And i had a table prefix. It works great thanks.
Thanks,
Gabriel
There is a little problem, but i cant figure it out.
This is from sitemap.php:
http://localhosthttp://localhost/.../sitemap.php?merchant=ID+Security+SuiteThis is from searchquery.php:
http://localhosthttp://localhost/.../search.php?q=dYou see the errors.
Thanks,
Gabriel
Hello Gabriel,
The URLs in the sitemap are being generated by these lines:
$sitemapBaseHREF = "http://".$_SERVER["HTTP_HOST"].$config_baseHREF;..and then for each URL:
$sitemapHREF = "search.php?q=".urlencode($row["query"]);...combining to give:
print "<loc>".$sitemapBaseHREF.$sitemapHREF."</loc>";The first thing to ask is, what is in your config.php for $config_baseHREF?
Cheers,
David.
Well, yeah, my mistake again. I had "http://localhost" in $config_baseHref ... Now it works. Thanks.
I sent you an email. When you got the time please reply to me the answer.
Thanks again,
Gabriel
Mail sent to the new email adress.
Thanks,
Gabriel
hi david,
is it possible to add this feature on the setup files so that ist would be enabled by default?
Hi,
It's not something that i'm planning on adding to the distribution; but if you make the file modifications (and addition) above; you can add the "CREATE TABLE" SQL statement on the end of setup.sql and the querylog table would then be created when you run setup.php.
The code you need add is just:
CREATE TABLE `querylog` (
`id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`query` VARCHAR( 255 ) NOT NULL ,
UNIQUE (
`query`
)
) ENGINE = MYISAM ;Hope this helps...
Cheers,
David.
Alternatively, if you want to add the new table to an existing installation, and don't have an easy way to create tables you could create the table using a script; for example:
createQuerylog.php
<?php
require("includes/common.php");
$sql =
"
CREATE TABLE `querylog` (
`id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`query` VARCHAR( 255 ) NOT NULL ,
UNIQUE (
`query`
)
) ENGINE = MYISAM ;
";
database_queryModify($sql,$rows);
print "<p>Done.</p>";
?>
If you're using a table prefix you will need to add your prefix in front of "querylog" in the SQL, otherwise this code should do the trick!
Cheers,
David.
hi david,
in following this thread.. i got the error.
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/dealzoom/public_html/includes/database.php on line 21
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/dealzoom/public_html/includes/database.php on line 26
i also created my table using createQuerylog.php
thanks.
Hi,
Just a quick thought - you're not using a table prefix in your config.php are you? If you are, the SQL in createQuerylog.php would need to be changed to include your prefix, for example:
$sql =
"
CREATE TABLE `yourprefix_querylog` (
`id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`query` VARCHAR( 255 ) NOT NULL ,
UNIQUE (
`query`
)
) ENGINE = MYISAM ;
";Is this the case?
Cheers,
David.
i also got this error message
Unknown column 'count' in 'order clause'
Hi atman,
Are you trying to implement the "Top Searches" feature from this thread?
You will need to use the SQL from that thread instead of the one in this thread to do that...
Cheers,
David.
Hi David,
I enabled this which is very good how do i change to select max of 60 searched querys and print to screen with max of 6 rows and 6 columns agross the the page.
Many Thanks
Darren
Hi Darren,
To limit to 60 queries in your sitemap you just need to change this line:
$sql = "SELECT * FROM querylog ORDER BY id LIMIT 50000";...where 50000 is the limit you require. Note that this is currently set to 50000 as this is the limit set by Google for the maximum number of URLs in a sitemap XML document.
If you're referring to the top queries display (rather than sitemap), I assume that you are using this script from the other thread:
<?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>";
?>
To change this to show 60 queries and to display the results in a table try something like this:
<?php
print "<p>Top Searches</p>";
print "<table>";
print "<tr>";
$sql = "SELECT * FROM querylog ORDER BY count DESC LIMIT 60";
if (database_querySelect($sql,$rows))
{
$i = 0;
foreach($rows as $row)
{
if ($i % 6 == 0)
{
print "</tr>";
print "<tr>";
}
$i++;
$url = $config_baseHREF."search.php?q=".urlencode($row["query"]);
print "<td><a href='".$url."'>".$row["query"]."</a></td>";
}
}
print "</tr>";
print "</table>";
?>
Hope this helps!
Cheers,
David.
Hello,
When I got this working. It is really cool but I wish I could have it one on each line like:
* Dogs
* Toys
* etc
Right now there is about 4 or so on each line.
Thank you,
Michael
Hi Michael,
The code above is designed to output the result in rows. If you just want them in a column you can remove the table code and just use this:
<?php
print "<p>Top Searches</p>";
$sql = "SELECT * FROM querylog ORDER BY count DESC LIMIT 60";
if (database_querySelect($sql,$rows))
{
foreach($rows as $row)
{
$url = $config_baseHREF."search.php?q=".urlencode($row["query"]);
print "<p><a href='".$url."'>".$row["query"]."</a></p>";
}
}
?>
Cheers,
David.
David,
I added the sitemapquery.php file and submitted this file to google sitemaps.
Google comes with the following error message for this sitemap:
Empty Sitemap
Your Sitemap does not contain any URLs. Please validate and resubmit your Sitemap
I checked the file and it looks lik this:
<?xml version="1.0" encoding="UTF-8" ?>
- <urlset xmlns="http://www.google.com/schemas/sitemap/0.84" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.google.com/schemas/sitemap/0.84 http://www.google.com/schemas/sitemap/0.84/sitemap.xsd">
- <url>
<loc>http://www.dutchshops.nl/search.php?q=Massagebubbelmat</loc>
</url>
- <url>
<loc>http://www.dutchshops.nl/search.php?q=bubbelmat</loc>
</url>
- <url>
<loc>http://www.dutchshops.nl/search.php?q=sex</loc>
</url>
- <url>
<loc>http://www.dutchshops.nl/search.php?q=parfum</loc>
</url>
- <url>
<loc>http://www.dutchshops.nl/search.php?q=auto</loc>
</url>
- <url>
<loc>http://www.dutchshops.nl/search.php?q=speelgoed</loc>
</url>
- <url>
<loc>http://www.dutchshops.nl/search.php?q=kado</loc>
</url>
</urlset>How do i fix the error message ??
Mikey
Hi Mikey,
I've looked at the file on your server and it looks absolutely fine. One possibility is that if you had registered it with Google immediately after adding this mod there might have been no queries in the log at the time you submitted it.
If this is the case, Google may have cleared the error by now automatically; or if not, try removing the sitemap and then resubmitting. I think that should be all it is...
Cheers,
David.
Hi David,
I use the sitemapquery-script and it work good.
Would it be possible to write all searches from SEs to the database? E.g. if so searches for 212 nyc clothing in google and finds my site and clicks on the search result my logs show the following entry:
http://www.google.com/search?q=212nyc+clothing&hl=en&rls=RNWE,RNWE:2005-24,RNWE:en
This search is not written to the database but I want that it is written to.
Regards
Roland
Hi Roland,
That can be done. Have a go with the following code; which 1) Looks for "google" in the refering URL, 2) decomposes the referer URL to extract the query string, 3) parses the query string to extract the "q" parameter, and 4) inserts "q" from the referer into the querylog.
if (strpos($_SERVER["HTTP_REFERER"],"google")) // if the referer is Google
{
$parts = parse_url($_SERVER["HTTP_REFERER"]); // decompose the URL to extract query string
parse_str($parts["query"],$vars); // parse query string to extract "q" into the $vars array
if ($vars["q"])
{
$sql = "INSERT INTO querylog SET query='".database_safe($vars["q"])."'";
database_queryModify($sql,$result);
}
}Add the above code at the top of products.php, after the require("includes/common.php"); line...
Hope this helps,
Cheers,
David.
Can I confirm, I've read this post with interest, but I'm not sure why its wanted.
The original post was
Hy,
How could i be able to index in a "sitemap" (for ex) all the pages witch were searched by users? This is for google optimization.
Thanks,
GabrielWith the file created above, what should we do with it or submit it?
Cheers
Mally
Hi Mally,
I think the idea is to get search results pages into the sitemap based on what people are actually searching for on the site (first code), and then secondly to do the same based on what people actually searched for on Google (rather than your site) to find a given page.
The file can just be submitted to Google in the same way as any other sitemap file; but you could also list in the main sitemap index (sitemap.php) in exactly the same way as described in the post mentioned in the sitemapextra thread. As you have already done this, again look for the following code in sitemap.php:
print "<sitemapindex ......then add the following on the next line:
print "<sitemap><loc>http://www.yoursite.com/sitemapquery.php</loc></sitemap>";(so this would be in addition to your existing sitemapextra.xml line)
Cheers,
David.
Hello David
Could you help put together the following mysql file, (to make an install easier for me)
You mentioned above how to add a count
<?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>";
?>Could you please show me how to add the following addition to the above install code
simply add a "clicks" field of type "INT" to the products table.
thanks
Mally
Hi Mally,
I take it you want to edit setup.sql to do this so that the new field is included automatically from installation. No probs - just look for the current line that is part of the products table definition:
rating int(11) NOT NULL default '0',...and add the following on the next line:
clicks int(11) NOT NULL default '0',It's in the middle of the SQL so you don't need to worry about commas etc.!
Cheers,
David.
Hello David
I'm getting the following errors
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/magaa/public_html/includes/database.php on line 21
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/magaa/public_html/includes/database.php on line 26
any advice please?
Mally
Hi Mally,
The errors appear to occur where you intend to display top searches; so i'm wondering if this is a new installation and you haven't yet applied the database changes to support the top 10 searches query - possibly from the following thread...
http://www.pricetapestry.com/node/892
Cheers,
David.
Hello Gabriel,
There's quite a few steps involved in doing this. In the first instance, you need to be capturing users' queries. Have you put anything in place to do this yet, or do you need help with this part of it?
Are you familiar with any MySQL administration tools, for example phpMyAdmin; or does your hosting provider offer a MySQL control panel that you could use to create a table in your Price Tapestry database?
Cheers,
David.