X-Cart forums - How TO Order Products List By Field

X-Cart forums (https://forum.x-cart.com/index.php)

- Dev Questions (https://forum.x-cart.com/forumdisplay.php?f=20)

- - How TO Order Products List By Field (https://forum.x-cart.com/showthread.php?t=802)

Rondoosh

11-26-2002 06:03 PM

How TO Order Products List By Field

Hello, i have a lot of products on my site and i wish to organize them. How to i arrange them by price...with out having to do it one by one? Thanks.

funkydunk

11-26-2002 11:06 PM

There is a fair amount of info on this on this thread. http://forum.x-cart.com/viewtopic.php?t=677&highlight=

The file to amend is the the customer/products.php file

Line 61 is:

Code:

$search_query = "($sql_tbl[products].categoryid='$cat' or $sql_tbl[products].categoryid1='$cat' or $sql_tbl[products].categoryid2='$cat' or $sql_tbl[products].categoryid3='$cat') and $sql_tbl[products].forsale='Y' group by $sql_tbl[products].productid order by $sql_tbl[products].orderby ".($config["General"]["product_order_reversed"]=="Y"?"desc":"asc")." limit $first_page, ".$config["General"]["products_per_page"];

Change this to:

Code:

$search_query = "($sql_tbl[products].categoryid='$cat' or $sql_tbl[products].categoryid1='$cat' or $sql_tbl[products].categoryid2='$cat' or $sql_tbl[products].categoryid3='$cat') and $sql_tbl[products].forsale='Y' group by $sql_tbl[products].productid order by $sql_tbl[pricing].price ".($config["General"]["product_order_reversed"]=="Y"?"desc":"asc")." limit $first_page, ".$config["General"]["products_per_page"];

I have left this with the product order reversed bit in so that you can set to increase or decrease through the admin centre.

You will also need to amend the featured products php file in the same way if you want that to display in a similar way.

Enjoy.

Rondoosh

12-02-2002 05:53 PM

thanks funkydunk, but i can't find the customer/products.php file. step by step how do i get to that template? Thanks for your patience.

funkydunk

12-02-2002 10:40 PM

Sorry,

You cant get to it through the admin area in x-cart, you need to open up the products.php file (either your local version or the one on the server) in a text editor. I use ultraedit (it highlights the php code very well).

You can then make any amendments needed.

aguy2no

07-29-2003 02:11 AM

I posted a similar question in the MySQL forum, and just found the solution overhere.

Nevertheless, I tried this code in the products.php file and I received this error:

INVALID SQL: 1064 : You have an error in your SQL syntax near 'group by xcart_products.productid limit 45' at line 1
SQL QUERY FAILURE: select xcart_products.*, xcart_categories.category, min(xcart_pricing.price) as price from xcart_products, xcart_pricing, xcart_categories where xcart_pricing.productid=xcart_products.productid and xcart_pricing.quantity=1 and xcart_products.categoryid=xcart_categories.categor yid AND (xcart_categories.membership='' OR xcart_categories.membership='') and (xcart_pricing.membership='' or xcart_pricing.membership='') and (xcart_products.categoryid='46' or xcart_products.categoryid1='46' or xcart_products.categoryid2='46' or xcart_products.categoryid3='46') and xcart_products.forsale='Y' group by xcart_products.productid order by xcart_pricing.price asc limit 0, group by xcart_products.productid limit 45

Please can anybody help me out, cause I am pulling my hair out for the last couple of days!

I really would like to have my products sorted by price!!

thx!

funkydunk

07-29-2003 02:18 AM

what version are you using?

the code posted above was for a really old version and may not work with newer xcarts.

aguy2no

07-29-2003 02:37 AM

Hi Funkydunk,

Currently I am upgrading form 2.3.0 to 3.4.3 Better to say that I am installing a complete new cart.... 8O

My line in teh products.php looks like:

Code:

$search_query = "($sql_tbl[products].categoryid='$cat' or $sql_tbl[products].categoryid1='$cat' or $sql_tbl[products].categoryid2='$cat' or $sql_tbl[products].categoryid3='$cat') and $sql_tbl[products].forsale='Y'";

I added at the end:

Code:

group by $sql_tbl[products].productid order by $sql_tbl[pricing].price ".($config["General"]["product_order_reversed"]=="Y"?"desc":"asc")." limit $first_page, ".$config["General"]["products_per_page"];

but this will result in the error previously described.

First I thought it was the " that was missing at the end...But I received a parse error when adding the quotationmark.

It's puzzling me...

:(

funkydunk

07-29-2003 07:58 AM

Little known secret shhhh... :wink:

but you can get it do do an ascending sort by price by adding a link to the product page with additional variable of:

Code:

&orderby=price

Job done :)

same goes with alphabetically

Code:

&orderby=product

Now for one site I just added a table into the top of the products.tpl such as:

Code:

{* custom mod *}

{if $products ne ""}

<table border="0" cellspacing="0" cellpadding="2" width="100%">

<tr>

<td valign="top" align="center" colspan="4">

Sort by:

</td>

</tr>

<tr>

<td valign="top" align="center">

Price

</td>

<td valign="top" align="center">

Product Name

</td>

<td valign="top" align="center">

Brand

</td>

<td valign="top" align="center">

Date Added

</td>

</tr>

</table>











{/if}

{* end of custom mod *}

Now this uses the brand field in the database but xcart does not have that switched on as standard.

You will also need to add:

Code:

// funkydunk mod - added in so that sorting can carry forward across the pages

$smarty->assign("orderby",$orderby);

into the bottom of customer/products.php

and change navigation.tpl to the following so that the orderby variable is carried over to the next page:

Code:

{* $Id: navigation.tpl,v 1.6 2002/10/22 10:27:05 olga Exp $ *}

{if $total_pages gt 2}

<table cellpadding=0 border=0>

<tr height=14>

{section name=page loop=$total_pages start=1}

{if %page.index% eq 1}

<td>{$lng.lbl_result_pages}: </td>

{if $navigation_page gt 1}

<td valign=middle>[img]{$ImagesDir}/larrow.gif[/img]</td>

{/if}

{/if}

{if %page.index% eq $navigation_page}

<td align=center>

{%page.index%}

</td>

{else}

<td width=17 background="{$ImagesDir}/page.gif" align=center>

{%page.index%}

</td>

{/if}

{if %page.last%}

{capture name=total_pages_minus}

{math equation="pages-1" pages=$total_pages}

{/capture}

{if $navigation_page lt $smarty.capture.total_pages_minus}

<td valign=middle>[img]{$ImagesDir}/rarrow.gif[/img]</td>

{/if}

{/if}

{/section}

</tr></table>








{/if}

aguy2no

07-29-2003 12:16 PM

He Funkydunk,

a million, man! I placed the code in the proper files.

I started with

Code:

&orderby=price

I did not really get it where to place it. What I did is the following:

Code:

$search_query = "($sql_tbl[products].categoryid='$cat' or $sql_tbl[products].categoryid1='$cat' or $sql_tbl[products].categoryid2='$cat' or $sql_tbl[products].categoryid3='$cat') and $sql_tbl[products].forsale='Y' &orderby=price";

As you can see I put it at the end as an argument. Not sure if that's ok....Well I am sure that it is nog OK, because it is not working.... :wink: :?

The order by price argument doesn't bring up any errormessage but no products will show up.

I reckon I made a mistake here, ah... :oops: What did I do wrong???

The rest of your mods seem to be fine. Thanks a lot...very usefull mods!
[/quote]

aguy2no

07-29-2003 01:30 PM

OK...I got a little bit further....

Now I changed the query in products.php into:

Code:

$search_query = "($sql_tbl[products].categoryid='$cat' or $sql_tbl[products].categoryid1='$cat' or $sql_tbl[products].categoryid2='$cat' or $sql_tbl[products].categoryid3='$cat') and $sql_tbl[products].forsale='Y' &orderby=price &orderby=product ";

This seems to work.... Products will show up alphabetically. When I leave "&orderby=products"... I'll get no searchresults. The same happens when I skip "&orderby=price"....No results... :o

I am running bazurk! :roll:

funkydunk

07-29-2003 09:49 PM

The sort mechanism is already built by xcart just they haven't switched it on.

Just add a link to the top of the products.tpl

Sort this list by Price

And amend the navigation.tpl as shown

Then amend products.php by adding the assign to smarty code at the bottom.

You dont need to amend the sql query in any way.

aguy2no

07-30-2003 01:07 AM

Thanks Funkydunk for being so helpfull!

Forgot to mention that I did this aswell. I kept the SQL query as it was initially brought by XCart. Then I amended the products.tpl with (like you proposed earlier)

Code:

* custom mod *}

{if $products ne ""}

<table border="0" cellspacing="0" cellpadding="2" width="100%">

<tr>

<td valign="top" align="center" colspan="4">

Sort by:

</td>

</tr>

<tr>

<td valign="top" align="center">

Price

</td>

<td valign="top" align="center">

Product Name

</td>

<td valign="top" align="center">

Brand

</td>

<td valign="top" align="center">

Date Added

</td>

</tr>

</table>











{/if}

{* end of custom mod *}

which is basically the same as amending :

Code:

Sort this list by Price

When clicking; the list remains in the same sequence. So probably in my version sorting by price isn't switched on.... :(

Where can I do that...? :oops:

funkydunk

07-30-2003 01:40 AM

Sorry my mistake :oops: forgot what I did now... it was a while back

Code for func.php search for the func_search_products and replace the first line of that function declaration with

Code:

// function func_search_products($query, $membership,$first=0,$count_all=-1,$get_all=0, $orderby="orderby") {

// funkydunk mod to add sort facilities

function func_search_products($query, $membership,$first=0,$count_all=-1,$get_all=0, $orderby) {

aguy2no

07-30-2003 03:03 AM

we are dealing here with a persistent little bastard..... :twisted:

Amending the func.php, will result in:

Warning: Missing argument 6 for func_search_products() in c:\program files\apache group\apache\htdocs\shop\include\func.php on line 977

Warning: Missing argument 6 for func_search_products() in c:\program files\apache group\apache\htdocs\shop\include\func.php on line 977

However, changing the passage into:

Code:

function func_search_products($query, $membership,$first=0,$count_all=-1,$get_all=0, $orderby="price") {

Makes sure that every single category is sorted by price (ascending) by default! YEAHAAAAAAAAAAA! :D :D This is what I initially wanted.... Thank you funkydunk!

Other amendmends are not necessary anymore. However when you want the customer to choose his own sorting method, there is still one error as mentioned above.

Anyway...Funkydunk, you made a sunnyday out of myday today! :D

PhilJ

08-28-2003 12:02 PM

I replaced the code

Code:

function func_search_products($query, $membership,$first=0,$count_all=-1,$get_all=0, $orderby="price") {

in func.php and my products are sorted by price DESCENDING when I really want them ascending.

How come yours are ascending and mine are descending?!

By the way this is in v3.4.5

funkydunk

08-28-2003 10:21 PM

Have you tried ticking or unticking the list products in reverse order in admin?

PhilJ

08-29-2003 06:11 AM

That works now, thanks Funkydunk

Should have spotted that one :roll:

funkydunk

08-29-2003 06:34 AM

:lol:

jimskodi

01-08-2004 12:29 PM

How would you sort by alphabetized in version 3.5.0

you had talked about being able to list the items via alphabetizing them from the data base instead of placing positions next to each item. what code do you have to implement and where?

I also need to do this for the items, but also for a subcatagories of names.
I have artists first and last names and want them to show up alphabetized.

Do I have to list them with their last name first or with a comma between first and last in order for this to work?

funkydunk

01-08-2004 01:23 PM

In version 3.5 the facility exists in admin to change the order of the products on the page by going into general settings.

The categories will need to be put into order by using the position field unless you want to list them all by last name then first name.

hth

spidey

01-27-2004 04:24 PM

Hey everyone,

I managed to get a sort working by Price and by Product (can be further applied for any other product fields), along the lines that this original thread was going towards (i.e. A "Sort By" link at the top of the product listings).

Did the following:

1) Made the changes in navigation.tpl and products.tpl (basically adding the code so that $orderby variable can be passed back and forth), according to what funkydunk mentioned earlier in this thread

2) DID NOT MODIFY func.php at all... Instead, modified products.php to:

Code:

$products = func_search_products($search_query, !empty($user_account['membership'])?$user_account['membership']:"", $first_page,$current_category["product_count"],0,$orderby);

The sorting is then not only limited to price, but whatever variables you would like to add to products.tpl for sorting... Hope this works for everyone!

All times are GMT -8. The time now is 01:09 AM.