Keep track of keywords searched!

Emerson · #1 04-07-2004, 05:34 PM

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

domspe · #2 04-10-2004, 11:38 AM

ROCK ON - can't wait to try it!

did you implement this on 3.5.5??

Thank you for a useful mod!!

Emerson · #3 04-10-2004, 11:44 AM

Hi Jaime,

I used this on a 3.5.6 Pro but I am pretty sure it will work with 3.5.5 as all it does is collect the keyword from the url and save it to the DB tables you create

anandat · #4 04-12-2004, 07:44 AM

Thanks lot Emerson

this was much wanted mod.
It's working with 3.5.5 too

Thanks once again for it mate.

Emerson · #5 04-12-2004, 07:46 AM

YW

I'm glad I could help

Emerson · #6 04-12-2004, 05:20 PM

While trying to figure out how to fix the count problem, I came across this.

I changed...

Code:

$sql_q = mysql_query("select search_text, search_count from search_queries_sorted order by " . 
         $HTTP_POST_VARS['sortorder'] . " " . $HTTP_POST_VARS['ascdsc']);

to

Code:

$sql_q = mysql_query("select search_text, search_count+1 from search_queries_sorted order by " . 
         $HTTP_POST_VARS['sortorder'] . " " . $HTTP_POST_VARS['ascdsc']);

...which should have fixed the problem.
However, the results are blank.

I tried running the query manually via phpMyAdmin and it works fine.

Anyone have any ideas what could be causing the problem?

Emerson · #7 04-12-2004, 05:29 PM

Found a solution...

It now displays the correct count for the keywords searched...

The code has been updated in the original post to reflect this change.

maki · #8 05-21-2004, 12:13 AM

Hi !!

I have followed the instructions indicated, but when I send "stats_keywords.php" it gives back me home main

I running v3.4.14

Anyone have any ideas?

greetings!!

GM · #9 05-26-2004, 08:51 AM

AWESOME! VERY AWESOME! It works perfectly on 3.5.7
I'm gonna' see what I can do to incorporate it into admin.
Thank You, Great Mod!

GM · #10 05-26-2004, 01:46 PM

Ok, I made a few small changes that allow you to call this from the Admin area and cleaned up the GUI a little (room for improvement)

First of all stats_keywords.php goes in XCART/admin not XCART/customer, here it is with the changes:

UPDATED 05/27/04 TO FUNKY PURPLE (you get the idea)

Code:

<?php 
require "./auth.php"; 
require $xcart_dir."/include/categories.php";
require $xcart_dir."/include/security.php"; 
?> 
<!doctype html public "-//W3C//DTD HTML 4.01 Transitional//EN"> 
<html> 
<head> 
<title></title> 

</head> 
<body marginwidth="0" marginheight="0" topmargin="50" bottommargin="0" leftmargin="0" rightmargin="0" bgcolor="#73004E"> 
<table width="435" border="2" cellspacing="0" cellpadding="4" align="center" bgcolor="#73004E"> 
  <tr> 
    <td width="104" valign="middle" bgcolor="#73004E"> 
      <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>
                <form method="post" action="home.php">
                <input type="submit" value="Back To Admin"></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" bgcolor="#FFFFFF"><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 search_count DESC"); 

      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>

OOoops! I forgot this... Add this line to skin1/admin/menu_admin.tpl

Code:

Search Stats

Done!