Search Log

[woza]

Hi

For sometime now I’ve been using Zen Cart and setting it up for clients and I’ve always wanted to “put something back” into the project. as a way of appreciation if you like. So I’ve come up with a little contribution, which I hope shop owners will find useful.
I know a little PHP but I’m certainly no programmer. Likewise I know how to manipulate Zen Cart’s front end but I couldn’t tell you how it all works under the hood.
Which is why when you see my little contribution please don’t have a go at me for the bad code or anything.
One of the reason I’m putting it on here is because I want people who do know how to write contributions to tell me where I can improve it and where I've gone wrong. I’ve have ideas of new functionality I want to add to it as well so hope it will be come a nice little project.

I’ve read a lot of stuff about e-commerce on the web and in books. How to promote your site, how to sell your products, etc, etc.
Out of all the literature I came across a very interesting question. Wouldn’t it be great to find out what your customers want rather than guessing? The answer was simple! Log what searches are entered via the Zen Cart search box! That way you get a list of all the items people have been looking for. Yes, it may contain a load of products you already sell, but if your site sells, say, sweets and you find out 100+ people searched for Lemon Drops and you don’t sell them, wouldn’t it be a good idea to start selling what your potential customers want? It could mean extra sales.

I’ve been trying this out on a few client sites for a couple of months and I’ve had great feedback from it. I have a customer with a pet site. He sells various pet products but we discovered a lot of people searched for parrot cages. He now sells parrot cages and it’s turned out to be one of is biggest money makers. He’s analysed the data and has added various products he didn’t sell before based on the data captured from the search box. I’m now wishing I’d charged him for the functionality ;)


There are a couple of things I want to add.
* I want to be able to filter the results by a date range.
* Search within the results.
* Archive results. This would have 2 options. Archive all and archive by date.
* Add a “popular/ top searches” to the front end.

I’m looking to learn to add the above list, but if anyone has any ideas on code to use then I would be most grateful.

Like I said it may not be pretty, but it works. Plus, I hope (with a little help) to make is a nice clean contrib.

I will be setting up a micro-site for the project soonish, that way it will be easier to keep track of releases and changes.

If anyone has any feedback no mater what it is, please let me know, plus you can always PM me if you like.

I look forward to hearing from you all…

P.S. I do have this working on a few live sites with no probs, but I would try it out on a test site first. After all it's a BETA (maybe it should be an ALPHA, but I'm not up on my development jargon)! As always, BACKUP, BACKUP, and BACKUP some more!

[TheOracle]

Since this is a beta release, do you require additional technical assistance for patchng this MOD or it can simply remain this way ?

[woza]

By all means assist away! I only put beta on it 'coz...... well actually I'm not sure why. Should I take it off?

As the origianl post says I'm after some help, advice, general feedback, etc on the mod....

[TheOracle]

I have just finished modifying the whole ZIP pack. Some errors were involved from your Beta release. It's time to make it an official release (or will it ? ).

[FONT="Arial"]// Bug fixes.[/FONT]

- Running into multi-languages.
- Using more adaptive target actions with Zen Cart modules.
- Increased routines for more severe inspections (admin section).
- Redirects the proper way by checking, first, if the webmaster is under SSL or NON-SSL page before redirecting.
- Sessions are, now, properly gathered from the admin section.
- SQL injections from includes/templates/<your_template>/templates/tpl_advanced_search_result_default.php file.
- More protective SQL injections.
- Expanded SQL selection statements.
- Additional status text in case unexpected failures would be manifested in the future.

I believe this covers all (for now).

Good luck !

(Note: I'm not posting the codings this time).

[TheOracle]

Since I cannot edit my post above, this is the right ZIP file.

[FONT="Arial"]// Additional Bug fixes.[/FONT]

- While loop statement was badly initiated.

Good luck !

[woza]

That's great thanks Oracle. I can't however get it to work.
I was getting a lot of errors which most of them I've fixed, but I'm stuck with this error:

Warning: main(DIR_WS_INCLUDESheader.php): failed to open stream: No such file or directory in /home/***/***/***/admin/searchlog.php on line 61

Warning: main(DIR_WS_INCLUDESheader.php): failed to open stream: No such file or directory in /home/***/***/***/admin/searchlog.php on line 61

Fatal error: main(): Failed opening required 'DIR_WS_INCLUDESheader.php' (include_path='.:/usr/lib/php:/usr/local/lib/php') in /home/***/***/***/admin/searchlog.php on line 61

It does it the same on 1.2.6 + 1.3.0.*

It's obviously not pulling the path for DIR_WS_INCLUDES from the config file, but I'm not sure what it missing.

Cheers

[woza]

OK, been playing and it's can't seem to find a file in this section

if (@file_exists('includes/application_top.php') && defined('DIR_WS_CLASSES') && @file_exists(DIR_WS_CLASSES. 'currencies.php')) {
require('includes/application_top.php');
require(DIR_WS_CLASSES . 'currencies.php');
$currencies = new currencies();
$languages_id = $_SESSION['languages_id'];
}

[TheOracle]

In your admin/includes/configure.php file, you must probably have your DIR_WS_CLASSES path set like this:

define('DIR_WS_CLASSES', DIR_WS_INCLUDES. 'classes'); 

If so, then simply change:

if (@file_exists('includes/application_top.php') && defined('DIR_WS_CLASSES') && @file_exists(DIR_WS_CLASSES. 'currencies.php')) {
require('includes/application_top.php');
require(DIR_WS_CLASSES . 'currencies.php');
$currencies = new currencies();
$languages_id = $_SESSION['languages_id'];
} 

to read:

require('includes/application_top.php');
require(DIR_WS_CLASSES . '/currencies.php');
$currencies = new currencies();
$languages_id = $_SESSION['languages_id']; 

This should fix the problem (as the if statement was not required there neither - I put it there to see if it worked but it doesn't).

[jeking]

Woza,

Thanks for putitng this together. It's exactly what I was looking for. The search function I use on another site has an more advanced version of this and it's VERY helpful to know what visitors are looking for. I'll get to installing it shortly and let you know how it turns out.

[woza]

Hay Jenkin. Glad to hear you like the idea! Just gotta get it working now :)

I've added that code change got the scrip to load. Problem now is it's not loading any data. It just return loads of blank rows in 1.2.6 and in 1.3.0.2
"Fatal error: Maximum execution time of 60 seconds exceeded in /home/***/***/***/admin/searchlog.php on line 196".
It's seem stuck in a loop.

I echoed out the SQL that is being run and if I run it via PHPMYADMIN and it works, but not via the script:

SELECT id, date, searchterm, count(*) AS Number FROM zen_search_log WHERE searchterm is not null AND searchterm not like '' GROUP BY searchterm ORDER BY Number DESC LIMIT 0, 15

It also doesn't appear to be loading the language file either.


This is a the code I've got so far:
By the way I'm trying this on 1.2.6 + 1.3.0.2

<?php
//
// +----------------------------------------------------------------------+
// |zen-cart Open Source E-commerce                                       |
// +----------------------------------------------------------------------+
// | Copyright (c) 2003 The zen-cart developers                           |
// |                                                                      |
// | http://www.zen-cart.com/index.php                                    |
// |                                                                      |
// | Portions Copyright (c) 2003 osCommerce                               |
// +----------------------------------------------------------------------+
// | This source file is subject to version 2.0 of the GPL license,       |
// | that is bundled with this package in the file LICENSE, and is        |
// | available through the world-wide-web at the following url:           |
// | http://www.zen-cart.com/license/2_0.txt.                             |
// | If you did not receive a copy of the zen-cart license and are unable |
// | to obtain it through the world-wide-web, please send a note to       |
// | [email protected] so we can mail you a copy immediately.          |
// +----------------------------------------------------------------------+
//  $Id: sales_log.php,v 0.1 2006/07/08 15:02:25 wilt Exp $
//  Revised by: TheOracle.

require('includes/application_top.php');
require(DIR_WS_CLASSES . 'currencies.php');
$currencies = new currencies();
$languages_id = $_SESSION['languages_id'];

?>

<!doctype html public "-//W3C//DTD HTML 4.01 Transitional//EN">
<html <?php echo HTML_PARAMS; ?>>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=<?php echo CHARSET; ?>">
<title><?php echo TITLE; ?></title>
<link rel="stylesheet" type="text/css" href="includes/stylesheet.css">
<link rel="stylesheet" type="text/css" href="includes/cssjsmenuhover.css" media="all" id="hoverJS">

<link rel="stylesheet" href="cal/dhtmlgoodies_calendar/dhtmlgoodies_calendar.css?random=20051112" media="screen"></LINK>
<SCRIPT type="text/javascript" src="cal/dhtmlgoodies_calendar/dhtmlgoodies_calendar.js?random=20060118"></script>

<script language="javascript" src="includes/menu.js"></script>
<script language="javascript" src="includes/general.js"></script>
<script type="text/javascript">
  <!--
  function init()
  {
    cssjsmenu('navbar');
    if (document.getElementById)
    {
      var kill = document.getElementById('hoverJS');
      kill.disabled = true;
    }
  }
  // -->
</script>
</head>
<body onLoad="init()">
<!-- header //-->
<?php require(DIR_WS_INCLUDES . 'header.php'); ?>
<!-- header_eof //-->
<br/>
<table width="100%" border="0" cellspacing="0" cellpadding="8">
  <tr>
    <td align="center" bgcolor="#CCCCCC"><?php echo DATE_RANGE. ' ' . MATCH_SEARCH_TERM; ?></td>
    <td align="center" bgcolor="#CCCCCC"><?php echo MATCH_SEARCH; ?></td>
    <td align="center" bgcolor="#CCCCCC"><?php echo GLOBAL_ACTIONS; ?></td>
    <td width="25%" bgcolor="#FF9900"><?php echo INFO; ?></td>
  </tr>
  <tr>
    <td align="center" valign="top" bgcolor="#CCCCCC">
	<?php echo COMING_SOON; ?>
	<?php $mydate = date("Y-m-d"); ?>
	<!--<form name="form1" method="post" action="">
      <label>First Date
          <input type="text" value="<?php echo $mydate; ?>"  name="theDate">
          <input name="button" type="button" onClick="displayCalendar(document.forms[0].theDate,'yyyy-mm-dd',this)" value="Cal">
        </label>
		 <label>
		 <br>
		 Second Date:
          <input type="text" value="<?php echo $mydate; ?>"  name="theDate2">
          <input name="button2" type="button" onClick="displayCalendar(document.forms[0].theDate,'yyyy-mm-dd',this)" value="Cal">
        </label>
		 <br>
	    <br>


        <input type="submit" name="submit" type="<?php echo SEARCHLOG_SUBMIT; ?>">
      </form>--></td>
    <td align="center" valign="top" bgcolor="#CCCCCC"><p><?php echo COMING_SOON; ?></p></td>
    <td align="center" valign="top" bgcolor="#CCCCCC"><p><?php echo COMING_SOON; ?></p></td>
    <td width="25%" valign="top" bgcolor="#FF9900"><p><?php echo COPYRIGHT_AUTHOR_URL; ?></p>
    <p><?php zen_href_link('#', '', 'NONSSL'); ?></a></p></td>
  </tr>
</table>

<!-- body //-->
<table border="0" width="100%" cellspacing="2" cellpadding="2">
  <tr>
<!-- body_text //-->
    <td width="100%" valign="top">
    <?php

    if (isset($_GET['id']) || isset($_POST['id'])) {
    $id = (isset($_POST['id'])) ? intval(trim(zen_db_prepare_input($_POST['id']))) : 0;
    } else {
    $id = 0;
    } ###### End of if statement.

	switch( $_GET['action'] ) {
    case "delete":

  if ($_SESSION['admin_id'] && !empty($id)) {
  $sql_delete = $db->Execute("delete from '"  . TABLE_SEARCHLOG . "' where id = ".(int)$id);
  if ($sql_delete) {
  $messageStack->add_session(ENTRY_SEARCHLOG_DELETE_SUCCESSFUL. ' ' .$id, 'success');
  } else {
  $messageStack->add_session(ENTRY_SEARCHLOG_DELETE_FAILED. ' ' .$id, 'error');
  } ###### End of if statement.
  } else {
  $messageStack->add_session(ENTRY_SEARCHLOG_ID_NOT_FOUND, 'error');
  } ###### End of if statement.
    break;

    case "deleteall":

  if ($_SESSION['admin_id'] && !empty($id)) {
  $sql_delete = $db->Execute("delete from '"  . TABLE_SEARCHLOG . "' where id = ".$id);
  if ($sql_delete) {
  $messageStack->add_session(ENTRY_SEARCHLOG_DELETE_SUCCESSFUL. ' ' .$id, 'success');
  } else {
  $messageStack->add_session(ENTRY_SEARCHLOG_DELETE_FAILED. ' ' .$id, 'error');
  } ###### End of if statement.
  } else {
  $messageStack->add_session(ENTRY_SEARCHLOG_ID_NOT_FOUND, 'error');
  } ###### End of if statement.
   	break;
  } ###### End of switch case statement.
  ?>
	<table width=\"100%\" border=\"0\" cellpadding=\"4\" cellspacing=\"0\">
    <tr>
        <td width=\"110\"><?php echo SEARCH_TERM; ?></td>
        <td><?php echo NUMBER_TIMES_SEARCHED_FOR; ?></td>
		<td><?php echo SEARCHLOG_DATE; ?></td>
		<td><?php echo SEARCHLOG_ACTION; ?></td>
    </tr>
  <?php

  $color1 = "#BFD8BC";
  $color2 = "#E0E0E0";
  $row_count = 0;

  if (!isset($_GET['page']) || empty($_GET['page'])) {
  $page = 1;
  } else {
  $page = $_GET['page'];
  } ###### End of if statement.

  // Define the number of results per page
  if (isset($resultcount) && $resultcount != 0) {
  $max_results = $resultcount;
  } else {
  $max_results = 15;
  } ###### End of if statement.

  // Figure out the limit for the query based
  // on the current page number.
  $set_page = intval(trim($page));
  $set_max_results = intval(trim($max_results));
  $from = (($set_page * $set_max_results) - $set_max_results);
  $set_from = (isset($from)) ? trim($from) : '';

  // Perform MySQL query on only the current page number's results
  if ($_SESSION['admin_id'] && isset($theDate) && isset($theDate2) && $theDate != "0000-00-00" && $theDate2 != "0000-00-00") {

   $sql = "SELECT id, date, searchterm, count(*) AS Number
  		   FROM " . TABLE_SEARCHLOG . "
   		   WHERE searchterm is not null AND searchterm not like '' AND date BETWEEN '".$theDate."' AND '".$theDate2."'
    	   GROUP BY searchterm
      	   ORDER BY Number DESC
           LIMIT ".$set_from.", ".$set_max_results;

  } else {
   $sql = "SELECT id, date, searchterm, count(*) AS Number
           FROM " . TABLE_SEARCHLOG . "
           WHERE searchterm is not null AND searchterm not like ''
           GROUP BY searchterm
           ORDER BY Number DESC
           LIMIT ".$set_from.", ".$set_max_results;
  } ###### End of if statement.

  $sql_select = $db->Execute($sql);
echo $sql;
  if ($sql_select) {

  while (!$searchlog_row->EOF) {
  $search_id = (isset($searchlog_row->fields['id'])) ? intval(trim($searchlog_row->fields['id'])) : 0;
  $mydatea = (isset($searchlog_row->fields['date'])) ? trim($searchlog_row->fields['date']) : "0000-00-00";
  $searchterm = (isset($searchlog_row->fields['searchterm'])) ? (strip_tags(trim($searchlog_row->fields['searchterm']))) : "";
  $number = (isset($searchlog_row->fields['Number'])) ? intval(trim($searchlog_row->fields['Number'])) : 0;

  /* Now we do this small line which is basically going to tell
  PHP to alternate the colors between the two colors we defined above. */

  $row_color = ($row_count % 2) ? trim($color1) : trim($color2);

  // Echo your table row and table data that you want to be looped over and over here.

  if ($_SESSION['admin_id'] && !empty($search_id) && $search_id != 0 && !empty($mydatea) && $mydatea != "0000-00-00" && !empty($searchterm) && !empty($number) && $number != 0) {
  ?>
  <tr>
  <td width="110" bgcolor="<?php echo $row_color; ?>" nowrap><?php echo $searchterm; ?></td>
  <td bgcolor="<?php echo $row_color; ?>"><?php echo $number; ?></td>
  <td bgcolor="<?php echo $row_color; ?>"><?php echo $mydatea; ?></td>
  <td width="100" bgcolor="<?php echo $row_color; ?>"><?php zen_href_link(FILENAME_SEARCH_LOG, 'action=delete&id='.intval(trim($search_id)), 'NONSSL'). ENTRY_DELETE_LINK; ?></a><br /></td>
  </tr>
  <?php

  // Add 1 to the row count

  $row_count++;
  } ###### End of if statement.
  } ###### End of while statement.

   }else {
  $messageStack->add_session(SEARCHLOG_FAILED_TO_SELECT_FIELDS, 'error');
  } ###### End of if statement.

  // Close out your table.
  ?>
  </table>
  <?php

  if (isset($dateone) && isset($datetwo) && !empty($dateone) && $dateone != "0000-00-00" && !empty($datetwo) && $datetwo != "0000-00-00") {
  $total_results = $db->Execute("SELECT count(DISTINCT searchterm) FROM " . TABLE_SEARCHLOG . " WHERE searchterm IS NOT NULL AND date BETWEEN '".$theDate."' and   '".$theDate2."' AND searchterm NOT LIKE '' ");
  } else {
  $total_results = $db->Execute("SELECT count(DISTINCT searchterm) FROM " . TABLE_SEARCHLOG . " WHERE searchterm IS NOT NULL AND searchterm NOT LIKE ''  ");
  } ###### End of if statement.

  // Figure out the total number of pages. Always round up using ceil()
  $total_pages = ceil($total_results / $max_results);
  #echo "Total number of distinct search terms: ". $total_results ."";
  // Build Page Number Hyperlinks
  echo trim(SEARCHLOG_SELECT_PAGE)."<br />";

  // Build Previous Link

  if ($_SESSION['admin_id'] && isset($page) && $page > 1) {
  $prev = ($page - 1);
  zen_href_link(FILENAME_SEARCH_LOG, 'page='.intval(trim($prev)), 'NONSSL'). SEARCHLOG_PREVIOUS_PAGE . '</a>&nbsp;';
  } ###### End of if statement.

  for ($i = 1; $i <= $total_pages; $i++) {
  if(($page) == $i) {
  echo "$i&nbsp;";
  } else {
  zen_href_link(FILENAME_SEARCH_LOG, 'page='.$i, 'NONSSL') . '$i</a>&nbsp;';
  } ###### End of if statement.
  } ###### End of for statement.

  // Build Next Link
  if ($_SESSION['admin_id'] && $page < $total_pages) {
  $next = ($page + 1);
  zen_href_link(FILENAME_SEARCH_LOG, 'page='.intval(trim($next)), 'NONSSL'). SEARCHLOG_NEXT_PAGE . '</a>&nbsp;';
  } ###### End of if statement.
  ?>
  </td>
  <!-- body_text_eof //-->
  </tr>
  </table>
  <!-- body_eof //-->

  <!-- footer //-->
  <?php require(DIR_WS_INCLUDES . 'footer.php'); ?>
  <!-- footer_eof //-->
  <br>
  </body>
  </html>
<?php require(DIR_WS_INCLUDES . 'application_bottom.php'); ?>