Duplicated Results

GreenMamba

Hi guys

I'm new to PHP and after some trial and error managed to get the results from the following code, except that some of the results are being duplicated.

<?php
      $notify_query = tep_db_query("select products_id, products_name from " . TABLE_PRODUCTS_NOTIFICATIONS . " where customers_id = '" . (int)$customer_id . "'");
       while ($notify_products = tep_db_fetch_array($notify_query)) {
       $check_notify = substr($notify_products['products_name'], 0, strpos($notify_products['products_name'], "#"));

   $shipment_query = tep_db_query("select products_name, products_date_available from " . TABLE_SHIPMENTS . " order by products_name");
    while ($shipment_products = tep_db_fetch_array($shipment_query)) {
    $check_shipment = substr($shipment_products['products_name'], 0, strpos($shipment_products['products_name'], "#"));
      if ($check_notify == $check_shipment) {
    $list_products = '<tr class="listing_even"><td class="listing_txt">' . $shipment_products['products_name'] . '</td><td class="listing_txt" align="right">Received ' . $shipment_products['products_date_available'] . '</td></tr>';
      }
   }
   echo $list_products;
 }
?>

I've tried a number of variations of foreach and array_unique but can't get it to work right.

Can anybody fix this for me, please?

bpat1434

Which results are being duplicated? The shipment table, or the list of products?

Try adding a DISTINCT to the front of your query(ies) to remove duplicates.

GreenMamba

Thanks

The Shipment Table.

DISTINCT doesn't work because I'm comparing 2 tables and pulling data from the Shipment Table where it equals the Notifications Table (after strpos).
The Shipment Table contains no duplicate entries, it's simply duplicating the results.

From what I can tell, it looks like if there is 10 entries in the Notifications Table it generates 10 results (duplicating some) regardless of whether there are less than 10 matches.

Unless there is some way to use DISTINCT on

if ($check_notify == $check_shipment)

bpat1434

Okay, so you're joining information from the shipments table onto the information in the notifications table. Why not execute one query and loop through it:

$query = "SELECT n.products_id, n.products_name, s.products_date_available
FROM " . TABLE_NOTIFICATIONS . " AS n
LEFT JOIN " . TABLE_SHIPMENTS . " AS s
    ON  n.products_name = s.products_name
WHERE n.customer_id = '" . (int)$customer_id . "'";

GreenMamba

Maybe I left out a crucial bit of info.

The products_name in the tables are not always exactly the same. Sometimes a name like X-Men #100 needs to be compared to X-Men #99 (thats why the strpos before I can compare the two values), so the way I understand it, I have to make 2 seperate queries (but I could be wrong). I tried combining the queries and using the strpos function in the query itself, but that keeps giving me an error and I couldn't figure out how to make it work.

laserlight

The products_name in the tables are not always exactly the same. Sometimes a name like X-Men #100 needs to be compared to X-Men #99

That sounds like you need to tweak your database schema. The "#N" should not be part of the product name, but perhaps as a separate "issue number" or something.

bpat1434

Yes, that is a crucial bit of information.

Now, if you restructure your database to be how Laserlight suggested, the above query would work 😉 In all seriousness, you should think about the structure of your database. Right now you kind of have it working against you in the sense that you're crippling it. You can't exactly join tables easily, so you have to run multiple queries and manually join the information. This is perfect stuff for a database to do.

Now, what you could (though I suggest not) do is to use the LOCATE() function inside a SUBSTRING() function....

$query = "SELECT n.products_id, n.products_name, s.products_date_available
FROM " . TABLE_NOTIFICATIONS . " AS n
LEFT JOIN " . TABLE_SHIPMENTS . " AS s
    ON n.products_name = SUBSTRING(s.products_name FROM 0 FOR LOCATE('#', s.products_name))
WHERE n.customer_id='" . (int) $customer_id . "'
ORDER BY s.products_name ASC";

GreenMamba

Thanks guys

Yes, I've been considering changing the database. The only thing that's been holding me back is the 80000 products that I would have to upload again.

Looks like that LOCATE function might work, except that at the moment it is returning the values in TABLE_NOTIFICATIONS instead of TABLE_SHIPMENTS.

Rudi

GreenMamba

Woohoo!

Thanks guys. I split the products_name collumn in TABLE_SHIPMENTS into name and number and used that in the where statement for shipment_query.

Now she works like a charm.

Rudi