Code Help - oscommerce

[dubster5]

Hi,

 

I have an oscommerce based online store which up until now i've been modifying myself despite not having any formal training in programming. I've been modifying the code and trying to learn phpalong the way but i'm currently trying to write some code which is a little beyond my current capabilities so i'm coming here for help!

 

I'm trying to write a report in admin section of the store that will look at all the products that have been sold and the customer that has purchased them. I then want the code to take this information and compare it to the reviews table to show whether the customer has reviewed the product they've purchased.

 

I decided the best way to approach it was to build the code up in sections so to start with i used this code to pull all products that have a status of dispatched from the order_products table and the orders table and put them into a list.

 

$products_query_raw = "select c.customers_id, c.customers_firstname, c.customers_lastname, op.orders_id, op.products_name, op.products_id, o.orders_status from " . TABLE_CUSTOMERS . " c, " . TABLE_ORDERS_PRODUCTS . " op, " . TABLE_ORDERS . " o where  o.orders_status = 3 and o.customers_id = c.customers_id and o.orders_id = op.orders_id";

  $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">
                <td class="dataTableContent"><?php echo '<a href="' . tep_href_link(FILENAME_CUSTOMERS, 'search=' . $products['customers_lastname'], 'NONSSL') . '">' . $products['customers_firstname'] . ' ' . $products['customers_lastname'] . '</a>'; ?></td>
                <td class="dataTableContent" align="left"><?php echo '<a href="' . tep_href_link(FILENAME_ORDERS, tep_get_all_get_params(array('oID', 'action')) . 'oID=' . $products['orders_id'] . '&action=edit') . '">' . $products['orders_id'] . '</a>'; ?></td>
                <td class="dataTableContent" align="left"><?php echo $products['products_name']; ?></td>
                <              </tr>
<?php
  }
            }

 

That seemed to work ok and pulled the correct information, but now i've got to the sticking point trying to compare this with the review table.

What i want the table to show is the products that have not yet been reviewed by the customer that purchased them. I tried modifying the code as below and it produced exactly the same output as the original code, it didn't appear to be excluding anything so just to double check i removed the ! and ran the code again (if the code worked i think i should have got all items that had been reviewed) and got a blank result.

 

$products_query_raw = "select c.customers_id, c.customers_firstname, c.customers_lastname, op.orders_id, op.products_name, op.products_id, o.orders_status from " . TABLE_CUSTOMERS . " c, " . TABLE_ORDERS_PRODUCTS . " op, " . TABLE_ORDERS . " o where  o.orders_status = 3 and o.customers_id = c.customers_id and o.orders_id = op.orders_id";

  $rows = 0;
  $products_query = tep_db_query($products_query_raw);
  while ($products = tep_db_fetch_array($products_query)) {
  

$review_query = "select reviews_id from " . TABLE_REVIEWS . " where products_id = '" . $products['products_id'] . "' and customers_id = '" . $products['customers_id'] . "'";
$review = tep_db_fetch_array($review_query);


if (!$review) {

    $rows++;

    if (strlen($rows) < 2) {
      $rows = '0' . $rows;
    }
?>
              <tr class="dataTableRow">
                <td class="dataTableContent"><?php echo '<a href="' . tep_href_link(FILENAME_CUSTOMERS, 'search=' . $products['customers_lastname'], 'NONSSL') . '">' . $products['customers_firstname'] . ' ' . $products['customers_lastname'] . '</a>'; ?></td>
                <td class="dataTableContent" align="left"><?php echo '<a href="' . tep_href_link(FILENAME_ORDERS, tep_get_all_get_params(array('oID', 'action')) . 'oID=' . $products['orders_id'] . '&action=edit') . '">' . $products['orders_id'] . '</a>'; ?></td>
                <td class="dataTableContent" align="left"><?php echo $products['products_name']; ?></td>
                              </tr>
<?php
  }
            }

 

As i said my coding skills aren't great so i could be going in completely the wrong direction or i could just have missed a really simple bit of code but either way i'd really appreciate peoples input and hopefully i can get somewhere with this idea.

 

Thanks

Scott

 

 

[dubster5]

ok so i've been having a little think about this and i'm thinking maybe i need to use a JOIN LEFT so that everything is done in one query instead of 2??

 

Is it possible to do this when i need to match 2 columns rather than 1 and if so could someone point me in the right direction as at the moment i'm not even able to do it with the 1 column!

 

Thanks

[dubster5]

ok so i'm still no closer to figuring this one out. Can anyone at all point me in the right direction and give me some helpful hints please?