MySQL Query

[CGhoST]

I have this MySQL Query from Separate Pricing Per Customer

 

$products_options_query = tep_db_query("select pov.products_options_values_id, pov.products_options_values_name, pa.options_values_price, pa.price_prefix, pa.products_attributes_id, pov2po.sort_order from " . TABLE_PRODUCTS_ATTRIBUTES . " pa, " . TABLE_PRODUCTS_OPTIONS_VALUES . " pov join " . TABLE_PRODUCTS_OPTIONS_VALUES_TO_PRODUCTS_OPTIONS . " pov2po on pov.products_options_values_id=pov2po.products_options_values_id where pa.products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and pa.options_id = '" . (int)$products_options_name['products_options_id'] . "' and pa.options_values_id = pov.products_options_values_id and pov.language_id = '" . (int)$languages_id . "' and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and find_in_set('".$customer_group_id."', attributes_hide_from_groups) = 0");

 

To this query I need to add "ORDER BY pov2po.sort_order ASC, pov.products_options_values_name ASC" to the end of the query. Meaning:

 

$products_options_query = tep_db_query("select pov.products_options_values_id, pov.products_options_values_name, pa.options_values_price, pa.price_prefix, pa.products_attributes_id, pov2po.sort_order from " . TABLE_PRODUCTS_ATTRIBUTES . " pa, " . TABLE_PRODUCTS_OPTIONS_VALUES . " pov join " . TABLE_PRODUCTS_OPTIONS_VALUES_TO_PRODUCTS_OPTIONS . " pov2po on pov.products_options_values_id=pov2po.products_options_values_id where pa.products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and pa.options_id = '" . (int)$products_options_name['products_options_id'] . "' and pa.options_values_id = pov.products_options_values_id and pov.language_id = '" . (int)$languages_id . "' and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' ORDER BY pov2po.sort_order ASC, pov.products_options_values_name ASC and find_in_set('".$customer_group_id."', attributes_hide_from_groups) = 0");

 

Could someone please be kind enough to show how i can merge the two please.

[MrPhil]

Put ORDER BY and LIMIT clauses at the very end of the statement, not embedded in the middle of the WHERE clause like you have right now.

 

Are you asking where to find the original PHP code so that you can add the new clause? On Windows, "findstr" is your friend (on Linux, "grep"). Depending on how the statement is split across lines in the PHP file, it may take some trial and error to locate the code (or do you already have it, as suggested by your use of PHP code in your question?).

[CGhoST]

The contribution I am trying to install is http://addons.oscommerce.com/info/8400

 

It wants me to change one of the queries to this:

 

$products_options_query = tep_db_query("select pov.products_options_values_id, pov.products_options_values_name, pa.options_values_price, pa.price_prefix, pa.products_attributes_id, pov2po.sort_order from " . TABLE_PRODUCTS_ATTRIBUTES . " pa, " . TABLE_PRODUCTS_OPTIONS_VALUES . " pov join " . TABLE_PRODUCTS_OPTIONS_VALUES_TO_PRODUCTS_OPTIONS . " pov2po on pov.products_options_values_id=pov2po.products_options_values_id where pa.products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and pa.options_id = '" . (int)$products_options_name['products_options_id'] . "' and pa.options_values_id = pov.products_options_values_id and pov.language_id = '" . (int)$languages_id . "' and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' ORDER BY pov2po.sort_order ASC, pov.products_options_values_name ASC");

 

Because i also have Separate Pricing Per Customer it has the red part of the above query to and find_in_set('".$customer_group_id."', attributes_hide_from_groups) = 0

 

When i have one or the other of the red additions that contribution works fine. I wish to combine the two if possible.

[MrPhil]

Just stick the "ORDER BY...ASC" at the end, after "and find_in_set...= 0".

[CGhoST]

This is the new query. Please correct me if i am wrong:

 

$products_options_query = tep_db_query("select pov.products_options_values_id, pov.products_options_values_name, pa.options_values_price, pa.price_prefix, pa.products_attributes_id, pov2po.sort_order from " . TABLE_PRODUCTS_ATTRIBUTES . " pa, " . TABLE_PRODUCTS_OPTIONS_VALUES . " pov join " . TABLE_PRODUCTS_OPTIONS_VALUES_TO_PRODUCTS_OPTIONS . " pov2po on pov.products_options_values_id=pov2po.products_options_values_id where pa.products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and pa.options_id = '" . (int)$products_options_name['products_options_id'] . "' and pa.options_values_id = pov.products_options_values_id and pov.language_id = '" . (int)$languages_id . "' and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and find_in_set('".$customer_group_id."', attributes_hide_from_groups) = 0" . "ORDER BY pov2po.sort_order ASC, pov.products_options_values_name ASC");

 

On the website it comes up with the following error:

 

1064 - 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 'BY pov2po.sort_order ASC, pov.products_options_values_name ASC' at line 1

 

select pov.products_options_values_id, pov.products_options_values_name, pa.options_values_price, pa.price_prefix, pa.products_attributes_id, pov2po.sort_order from products_attributes pa, products_options_values pov join products_options_values_to_products_options pov2po on pov.products_options_values_id=pov2po.products_options_values_id where pa.products_id = '2' and pa.options_id = '4' and pa.options_values_id = pov.products_options_values_id and pov.language_id = '1' and p.products_id = pd.products_id and pd.language_id = '1' and find_in_set('0', attributes_hide_from_groups) = 0ORDER BY pov2po.sort_order ASC, pov.products_options_values_name ASC

 

Looks like attributes_hide_from_groups) = 0 and ORDER are connecting.

 

I appreciate all the time you're spending in helping me out.

[MrPhil]

You ran the "= 0" and "ORDER" together.

attributes_hide_from_groups) = 0 ORDER BY pov2po.sort_order ASC, pov.products_options_values_name ASC");

[CGhoST]

Thank you soooooo much MrPhil. That worked the way it should.

 

Really appreciate it.

 

Thanks again :D