On a product page I have a dropdown that lists current colour options associated to product page.
In this example, the product page SKU is 250E and it available in:
GREEN
BLACK
If the customer selects GREEN, then I want to run a MySQL command that will change the data to show GREEN values first based in the custom_order value shown below.
The start value should overide the other data items and then it should retain custom_order values. The custom_order field has letters like c1, c2 (they will always be at the bottom)
Colour Table
============
ID COLOURID NAME
-------------------------------------
2 5 BLACK
3 6 GREEN
Product Table
=============
ID SKU PICTURE URL COLOURID CUSTOM_ORDER
-----------------------------------------------------------------
22 250E cdn_hash_1.jpg 5 1
23 250E cdn_hash_2.jpg 5 2
24 250E cdn_hash_3.jpg 5 3
225 250E cdn_hash_4.jpg 5 4
226 250E cdn_hash_5.jpg 6 5
227 250E cdn_hash_6.jpg 6 6
325 250E cdn_hash_c1.jpg - c1
426 250E cdn_hash_c2.jpg - c2
527 250E cdn_hash_c3.jpg - c3
SELECT * FROM products WHERE ORDER BY custom_order DESC
Now, I want to do the following:
SELECT * FROM products WHERE ORDER BY custom_order AND START VALUE = '6'
Therefore, no matter what, the c1, c2 remain intact, result would be:
22 250E cdn_hash_5.jpg 6 5
23 250E cdn_hash_6.jpg 6 6
24 250E cdn_hash_1.jpg 5 1
225 250E cdn_hash_2.jpg 5 2
226 250E cdn_hash_3.jpg 5 3
227 250E cdn_hash_4.jpg 5 4
325 250E cdn_hash_c1.jpg - c1
426 250E cdn_hash_c2.jpg - c2
527 250E cdn_hash_c3.jpg - c3
This is the answer I was looking for. I hope I am not downvoted for this but I managed to resolve this.
Let me explain again simpler terms. MySQL ORDER BY + Start with..
MySQL:
id | name |
------------
1 | Joe |
2 | Craig |
3 | Shawn |
4 | Ryan |
5 | Seth |
PHP:
$a = mysql_query("SELECT * FROM table_name ORDER BY name DESC");
what I want to do though is, I want to start at id: 3, so it should output:
3,4,5,1,2
SELECT id, name
FROM table_name
ORDER BY id < 3, id
Result:
3 Shawn
4 Ryan
5 Seth
1 Joe
2 Craig
You can use FIELD
, eg
SELECT *
FROM products
ORDER BY FIELD(`order`, 6) ASC
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With