Zen Cart 1.3.8a
Site was updated from 1.3.7 back in 2008

I am not sure of any add-ins as I am the new webmaster for this site. I'm also new to Zen Cart. My background in php and mysql so learning a new tool is not very difficult.

My client has requested a change to the Advanced Search to allow customer to search past orders on a product attribute. I have followed the recommendations of this post and have modified 4 files to implement this change. I think I just need a few more tweaks, but am stuck.

Here are the changes I have made:

1. modified the file
\my_template\includes\templates\my_template\templates\tpl_advanced_search_defaul t.php by adding a new fieldset

Code:
<fieldset>
<legend>OR Search Past Orders by Patient Name</legend>
 <div class="centeredContent"><?php echo zen_draw_input_field('patient', $sData['patient'], 'onfocus="RemoveFormatString(this, \'' . PATIENT_FORMAT_STRING . '\')"'); ?>&nbsp;&nbsp;&nbsp;<?php echo zen_draw_checkbox_field('search_in_patient', '1', $sData['search_in_patient'], 'id="search-in-patient"'); ?><label class="checkboxLabel" for="search-in-patient"><?php echo TEXT_SEARCH_IN_PATIENT; ?></label></div>

<br class="clearBoth" />
</fieldset>
2. modified the file my_template\includes\modules\pages\advanced_search\header_php.php to define the $sData field

Code:
  $sData['patient'] =  (isset($_GET['patient']) ? zen_output_string($_GET['patient']) : zen_output_string(PATIENT_FORMAT_STRING));

  $sData['search_in_patient'] = (isset($_GET['search_in_patient']) ? zen_output_string($_GET['search_in_patient']) : 1);

3. modified the file my_template\includes\languages\english\advanced_search.php

Code:
define('TEXT_SEARCH_IN_PATIENT', 'Search In Order for Patient Attribute');
  define('PATIENT_FORMAT_STRING', 'patient');
4. modified the file my_template\includes\modules\pages\advanced_search_result\header_php.php to adjust the from and where clause code. I'm only allowing them to search either by product description OR by past order product attritbute 'Patient. So this was a big code change to this file. I'm sure I haven't done it correctly and not gettting any result.

I will break the code down into places that I modified as this page has many lines.

1. added get my variable like the keyword search does.
Code:
$_GET['keyword'] = trim($_GET['keyword']);

$_GET['patient'] = trim($_GET['patient']);
2. Added the new patient variable to the test if any search criteria was entered so I don't get the error message.

Code:
if ( (isset($_GET['keyword']) && (empty($_GET['keyword']) || $_GET['keyword']==HEADER_SEARCH_DEFAULT_TEXT || $_GET['keyword'] == KEYWORD_FORMAT_STRING ) ) &&

(isset($_GET['dfrom']) && (empty($_GET['dfrom']) || ($_GET['dfrom'] == DOB_FORMAT_STRING))) &&

(isset($_GET['dto']) && (empty($_GET['dto']) || ($_GET['dto'] == DOB_FORMAT_STRING))) &&

(isset($_GET['pfrom']) && !is_numeric($_GET['pfrom'])) &&

(isset($_GET['pto']) && !is_numeric($_GET['pto'])) &&

(isset($_GET['patient]) && (empty($_GET['patient'])) ) {
3. added variable to hold the contents of the new fiield on the page.

Code:
 if (isset($_GET['patient'])) {

    $patients = $_GET['patient'];

  }

4. Since my search needs to be on the orders_products_attributes table and having to link other tables to search by customer, I had to change the entire "from str" . So I'm creating my own From string.


Code:
if (isset($_GET['patient'])) {

  $from_str = "FROM  " . TABLE_ORDERS_PRODUCTS_ATTRIBUTES . " pat,
                     " . TABLE_ORDERS_PRODUCTS . " op,
                     " . TABLE_ORDERS . " o,
					 " . TABLE_PRODUCTS . " p,
					 " . TABLE_PRODUCTS_DESCRIPTION . " pd";

		 


  }
 else
 {
$from_str = "FROM (" . TABLE_PRODUCTS . " p

             LEFT JOIN " . TABLE_MANUFACTURERS . " m

             USING(manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c )

             LEFT JOIN " . TABLE_META_TAGS_PRODUCTS_DESCRIPTION . " mtpd

             ON mtpd.products_id= p2c.products_id

             AND mtpd.language_id = :languagesID";


}
$from_str = $db->bindVars($from_str, ':languagesID', $_SESSION['languages_id'], 'integer');
the else statement was added to use the original From_str if they don't search by patient.

5. Same for the Where String since I have different tables to join.

Code:
if (isset($_GET['patient'])) {
$where_str = " WHERE (p.products_status = 1
               AND p.products_id = pd.products_id
               AND pat.orders_id = o.orders_id
			   AND op.orders_id = o.orders_id
			   AND op.products_id = p.products_id
			   AND pat.products_options = 'Patient'
               AND pd.language_id = :languagesID
			   AND o.customers_id = :customerID
			   AND pat.products_options_values LIKE '%:patient%'";
			   
$where_str = $db->bindVars($where_str, ':customerID', $_SESSION['customers_id'], 'integer');

}
else
{
$where_str = " WHERE (p.products_status = 1

               AND p.products_id = pd.products_id

               AND pd.language_id = :languagesID

               AND p.products_id = p2c.products_id

               AND p2c.categories_id = c.categories_id ";


}
$where_str = $db->bindVars($where_str, ':languagesID', $_SESSION['languages_id'], 'integer');

6. I didn't change the Select or Sort order string because I want the same fields selected for display and assume it will continue sorting by product name. That is why I joined the Products_Description table.

So my question is now that I have my SQL statement constructed with the from, where and sort which I assume happens in this line

Code:
$listing_sql = $select_str . $from_str . $where_str . $order_str;
how does it call the database and execute the search. Here is the remaining code in this page, but not sure what it is calling.

Code:
$zco_notifier->notify('NOTIFY_SEARCH_ORDERBY_STRING', $listing_sql);

$breadcrumb->add(NAVBAR_TITLE_1, zen_href_link(FILENAME_ADVANCED_SEARCH));

$breadcrumb->add(NAVBAR_TITLE_2);





$result = new splitPageResults($listing_sql, MAX_DISPLAY_PRODUCTS_LISTING, 'p.products_id', 'page');

if ($result->number_of_rows == 0) {

  $messageStack->add_session('search', TEXT_NO_PRODUCTS, 'caution');

  zen_redirect(zen_href_link(FILENAME_ADVANCED_SEARCH, zen_get_all_get_params('action')));

}



// This should be last line of the script:

$zco_notifier->notify('NOTIFY_HEADER_END_ADVANCED_SEARCH_RESULTS', $keywords);

As I said I'm a newbie to Zen Cart. It is hard to follow at times, but I was able to figure out where to change the code, just need a little push to get it working.

Thanks,
Marnie