Jump to content
dev osCommerce
Forum
  • Checkout
  • Login
  • Contact Us
  • Get in touch

osCommerce

The e-commerce.

New Demo
Our Partners

php problem with producing a list

Guest

By Guest
in General Support

Recommended Posts

Guest

Guest

Posted
Posted

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
Guest

Guest

Posted
Posted

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
  • 3 weeks later...
npn2531

npn2531

Posted
Posted

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
Guest

Guest

Posted
Posted

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

Archived

This topic is now archived and is closed to further replies.

Go to topic listing
×
×
  • Create New...