Kryptronic Software Support Forum

webmaster · 09-16-2003 08:45:33

This mod is done to ./cgi-bin/library/modules/ste_prod.pl in the routine 'ste_prod_disp' to return products in a particular order when displaying them as related products, products with categories or products on pages like splash, new items, specials, best sellers, etc.

Be aware that this mod orders by product name (product_name field), but with the modification of the line with the ORDER BY clause in it, you could order by other fields like the product id (product_id field).

Here's how to do it:

In the file ./cgi-bin/library/modules/ste_prod.pl in the routine 'ste_prod_disp', right below:

Code:


if ($tables_data_source{'product'} eq "CSV") {

$sql_limit_string = "$csv_limit_syntax";

} elsif ($tables_data_source{'product'} eq "ALT") {

$sql_limit_string = "$alt_limit_syntax";

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

Add:

Code:


$sql_limit_string = "ORDER BY product_name $sql_limit_string";

Then replace:

Code:


if ($related ne "" && $prodxcat_count >= "$first_row" && $prodxcat_count <= "$last_row") {

With:

Code:


if ($related ne "") {

Then replace:

Code:


if ($related ne "" && $prodxprod_count >= "$first_row" && $prodxprod_count <= "$last_row") {

With:

Code:


if ($related ne "") {

Then replace:

Code:


$sql_statement = "

$sql_select_string
WHERE $prodxcat_sql

";

With:

Code:


$sql_statement = "

$sql_select_string
WHERE $prodxcat_sql
$sql_limit_string

";

Then replace:

Code:


$sql_statement = "

$sql_select_string
WHERE $prodxprod_sql

";

With:

Code:


$sql_statement = "

$sql_select_string
WHERE $prodxprod_sql
$sql_limit_string

";

That's all there is to it. Just be sure to make all changes exactly as they are listed above.

Charles · 09-17-2003 02:21:14

You can just copy and paste the code right from the forum!!
This Forum is Code Friendly!!!

larry · 09-17-2003 10:29:40

If you add a user field in product called product_sortorder, then populate this field for each product within a category (such as A01, B01, C01,D01 to order 4 different products) you can force a sort order "behind the scenes" for each product-category.

I have used this method since we cannot sort by product_id, product_name or any other "visible" field.

Larry

lgbsteve · 09-19-2003 08:58:44

Nick, I've been hopping for a mod like this, Thank you.
But, I know nothing is never enough. My products either are sorted on product_number or product_name. I'm using Larry's (last post) idea and created a new field product_sort.
The mod is working fine but I'm either entering the number or name in the prodcut_sort field. Could you give me your mod in an if/else script.

like
IF product_number = "A" sort on product_name else on "N" product_number
This way I can save on my product file size.

Can this also be easily made into a choice that the customer can make?

Thank you -Steve

webmaster · 09-25-2003 10:41:07

I'm not sure if that's possible in SQL. Perhaps Mike L has a bit of SQL for that...

Jump · 11-07-2003 22:29:10

I made these changes to display products by product_id field but it doesn't seem to work... do I need to delete products and re-enter them into store catalogue?

webmaster · 11-10-2003 11:40:26

No - you don't need to delete them and re-enter them. This code is ordering by product_name - are you sure your displays aren't ordering by that field? Did you double-check to make sure the ste_prod.pl file on the server was overwritten with all the changes?

Jump · 11-10-2003 22:57:09

Doh! What I wanted was product rather than id which was what I had entered. thanks for your help

rgarrison · 11-11-2003 19:24:18

Ok I followed this to the letter and it still will not list my products are now in order, but categories still aren't Any ideas?

webmaster · 11-15-2003 09:04:50

This is for products - not categories. I just posted a response to a trhead in this forum for the category mod.

sgreiner · 11-18-2003 16:48:03

How would I get my products listed in order by prod ref string on my admin-->manage products page?

webmaster · 11-19-2003 10:35:07

That would be done by editing the file ./cgi-bin/library/modules/adm_iud.pl. In the 'adm_iud_list_disp' subroutine, look for:

Code:


if ($sql_limit_string ne "") {

$sql_limit_string =~ s/start/$fd_startrow/gs;
$sql_limit_string =~ s/rows/$admin_rows_to_return_per_page/gs;

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

And right below it, add:

Code:


if ($tablename eq "product") {

$temp_string = "ORDER BY product_id";

$sql_limit_string = "$temp_string $sql_limit_string";

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

arkmay · 12-22-2003 14:32:37

Thank you! This has been very helpful.

But now here's a toughie.. would it be possible to have products arranged by different orders on separate categories?

for example, products in one category listed by item number, products in another category listed by item name.

this seems a bit more difficult but would it be possible?
thanks

lgbsteve · 12-22-2003 14:51:11

I added another field to my product table. called product_sort. I deal in books and in some categories I want them listed by book number and in others by titles.
I enter either the title or the book number in the product_sort field.

I then use
$temp_string = "ORDER BY product_sort";

instead of:
$temp_string = "ORDER BY product_id";

arkmay · 12-22-2003 19:16:21

sounds good
however, I have some products that will be in multiple categories..
and was wondering if it would be possible for different categories be ordered not just by one custom field but actually ordered based on different fields (depending on the category).

basically we are selling CDs, and my goal is to have things like "CDs by release date" and "CDs by artist" as separate categories (basically the same list products with a different order).

or maybe there's a better solution?

webmaster · 01-08-2004 10:51:58

That's not possible to do in the adm_iud interface because the category information is not known until the query is submitted and results are reviewed.

geneva66 · 02-18-2005 07:59:53

I just want to thank all those guys who post solutions on the forums that help all the newbies like myself. I would not be able to make any of the changes I do without your knowledge and guidance. Thanks again.

andyM · 10-08-2005 16:57:05

I implemented this sorting by product_number. It seems to work, but i noticed some new errors showing up in logs:

cp-app.cgi: DBD::mysql::st execute failed: You have an error in your SQL syntax near 'ORDER BY product_number LIMIT 0, 30

Can someone tell me what this means, and what i might do to rectify it?

I am also getting this error at the exact same time:

cp-app.cgi: ' at line 5 at /**my path to ccp installation**/cgi-bin/library/common/database.pl line 623.

Jess · 11-02-2005 19:34:59

Thanks for this mod!

Is there any way to list by REVERSE product_number? In other words, I would like items with higher numbers to be listing first in categories.

Thanks!
Jess

dtwg · 11-09-2005 09:51:58

I think you can just add DESC after your ORDER BY clause.

ORDER BY product_number DESC

Dave

wyattea · 05-26-2006 22:17:19

Nick, I used your code as described and it worked, except that I'm sorting by 'product_regprice' and the problem is that prices over $100 are being sorted first, so 100, 140, 250, 250, 32, 33, 35, 44, 66 - I guess it's sorting 'alphabetically' rather than numerically. How can I have it sorted by price from lowest to highest?

Thanks,

James...

dh783 · 05-26-2006 23:12:36

It's sorting numerically but it doing it number by number from left to right:

100
140
250
32
33
35
44

if you look at it like this 1 is equal to 1 but 0 is smaller than 4 and so on, when it finds a number smaller than the one it is comparing it orders it first. You will have to get it to look at the number as a hole. I ran into the same problem with the mode to do a search by price that is posted in this forum "Searching With Min/max Prices" . I don't think that that solution will work in this case but it is the general idea of getting the script to compare the hole number. Mabe a sprintf statement may make the amouts into a decimal which will make the script compare the number as a hole but I'm realy not shure.

John

wyattea · 05-27-2006 18:07:28

I understand why it's doing it, I just don't know how to fix it

James....

wyattea · 05-27-2006 18:12:19

John, I checked out the referenced post but i don't care about searching, it's just sorting the products by price in a category from lowest to highest that I want...I'm SHOCKED that this hasn't come up before and been addressed.

James...

rachaelseven · 05-27-2006 18:32:46

See correct solution further down thread.

Kryptronic Software Support Forum

#1 09-16-2003 08:45:33

Order Products By X Field

Code:

Code:

Code:

Code:

Code:

Code:

Code:

Code:

Code:

Code:

#2 09-17-2003 02:21:14

Re: Order Products By X Field

#3 09-17-2003 10:29:40

Re: Order Products By X Field

#4 09-19-2003 08:58:44

Re: Order Products By X Field

#5 09-25-2003 10:41:07

Re: Order Products By X Field

#6 11-07-2003 22:29:10

Re: Order Products By X Field

#7 11-10-2003 11:40:26

Re: Order Products By X Field

#8 11-10-2003 22:57:09

Re: Order Products By X Field

#9 11-11-2003 19:24:18

Re: Order Products By X Field

#10 11-15-2003 09:04:50

Re: Order Products By X Field

#11 11-18-2003 16:48:03

Re: Order Products By X Field

#12 11-19-2003 10:35:07

Re: Order Products By X Field

Code:

Code:

#13 12-22-2003 14:32:37

Re: Order Products By X Field

#14 12-22-2003 14:51:11

Re: Order Products By X Field

#15 12-22-2003 19:16:21

Re: Order Products By X Field

#16 01-08-2004 10:51:58

Re: Order Products By X Field

#17 02-18-2005 07:59:53

Re: Order Products By X Field

#18 10-08-2005 16:57:05

Re: Order Products By X Field

#19 11-02-2005 19:34:59

Re: Order Products By X Field

#20 11-09-2005 09:51:58

Re: Order Products By X Field

#21 05-26-2006 22:17:19

Re: Order Products By X Field

#22 05-26-2006 23:12:36

Re: Order Products By X Field

#23 05-27-2006 18:07:28

Re: Order Products By X Field

#24 05-27-2006 18:12:19

Re: Order Products By X Field

#25 05-27-2006 18:32:46

Re: Order Products By X Field

Board footer