Jump to content
Sign in to follow this  
WillemB

Search Enhancement by Paul Whiter... MS2 ???

Recommended Posts

I would love to see a 'not' option in the search module. Only 'and' and 'or' and parenthesis. I'm trying to hack the search file to include 'not'. As now, when i search for lion, results of stallion also mixes in my search result. So I want to do 'lion not stallion' to get only lions. This is the section I think the hack takes place in advance_search_result.php:

 

   for ($i=0, $n=sizeof($search_keywords); $i<$n; $i++ ) {
    switch ($search_keywords[$i]) {
      case '(':
      case ')':
      case 'and':
      case 'or':
        $where_str .= " " . $search_keywords[$i] . " ";
        break;
      case 'not':
        $where_str .= " " . $search_keywords[$i] . " ";
        break;
      default:
        $keyword = tep_db_prepare_input($search_keywords[$i]);
        $where_str .= "(pd.products_name like '%" . tep_db_input($keyword) . "%' or p.products_model like '%" . tep_db_input($keyword) . "%' or m.manufacturers_name like '%" . tep_db_input($keyword) . "%'";
        if (isset($HTTP_GET_VARS['search_in_description']) && ($HTTP_GET_VARS['search_in_description'] == '1')) $where_str .= " or pd.products_description like '%" . tep_db_input($keyword) . "%'";
        $where_str .= ")";
        break;
    }
  }
  $where_str .= ")";
}

 

If you notice, I copied the 'or' section and just replaced it with 'not'. This is the problem I get:

 

1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'and (pd.products_name like '%stallion%' or p.products_model lik

select count(distinct p.products_id) as total from products p left join manufacturers m using(manufacturers_id), products_description pd left join specials s on p.products_id = s.products_id, categories c, products_to_categories p2c 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 p2c.categories_id = c.categories_id and ((pd.products_name like '%lion%' or p.products_model like '%lion%' or m.manufacturers_name like '%lion%') and not and (pd.products_name like '%stallion%' or p.products_model like '%stallion%' or m.manufacturers_name like '%stallion%'))

 

Why does it automatically put an 'and' before not and after it. It doesn't do that with the 'or'. I know that in the keywords, spaces are converted to 'and', but these ands are out of place. Please help because its driving me nuts! Thanks in advance to all.

Share this post


Link to post
Share on other sites

Jacobr - did you find a solution for your problem, because I'm struggling with the same problem.......the contri if fine but still needs some modifications but my PHP is :'( .

greetings,

Chris

Share this post


Link to post
Share on other sites

Hello There,

 

I just installed the search enhancement contribution, it works well, but I am looking for adding a split page function for the admin area, since it will shortly fill long listings on one page. Can anybody help me solve this problem ?

 

Earlier versions already have this included:

 

KEYWORD SEARCH REPORT

by Aaron Hiatt

aaron@scaredrabbit.com

 

 

Thanks a lot in advance.

 

Best regards,

Share this post


Link to post
Share on other sites

Hi,

 

I have installed this contribution and I think it is great. The only thing that I would like to know is ...

 

Is there a way to have the advanced search return MORE than one aternate? I can enter more than one alternate in the admin but when I do a search it does not show but one of them. I think it should be a list so that if someone searched for "abc" I could say

 

Did you mean:

 

abd

adr

abe

 

Because for instance I have several items on my store that have ALMOST the same part number (all but the end) so I need to be able to show them all of them.

 

Please let me know if this is possible.

 

Also, another thing I would like to do which would be a bonus if I get an answer here is to be able to make the quick search ALWAYS search descriptions. How can I do this?

 

Thanks so much!

 

Craig

Share this post


Link to post
Share on other sites

Maybe this can help a few people clear things up.

 

The problem described in above posts:

Entering two search words, one being mispelled, and having a keyword replacement already setup in admin for that mispelled keyword. This returns the two words with the replacement for the mispelled word in the 'you could also try" suggesiton. Clicking on that link returns nothing because of the + sign.

 

In catalog/advanced_search_result.php:

Find: the 2 instances of "urlencode" and replace it with "rawurlencode"

This fixed my problem.

 

About the stripping of the 's':

I removed mine, some of my items end in an s, and anyways, it was screwing up my searching by not actually searching for the string....it was weird.

 

In catalog/advanced_search_result.php:

Find: if($pwstr_check == 's'){ and remove the s so it looks like if($pwstr_check == ''){

 

Since I was having strange results where searching for body and clicking on the 'you could also try' link for body would return a completely different results set.

So in catalog/advanced_search_result.php:

I found &search_in_description=1 on the last line of the function (since there are a few instances of this) and replaced the number 1 with the number 0. This causes searching in product descriptions to become false and will now return the correct results.

 

Adding the ability to have more than 1 alternative keyword would be awesome

Adding ability to remove only some keywords in admin would be nice too, instead of removing all.

Having a search filter as described here would be a great contrib, Anyone know of one???

 

 

I have my own problem as well, Which would help if someone knowledgable would look into this or explain it.

I can't search for items using quotes. i.e. searching for "tires" returns no results and the keyword that appears in the searchbox is a forwad slash /.

 

Hope all this helped,

 

Chris

 

If you kept the stripping of the s enabled (default) find: search_in_description=1&s=1&keywords= about the 6th line down from the top in the same function and change the number 1 to a number 0 after search_in_description= so it looks like search_in_description=0&s=1&keywords=. This will fix any wierd search results if you kept the s stripping. Which I think i may enable now... :D

 

Any questions, just pm me, incase this wasnt very properly explained....

 

Chris

Share this post


Link to post
Share on other sites

One more problem I found

 

When I have an item in my alternate keyword list:

Say I have tier as the failed search word and tire as the suggested search word.

 

If I search my site for tiers (with an s, i have s stripping enabled), i get no search results and the suggestion is blank.

 

Also, when the s is stripped, the search phrase is no more the value of keywords. This leaves my search box empty, where it would usually have the previously searched keyword shown as its value.

 

Any help or mods would be appreciated.

 

Chris

Share this post


Link to post
Share on other sites

Basically, the s stripping function needs some fine tuning, I tried to add some variables and getting it to work, but I was unsuccessful in making it work right.

 

Maybe the contributer can fix this :D

 

Chris

Share this post


Link to post
Share on other sites

I fixed it, I don't know if it happens to everyone, but there is a closing table in /catalog/advance_search_result.php around line 350 before the mod is added and around line 410 after the mod is added that has to be removed.

Share this post


Link to post
Share on other sites

I just posted a small update to this excellent contrib if anyone is interested.

 

- Update to fix bug where if you had one term in admin keywords and it's exact opposite listed it would give you the same search term back. For example if I had dog = cat and then cat = dog in my backend and I search for dog I would get dog again. Now this has been fixed.

- Update to modify the text displayed after a search and to add back the 's' for the customer to see if it was taken off for the search. (cosmetic only, it still searches for terms without the 's')

 

-----------------------------------------------------------------

TO DOS

-----------------------------------------------------------------

- It would be great if someone could post a modification so more than one search suggestion is displayed if there are more available.

- Another neat feature would be if it would display categories that matched the search terms.


Most Valuable OsCommerce Contributions:

Also Purchased (AP) Preselection (cuts this resource hogging query down to nothing) -- Contribution 3294

FedEx Automated Labels -- Contribution 2244

RMA Returns system -- Contribution 1136

Sort Products By Dropdown -- Contribution 4312

Ultimate SEO URLs -- Contribution 2823

Credit Class & Gift Voucher -- Contribution 282

Cross-Sell -- Contribution 5347

Share this post


Link to post
Share on other sites

Good job on that. I have been planning on creating an update (i have it working on my site, but haven't got it in such a way i can upload as a contrib) that did that with the s. Not the major feature of my addition though. That will be a google type spelling suggestion. Actually done by google.

 

If no results were found, it connects to google via a soap connection and asks for spelling suggestions for the searched word and returns a link to search for the replacement if there is one. Unfortunately before I changed it all I decided I wasn't going to need the admin part of it since it was automatic. I was wrong. There are still instances where I need to set up my own suggestions. So as soon as I get some time to put that back in and update it so it can be uploaded as a contrib, I will.

 

I

 

Also, your todo list doesn't seem to be too difficult, so if I get some time I will try to do those as well. Problem being... Time. I have none that I don't spend actually getting paid or recreationally. Hopefully soon tho....

Share this post


Link to post
Share on other sites

Unfortunately, after more testing I think my fix for the duplicate keywords does not work quite right. What I did was change $pw_keywords[$i] to $pw_replacement_words[$i] in two places.

 

The example I gave was in admin dog = cat and cat = dog the previous error is corrected and it works fine for single keywords but now multi word phrases are not working so if you enter 'walk the dog' it will list the suggestion as 'walk the cat' but will only search for 'cat' now!

 

I'm going to be here another hour or so and I'll see if I can get anything figured out. Or if anyone else has any ideas please post them.


Most Valuable OsCommerce Contributions:

Also Purchased (AP) Preselection (cuts this resource hogging query down to nothing) -- Contribution 3294

FedEx Automated Labels -- Contribution 2244

RMA Returns system -- Contribution 1136

Sort Products By Dropdown -- Contribution 4312

Ultimate SEO URLs -- Contribution 2823

Credit Class & Gift Voucher -- Contribution 282

Cross-Sell -- Contribution 5347

Share this post


Link to post
Share on other sites

Ok I think I have that bug fixed now and one more instance of the 's' needing added fixed too. I'll post that later tonight.

 

Also I've been working on this for a couple of hours and I think I have a list categories in the search listings ready to go, it is working on my site but I have such a highly modified site it is very hard for me to test this for others. So if someone would like to test it out and let me know how it works in another oscommerce install I would appreciate it.

 

LIST CATEGORIES IN SEARCH LISTINGS (beta)

 

STEP 1: advanced_search_results.php

 

FIND:

  if (isset($search_keywords) && (sizeof($search_keywords) > 0)) {
$where_str .= " and (";
for ($i=0, $n=sizeof($search_keywords); $i<$n; $i++ ) {
  switch ($search_keywords[$i]) {
	case '(':
	case ')':
	case 'and':
	case 'or':
	  $where_str .= " " . $search_keywords[$i] . " ";
	  break;
	default:
	  $keyword = tep_db_prepare_input($search_keywords[$i]);
	  $where_str .= "(pd.products_name like '%" . tep_db_input($keyword) . "%' or p.products_model like '%" . tep_db_input($keyword) . "%' or m.manufacturers_name like '%" . tep_db_input($keyword) . "%'";
	  if (isset($HTTP_GET_VARS['search_in_description']) && ($HTTP_GET_VARS['search_in_description'] == '1')) $where_str .= " or pd.products_description like '%" . tep_db_input($keyword) . "%'";
	  $where_str .= ')';
	  break;
  }
}
$where_str .= " )";
 }

 

CHANGE TO:

// Modified to search categories also
 if (isset($search_keywords) && (sizeof($search_keywords) > 0)) {
$where_str .= " and (";
for ($i=0, $n=sizeof($search_keywords); $i<$n; $i++ ) {
  switch ($search_keywords[$i]) {
	case '(':
	case ')':
	case 'and':
	case 'or':
	  $where_str .= " " . $search_keywords[$i] . " ";
	  $where_cat .= " " . $search_keywords[$i] . " ";
	  break;
	default:
	  $keyword = tep_db_prepare_input($search_keywords[$i]);
	  $where_str .= "(pd.products_name like '%" . tep_db_input($keyword) . "%' or p.products_model like '%" . tep_db_input($keyword) . "%' or m.manufacturers_name like '%" . tep_db_input($keyword) . "%'";
	  if (isset($HTTP_GET_VARS['search_in_description']) && ($HTTP_GET_VARS['search_in_description'] == '1')) $where_str .= " or pd.products_description like '%" . tep_db_input($keyword) . "%'";
	  $where_str .= ')';
	  $where_cat .= "(categories_name like '%" . tep_db_input($keyword) . "%')";
	  break;
  }
}
$where_str .= " )";
 }

 

 

FIND:

 require(DIR_WS_MODULES . FILENAME_PRODUCT_LISTING_ALL);

 

CHANGE TO:

  $category_sql = "select categories_id, categories_name from " . TABLE_CATEGORIES_DESCRIPTION . " where " . $where_cat . " order by categories_name";
 require(DIR_WS_MODULES . 'product_listing_search.php');

 

 

STEP 2

 

COPY: includes/modules/product_listing.php

TO: includes/modules/product_listing_search.php

 

includes/modules/product_listing_search.php

FIND:

  $listing_split = new splitPageResults($listing_sql, MAX_DISPLAY_SEARCH_RESULTS, 'p.products_id');

 

RIGHT AFTER ADD:

// BEGIN List Categories matching search

$category = tep_db_query($category_sql);
$num_cats = mysql_num_rows($category);
if (($num_cats > 0)) {

?>
	  <tr>
		<td colspan="3" class="prodListHeader"><b>Matching Categories</b></td>
	  </td>
	  <tr>
		<td colspan="3" class="main"><?php echo tep_draw_separator('pixel_trans.gif', '100%', '8'); ?></td>
	  </tr>
	  <tr>
		  <td colspan="3">
		  <table cellspacing=1 cellpadding=0 border=0 width="100%">
		  <tr>
<?php
$cat_count = 0;
while ($category_values = tep_db_fetch_array($category)) {
	if ($cat_count > 2) {
?>
	  </tr>
	  <tr>
		<td class="main" width="33%"><?php echo '[ <a href="' . $category_values['categories_name'] .'-c-' . $category_values['categories_id'] . '.html"><u>' . $category_values['categories_name'] . '</u></a> ]'; ?></td>
<?php
	$cat_count = 1;
	} else {
?>
		<td class="main" width="33%"><?php echo '[ <a href="' . $category_values['categories_name'] .'-c-' . $category_values['categories_id'] . '.html"><u>' . $category_values['categories_name'] . '</u></a> ]'; ?></td>
<?php
	$cat_count++;
	}
}
?>
	  </tr>
	  </table>
	  </td>
	  </tr>
	  <tr>
		<td colspan="3" class="main"><?php echo tep_draw_separator('pixel_trans.gif', '100%', '8'); ?></td>
	  </tr>
<?php
}

 

 

THAT'S IT! Now just report back how it works on your oscommerce install!


Most Valuable OsCommerce Contributions:

Also Purchased (AP) Preselection (cuts this resource hogging query down to nothing) -- Contribution 3294

FedEx Automated Labels -- Contribution 2244

RMA Returns system -- Contribution 1136

Sort Products By Dropdown -- Contribution 4312

Ultimate SEO URLs -- Contribution 2823

Credit Class & Gift Voucher -- Contribution 282

Cross-Sell -- Contribution 5347

Share this post


Link to post
Share on other sites

Actually that last step should be slightly different for regular oscommerce sites, like I said mine is quite different, this should work:

 

FIND:

  $listing_split = new splitPageResults($listing_sql, MAX_DISPLAY_SEARCH_RESULTS, 'p.products_id');

 

RIGHT AFTER ADD:

// BEGIN List Categories matching search

$category = tep_db_query($category_sql);
$num_cats = mysql_num_rows($category);
if (($num_cats > 0)) {

?>
	  <tr>
		<td colspan="3" class="prodListHeader"><b>Matching Categories</b></td>
	  </td>
	  <tr>
		<td colspan="3" class="main"><?php echo tep_draw_separator('pixel_trans.gif', '100%', '8'); ?></td>
	  </tr>
	  <tr>
		  <td colspan="3">
		  <table cellspacing=1 cellpadding=0 border=0 width="100%">
		  <tr>
<?php
$cat_count = 0;
while ($category_values = tep_db_fetch_array($category)) {
	if ($cat_count > 2) {
?>
	  </tr>
	  <tr>
		<td class="main" width="33%"><?php echo 'index.php?cPath=' . $category_values['categories_id'] . '"><u>' . $category_values['categories_name'] . '</u></a>'; ?></td>
<?php
	$cat_count = 1;
	} else {
?>
		<td class="main" width="33%"><?php echo 'index.php?cPath=' . $category_values['categories_id'] . '"><u>' . $category_values['categories_name'] . '</u></a>'; ?></td>
<?php
	$cat_count++;
	}
}
?>
	  </tr>
	  </table>
	  </td>
	  </tr>
	  <tr>
		<td colspan="3" class="main"><?php echo tep_draw_separator('pixel_trans.gif', '100%', '8'); ?></td>
	  </tr>
<?php
}

 

 

THAT'S IT! Now just report back how it works on your oscommerce install!


Most Valuable OsCommerce Contributions:

Also Purchased (AP) Preselection (cuts this resource hogging query down to nothing) -- Contribution 3294

FedEx Automated Labels -- Contribution 2244

RMA Returns system -- Contribution 1136

Sort Products By Dropdown -- Contribution 4312

Ultimate SEO URLs -- Contribution 2823

Credit Class & Gift Voucher -- Contribution 282

Cross-Sell -- Contribution 5347

Share this post


Link to post
Share on other sites

Hey homewetbar,

 

I know what you mean by having a highly modified site. That's why I haven't gotten my update done yet. But what I do is obviously have a test server on my computer on which i do the coding. And there I have 2 installations of oscommerce. One that mirrors my running site, and one that I keep as a fresh install to test stuff out. Just an idea.

Share this post


Link to post
Share on other sites

I changed this part for the category links from your "regular oscommerce sites" post above:

<td class="main" width="33%"><?php echo 'index.php?cPath=' . $category_values['categories_id'] . '"><u>' . $category_values['categories_name'] . '</u></a>'; ?></td>
<?php
	$cat_count = 1;
	} else {
?>
		<td class="main" width="33%"><?php echo 'index.php?cPath=' . $category_values['categories_id'] . '"><u>' . $category_values['categories_name'] . '</u></a>'; ?></td>

 

to:

<td class="main" width="33%"><?php echo '<a href="' . tep_href_link(FILENAME_DEFAULT, 'cPath=' . $category_values['categories_id']) . '"><u>' . $category_values['categories_name'] . '</u></a>'; ?></td>

		<?php
	$cat_count = 1;
	} else {
?>
		<td class="main" width="33%"><?php echo '<a href="' . tep_href_link(FILENAME_DEFAULT, 'cPath=' . $category_values['categories_id']) . '"><u>' . $category_values['categories_name'] . '</u></a>'; ?></td>

 

May help someone else :)

Share this post


Link to post
Share on other sites
I changed this part for the category links from your "regular oscommerce sites" post above:

<td class="main" width="33%"><?php echo 'index.php?cPath=' . $category_values['categories_id'] . '"><u>' . $category_values['categories_name'] . '</u></a>'; ?></td>
<?php
	$cat_count = 1;
	} else {
?>
		<td class="main" width="33%"><?php echo 'index.php?cPath=' . $category_values['categories_id'] . '"><u>' . $category_values['categories_name'] . '</u></a>'; ?></td>

 

to:

<td class="main" width="33%"><?php echo '<a href="' . tep_href_link(FILENAME_DEFAULT, 'cPath=' . $category_values['categories_id']) . '"><u>' . $category_values['categories_name'] . '</u></a>'; ?></td>

		<?php
	$cat_count = 1;
	} else {
?>
		<td class="main" width="33%"><?php echo '<a href="' . tep_href_link(FILENAME_DEFAULT, 'cPath=' . $category_values['categories_id']) . '"><u>' . $category_values['categories_name'] . '</u></a>'; ?></td>

 

May help someone else :)

 

 

Thanks were you able to get the categories to display in the sarch results using the rest of the code?


Most Valuable OsCommerce Contributions:

Also Purchased (AP) Preselection (cuts this resource hogging query down to nothing) -- Contribution 3294

FedEx Automated Labels -- Contribution 2244

RMA Returns system -- Contribution 1136

Sort Products By Dropdown -- Contribution 4312

Ultimate SEO URLs -- Contribution 2823

Credit Class & Gift Voucher -- Contribution 282

Cross-Sell -- Contribution 5347

Share this post


Link to post
Share on other sites
Thanks were you able to get the categories to display in the sarch results using the rest of the code?

 

Yes, the categories display. I also made the following change to includes/modules/products_listing_search.php because if there were no categories to display the table layout messed up:

 

Find

 

// BEGIN List Categories matching search

$category = tep_db_query($category_sql);
$num_cats = mysql_num_rows($category);
if (($num_cats > 0)) {
?>
	  <tr>
		<td colspan="3" class="main"><b>Matching Categories</b></td>
	  </td>
	  <tr>
		<td colspan="3" class="main"><?php echo tep_draw_separator('pixel_trans.gif', '100%', '8'); ?></td>
	  </tr>
	  <tr>
		  <td colspan="3">
		  <table cellspacing=1 cellpadding=0 border=0 width="100%">
		  <tr>
<?php
$cat_count = 0;
while ($category_values = tep_db_fetch_array($category)) {
	if ($cat_count > 2) {
?>
	  </tr>
	  <tr>
		<td class="main" width="33%"><?php echo '<a href="' . tep_href_link(FILENAME_DEFAULT, 'cPath=' . $category_values['categories_id']) . '"><u>' . $category_values['categories_name'] . '</u></a>'; ?></td>

		<?php
	$cat_count = 1;
	} else {
?>
		<td class="main" width="33%"><?php echo '<a href="' . tep_href_link(FILENAME_DEFAULT, 'cPath=' . $category_values['categories_id']) . '"><u>' . $category_values['categories_name'] . '</u></a>'; ?></td>
<?php
	$cat_count++;
	}
}
?>
	  </tr>
	  </table>
	  </td>
	  </tr>
	  <tr>
		<td colspan="3" class="main"><?php echo tep_draw_separator('pixel_trans.gif', '100%', '8'); ?></td>
	  </tr>

 

ADD AFTER:

 

	<?php
}
else if (($num_cats == 0)) {
?>
	  <tr>
		<td colspan="3" class="main"><?php echo tep_draw_separator('pixel_trans.gif', '100%', '8'); ?></td>
	  </tr>

 

FIND NEXT

  if ( ($listing_split->number_of_rows > 0) && ( (PREV_NEXT_BAR_LOCATION == '1') || (PREV_NEXT_BAR_LOCATION == '3') ) ) {

 

ADD BEFORE

} // closed if $num_cats == 0 if statement

 

Testing site is here

 

I have also 1) disabled the "s" feature as this did not work even with the fix posted here and 2) removed the extra "</table>" as mentioned earlier in the thread (from advanced_search_results.php). Other than that, I am still working on it :rolleyes:

Share this post


Link to post
Share on other sites

I'm using this contribution and it works fine on my site.

 

Unfortunately the list of results is getting longer and longer...

Is there a way to show only the last 200 results? Or may be the best is to limit the records in the DB to 200, automatically deleting the oldest?

 

Thank you in advance for your help!!

Share this post


Link to post
Share on other sites
I'm using this contribution and it works fine on my site.

 

Unfortunately the list of results is getting longer and longer...

Is there a way to show only the last 200 results? Or may be the best is to limit the records in the DB to 200, automatically deleting the oldest?

 

Thank you in advance for your help!!

 

 

You could modify the search_results.php page and make it search only by titles so you get more accurate results or add some fields such as keyword1 keyword2 etc to your db via Myphp and then make the search_results.php seach those fields for keywords entered instead of the description. I'm not going to go into how to do this, its not hard but alot of steps, but if you have a little no how and will power you could do it....


Most Valuable OsCommerce Contributions:

Also Purchased (AP) Preselection (cuts this resource hogging query down to nothing) -- Contribution 3294

FedEx Automated Labels -- Contribution 2244

RMA Returns system -- Contribution 1136

Sort Products By Dropdown -- Contribution 4312

Ultimate SEO URLs -- Contribution 2823

Credit Class & Gift Voucher -- Contribution 282

Cross-Sell -- Contribution 5347

Share this post


Link to post
Share on other sites

Thank you for your reply homewetbar,

 

Well, someone already did something like this for me, and the results are quite accurate and very helpfull.

Yet with the time you got plenty of records, my keyword table is 5,1MB at the moment (because I don't wanted to use "delete all" button).

So I thought may be there is an easy way to show only the last 200 records.

 

Thank you any way

Best

Wes

Edited by SlowBrain

Share this post


Link to post
Share on other sites

Hi there Guys

 

would really like to get this category search code in place on my site...

 

any chance someone could roll it into an update for the contrib?

Share this post


Link to post
Share on other sites
Hi there Guys

 

would really like to get this category search code in place on my site...

 

any chance someone could roll it into an update for the contrib?

 

The code is all posted to do this line by line a couple of pages back. All you need to do is plug it into your web site, I need one more person to test the fixed code posted to make sure before I add it to the contrib. :thumbsup:


Most Valuable OsCommerce Contributions:

Also Purchased (AP) Preselection (cuts this resource hogging query down to nothing) -- Contribution 3294

FedEx Automated Labels -- Contribution 2244

RMA Returns system -- Contribution 1136

Sort Products By Dropdown -- Contribution 4312

Ultimate SEO URLs -- Contribution 2823

Credit Class & Gift Voucher -- Contribution 282

Cross-Sell -- Contribution 5347

Share this post


Link to post
Share on other sites

I Need A Little Help

 

I get this error:

1054 - Unknown column 'p2pef.manufacturers_id' in 'on clause'

 

select count(distinct p.products_id) as total from (products p left join products_to_products_extra_fields p2pef on p.products_id=p2pef.products_id) left join manufacturers m using(manufacturers_id) left join specials s on p.products_id = s.products_id, products_description pd, categories c, products_to_categories p2c 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 p2c.categories_id = c.categories_id and ((pd.products_name like '%test%' or p.products_model like '%test%' or m.manufacturers_name like '%test%' or p2pef.products_extra_fields_value like '%test%' or pd.products_description like '%test%') )

 

Here's my code:

  if (isset($search_keywords) && (sizeof($search_keywords) > 0)) {
$where_str .= " and (";
for ($i=0, $n=sizeof($search_keywords); $i<$n; $i++ ) {
  switch ($search_keywords[$i]) {
	case '(':
	case ')':
	case 'and':
	case 'or':
	  $where_str .= " " . $search_keywords[$i] . " ";
	  break;
	default:
	  $keyword = tep_db_prepare_input($search_keywords[$i]);

// START: Extra Fields Contribution
/*		  $where_str .= "(pd.products_name like '%" . tep_db_input($keyword) . "%' or p.products_model 
like '%" . tep_db_input($keyword) . "%' or m.manufacturers_name like '%" . tep_db_input($keyword) . "%'"; */
	  $where_str .= "(pd.products_name like '%" . tep_db_input($keyword) . "%' or p.products_model like '%" . tep_db_input($keyword) . "%' or m.manufacturers_name like '%" . tep_db_input($keyword) . "%' or p2pef.products_extra_fields_value like '%" . tep_db_input($keyword) . "%'";
// END: Extra Fields Contribution

	  if (isset($HTTP_GET_VARS['search_in_description']) && ($HTTP_GET_VARS['search_in_description'] == '1')) $where_str .= " or pd.products_description like '%" . tep_db_input($keyword) . "%'";
	  $where_str .= ')';
	  break;
  }
}
$where_str .= " )";
 }

 

Server is running: MySQL 4.1.18 and osCommerce version 2.2-MS2

 

Can someone help me solve this problem?

Thanks!

Share this post


Link to post
Share on other sites

Ignore the top code ... it's not the problem...

 

This is the search query

 $from_str = "from (" . TABLE_PRODUCTS . " p left join " . TABLE_PRODUCTS_TO_PRODUCTS_EXTRA_FIELDS . " p2pef on p.products_id=p2pef.products_id) left join " . TABLE_MANUFACTURERS . " m using(manufacturers_id) left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id";

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×