Advanced Search - customized product fields?

Hey Community!
I am trying do expand the advanced search feature so that it is capable of searching custom fields and tables i added to the database. I created some extra database tables that hold extra information about products by using this how to:
http://www.zen-cart.com/forum/showthread.php?t=120523

now i have - let`s say - one field called "class_of_absorbtion" in a table called "products_extra_stuff" and of course a products_id field in that same table so the extra information can be allocated easily to the product.

I figured out how to insert the field in the search tpl by editing:

/includes/templates/my_template/templates/tpl_advanced_search_default.php

i added this code to /tpl_advanced_search_default.php to generate a dropdown with the options from the database:

<?php $absorbtion_array = array(array('id' => '', 'text' => TEXT_NONE)); $abso = $db->Execute("select id, absorbtionclass from " . TABLE_ABSO . " order by id"); while (!$abso->EOF) { $absorbtion_array[] = array('id' => $abso->fields['absorbtionclass'], 'text' => $abso->fields['absorbtionclass']); $abso->MoveNext(); } ?>

and this where the form and the dropdown is created

<fieldset> <legend>Class of absorbtion</legend> <?php echo zen_draw_separator('pixel_trans.gif', '24', '15') . ' ' . zen_draw_pull_down_menu('class_of_absorbtion', $absorbtion_array, $pInfo->class_of_absorbtion); ?> <br class="clearBoth" /> </fieldset>

now i modified the
/include/modules/pages/advanced_search/header_php.php and added this code so the value of the input is processed in the search:

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

next step would be to modify the
/include/modules/pages/advanced_search_results/header_php.php

this is where the actual search is done, right?

Now im stuck :wacko: on how to edit the search query because i dont know where to tell zen cart that it should also pick results where the "class_of_absorbtion" value from the products_extra_stuff table concurs in the value of the search query.

Any suggestion and hints would be highly appreciated!

Thank you very much!

well somehow i figured it out but get a strange error...

the search can be expanded to custom tables by adding this to your
/include/modules/pages/advanced_search_results/header_php.php

• assuming you have a field called extras_flammability_test

if (isset($_GET['extras_flammability_test'])) {

$extras_flammability_test = $_GET['extras_flammability_test'];

}

then in the $define_list = array
i added something like

'PRODUCT_EXTRAS_FLAMMABILITY_TEST' => PRODUCT_EXTRAS_FLAMMABILITY_TEST,

in the query i added

LEFT JOIN " . TABLE_PRODUCTS_EXTRA_STUFF. " pu

         ON pu.products_id= p2c.products_id

and did some other minor changes to the file. The search performs and gets the products where the criteria are matching, but somehow on every result i get there are 4 buttons to add the product to the shopping cart - i m looking at it for quite a time now but cannot find out what went wrong - has anyone a hint or point my stupid face to it so i can find the problem? thank you -
the advanced search can be found here
(the page is under developement right now and there are only test products included but for testing purposes it should be okay)

thanks for help!

okay found the problem - often it just helps to write it down and ask in the forums and then give the answer to it myself :D
if anyone is interested in the solution let me know

It is always helpful if you post the solution to any problem, even if you do work it out for yourself, so that others can be helped by your discoveries ... :smile:

yeah - that`s what i want to do but ran into antother problem :frusty:

i want to select a value "class_of_absorbtion" which can be in three different tables (for each product). This value can be in one, two or three or neither of the tables. I have and sql statement like this:

WHERE (p.products_status = 1 AND p.products_id = pd.products_id AND pd.language_id = 1 AND p.products_id = p2c.products_id AND p.products_id = pu.products_id AND p.products_id = pac.products_id AND p.products_id = puc.products_id AND p.products_id = pic.products_id AND p2c.categories_id = c.categories_id AND (pac.class_of_absorbtion OR puc.class_of_absorbtion_second OR pic.class_of_absorbtion_third) ='B'

so there are three tables of class_of_absorbtion. Im confused about the OR and AND statement - my code doesn`t work so maybe i have again something wrong in my php or even in my logic.
If anyone could have a quick look at this would be very nice!
thank you!

no one helping? what a bummer....:cry:

Hi!
I`m really stuck over here so if anyone could help me that would be sooooooooo nice...:clap:
I'm trying to explain my problem again:

I have 3 Tables called:
products_acoustics
products_acoustics_second
products_acoustics_third

each table contains a field products_id and a field for the acoustics information which can be values from A to E.
the three fields for the acoustics information are called
class_of_absorbtion (for table products_acoustics)
class_of_absorbtion_second (for table products_acoustics_second)
class_of_absorbtion_third (for table products_acoustics_third)

so i think i need to "left join" these tables together so i can get the coresponding product with just one "where" clause, right?

I tried the following (from clause)

$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_PRODUCTS_EXTRA_STUFF. " pu

         ON pu.products_id= p2c.products_id

LEFT JOIN " . TABLE_PRODUCTS_ACOUSTICS. " pac
ON pac.products_id = p2c.products_id
LEFT JOIN " . TABLE_PRODUCTS_ACOUSTICS_SECOND. " puc
ON pac.products_id = puc.products_id
LEFT JOIN " . TABLE_PRODUCTS_ACOUSTICS_THIRD. " pic
ON pac.products_id = pic.products_id

         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');

and here the where clause

if (isset($_GET['class_of_absorbtion']) && zen_not_null($_GET['class_of_absorbtion'])) {

$where_str .= " AND (pac.class_of_absorbtion

OR puc.class_of_absorbtion_second
OR pic.class_of_absorbtion_third) = :absoID";
$where_str = $db->bindVars($where_str, ':absoID', $_GET['class_of_absorbtion'], 'string');

}

I know i must have gotten something wrong, because still only the first table "class_of_absorbtion" and the field "class_of_absorbtion" is searched. If anyone could help me a little on this, would be sooo nice. Hope anyone does. Thank you!

well now i solved it - was just a tiny little thing, but if no one helps these things are sometimes hard to find :blink:

here`s the code for the correct where clause

 $where_str .= " AND (pac.class_of_absorbtion=:absoID
 OR puc.class_of_absorbtion_second=:absoID
OR pic.class_of_absorbtion_third=:absoID)";

Thanks for posting the update to the coding syntax error ... :smile:

Hi Ajeh and Zvenson,

I'm having some similar problem with advanced search.

I've added a field called "products_author" in "products" table, and now I'm trying to edit the advanced search adding the research also in this new field.

Until now I have no result, I've looked for tutorials or hints on the web, but I can't find... can u give me some help, please?

Thank u very much,
Marco

sure - what did you do til now?
The main two files to edit are theses two
/include/modules/pages/advanced_search_results/header_php.php
/include/modules/pages/advanced_search/header_php.php

and of course the template for the search site...

i think in this thread there is all the information you need, but not nicely structured - so if you post your changes to the 2 files above i`ll have a look at it!

Hi Zvenson,

Thanks for the quick answer.

Actually I'm stucked, I understand that the answer I was looking for is in this thread but I can't extract it (as you said, you write during the process, so is not linear). Consider also that usually I work with xhtml, css and wordpress, so I'm not good in php.

Your case is different from mine because you have to search in new tables with new fields, while in my case I've to search in a new field (products_author) of an existing table (products).

It's not yet useful sending u my code because after some wrong attempts I bring back the code to the original.

If you have the time and the patience to show me how to procede, I'll be very grateful.

I can't extract it (as you said, you write during the process, so is not linear). - sorry for that - but unfortunately this forum only allows edits 7 minutes after posting :) - and it`s not a tutorial ...

while in my case I've to search in a new field (products_author) of an existing table (products).

Is this just a simple text input field where you type in some name and it looks this up in the database?

First you have to add a field to the advanced search form. Edit:

/includes/templates/YOUR-TEMPLATE/templates/tpl_advanced_search_default.php file

and add something like this:

  <div class="centeredContent"><?php echo zen_draw_input_field('YOUR_NEW_FIELD', $sData['your_new_field'], 'onfocus="RemoveFormatString(this, \'' . KEYWORD_FORMAT_STRING . '\')"'); ?>

where you want the new field to appear in the search form.

Then edit the
/include/modules/pages/advanced_search/header_php.php

to "GET" the data from the field by adding something like this:

  $your_new_field =      (isset($_GET['your_new_field'])   ? zen_output_string($_GET['your_new_field']) : '');

now you just (okay, this is the tricky part) need to tell zen-cart to search the new data when something is submitted:
edit:
/include/modules/pages/advanced_search_results/header_php.php

(look for similar looking lines to paste this somewhere in between)
(isset($_GET['your_new_field']) && !is_string($_GET['your_new_field'])) &&

(same thing here: look for similar lines and just paste:
  if (isset($_GET['your_new_field'])) {
    $your_new_field = $_GET['your_new_field'];
  }

(now search for "if (empty($dfrom)" and add somewhere between the &&empty something like this:)
&& empty($your_new_field)

(search for similar and past:)
case 'YOUR_NEW_FIELD':
    $select_column_list .= 'p.your_fieldname_in_db';
    break;

(search for similar lines and paste:)
if (isset($_GET['your_new_field']) && zen_not_null($_GET['your_new_field'])) {
  $where_str .= " AND p.your_new_fielname_inDB = :yourfieldnameID";
  $where_str = $db->bindVars($where_str, ':yourfieldnameID', $_GET['your_field_name'], 'string');
}

of course you have to change your_field_name to the variable you want to search and change your_new_fildname_inDB as well.

well this should be it - but i`m totally not sure - im the trial and error kind as well :) just try it and tell me when you succeed - hope it helps somehow...

Thanks for the detailed answer!
I've done the process but it doesn't work yet...

/tpl_advanced_search_default.php file

1. Ok, I've added:

<div class="centeredContent"><?php echo zen_draw_input_field('PRODUCTS_AUTHOR', $sData['products_author'], 'onfocus="RemoveFormatString(this, \'' . KEYWORD_FORMAT_STRING . '\')"'); ?>

/include/modules/pages/advanced_search/header_php.php

2. Ok, I've added:

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

/include/modules/pages/advanced_search_results/header_php.php

3. added:

(isset($_GET['products_author']) && !is_string($_GET['products_author'])) &&

4. added

if (isset($_GET['products_author'])) {
    $your_new_field = $_GET['products_author'];
  }

5. added

&& empty($products_author)

6. added

case 'PRODUCT_LIST_AUTHOR':
    $select_column_list .= 'p.products_author';
    break;

7. added, but I'm not sure about the ID part...

if (isset($_GET['products_author']) && zen_not_null($_GET['products_author'])) {
  $where_str .= " AND p.products_author = :productsauthorID";
  $where_str = $db->bindVars($where_str, ':productsauthorID', $_GET['products_author'], 'string');
}

Where I'm wrong?

would be nice if you would tell us what happens when you start the search. This would make things much easier - maybe you can .provide a link

Actually I'm testing locally, I haven't put it on the web yet. As soon as I put it I'll tell you.

Thanks,
Marco

well - you can tell us what the error message is - or what happens when you search - "it doesnt work" is in most cases not helpful at all :huh: - but its not aleways necessary to upload the site to the web...

:)

Sorry Zvenson,

you are right. That's the situation in advanced search:

• if I leave the search field "keywords" blank and i fill only the new search field with an author name (it is supposed to search in my new field "author"), zen cart tell me "You must fill at least one field of research"

• if i fill "keywords" with a word and the new field with an author name, zencart gives me back only the products that match the first criteria

So, Zenky seems to ignore my second search field...

Hi Marco!
Well, my first thought is, that there must be something wrong in this line where you put this:
&& empty($products_author)
this line should check if the author (or any other field) is empty and if so - you get the error message that nothing has been entered. Well - the reason for this could be, that the "$products_author" variable is empty - try inserting an

echo $products_author;

above the line where all the "&& emptys" are - then enter a search word in the products author field - do a search and look if the $produts_author is "echoed" (printed) somewhere on your screen. if it is not - then there must be something wrong bevor the actuall search is done...
Hope this again helps a little!

I'm now following this thread for my own reasons! I have added a new table of product details (e) instead of adding to the product table. All of possible search fields are boolean - either 1 or 0 which is served by checkboxes on the search form.

So looking at your code suggestion above,

1. (isset($_GET['dwarf']) && !is_numeric($_GET['dwarf'])) &&
2. && empty($dwarf)
3. case 'dwarf':
   $select_column_list .= 'e.dwarf';
4. if (isset($_GET['dwarf']) && zen_not_null($_GET['dwarf'])) {
   $where_str .= " AND e.dwarf = :dwarf";
   $where_str = $db->bindVars($where_str, ':dwarf', $_GET['dwarf'], 'string');
   }

this doesn't work - and I know the field isn't a string as in number 4.

When I say it doesn't work - the search page just refreshed itself and never goes to the results page.

I'm pretty sure I've got the table added correctly to the select statement and even if I didn't, I'm not getting that far!

What might be wrong?