Guest Posted March 23, 2010 Share Posted March 23, 2010 Who is able to help me with a php/mysql problem? This is what I like to produce: A list of the last order of a product ordered by a customer The customer bought 49 products in 28 orders. 19 products are unique. so the list should show orders_products.product_name, orders_products.products_model, orders_products.products_quantity, orders_products.final_price, orders.order_id (latest) What did I do? I took admin/stats_products_purchased.php as base and altered the db-query. Original query $products_query_raw = "select p.products_id, p.products_ordered, pd.products_name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd where pd.products_id = p.products_id and pd.language_id = '" . $languages_id. "' and p.products_ordered > 0 group by pd.products_id order by p.products_ordered DESC, pd.products_name"; new query $products_query_raw = "select o.customers_id, o.orders_id, op.orders_id , op.products_id, op.products_model, op.products_name, op.products_quantity , op.final_price from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_PRODUCTS . " op on (o.orders_id = op.orders_id) where o.customers_id = '" . $cID . " ' group by op.products_id order by op.products_id "; The $cID is send from admin/customers.php Now the list shows the unique products bought by the customer but...... the $products_split gives me more items than unique products sold and the order_id is the first order_id instead of the latest order. The $products_split gives me on the first page 'show 1 to 25 from 49 products' with only 19 items on it. on page 2 no products are listed but the $products_split gives me 'show 26 to 49 from 49 products' Please help out because it I don't know how to solve this. FYI I use PHP5 / MYSQL 5 The complete program is: <?php /* $Id: stats_products_purchased.php 2010-03-01 00:52:16Z hpdl $ osCommerce, Open Source E-Commerce Solutions http://www.oscommerce.com Copyright (c) 2003 osCommerce Released under the GNU General Public License */ require('includes/application_top.php'); // Get the customers_id sent from admin/customers.php $cID = tep_db_prepare_input($HTTP_GET_VARS['cID']); ?> <!doctype html public "-//W3C//DTD HTML 4.01 Transitional//EN"> <html <?php echo HTML_PARAMS; ?>> <head> <meta http-equiv="Content-Type" content="text/html; charset=<?php echo CHARSET; ?>"> <title><?php echo TITLE; ?></title> <link rel="stylesheet" type="text/css" href="includes/stylesheet.css"> <script language="javascript" src="includes/general.js"></script> </head> <body marginwidth="0" marginheight="0" topmargin="0" bottommargin="0" leftmargin="0" rightmargin="0" bgcolor="#FFFFFF"> <!-- header //--> <?php require(DIR_WS_INCLUDES . 'header.php'); ?> <!-- header_eof //--> <!-- body //--> <table border="0" width="100%" cellspacing="2" cellpadding="2"> <tr> <td width="<?php echo BOX_WIDTH; ?>" valign="top"><table border="0" width="<?php echo BOX_WIDTH; ?>" cellspacing="1" cellpadding="1" class="columnLeft"> <!-- left_navigation //--> <?php require(DIR_WS_INCLUDES . 'column_left.php'); ?> <!-- left_navigation_eof //--> </table></td> <!-- body_text //--> <td width="100%" valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="2"> <tr> <td><table border="0" width="100%" cellspacing="0" cellpadding="0"> <tr> <td class="pageHeading"><?php echo HEADING_TITLE; ?></td> <td class="pageHeading" align="right"><?php echo tep_draw_separator('pixel_trans.gif', HEADING_IMAGE_WIDTH, HEADING_IMAGE_HEIGHT); ?></td> </tr> </table></td> </tr> <tr> <td><table border="0" width="100%" cellspacing="0" cellpadding="0"> <tr> <td valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="2"> <tr class="dataTableHeadingRow"> <td class="dataTableHeadingContent"><?php echo TABLE_HEADING_NUMBER; ?></td> <td class="dataTableHeadingContent"><?php echo TABLE_HEADING_PRODUCTS; ?></td> <td class="dataTableHeadingContent" ><?php echo 'Products_model'; ?> </td> <td class="dataTableHeadingContent" ><?php echo 'Products_ordered'; ?> </td> <td class="dataTableHeadingContent" ><?php echo 'Final_price'; ?> </td> <td class="dataTableHeadingContent" ><?php echo 'Order_id'; ?> </td> </tr> <?php if (isset($HTTP_GET_VARS['page']) && ($HTTP_GET_VARS['page'] > 1)) $rows = $HTTP_GET_VARS['page'] * MAX_DISPLAY_SEARCH_RESULTS - MAX_DISPLAY_SEARCH_RESULTS; $products_query_raw = "select o.customers_id, o.orders_id, op.orders_id , op.products_id, op.products_model, op.products_name, op.products_quantity , op.final_price from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_PRODUCTS . " op on (o.orders_id = op.orders_id) where o.customers_id = '" . $cID . " ' group by op.products_id order by op.products_id "; $products_split = new splitPageResults($HTTP_GET_VARS['page'], MAX_DISPLAY_SEARCH_RESULTS, $products_query_raw, $products_query_numrows); $totaal=0; $rows = 0; $products_query = tep_db_query($products_query_raw); while ($products = tep_db_fetch_array($products_query)) { $rows++; if (strlen($rows) < 2) { $rows = '0' . $rows; } ?> <tr class="dataTableRow" onmouseover="rowOverEffect(this)" onmouseout="rowOutEffect(this)" onclick="document.location.href='<?php echo tep_href_link(FILENAME_CATEGORIES, 'action=new_product_preview&read=only&pID=' . $products['products_id'] . '&origin=' . FILENAME_ORDERS_PRODUCTS . '?cID='. $cID . '&page=' . $HTTP_GET_VARS['page'], 'NONSSL'); ?>'"> <td class="dataTableContent"><?php echo $rows; ?>.</td> <td class="dataTableContent"><?php echo '<a href="' . tep_href_link(FILENAME_CATEGORIES, 'action=new_product_preview&read=only&pID=' . $products['products_id'] . '&origin=' . FILENAME_ORDERS_PRODUCTS . '?cID='. $cID . '&page=' . $HTTP_GET_VARS['page'], 'NONSSL') . '">' . $products['products_name'] . '</a>'; ?></td> <td class="dataTableContent" align="right"><?php echo $products['products_model']; ?> </td> <td class="dataTableContent" align="right"><?php echo $products['products_quantity']; ?> </td> <td class="dataTableContent" align="right"><?php echo $products['final_price']; ?> </td> <td class="dataTableContent" align="right"><?php echo $products['orders_id']; ?> </td> </tr> <?php } ?> </table></td> </tr> <tr> <td colspan="3"><table border="0" width="100%" cellspacing="0" cellpadding="2"> <tr> <td class="smallText" valign="top"><?php echo $products_split->display_count($products_query_numrows, MAX_DISPLAY_SEARCH_RESULTS, $HTTP_GET_VARS['page'], TEXT_DISPLAY_NUMBER_OF_PRODUCTS); ?></td> <td class="smallText" align="right"><?php echo $products_split->display_links($products_query_numrows, MAX_DISPLAY_SEARCH_RESULTS, MAX_DISPLAY_PAGE_LINKS, $HTTP_GET_VARS['page'],'cID='.$cID); ?> </td> </tr> </table></td> </tr> </table></td> </tr> </table></td> <!-- body_text_eof //--> <!-- footer //--> <?php require(DIR_WS_INCLUDES . 'footer.php'); ?> <!-- footer_eof //--> </body> </html> <?php require(DIR_WS_INCLUDES . 'application_bottom.php'); ?> Link to comment Share on other sites More sharing options...
NodsDorf Posted March 23, 2010 Share Posted March 23, 2010 Isn't this info available simply by clicking on the customer's name in the admin section. Then hitting the order button on the left? Link to comment Share on other sites More sharing options...
Guest Posted March 24, 2010 Share Posted March 24, 2010 Isn't this info available simply by clicking on the customer's name in the admin section. Then hitting the order button on the left? If the customer has only 1 order it aint a problem. But if he calls and asked for the product he ones bought but doesn't know when it was and has more than a few orders you can't just simple run through all his orders. I'm not busy keeping me busy. I like to make such a list for a reason. Link to comment Share on other sites More sharing options...
Guest Posted April 10, 2010 Share Posted April 10, 2010 Noone has a clue how to solve this? Link to comment Share on other sites More sharing options...
npn2531 Posted April 10, 2010 Share Posted April 10, 2010 Noone has a clue how to solve this? I'm sure many people on this forum have a clue how to do this. To produce a list of a customer's orders sorted, or limited by one item like this is not a trivial piece of coding. However, perhaps you could look at account_history_info.php. Study the php on that page. Get a good grasp of what is going on there. The code there is not too far off from what you need, it is displaying a customer's order sorted by the call in account_history.php. First, you could for example, change the query in account_history_info.php so it simply produces a list of the customers orders without having a post from account_history.php. Since that list includes the items ordered, further change the query in account_history_info.php to limit the orders displayed to those that contain the item you are referencing. Then create an interface that allows you to enter the item you wish to look for, an interface that posts the product as a variable that can be used by the query you altered above. That's it! Once you become familiar with the php and structure of OSC, it probably won't take you more than a few days to do this, test and put it up on your website, Oscommerce site: OSC to CSS, http://addons.oscommerce.com/info/7263 -Mail Manager, http://addons.oscommerce.com/info/8120 Link to comment Share on other sites More sharing options...
Guest Posted April 10, 2010 Share Posted April 10, 2010 I'm sure many people on this forum have a clue how to do this. To produce a list of a customer's orders sorted, or limited by one item like this is not a trivial piece of coding. However, perhaps you could look at account_history_info.php. Study the php on that page. Get a good grasp of what is going on there. The code there is not too far off from what you need, it is displaying a customer's order sorted by the call in account_history.php. First, you could for example, change the query in account_history_info.php so it simply produces a list of the customers orders without having a post from account_history.php. Since that list includes the items ordered, further change the query in account_history_info.php to limit the orders displayed to those that contain the item you are referencing. Then create an interface that allows you to enter the item you wish to look for, an interface that posts the product as a variable that can be used by the query you altered above. That's it! Once you become familiar with the php and structure of OSC, it probably won't take you more than a few days to do this, test and put it up on your website, Thanks George for your reply. Only that my real problem is not the list it self but the product_split that gives me the wrong values. Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.