Jump to content

How do I sort Product Options


Guest BeerMeister

Recommended Posts

Guest BeerMeister

I did a search in cubecart.com and cubecart.org. I'm sure the answer is there somewhere; but your "Common Questions and Problems" seems to be the perfect home for this:

Question: How do I sort Product Options, but not in alphabetical order? For instance, sizes should display as "small","medium","large","x-large". Default is to display them alphabetically as "large","medium","small","x-large".

Answer: in /upload/includes/content/viewProd.inc.php

find this query around line 157

$query = "SELECT ".$glob['dbprefix']."CubeCart_options_bot.option_id, ".$glob['dbprefix']."CubeCart_options_bot.value_id, option_price, option_symbol, value_name, option_name, assign_id FROM `".$glob['dbprefix']."CubeCart_options_bot` INNER JOIN `".$glob['dbprefix']."CubeCart_options_mid` ON ".$glob['dbprefix']."CubeCart_options_mid.value_id = ".$glob['dbprefix']."CubeCart_options_bot.value_id INNER JOIN `".$glob['dbprefix']."CubeCart_options_top` ON ".$glob['dbprefix']."CubeCart_options_bot.option_id = ".$glob['dbprefix']."CubeCart_options_top.option_id WHERE product =".$db->mySQLSafe($_GET['productId'])." ORDER BY option_name, value_name ASC";



change the "ORDER BY" to:


$query = "SELECT ".$glob['dbprefix']."CubeCart_options_bot.option_id, ".$glob['dbprefix']."CubeCart_options_bot.value_id, option_price, option_symbol, value_name, option_name, assign_id FROM `".$glob['dbprefix']."CubeCart_options_bot` INNER JOIN `".$glob['dbprefix']."CubeCart_options_mid` ON ".$glob['dbprefix']."CubeCart_options_mid.value_id = ".$glob['dbprefix']."CubeCart_options_bot.value_id INNER JOIN `".$glob['dbprefix']."CubeCart_options_top` ON ".$glob['dbprefix']."CubeCart_options_bot.option_id = ".$glob['dbprefix']."CubeCart_options_top.option_id WHERE product =".$db->mySQLSafe($_GET['productId'])." ORDER BY value_id,option_name, value_name ASC";

This will sort the Product Options in the order by which they were entered.

Link to post
Share on other sites

Guest Denver Dave

Interesting, my question is the reverse. In the admin page /admin/products/options.php?exiPage=0 the list of existing options seems to be sorted by Product Name, then by Option Name and then by the Option Atribute ID (or something like that). If we have lots of product option atributes, this makes finding them very difficult. Any suggestions on how to also sort the admin list of existing product / options / attributes also by Attribute name?

Part of the issue may be with the code about line 242:

// existing options

if(isset($_GET['editAssigned']) && $_GET['editAssigned']>0){

$query = "SELECT * FROM ".$glob['dbprefix']."CubeCart_options_bot INNER JOIN ".$glob['dbprefix']."CubeCart_inventory ON product = productId WHERE assign_id = ".$db->mySQLSafe($_GET['editAssigned'])." ORDER BY name, value_id ASC";

} else {

$query = "SELECT * FROM ".$glob['dbprefix']."CubeCart_options_bot INNER JOIN ".$glob['dbprefix']."CubeCart_inventory ON product = productId ORDER BY name, option_id, value_id ASC";

}

if(isset($_GET['exiPage'])){

$exiPage = $_GET['exiPage'];

} else {

$exiPage = 0;

}

$existingOptions = $db->select($query, $existingOptionsPerPage, $exiPage);

===========

and the table CubeCart_options_mid contains the attribute values

===========

The solution may be close to the following which joins a third table CubeCart_options_mid which contains the option value names

// existing options

if(isset($_GET['editAssigned']) && $_GET['editAssigned']>0){

$query = "SELECT * FROM ".$glob['dbprefix']."CubeCart_options_bot, CubeCart_options_mid INNER JOIN ".$glob['dbprefix']."CubeCart_inventory ON product = productId WHERE assign_id = ".$db->mySQLSafe($_GET['editAssigned'])." AND ".$glob['dbprefix']."CubeCart_options_bot.value_id =".$glob['dbprefix']."CubeCart_options_mid.value_id ORDER BY name ASC";

} else {

$query = "SELECT * FROM ".$glob['dbprefix']."CubeCart_options_bot ," .$glob['dbprefix']." CubeCart_options_mid INNER JOIN ".$glob['dbprefix']."CubeCart_inventory ON product = productId WHERE ".$glob['dbprefix']."CubeCart_options_bot.value_id =".$glob['dbprefix']."CubeCart_options_mid.value_id ORDER BY name, value_name ASC";

}

==========

There is an existing function

$idKey = $existingOptions[$i]['value_id'];

echo $optionValues[$idKey];

which is currently used to find the Value Name and in theory is no longer needed because of the third join. This may remove some of the funky behavior where the option value names and the product option names have to be coordinated 10 (or specified) items at a time.

=============

Any suggestions appreciated

Link to post
Share on other sites
Guest Denver Dave

Hafree - thanks for the post. Not sure I understand since the option value name does not seem to be included in the initail query for the existing attributes and the value is determined by a function: $optionValues[$idKey]

After joining with the third table as descibed above and changing the sort order, I was able to do the following simple code:

// *** davehack - use the option value name from 3rd table joined above

//$idKey = $existingOptions[$i]['value_id'];

//echo $optionValues[$idKey];

echo $existingOptions[$i]['value_name'];

Not saying that I understand this module and your approach of just changing the sort order is much more simple if it works - I just don't see the value_name included in the existing options query. I'm down about 100 lines below you about line 242 for the old query (before adding the value name table):

$query = "SELECT * FROM ".$glob['dbprefix']."CubeCart_options_bot INNER JOIN ".$glob['dbprefix']."CubeCart_inventory ON product = productId WHERE assign_id = ".$db->mySQLSafe($_GET['editAssigned'])." ORDER BY name, value_id ASC";

I am still running 3.0.10 - that might be the difference

Link to post
Share on other sites
  • 1 month later...

I'm very new to cube cart but I was frustrated by this as well. I tried the proposed solutions but at times they still didn't work. I had some options that still wouldn't sort correctly. Here's how I fixed it. This solution will give you absolute control over how the options are sorted.

Go through your sql editor of choice and alter the CubeCart_options_mid table by adding a column named display_order. Make it not null with a defalt value of 0.

Here's the sql that I used to alter the table.

ALTER TABLE `your db alias goes here`.`CubeCart_options_mid` ADD COLUMN `display_order` INTEGER(16) NOT NULL DEFAULT 0 AFTER `father_id`;

Once you have added the display_order column you can update each option:

ex.

value_id value_name father_id display_order

1 XS 1 1

2 S 1 2

3 M 1 3

4 L 1 4

Now update your sql in the viewProd.inc.php

Find the following line of code

// build sql query for product options luuuuuurvely

$query = "SELECT ".$glob['dbprefix']."CubeCart_options_bot.option_id, ".$glob['dbprefix']."CubeCart_options_bot.value_id, option_price, option_symbol, value_name, option_name, assign_id FROM `".$glob['dbprefix']."CubeCart_options_bot` INNER JOIN `".$glob['dbprefix']."CubeCart_options_mid` ON ".$glob['dbprefix']."CubeCart_options_mid.value_id = ".$glob['dbprefix']."CubeCart_options_bot.value_id INNER JOIN `".$glob['dbprefix']."CubeCart_options_top` ON ".$glob['dbprefix']."CubeCart_options_bot.option_id = ".$glob['dbprefix']."CubeCart_options_top.option_id WHERE product =".$db->mySQLSafe($_GET['productId'])." ORDER BY option_name, value_name ASC";

and change it to

// build sql query for product options luuuuuurvely

$query = "SELECT ".$glob['dbprefix']."CubeCart_options_bot.option_id, ".$glob['dbprefix']."CubeCart_options_bot.value_id, option_price, option_symbol, value_name, option_name, assign_id, display_order FROM `".$glob['dbprefix']."CubeCart_options_bot` INNER JOIN `".$glob['dbprefix']."CubeCart_options_mid` ON ".$glob['dbprefix']."CubeCart_options_mid.value_id = ".$glob['dbprefix']."CubeCart_options_bot.value_id INNER JOIN `".$glob['dbprefix']."CubeCart_options_top` ON ".$glob['dbprefix']."CubeCart_options_bot.option_id = ".$glob['dbprefix']."CubeCart_options_top.option_id WHERE product =".$db->mySQLSafe($_GET['productId'])." ORDER BY display_order ASC";

I hope this helps.

Link to post
Share on other sites
  • 4 months later...

I'm very new to cube cart but I was frustrated by this as well. I tried the proposed solutions but at times they still didn't work. I had some options that still wouldn't sort correctly. Here's how I fixed it. This solution will give you absolute control over how the options are sorted.

Go through your sql editor of choice and alter the CubeCart_options_mid table by adding a column named display_order. Make it not null with a defalt value of 0.

Here's the sql that I used to alter the table.

ALTER TABLE `your db alias goes here`.`CubeCart_options_mid` ADD COLUMN `display_order` INTEGER(16) NOT NULL DEFAULT 0 AFTER `father_id`;

...

...

...

I hope this helps.

This one almost had it working for me. I had to do a few things different. In the above query I used the following query:

$query = "SELECT ".$glob['dbprefix']."CubeCart_options_bot.option_id, .$glob['dbprefix']."CubeCart_options_bot.value_id, option_price, option_symbol

, value_name, option_name, assign_id, display_order FROM `".$glob['dbprefix']."CubeCart_options_bot` INNER JOIN `".$glob['dbprefix']."CubeCart_options_mid` O

N ".$glob['dbprefix']."CubeCart_options_mid.value_id = ".$glob['dbprefix']."CubeCart_options_bot.value_id INNER JOIN `".$glob['dbprefix']."CubeCart_options_t

op` ON ".$glob['dbprefix']."CubeCart_options_bot.option_id = ".$glob['dbprefix']."CubeCart_options_top.option_id WHERE product =".$db->mySQLSafe($_GET['produ

ctId'])." ORDER BY option_name, display_order ASC";

Which you will see is a little different as it sorts by option_name, display_order; which sorts by the name 1st which if you forget this then it will display your product options in a really funky order :)

then what good is this if you can't edit it easily :)

So go & edit admin/product/options.php. Around line 417 add the following (mod marked by start/stop)

<td class="<?php echo $cellColor; ?>"><?php echo $attributes[$i]['value_id']; ?></td>^M

<td class="<?php echo $cellColor; ?>"><?php echo $attributes[$i]['option_name']; ?></td>^M

<td class="<?php echo $cellColor; ?>"><?php echo $attributes[$i]['value_name']; ?></td>^M

<? // mod start: Order Options, [email protected] ?>

<td class="<?php echo $cellColor; ?>"><?php echo $attributes[$i]['display_order']; ?></td>^M

<? // mod stop ?>

Then again round line 439:

<td><input name="attribute" type="text" id="attribute" class="textbox" value="<?php if(isset($attributes[0]['value_name']) && isset($_GET['editAttrib

ute'])) echo $attributes[0]['value_name'];?>" /></td>^M

<? // mod start: option order [email protected] ?>

<td><input name="display_order" type="text" id="display_order" class="textbox" value="<?php if(isset($attributes[0]['display_order']) && isset($_GET[

'editAttribute'])) echo $attributes[0]['display_order'];?>" /></td>^M

<? // mod stop: option order [email protected] ?>

<td><input type="submit" name="Submit" value="<?php if(isset($_GET['editAttribute'])){ echo $lang['admin']['edit']; } else { echo $lang['admin']['ad

d']; } echo " ".$lang['admin']['products']['attribute'];?>" class="submit" /></td>^M

Then you need to edit the language file so it will display the column label:

language/en/lang.inc.php

Around line 844 after:

'option_attributes' => "Option Attributes",

add the following line:

'display_order' => "Option Order",

That's it, now you can edit the order while you are adding or editing option attributes :)

If you have any problems or questions feel free to contact me directly.

David

I've looked at this a little more, a few mistakes (1st mod to cubecart, still figuring out what does what where.

Near line 226 where the attributes are retrieved:

// mod start: options display_order - [email protected]

//$query = "SELECT * FROM ".$glob['dbprefix']."CubeCart_options_mid INNER JOIN ".$glob['dbprefix']."CubeCart_options_top ON father_id = opti

on_id WHERE value_id = ".$db->mySQLSafe($_GET['editAttribute'])." ORDER BY option_name, value_name ASC";^M

$query = "SELECT * FROM ".$glob['dbprefix']."CubeCart_options_mid INNER JOIN ".$glob['dbprefix']."CubeCart_options_top ON father_id = option

_id WHERE value_id = ".$db->mySQLSafe($_GET['editAttribute'])." ORDER BY option_name, display_order ASC";^M

// mod stop

} else { ^M

// mod start: options display_order - [email protected]

//$query = "SELECT * FROM ".$glob['dbprefix']."CubeCart_options_mid INNER JOIN ".$glob['dbprefix']."CubeCart_options_top ON father_id = option_id ORDER BY option_name, value_name ASC";^M

$query = "SELECT * FROM ".$glob['dbprefix']."CubeCart_options_mid INNER JOIN ".$glob['dbprefix']."CubeCart_options_top ON father_id = option_id ORDER BY option_name, display_order ASC";^M

// mod stop:

This will sort the Create Options value by the name & display order.

Then I just noticed that while I had the input for editing the display order it wasn't saving to the database. So down near line 170:

$record['value_name'] = $db->mySQLSafe($_POST["attribute"]);^M

$record['father_id'] = $db->mySQLSafe($_POST["option"]);^M

// mod start: option display_order [email protected]

$record['display_order'] = $db->mySQLSafe($_POST["display_order"]);^M

// mod stop

This should work,

Link to post
Share on other sites
  • 2 years later...
Guest alphonseha

I tried digging through the forum but I can't find how to sort by price in CC3.

I have tried this but it doesn't work.

	$query = "SELECT ".$glob['dbprefix']."CubeCart_options_bot.option_id, ".$glob['dbprefix']."CubeCart_options_bot.value_id, option_price, option_symbol, value_name, option_name, assign_id FROM `".$glob['dbprefix']."CubeCart_options_bot` INNER JOIN `".$glob['dbprefix']."CubeCart_options_mid` ON ".$glob['dbprefix']."CubeCart_options_mid.value_id = ".$glob['dbprefix']."CubeCart_options_bot.value_id INNER JOIN `".$glob['dbprefix']."CubeCart_options_top` ON ".$glob['dbprefix']."CubeCart_options_bot.option_id = ".$glob['dbprefix']."CubeCart_options_top.option_id WHERE product =".$db->mySQLSafe($_GET['productId'])." ORDER BY [b]option_price,[/b] option_name, value_name ASC";

The code in bold is what I added.

Anybody have any ideas?

Thanks

I tried digging through the forum but I can't find how to sort by price in CC3.

I have tried this but it doesn't work.

	$query = "SELECT ".$glob['dbprefix']."CubeCart_options_bot.option_id, ".$glob['dbprefix']."CubeCart_options_bot.value_id, option_price, option_symbol, value_name, option_name, assign_id FROM `".$glob['dbprefix']."CubeCart_options_bot` INNER JOIN `".$glob['dbprefix']."CubeCart_options_mid` ON ".$glob['dbprefix']."CubeCart_options_mid.value_id = ".$glob['dbprefix']."CubeCart_options_bot.value_id INNER JOIN `".$glob['dbprefix']."CubeCart_options_top` ON ".$glob['dbprefix']."CubeCart_options_bot.option_id = ".$glob['dbprefix']."CubeCart_options_top.option_id WHERE product =".$db->mySQLSafe($_GET['productId'])." ORDER BY [b]option_price,[/b] option_name, value_name ASC";

The code in bold is what I added.

Anybody have any ideas?

Thanks

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...