Jump to content
Latest News: (loading..)
dix4life

ORDERLIST - SORT ON PRODUCT NAME

Recommended Posts

Hello,

 

I've trying to sort on product name with this script. But everything i try the output always is sort on order number and i want to sort on product name. Please can anyone rewrite this script so the output is sort on product name? Thanks in advanced.

 

<?php

$orders_query = tep_db_query("SELECT * from " . TABLE_ORDERS . " WHERE (orders_status = '1' OR orders_status = '2')");

while($orders_rows = tep_db_fetch_array($orders_query))

{

$products_query = tep_db_query("SELECT * FROM " . TABLE_ORDERS_PRODUCTS . " WHERE orders_id = '" . $orders_rows['orders_id'] . "' ORDER BY products_name");

while($products_rows = tep_db_fetch_array($products_query))

{

$ordStatus_query = tep_db_query("SELECT * from " . TABLE_ORDERS_STATUS . " WHERE orders_status_id = '" . $orders_rows['orders_status'] . "' ");

$ordStatus_rows = tep_db_fetch_array($ordStatus_query);

echo "<tr>";

echo "<td class=dataTableContent>" . $ordStatus_rows["orders_status_name"] . "</td>";

echo "<td class=dataTableContent>" . $orders_rows["orders_id"] . "</td>";

echo "<td class=dataTableContent>" . $products_rows["products_quantity"] . "</td>";

echo "<td class=dataTableContent>" . $products_rows["products_name"] . "</td>";

echo "<td class=dataTableContent>" . $products_rows["products_price"] . "</td>";

echo "</tr>";

}

}

?>

 

Best regards,

 

Martin

Share this post


Link to post
Share on other sites

you need to get everything combined into one query like this.

Note, this is not tested code, I hope I didn't make any typo's ...

 

$products_query = tep_db_query("SELECT * from " . TABLE_ORDERS . " o join ". TABLE_ORDERS_PRODUCTS . " op using (orders_id) join ".TABLE_ORDERS_STATUS ." os on (o.orders_status = os.orders_status_id.)
WHERE (o.orders_status = '1' OR o.orders_status = '2')
ORDER BY op.products_name ");
while($products_rows = tep_db_fetch_array($products_query))
{
echo "<tr>";
echo "<td class=dataTableContent>" . $products_rows["orders_status_name"] . "</td>";
echo "<td class=dataTableContent>" . $products_rows["orders_id"] . "</td>";
echo "<td class=dataTableContent>" . $products_rows["products_quantity"] . "</td>";
echo "<td class=dataTableContent>" . $products_rows["products_name"] . "</td>";
echo "<td class=dataTableContent>" . $products_rows["products_price"] . "</td>";
echo "</tr>";
}
?>

Edited by bruyndoncx

KEEP CALM AND CARRY ON

I do not use the responsive bootstrap version since i coded my responsive version earlier, but i have bought every 28d of code package to support burts effort and keep this forum alive (albeit more like on life support).

So if you are still here ? What are you waiting for ?!

 

Find the most frequent unique errors to fix:

grep "PHP" php_error_log.txt | sed "s/^.* PHP/PHP/g" |grep "line" |sort | uniq -c | sort -r > counterrors.txt

Share this post


Link to post
Share on other sites

Thanks for your reply! The script you send has been tested and get 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 ')WHERE (o.orders_status = '1' OR o.orders_status = '2')ORDER BY op.products_name' at line 1

 

SELECT * from orders o join orders_products op using (orders_id) join orders_status os on (o.orders_status = os.orders_status_id.)WHERE (o.orders_status = '1' OR o.orders_status = '2')ORDER BY op.products_name

Share this post


Link to post
Share on other sites

remove the decimal point after order_status_id

make sure there is a space before WHERE and ORDER BY


KEEP CALM AND CARRY ON

I do not use the responsive bootstrap version since i coded my responsive version earlier, but i have bought every 28d of code package to support burts effort and keep this forum alive (albeit more like on life support).

So if you are still here ? What are you waiting for ?!

 

Find the most frequent unique errors to fix:

grep "PHP" php_error_log.txt | sed "s/^.* PHP/PHP/g" |grep "line" |sort | uniq -c | sort -r > counterrors.txt

Share this post


Link to post
Share on other sites

Yes, Now it is working! Thanks!

 

Only he shows both languages now. The Dutch version and english version of the order status.

 

STATUS ORDERID QUANTITY PRODUCTNAME PRICE

Payment Received 21887 1 q13952-2 - Licht roze 30.5372

Betaling ontvangen 21887 1 q13952-2 - Licht roze 30.5372

 

This has to be only one row off course.

 

Further i want to add the size attribute. I have an contribution for attributes. They are stored in the TABLE orders_products_attributes. Then the field orders_id can be linked to get the right row and the field products_options_values shows the size name (like Small, Medium, Large ed.)

 

Hope this is not asked to much. This will help me a lot if this all is working.

 

Thanks in advanced!!

Edited by burt
removed link to posters cpanel !

Share this post


Link to post
Share on other sites

add in the where clause

 

and os.language_id = 4

 

if dutch is your 4th language (english, french, german being the default first 3).

 

For product attributes, add to the from clause

 

left join ". TABLE_ORDERS_PRODUCTS_ATTRIBUTES . " opa on (op.orders_products_id = opa.orders_products_id)

Edited by bruyndoncx

KEEP CALM AND CARRY ON

I do not use the responsive bootstrap version since i coded my responsive version earlier, but i have bought every 28d of code package to support burts effort and keep this forum alive (albeit more like on life support).

So if you are still here ? What are you waiting for ?!

 

Find the most frequent unique errors to fix:

grep "PHP" php_error_log.txt | sed "s/^.* PHP/PHP/g" |grep "line" |sort | uniq -c | sort -r > counterrors.txt

Share this post


Link to post
Share on other sites

Is there also an solution for getting the categorie name where the products is in? Carine Bruyndoncx - I'm very very thankfull for your work today :)

Share this post


Link to post
Share on other sites

add to from claus

 

join " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c using (products_id)

join " . TABLE_CATEGORIES_DESCRIPTION . " cd using (categories_id)

 

add to where clause

 

and cd.language_id = 4


KEEP CALM AND CARRY ON

I do not use the responsive bootstrap version since i coded my responsive version earlier, but i have bought every 28d of code package to support burts effort and keep this forum alive (albeit more like on life support).

So if you are still here ? What are you waiting for ?!

 

Find the most frequent unique errors to fix:

grep "PHP" php_error_log.txt | sed "s/^.* PHP/PHP/g" |grep "line" |sort | uniq -c | sort -r > counterrors.txt

Share this post


Link to post
Share on other sites

Thanks! This is also working. The only problem now is that some products are in multiple categories. This means in the orderlist the products shows up 2 or even 3 times because of the double categorie. Like this:

 

STATUS ORDER# CATEGORIE QUANTITY PRODUCTNAME SIZE PRICE

In behandeling 21897 Jeans 1 q12938-1 - Zwart 38 € 28.88

In behandeling 21897 Leggings 1 q12938-1 - Zwart 38 € 28.88

In behandeling 21897 Broeken 1 q12938-1 - Zwart 38 € 28.88

 

Is it possible to get it like this:

 

STATUS ORDER# CATEGORIE QUANTITY PRODUCTNAME SIZE PRICE

In behandeling 21897 Jeans/Leggings/Broeken 1 q12938-1 - Zwart 38 € 28.88

 

Again thanks for the help!

Edited by dix4life

Share this post


Link to post
Share on other sites

that is possible with the mysql group_concat function

 

so the select statement needs to be extended with;

group_concat(categories_name)

 

or better should become

 

select distinct orders_status_name, orders_id, products_quantity, products_name, products_options_values, group_concat(categories_name)

 

and add group by statement

 

group by products_name, orders_status_name, orders_id, products_quantity, products_options_values


KEEP CALM AND CARRY ON

I do not use the responsive bootstrap version since i coded my responsive version earlier, but i have bought every 28d of code package to support burts effort and keep this forum alive (albeit more like on life support).

So if you are still here ? What are you waiting for ?!

 

Find the most frequent unique errors to fix:

grep "PHP" php_error_log.txt | sed "s/^.* PHP/PHP/g" |grep "line" |sort | uniq -c | sort -r > counterrors.txt

Share this post


Link to post
Share on other sites

Thanks, But i don't think i get it. Can you please place it for me in the script:

 

<?php

$products_query = tep_db_query("SELECT * from " . TABLE_ORDERS . " o join ". TABLE_ORDERS_PRODUCTS . " op using (orders_id) join " . TABLE_ORDERS_STATUS . " os on (o.orders_status = os.orders_status_id) left join ". TABLE_ORDERS_PRODUCTS_ATTRIBUTES . " opa on (op.orders_products_id = opa.orders_products_id) join " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c using (products_id) join " . TABLE_CATEGORIES_DESCRIPTION . " cd using (categories_id) WHERE (o.orders_status = '1' OR o.orders_status = '2') AND os.language_id = '4' AND cd.language_id = 4 ORDER BY op.products_name");

while($products_rows = tep_db_fetch_array($products_query))

{

 

echo "<tr>";

echo "<td class=dataTableContent style='border: 1px solid #C0C0C0;'>" . $products_rows["orders_status_name"] . "</td>";

echo "<td class=dataTableContent style='border: 1px solid #C0C0C0;'>" . $products_rows["orders_id"] . "</td>";

echo "<td class=dataTableContent style='border: 1px solid #C0C0C0;'>" . $products_rows["categories_name"] . "</td>";

echo "<td class=dataTableContent style='border: 1px solid #C0C0C0;'>" . $products_rows["products_quantity"] . "</td>";

echo "<td class=dataTableContent style='border: 1px solid #C0C0C0;'>" . $products_rows["products_name"] . "</td>";

echo "<td class=dataTableContent style='border: 1px solid #C0C0C0;'>" . $products_rows["products_options_values"] . "</td>";

$english_format_number = number_format($products_rows["products_price"], 2, '.', '');

echo "<td class=dataTableContent style='border: 1px solid #C0C0C0;'>€ " . $english_format_number . "</td>";

echo "<td class=dataTableContent style='border: 1px solid #C0C0C0;'><BR></td>";

echo "</tr>";

}

?>

Share this post


Link to post
Share on other sites

a litle test page , added 'as categories_name' to the select list

added o.orders_id, since orders_id was ambiguous in the select list

surrounded the whole thing with table tags

<?php
include('includes/application_top.php');
$products_query = tep_db_query("
select distinct orders_status_name, o.orders_id, products_quantity, products_name,
products_options_values, products_price, group_concat(categories_name) as categories_name
 from " . TABLE_ORDERS . " o
 join ". TABLE_ORDERS_PRODUCTS . " op using (orders_id)
 join " . TABLE_ORDERS_STATUS . " os on (o.orders_status = os.orders_status_id)
 left join ". TABLE_ORDERS_PRODUCTS_ATTRIBUTES . " opa on (op.orders_products_id = opa.orders_products_id)
 join " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c using (products_id)
 join " . TABLE_CATEGORIES_DESCRIPTION . " cd using (categories_id)
 WHERE (o.orders_status = '1' OR o.orders_status = '2') AND os.language_id = '4' AND cd.language_id = 4
 group by products_name, orders_status_name, o.orders_id, products_quantity,  products_options_values
 ORDER BY op.products_name");
echo '<table>';
while($products_rows = tep_db_fetch_array($products_query))
{

echo "<tr>";
echo "<td class=dataTableContent style='border: 1px solid #C0C0C0;'>" . $products_rows["orders_status_name"] . "</td>";
echo "<td class=dataTableContent style='border: 1px solid #C0C0C0;'>" . $products_rows["orders_id"] . "</td>";
echo "<td class=dataTableContent style='border: 1px solid #C0C0C0;'>" . $products_rows["categories_name"] . "</td>";
echo "<td class=dataTableContent style='border: 1px solid #C0C0C0;'>" . $products_rows["products_quantity"] . "</td>";
echo "<td class=dataTableContent style='border: 1px solid #C0C0C0;'>" . $products_rows["products_name"] . "</td>";
echo "<td class=dataTableContent style='border: 1px solid #C0C0C0;'>" . $products_rows["products_options_values"] . "</td>";
$english_format_number = number_format($products_rows["products_price"], 2, '.', '');
echo "<td class=dataTableContent style='border: 1px solid #C0C0C0;'>€ " . $english_format_number . "</td>";
echo "<td class=dataTableContent style='border: 1px solid #C0C0C0;'><BR></td>";
echo "</tr>";
}
echo '</table>';
?>


KEEP CALM AND CARRY ON

I do not use the responsive bootstrap version since i coded my responsive version earlier, but i have bought every 28d of code package to support burts effort and keep this forum alive (albeit more like on life support).

So if you are still here ? What are you waiting for ?!

 

Find the most frequent unique errors to fix:

grep "PHP" php_error_log.txt | sed "s/^.* PHP/PHP/g" |grep "line" |sort | uniq -c | sort -r > counterrors.txt

Share this post


Link to post
Share on other sites

Nu verwacht ik toch echt wel wat feedback op mijn winkeltje :P


KEEP CALM AND CARRY ON

I do not use the responsive bootstrap version since i coded my responsive version earlier, but i have bought every 28d of code package to support burts effort and keep this forum alive (albeit more like on life support).

So if you are still here ? What are you waiting for ?!

 

Find the most frequent unique errors to fix:

grep "PHP" php_error_log.txt | sed "s/^.* PHP/PHP/g" |grep "line" |sort | uniq -c | sort -r > counterrors.txt

Share this post


Link to post
Share on other sites

Hallo,

 

Ik had hem er zo ingeplaatst zoals jij hem nu ook hebt gemaakt. Echter werkte het bij mij niet. Ik zal ergens wel een typ foutje tussen hebben zitten. Ook had ik stiekem al even je site bekeken. Ziet er goed uit zeg. Daar zag ik ook aan dat we ook gewoon Nederlands konden praten echter voor de andere leden dacht ik ik blijf wel in het engels. Super bedankt voor de hulp en dit gaat ons gigantisch veel tijd schelen in alle producten! Echt super super! Ik heb je even toegevoegd in de favorieten. Mocht er wat zijn waar ik bij kan helpen graag. Dat zal niet op mysql gebied zijn wellicht hahaha ;) Gewoon alles uit hobby geleerd? Erg knap!

 

Groetjes Martin

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×