Kryptronic Software Support Forum

webmaster · 09-25-2003 11:22:02

MYSQL ONLY!!!!

Assuming that you are using MySQL (because the SQL::Statement module used to execute on CSVs cannot handle any more options than what the base search is currently doing), follow these steps:

First, add your search fields to the search form under HTML Pages & Elements | Manage Site Elements. In the element named 'Product Search Form', right below:

Code:


<TR BGCOLOR="(CGIVAR)html_alt_tablerow_color(/CGIVAR)">

<TD VALIGN="TOP" COLSPAN="2"><FONT FACE="(CGIVAR)html_small_font_face(/CGIVAR)" SIZE="(CGIVAR)html_small_font_size(/CGIVAR)" COLOR="(CGIVAR)html_small_font_color(/CGIVAR)"><B>Results Per Page <FONT COLOR="(CGIVAR)html_notnull_font_color(/CGIVAR)"><B>(CGIVAR)html_notnull_character(/CGIVAR)</B></FONT></B><BR><BR></FONT>

<SELECT NAME="searchrpp">
<OPTION VALUE="5" SELECTED>5</OPTION>
<OPTION VALUE="10">10</OPTION>
<OPTION VALUE="15">15</OPTION>
<OPTION VALUE="20">20</OPTION>
<OPTION VALUE="25">25</OPTION>
<OPTION VALUE="30">30</OPTION>
</SELECT>

</TD>

</TR>

Add:

Code:


<TR BGCOLOR="(CGIVAR)html_pri_tablerow_color(/CGIVAR)">

<TD VALIGN="TOP"><FONT FACE="(CGIVAR)html_small_font_face(/CGIVAR)" SIZE="(CGIVAR)html_small_font_size(/CGIVAR)" COLOR="(CGIVAR)html_small_font_color(/CGIVAR)"><B>Minimum Price</B><BR><BR></FONT>

<SELECT NAME="minprice">
<OPTION VALUE="" SELECTED>No Minimum</OPTION>
<OPTION VALUE="100">(CGIVAR)currency_symbol(/CGIVAR)100.00</OPTION>
<OPTION VALUE="200">(CGIVAR)currency_symbol(/CGIVAR)200.00</OPTION>
<OPTION VALUE="300">(CGIVAR)currency_symbol(/CGIVAR)300.00</OPTION>
</SELECT>

</TD>

<TD VALIGN="TOP" COLSPAN="2"><FONT FACE="(CGIVAR)html_small_font_face(/CGIVAR)" SIZE="(CGIVAR)html_small_font_size(/CGIVAR)" COLOR="(CGIVAR)html_small_font_color(/CGIVAR)"><B>Maximum Price</B><BR><BR></FONT>

<SELECT NAME="maxprice">
<OPTION VALUE="" SELECTED>No Maximum</OPTION>
<OPTION VALUE="1000">(CGIVAR)currency_symbol(/CGIVAR)1000.00</OPTION>
<OPTION VALUE="2000">(CGIVAR)currency_symbol(/CGIVAR)2000.00</OPTION>
<OPTION VALUE="3000">(CGIVAR)currency_symbol(/CGIVAR)3000.00</OPTION>
</SELECT>

</TD>

</TR>

Next, open up the file ./cgi-bin/library/modules/ste_prod.pl for editing. In the routine 'ste_prod_disp' look for:

Code:


#########
######### Figure out what we're using for the results per page
######### variable and other search variables.
#########

And right below it, add:

Code:


$fd_minprice = $q->param('minprice');
$fd_maxprice = $q->param('maxprice');

Then replace the line:

Code:


$sql_statement = ste_prod_searchsql_proc('SELECT COUNT(*) FROM product','',$fd_searchstr,$searchopr,$fd_searchtyp);

With:

Code:


$sql_statement = ste_prod_searchsql_proc('SELECT COUNT(*) FROM product','',$fd_searchstr,$searchopr,$fd_searchtyp,$fd_minprice,$fd_maxprice);

Then replace the line:

Code:


print <<ENDOFTEXT;
<A HREF=\"$common_url&pg=$fd_pg&startrow=$prev_startrow&searchstr=$fd_searchstr_encoded&searchrpp=$store_products_to_display_per_page&searchtyp=$fd_searchtyp_encoded&ref=$fd_ref_encoded&cat=$fd_cat_encoded\">$html_previous_string</A> \| 
ENDOFTEXT

With:

Code:


print <<ENDOFTEXT;
<A HREF=\"$common_url&pg=$fd_pg&startrow=$prev_startrow&searchstr=$fd_searchstr_encoded&searchrpp=$store_products_to_display_per_page&searchtyp=$fd_searchtyp_encoded&ref=$fd_ref_encoded&cat=$fd_cat_encoded&minprice=$fd_minprice&maxprice=$fd_maxprice\">$html_previous_string</A> \| 
ENDOFTEXT

And replace:

Code:


print <<ENDOFTEXT;
<A HREF=\"$common_url&pg=$fd_pg&startrow=$next_startrow&searchstr=$fd_searchstr_encoded&searchrpp=$store_products_to_display_per_page&searchtyp=$fd_searchtyp_encoded&ref=$fd_ref_encoded&cat=$fd_cat_encoded\">$html_next_string</A>
ENDOFTEXT

With:

Code:


print <<ENDOFTEXT;
<A HREF=\"$common_url&pg=$fd_pg&startrow=$next_startrow&searchstr=$fd_searchstr_encoded&searchrpp=$store_products_to_display_per_page&searchtyp=$fd_searchtyp_encoded&ref=$fd_ref_encoded&cat=$fd_cat_encoded&minprice=$fd_minprice&maxprice=$fd_maxprice\">$html_next_string</A>
ENDOFTEXT

Then, edit the routine 'ste_prod_searchsql_proc'. Replace:

Code:


my ($sel,$lim,$str,$opr,$typ) = @_;

my $sql_statement = "";

With:

Code:


my ($sel,$lim,$str,$opr,$typ,$min,$max) = @_;

my $sql_statement = "(";

Then right above:

Code:


$sql_statement = "

$sel
$where
$lim

";

Add:

Code:


$where .= ")";

if ($min ne "") {

$min_quoted = database_quote('product',$min);
$psr_quoted = database_quote('product','R');
$pss_quoted = database_quote('product','R');

$where .= " AND ((product_regprice>$min_quoted AND product_pricestatus=$psr_quoted) OR (product_saleprice>$min_quoted AND product_pricestatus=$pss_quoted) OR (product_pricestatus<>$psr_quoted AND product_pricestatus<>$pss_quoted))";

} ######### End of if statement.

if ($max ne "") {

$max_quoted = database_quote('product',$max);
$psr_quoted = database_quote('product','R');
$pss_quoted = database_quote('product','R');

$where .= " AND ((product_regprice<$max_quoted AND product_pricestatus=$psr_quoted) OR (product_saleprice<$max_quoted AND product_pricestatus=$pss_quoted) OR (product_pricestatus<>$psr_quoted AND product_pricestatus<>$pss_quoted))";

} ######### End of if statement.

In this last codeblock please note that if you are using regular or sale prices for products, this will work correctly. If you are using Volume Pricing, Option Based Pricing, on Not For Sale Pricing for any product, it will pull that product regardless of the price.

mikeduma · 04-12-2004 16:58:11

Hi

Just done this and now my search doesnt work at all!!

Site

Where have i gone wrong?

Mike

webmaster · 04-17-2004 13:30:52

I just tested and the search seems to be working correctly.

Synthetic · 04-27-2004 18:08:30

I tried installing the mod but it didn't work, so when attempting to do it again for the second time I noticed that I couldn't find this line anywhere in ste_prod.pl:

print <<ENDOFTEXT;
<A HREF=\"$common_url&pg=$fd_pg&startrow=$next_startrow&searchstr=$fd_searchstr_encoded&searchrpp=$store_products_to_display_per_page&searchtyp=$fd_searchtyp_encoded&ref=$fd_ref_encoded&cat=$fd_cat_encoded\">$html_next_string</A>
ENDOFTEXT

webmaster · 05-02-2004 12:26:49

Look for a similar line at the bottom of the first routine in ste_prod.pl. Since this original post, the script has been updated here and there and that line has been changed. It won't read exactly the same way as it did last September.

sbwebsmith · 06-14-2004 19:44:06

Greetings!

This is a great idea, but I am also having trouble making it work. I've tried to apply the modifications to my test store site (wwwturnpike.cc/ccp51) but have not succeeded. Any help or suggestions would be appreciated.

Ron Smith

webmaster · 06-15-2004 12:34:57

This error was in the source of your search results page. Check this out:

Code:


SUBROUTINE ste_prod_disp GENERATED MESSAGE: DBD::mysql::st execute failed: 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 ')' at line 2 at /home2/turnpike/public_html/ccp51/cgi-bin/library/common/database.pl line 621.



SELECT COUNT(*) FROM product
WHERE product_id LIKE '%%' OR product_name LIKE '%%' OR product_number LIKE '%%' OR product_keywords LIKE '%%' OR product_descshort LIKE '%%' OR product_desclong LIKE '%%')

It looks like there's an extra parenthesis in the SQL statement. I believe changing:

$sql_statement = "

$sel
$where
$lim

";

To:

$sql_statement .= "

$sel
$where
$lim

";

(Notice the .= instead of will correct. Please let me know. Thanks.

sbwebsmith · 06-15-2004 14:37:44

Greetings!

Thanks for getting back to me.

We are making progress, but we are not there. Before making the change, I could not get any results, even if I only entered a search term and left the min/max fields at "No Min" and "No Max".

After making the change, I can get search results when I enter a search term, but not if I also choose a min and/or max amount.

Thanks for the help. This is really important to my business.

Ron Smith

webmaster · 06-15-2004 16:03:36

Is this code intact:

Code:


$where .= ")";

if ($min ne "") {

$min_quoted = database_quote('product',$min);
$psr_quoted = database_quote('product','R');
$pss_quoted = database_quote('product','R');

$where .= " AND ((product_regprice>$min_quoted AND product_pricestatus=$psr_quoted) OR (product_saleprice>$min_quoted AND product_pricestatus=$pss_quoted) OR (product_pricestatus<>$psr_quoted AND product_pricestatus<>$pss_quoted))";

} ######### End of if statement.

if ($max ne "") {

$max_quoted = database_quote('product',$max);
$psr_quoted = database_quote('product','R');
$pss_quoted = database_quote('product','R');

$where .= " AND ((product_regprice<$max_quoted AND product_pricestatus=$psr_quoted) OR (product_saleprice<$max_quoted AND product_pricestatus=$pss_quoted) OR (product_pricestatus<>$psr_quoted AND product_pricestatus<>$pss_quoted))";

} ######### End of if statement.

?? Do you see any error messages embedded in HTML comments in your source for the search results page?

sbwebsmith · 06-15-2004 16:18:19

Hey Nick!

I appreciate your help here!

The code is indeed intact. I checked each character carefully, and then for good measure, cut and pasted it into the script. No difference.

As for error code, I am not being returned to the search results page. Instead I am simply being returned to the search page with whatever values I entered changed back to default values. If I do not enter a min and/or max value, but only a search term. I am returned to the search results page and results are listed.

Thanks again!

Ron Smith

TheThinker · 06-17-2004 00:34:07

From looking at the changes, I'm wondering if it is truley "MySQL Only" or if it will work on any non-CSV database? For example, will this mod work on PostgreSQL?

Regards,
Eric

sbwebsmith · 06-18-2004 12:35:46

sbwebsmith,06/15/2004 04:18:19 PM wrote:
Hey Nick!

I appreciate your help here!

The code is indeed intact. I checked each character carefully, and then for good measure, cut and pasted it into the script. No difference.

As for error code, I am not being returned to the search results page. Instead I am simply being returned to the search page with whatever values I entered changed back to default values. If I do not enter a min and/or max value, but only a search term. I am returned to the search results page and results are listed.

Thanks again!

Ron Smith

Hi Nick,

Any ideas on how I can get this thing working?

Thanks,

Ron

webmaster · 06-21-2004 13:42:36

This is truly 'MySQL Only' as the parser used for CSV SQL Statements (SQL::Statement perl module) is not capable of parsing SQL that complex (yet).

thedon122 · 11-27-2004 08:33:49

hi,

Has anyone got the above mod to work -

I put this on and it does not work??

thanks in advance
Charlie

Steven · 11-29-2004 03:33:39

I've been unsuccessful in getting this to work too.

Nick? .. I'm getting the same display error as above,

webmaster · 12-02-2004 12:07:48

What error are you getting - the MySQL syntax error?

Steven · 12-02-2004 23:00:00

Hi Nick, yes. Very much like this one..

SUBROUTINE ste_prod_disp GENERATED MESSAGE: DBD::mysql::st execute failed: 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 ')' at line 2 at /home/collarsa/public_html/ccp51/cgi-bin/library/common/database.pl line 621

thedon122 · 12-03-2004 07:28:38

hi nick,

I having problems with the above - however i am not getting any error messages - it just returns with normal result regardless of price - so if you put min £100 it would show all even products less than £100

any idea?

charlie

p.s. i am runing mysql

webmaster · 12-06-2004 10:50:38

This only works for regular and sale price items - are you using recurring or option based pricing?

thedon122 · 12-06-2004 11:17:22

hi, Nick

I am just using reg and sale prices only

cheers
Charlie

webmaster · 12-07-2004 09:11:58

Do a search then when you see no results displayed, view the source of the page and see if there is an error message in the HTML inside comment () tags. If there is, post it here.

thedon122 · 12-07-2004 19:05:30

hi NIck

heres the error message



regadrs
charlie

webmaster · 12-08-2004 09:48:32

Did you miss this code edit?:

Code:


my ($sel,$lim,$str,$opr,$typ,$min,$max) = @_;

my $sql_statement = "(";

thedon122 · 12-08-2004 10:05:28

Hi, Nick

yes i did that edit.

bump??????

webmaster · 12-08-2004 10:42:59

Please post the relevant portion of code that you have. Thank you.

Kryptronic Software Support Forum

#1 09-25-2003 11:22:02

Searching With Min/max Prices

Code:

Code:

Code:

Code:

Code:

Code:

Code:

Code:

Code:

Code:

Code:

Code:

Code:

Code:

#2 04-12-2004 16:58:11

Re: Searching With Min/max Prices

#3 04-17-2004 13:30:52

Re: Searching With Min/max Prices

#4 04-27-2004 18:08:30

Re: Searching With Min/max Prices

#5 05-02-2004 12:26:49

Re: Searching With Min/max Prices

#6 06-14-2004 19:44:06

Re: Searching With Min/max Prices

#7 06-15-2004 12:34:57

Re: Searching With Min/max Prices

Code:

#8 06-15-2004 14:37:44

Re: Searching With Min/max Prices

#9 06-15-2004 16:03:36

Re: Searching With Min/max Prices

Code:

#10 06-15-2004 16:18:19

Re: Searching With Min/max Prices

#11 06-17-2004 00:34:07

Re: Searching With Min/max Prices

#12 06-18-2004 12:35:46

Re: Searching With Min/max Prices

sbwebsmith,06/15/2004 04:18:19 PM wrote:

#13 06-21-2004 13:42:36

Re: Searching With Min/max Prices

#14 11-27-2004 08:33:49

Re: Searching With Min/max Prices

#15 11-29-2004 03:33:39

Re: Searching With Min/max Prices

#16 12-02-2004 12:07:48

Re: Searching With Min/max Prices

#17 12-02-2004 23:00:00

Re: Searching With Min/max Prices

#18 12-03-2004 07:28:38

Re: Searching With Min/max Prices

#19 12-06-2004 10:50:38

Re: Searching With Min/max Prices

#20 12-06-2004 11:17:22

Re: Searching With Min/max Prices

#21 12-07-2004 09:11:58

Re: Searching With Min/max Prices

#22 12-07-2004 19:05:30

Re: Searching With Min/max Prices

#23 12-08-2004 09:48:32

Re: Searching With Min/max Prices

Code:

#24 12-08-2004 10:05:28

Re: Searching With Min/max Prices

#25 12-08-2004 10:42:59

Re: Searching With Min/max Prices

Board footer