Kryptronic Software Support Forum

thedon122 · 12-09-2004 09:33:25

Hi Nick

I add the code as listed in your first post on here and made the change in the sql statement and added the . to it

?????

webmaster · 12-10-2004 10:16:40

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

Please post actual code. I want to make sure you didn't miss something. Your last statement is correct, but it seems like you've missed something if it's not working.

steveblueradio · 12-10-2004 11:17:37

Hi Nick,
I also have had problems with this mod, followed the code and when I do a search it just comes up blank, no results or writing .
I have the SEO mod installed on my cart will this make a diff or does it not matter?

Cheers ;-) ,
Steve

webmaster · 12-10-2004 11:22:33

SEO doesn't matter. If somebody could post the code they're actually using and the error message in the page source of the results page (if there is one) I can help...

steveblueradio · 12-11-2004 18:57:20

Hi Nick,
When I use this mod I just get the ste_layout and a blank screen where the search out put is ment to be, I dont get any sql errors ,

If you could and it would be a great help is display the line numbers where the text needs to be changed

IE:

line 256 change this to this

line 528 change this to this

[if you know what i mean ]

Cheers ;-) ,
Steve

webmaster · 12-13-2004 11:53:08

Here's diff output:

Code:


32,34d31
< $fd_minprice = $q->param('minprice');
< $fd_maxprice = $q->param('maxprice');
< 
308c305
< $sql_statement = ste_prod_searchsql_proc('SELECT COUNT(*) FROM product','',$fd_searchstr,$searchopr,$fd_searchtyp,$fd_minprice,$fd_maxprice);
---
> $sql_statement = ste_prod_searchsql_proc('SELECT COUNT(*) FROM product','',$fd_searchstr,$searchopr,$fd_searchtyp);
571c568
< <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&catstr=$catstring_encoded&minprice=$fd_minprice&maxprice=$fd_maxprice\">$html_previous_string</A> \| 
---
> <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&catstr=$catstring_encoded\">$html_previous_string</A> \| 
585c582
< <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&catstr=$catstring_encoded&minprice=$fd_minprice&maxprice=$fd_maxprice\">$html_next_string</A>
---
> <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&catstr=$catstring_encoded\">$html_next_string</A>
623c620
< my ($sel,$lim,$str,$opr,$typ,$min,$max) = @_;
---
> my ($sel,$lim,$str,$opr,$typ) = @_;
625c622
< my $sql_statement = "(";
---
> my $sql_statement = "";
738,761c735
< $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.
< 
< 
< $sql_statement . = "
---
> $sql_statement = "

steveblueradio · 02-07-2005 17:44:34

Hi all,
This is a good mod, got it to work. there is a mis type in Nicks code that stops it from working....

Change this:

Code:

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

my $sql_statement = "(";

To this:

Code:

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

my $sql_statement = " ";

Cheers ;-) ,
Steve

Steven · 02-13-2005 03:43:16

With this mod installed (I tried installing it the first time and got errors like everybody else), is it possible to have links to the search queries and display the search results?.. For Example;

and have this link take me to a page with all items under $10 ... using this mod.

I think I will try installing it again, thanks Steve for finding the bug.

Regards

Steven

marcingr · 04-23-2005 22:22:53

:-( Did anybody installed that mod? Is there is somebody who can help me out with installation? I did not sleept past two days...

I apreciate your help...

dh783 · 04-28-2006 14:19:13

I installed this mod with the code at the top of this post and keept getting this error message:

DISPLAY ERROR: 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 ') AND ((product_regprice<'450' AND product_pricestatus='R') OR (product_salepric' at line 2 at /library/webserver/cgi-executables/dhmc/library/common/database.pl line 623.

I did some playing with it and came up with this sollution:

changed

Code:

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

my $sql_statement = "(";
my $where = "WHERE ";

TO

Code:

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

my $sql_statement = "";
my $where = "WHERE (";

Changed

Code:

$where .= ")";

if ($min ne "") {

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

$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','S');

$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.

$sql_statement .= "

$sel
$where
$lim

";

NOTE CHANGES in the above code:

Code:

$pss_quoted = database_quote('product','S');

to get it to work with sales prices.

I also replaced this code in the "Html Pages & Elements > Manage Site Elements > Product Search Form" so I could input the amount, but the origanel code will work too.
CHANGED

Code:

<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>

TO

Code:

<INPUT TYPE="TEXT" NAME="minprice" SIZE="20" VALUE="(CGIVAR)fd_minprice(/CGIVAR)"></TD>

AND CHANGED

Code:

<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>

TO

Code:

<INPUT TYPE="TEXT" NAME="maxprice" SIZE="20" VALUE="(CGIVAR)fd_maxprice(/CGIVAR)"></TD>

This mod is working now but in a search for a product with no min and a max limit of say "450.00" it returnes a list which contains an item with a price of 1071.99. I seems to be comparing the first three numbers and thinks that the 107 part of the price is lower than 450, but in a search with a min of 108 and a max of 450 it returnes the list with out the 1071.99 item.

John

dh783 · 04-28-2006 15:09:15

This is a PS to my last post
You could also change

Code:

$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))";

TO

Code:

$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))";

AND CHANGE

Code:

$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))";

TO

Code:

$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))";

This just add the = sign so you could return results equal to or more than, and equal to or less than.

rachaelseven · 04-28-2006 21:31:23

Your problem with the wrong values being returned in the search is due to the fact that the prices are string variables, rather than decimal numbers. SQL searches strings one character at a time from left to right, rather than by numeric value. You could probably resolve this with an explicit CAST statement in the SQL call to insure that the values are treated as decimals. You can read the MySQL manual page that covers variable casting . Basically, it will probably look something like:

Code:

WHERE CAST (product_regprice as DECIMAL) >= CAST ($min_quoted as DECIMAL) OR ...

I haven't tested this, so the exact syntax may be a little off, but that's the basic idea, anyway. Like I said, reference the MySQL manual at the link above for all the details and hopefully the cast functions are properly implemented in DBD::mysql. It's also possible that by sprintf formatting the input search price and the product prices in the database to 2 decimal places, it would do the proper casting implicity; but I'm not sure on that one.

Rachael

dh783 · 04-29-2006 00:22:51

Ok thanks to Rachael and the pointer to the CAST statement I think this is working. So it goes as fellows

CHANGE

Code:

$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))";

TO

Code:

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

AND CHANGED

Code:

$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))";

TO

Code:

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

This seems to be working, I have ran several test on my site with out any problems.

John

Kryptronic Software Support Forum

#26 12-09-2004 09:33:25

Re: Searching With Min/max Prices

#27 12-10-2004 10:16:40

Re: Searching With Min/max Prices

#28 12-10-2004 11:17:37

Re: Searching With Min/max Prices

#29 12-10-2004 11:22:33

Re: Searching With Min/max Prices

#30 12-11-2004 18:57:20

Re: Searching With Min/max Prices

#31 12-13-2004 11:53:08

Re: Searching With Min/max Prices

Code:

#32 02-07-2005 17:44:34

Re: Searching With Min/max Prices

Code:

Code:

#33 02-13-2005 03:43:16

Re: Searching With Min/max Prices

#34 04-23-2005 22:22:53

Re: Searching With Min/max Prices

#35 04-28-2006 14:19:13

Re: Searching With Min/max Prices

Code:

Code:

Code:

Code:

Code:

Code:

Code:

Code:

#36 04-28-2006 15:09:15

Re: Searching With Min/max Prices

Code:

Code:

Code:

Code:

#37 04-28-2006 21:31:23

Re: Searching With Min/max Prices

Code:

#38 04-29-2006 00:22:51

Re: Searching With Min/max Prices

Code:

Code:

Code:

Code:

Board footer