Hello everyone,
I am new to x-cart (migrated from oscommerce) and wanted to be able to see what my customers were searching for when visiting my shop.
I had this feature in my oscommerce shop (from a contribution) so I decided to try and make it happen with xcart.
I Must warn you, this is my first attempt at doing anything like this and I am no programer... SO USE IT AT YOUR OWN RISK!!!!!!
and
BACKUP!!!BACKUP!!!BACKUP!!!
Oh, and it also looks ugly. Maybe one of the pros can make it a little prettier?
Here we go....
First ad 2 new tables to the DB...
Code:
#
# Table structure for table `search_queries`
#
CREATE TABLE `search_queries` (
`search_id` int(11) NOT NULL auto_increment,
`search_text` tinytext,
PRIMARY KEY (`search_id`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;
# --------------------------------------------------------
#
# Table structure for table `search_queries_sorted`
#
CREATE TABLE `search_queries_sorted` (
`search_id` smallint(6) NOT NULL auto_increment,
`search_text` tinytext NOT NULL,
`search_count` int(11) NOT NULL default '1',
PRIMARY KEY (`search_id`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;
Now open /xcart/customer/search.php and add the following (
in between the comments)...
Code:
#
# $Id: search.php,v 1.50 2003/11/12 14:42:07 svowl Exp $
#
require "./auth.php";
require $xcart_dir."/include/categories.php";
$tmp=strstr($QUERY_STRING, "$XCART_SESSION_NAME=");
if (!empty($tmp))
$QUERY_STRING=ereg_replace("$XCART_SESSION_NAME=([0-9a-zA-Z]*)", "", $QUERY_STRING);
// ADD THIS FOR THE KEYWORDS SEARCHED TO FUNCTION
mysql_query("insert into search_queries (search_text) values ('" . $substring . "')");
// END OF ADD THIS FOR THE KEYWORDS SEARCHED TO FUNCTION
if(!empty($QUERY_STRING)) {
Now create a new file /xcart/customer/stats_keywords.php with the code below...
Code:
<?php
require "./auth.php";
require $xcart_dir."/include/categories.php";
?>
<!doctype html public "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title></title>
</head>
<body marginwidth="0" marginheight="0" topmargin="0" bottommargin="0" leftmargin="0" rightmargin="0" bgcolor="#FFFFFF">
<table width="435" border="0" cellspacing="0" cellpadding="0" align="center">
<tr>
<td width="104" valign="top">
<form method="post" action="stats_keywords.php">
<input type="hidden" name="action" value="deletedb">
<input type="submit" value="deleteDB">
</form>
<form method="post" action="stats_keywords.php">
<input type="hidden" name="action" value="updatedb">
<input type="submit" value="UpdateDB">
</form>
<form method="post" action="stats_keywords.php">
<input type="hidden" name="action" value="show_searches">
<input type="hidden" name="sortorder" value="search_text">
<input type="hidden" name="ascdsc" value="ASC">
<input type="submit" value="Sort By Name">
</form>
<form method="post" action="stats_keywords.php">
<input type="hidden" name="action" value="show_searches">
<input type="hidden" name="sortorder" value="search_count">
<input type="hidden" name="ascdsc" value="DESC">
<input type="submit" value="Sort By Count">
</form></td>
<td width="200" valign="top">
<?php
if ($HTTP_POST_VARS['action'] == 'deletedb') {
mysql_query("delete from search_queries_sorted");
} // delete db
if ($HTTP_POST_VARS['action'] == 'updatedb') {
$sql_q = mysql_query("select search_id, search_text from search_queries order by search_text");
while ($sql_q_result = mysql_fetch_array($sql_q)) {
/*
$sql_count = mysql_query("select count(*) as total from search_queries where search_text = '" .
$sql_q_result['search_text'] . "'");
$sql_count_result = mysql_fetch_array($sql_count);
*/
$update_q = mysql_query("select search_text, search_count from search_queries_sorted where
search_text = '" . $sql_q_result['search_text'] . "'");
$update_q_result = mysql_fetch_array($update_q);
$count = 1 + $update_q_result['search_count'];
if ($update_q_result['search_count'] != '') {
mysql_query("update ignore search_queries_sorted set search_count = '" .
$count . "' where search_text = '" .
$sql_q_result['search_text'] . "'");
} else {
mysql_query("insert ignore into search_queries_sorted (search_text, search_count) values ('" .
$sql_q_result['search_text'] . "','" . $sql_count_result['total'] . "')");
} // search_count
mysql_query("delete from search_queries where search_id = '" . $sql_q_result['search_id'] . "'");
} // while
} // updatedb
if ($HTTP_POST_VARS['action'] == 'show_searches') {
?>
<table cellpadding=2 cellspacing=2 border="1"><tr><td class="main" align="left"> [b]<u>Keyword </td><td class="main"
align="center"> [b]<u>Searches </td></tr>
<?php
$sql_q = mysql_query("select search_text, search_count from search_queries_sorted order by " . $HTTP_POST_VARS['sortorder'] . " " . $HTTP_POST_VARS['ascdsc']);
while ($sql_q_result = mysql_fetch_array($sql_q)) {
$searchcount = ($sql_q_result['search_count'] + 1);
echo '<tr><td class="main" nowrap> ' . $sql_q_result['search_text'] . '</td><td>
' . $searchcount . '</td></tr>';
} // while
?>
</table>
<?php
} // if show_searches
?>
</td>
</tr>
</table>
</p>
</body>
</html>
-----------------------------------
This should be it.
Now everytime someone searches for anything in your store, it will be saved to the DB for your viewing.
This is very useful so you know what people are most looking for.
-----------------------------------
One thing:
1. When you go to
http://www.domain.com/xcart/customer/stats_keywords.php , you'll not see the keywords yet. You'll need to click on "Update DB" then click on either "Sort by name" or "Sort by count" buttons to view the collected data.
I think that is it.
Hope this helps and I look forward to any suggstions for improvement
