Suppress duplicated results

[tHud]

Hello,

I have 3 tables for clients, orders and the products they ordered.

 

Here are the tables...

Clients
CLIENT_ID
CLIENT_NAME

Orders
o_id

client_id
o_number
o_date

o_notes

 

Products
o_id
qty
name

 

I am getting the results I need with this query...

SELECT
    clients.CLIENT_ID, clients.CLIENT_NAME,
    orders.o_id, orders.client_id, orders.o_date, orders.o_number, orders.o_notes,
    products.o_id, products.qty, products.name
    FROM clients, orders, products
    WHERE clients.CLIENT_ID = orders.client_id
    AND orders.o_id = products.o_id
    AND orders.o_id = $theOrder

..and this is the php I use to display it...

while ($row = $result->fetch_assoc()) {

    echo "<h2>$row[CLIENT_NAME]</h2>";
    echo '<label>Date</label><input type="text" value="'.timechange($row['o_date']).'" readonly><br>';
    echo '<label>Order No.</label><input type="text" value="'.$row['o_number'].'" readonly><br>';
    echo '<label>Notes</label><input type="text" value="'.$row['o_notes'].'" readonly>';
    echo '<label>Product Qty.</label><input type="text" value="'.$row['qty'].'" readonly><br>';
    echo '<label>Product Name</label><input type="text" value="'.$row['name'].'" readonly><br>';
}

However - rather predictably I am getting three forms printed out as the results from MySQL look like this...

 

Is it possible to somehow get my form to display the results, like this...

 

I hope I'm explaining myself well enough - and thank you for reading.

Edited July 4, 2014 by tHud

[mac_gyver]

see my reply at the following link on how to do something once when a value changes - http://forums.phpfreaks.com/topic/289403-issue-showing-results-1-row-keeps-alternating/?do=findComment&comment=1483684

 

also, about your tables. the table you show for Products (i would name it order_details) should have an id (auto-increment), o_id (order id), product id, and quantity. the product name should be gotten by joining with your product/item table (whatever you have named it) via the product id.

 

i would also recommend using alias names for the tables in your query. your current query would become -

SELECT
    c.CLIENT_ID, c.CLIENT_NAME,
    o.o_id, o.client_id, o.o_date, o.o_number, o.o_notes,
    p.o_id, p.qty, p.name
    FROM clients c, orders o, products p
    WHERE c.CLIENT_ID = o.client_id
    AND o.o_id = p.o_id
    AND o.o_id = $theOrder

[tHud]

Hi Mac - thanks for the advice which I am taking on board.

I am redesigning the table structure - hopefully, this is what you meant?

 

 

I'm afraid I am not well enough versed in php to understand the terminology in your other post (about data sections).

 

In this line...

// there is a previous data section, close it here...

 

I see that the data has been 'ouput'(?) previously - but I'm not sure what is meant by closing the data section.

[tHud]

Sorry - I can't edit the above post and I've just realised it won't work, as it can't track multiple products.

Ignore please - I'll have to rethink this. 

[mac_gyver]

a data section would be whatever context you are building/outputting a block of related data as - html (close a </div> for example), table (fill a short/incomplete row for example) , form, xml, array, json... 'closing' it would be whatever syntax you need to add to end the previous section to finish it and make it valid. you may in fact not need to do anything, the pseudo code is general purpose.

[kicken]

closing the data section just refers to generating the HTML to close the section on the page holding the information. For example closing out table or div tags.

 

An alternative way to process things is to build a multi-dimensional array out of the results. The way you do that is first decide on an array format and then choose unique values you can use to index various levels of the array.

 

In this case your array would end up having two levels. The first level would be the orders, and the second the products associated with each order, something like:

 

array(
  'o_id' => array(
    // order details
    , 'products' => array(
      //list of products
    )
  )
  , //additional orders
)

So to process that you need the unique values of o_id for the top level. As you loop the results you'd test if an entry for $row['o_id'] exists in the order list and if not create it, then add the product for the current row to the order indicated by $row['o_id']. Something like:

$orders = array();
while ($row=$query->fetch()){
   $oid = $row['o_id'];
   if (!isset($orders[$oid])){
      $orders[$oid] = array(
         'clientName' => $row['CLIENT_NAME']
	 , 'date' => $row['date']
	 , // rest of the fields
	 , 'products' => array() //empty array to hold products
      );
   }
   
   //Add the product to the order
   $orders[$oid]['products'][] = array(
      'name' => $row['productName']
      , 'qty' => $row['qty']
   );
}

Now $orders will contain a list of each order, and within each order you have the products array listing each product. You can use that to build your final HTML output:

<?php foreach ($orders as $o): ?>
<div class="order">
  <h1><?=htmlentities($o['clientName'])?></h1>
  <p>On: <?=htmlentities($o['date'])?></p>
  <p>Products ordered:</p>
  <ul>
  <?php foreach ($o['products'] as $p): ?>
     <li><?=htmlentities($p['name'])?>; qty: <?=htmlentities($p['qty']);?></li>
  <?php endforeach; ?>
  </ul>
</div>
<?php endforeach; ?>

[tHud]

Thank you Mac & Kicken

On this occasion I think that the array solution would be the best option for me.

 

Could I just ask (hopefully) one last question?

 

I have a fourth table now that tracks the number/ cost etc of items in the product table...

Array
(
    [3] => Array
        (
            [clientId] => 3
            [clientName] => Joe
            [o_date] => 2014-07-01
            [o_number] => 11112
            [o_notes] => blah blah
            [details] => Array
                (
                    [0] => Array
                        (
                            [qty] => 10
                            [cost] => 19.5
                        )

                    [1] => Array
                        (
                            [qty] => 2
                            [cost] => 250.5
                        )

                )

            [products] => Array
                (
                    [0] => Array
                        (
                            [prod_id] => 1
                            [product] => door
                        )

                    [1] => Array
                        (
                            [prod_id] => 2
                            [product] => window
                        )

                )

        )

)

I would like to output the sub-arrays details and products but I'm failing with the nested for-each loops.

The results are getting duplicated.

<table border="1">

<?php foreach ($orders as $o): ?>

	<?php foreach ($o['details'] as $d): ?>
	<?php foreach ($o['products'] as $p): ?>


		<tr>
		<td>  Qty: <?=htmlentities($d['qty'])?>; </td>
		<td>  id: <?=htmlentities($p['prod_id'])?>; </td>
		<td>  Name: <?=htmlentities( $p['product']);?></td>
		</tr>

		<?php endforeach; ?>
		<?php endforeach; ?>


<?php endforeach; ?>


</table>

I have beeen Googling on how to do this, but can't find a solution.

 

Thank you again.

 

 

[Zane]

I would imagine that you could just GROUP BY o_id.

SELECT
    c.CLIENT_ID, c.CLIENT_NAME,
    o.o_id, o.client_id, o.o_date, o.o_number, o.o_notes,
    p.o_id, p.qty, p.name
    FROM clients c, orders o, products p
    WHERE c.CLIENT_ID = o.client_id
    AND o.o_id = p.o_id
    AND o.o_id = $theOrder
    GROUP BY o.o_id

And you definitely need to redesign your database schema and use JOINs,  otherwise, you are multiplying the number of rows from each table you have after FROM clients, orders, products
 

[mac_gyver]

to include the price, you would still only have two levels in the array and two foreach() loops, because the price information would be retrieved in the query.

 

price would just become another value in the second-level array, using the example array build logic from kicken's post -
 

//Add the product to the order
$orders[$oid]['products'][] = array(
'name' => $row['productName']
, 'qty' => $row['qty']

,'cost' => $row['cost']
);

 

[tHud]

That's the best news you could have given me.

 

Thank you so much (everybody) for your assistance.