Select table and Loop inside a loop

Question

[EDIT 3]

I have tried the codes bellow, but in only gets one row from the main table.

<?php
include ("config.php");
$results = $mysqli->query
("SELECT transaction_id FROM orders_list WHERE customer_name = 'Al Kasih' ORDER BY id");            
if ($results) {
    while($obj = $results->fetch_object()) {
        echo '<div>';
        echo '<ul>';            
        echo '<li>'.$obj->transaction_id.'</li>';
                $thisthat=$obj->transaction_id;         
                $results = $mysqli->query
                            ("SELECT transaction_id, items, quantity, one_product_price FROM orders_history
                              WHERE transaction_id = '$thisthat'");         
                if ($results) {
                    while($obj = $results->fetch_object()) {
                        echo '<ul>';
                        echo '<li>'.$obj->items.'</li>';
                        echo '<li>'.$obj->quantity.'</li>';
                        echo '<li>'.$obj->one_product_price.'</li>';
                        echo '</ul>';
                    }
                }                                       
        echo '</div>';
        echo '</ul>';
    }
}
?>

In the table of order_list it will show all the order history from all customers.

 -----------------------------------------------------------------------
 id_cart         products        quantity       invoices     status
 -----------------------------------------------------------------------
  0001           this               2             $20        delivered
  0001           that               1             $20        pending
  0001           those              2             $20        approved
  0002           this               2             $20        delivered
  0002           that               1             $20        pending
  0002           those              2             $20        approved
  0003           this               2             $20        delivered
  0003           that               1             $20        pending
  0003           those              2             $20        approved
  0004           this               2             $20        delivered
  0004           that               1             $20        pending
  0004           those              2             $20        approved
 -----------------------------------------------------------------------

While in the table of member_carts, it will show only the named of customer and the their unique id_cart.

 -------------------------------------------
 id_cart         customer        payment       
 -------------------------------------------
  0001           Klaudia         creditcard     
  0002           Klaudia         paypal        
  0003           MyFather        Transfer      
  0004           MyMother        Transfer      
 -------------------------------------------

In the page account of customer named Klaudia, I want to show the all of the Order History based on the id_cart

 -----------------------------------------------------------------------
 id_cart         products        quantity       invoices     status
 -----------------------------------------------------------------------
                 this               2             $20        delivered
  0001           that               1             $20        pending
                 those              2             $20        approved
 -----------------------------------------------------------------------
                 this               2             $20        delivered
  0002           that               1             $20        pending
                 those              2             $20        approved
 -----------------------------------------------------------------------

How to make/select it in query so that the result in the page account of Klaudia will return like this. I am still learning how to join the table.

Note: cart_id would be unique because it is actually a hidden input of date/month/year/hour/second/millisecond that will be send to the database table.

////////[UPDATED]///////////////

Fisrt I am going to select the table

 SELECT order_list.id_cart,
       order_list.products,
       order_list.quantity,
       order_list.invoices,
       order_list.status,
 FROM order_list, member_carts
 WHERE order_list.id_cart = member_carts.id_cart
 AND member_carts.customer = 'Klaudia';

And then retrieve the queries:

 $id_cart  = $row['$id_cart'];
 $products = $row['$products'];
 $quantity = $row['$quantity'];
 $invoices = $row['$invoices'];
 $status   = $row['$status'];

And now the complicated part is how to loop it

<table width="780" border="1">
  <tr>
    <td width="134">id_cart</td>
    <td width="173">products</td>
    <td width="155">quantity</td>
    <td width="135">invoices</td>
    <td width="149">status</td>
  </tr>
  <tr>
    <td rowspan="3"> ?php echo $id_cart ? </td>
    <td> ?php echo $products ? </td>
    <td> ?php echo $quantity ? </td>
    <td> ?php echo $invoices ? </td>
    <td> ?php echo $status ? </td>
  </tr>
  <tr>
    <td> looping of products </td>
    <td> looping of quantity </td>
    <td> looping of invoices </td>
    <td> looping of status </td>
  </tr>
  <tr>
    <td> looping of products </td>
    <td> looping of quantity </td>
    <td> looping of invoices </td>
    <td> looping of status </td>
  </tr>

@cuSK Yes First I want to select it in order to show it via php — Al Kasih
– Al Kasih, Commented Nov 2, 2014 at 19:29
wait a minute I am preparing a php code which suits your need. — cuSK
– cuSK, Commented Nov 2, 2014 at 19:30

krynio · Accepted Answer · 2014-11-02 21:02:15Z

2

Query for get your data:

SELECT      * 
FROM        order_list ol
JOIN        member_carts mc 
    ON      ol.id_cart = mc.id_cart
WHERE       customer = 'Klaudia'
ORDER BY    ol.id_cart

// update

For calculate rows in cart you can use this code:

$cart_items_count = array();
for($result as $row) {
  if(!isset($cart_items_count[$row['cart_id']]) {
    $cart_items_count[$row['cart_id']] = 0;
  }
  $cart_items_count[$row['cart_id']]++;
}

For display data:

<table width="780" border="1">
  <tr>
    <td width="134">id_cart</td>
    <td width="173">products</td>
    <td width="155">quantity</td>
    <td width="135">invoices</td>
    <td width="149">status</td>
  </tr>
  <?php $current_cart_id = null; ?>
  <?php foreach($results as $row): ?>
  <?php
    $id_cart  = $row['$id_cart'];
    $products = $row['$products'];
    $quantity = $row['$quantity'];
    $invoices = $row['$invoices'];
    $status   = $row['$status'];
  ?>
  <tr>
    <?php if($id_cart != $current_cart_id): ?>        
      <td rowspan="<?php echo $cart_items_count[$id_cart] ?>"><?php echo $id_cart ?></td>
      <?php $current_cart_id = $id_cart; ?>
    <?php endif; ?>
    <td> ?php echo $products ? </td>
    <td> ?php echo $quantity ? </td>
    <td> ?php echo $invoices ? </td>
    <td> ?php echo $status ? </td>
  </tr>
  <?php foreach; ?>
</table>

edited Nov 2, 2014 at 21:02

answered Nov 2, 2014 at 19:32

krynio

2,49228 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

krynio Over a year ago

klaudia you should calculate number of products in each cart to display table and in loop you must have current cart_id. When cart_id changed you should display cart info.

krynio Over a year ago

In html table it will be unique, but each row is connected with cart_id . Check my answer and try this.

Al Kasih Over a year ago

Actually the cart_id is not the id of the row in the database table. cart_id would be generate by datetime in javascript in hidden input, so it would be unique. I will check your answer.

krynio Over a year ago

I update my answer. Maybe this resolve your problem.

cuSK · Accepted Answer · 2014-11-02 19:52:39Z

1

SQL query:

SELECT order_list.id_cart,
    order_list.products,
    order_list.quantity,
    order_list.invoices,
    order_list.status,
    FROM order_list, member_carts
    WHERE order_list.id_cart = member_carts.id_cart
    AND member_carts.customer = 'Klaudia';

This query will solve your problem...

answered Nov 2, 2014 at 19:52

cuSK

85911 silver badges26 bronze badges

1 Comment

Al Kasih Over a year ago

can you help me how to not loop the id_cart but only loop the others. Your SQL query works like charms. But now I need more. thanks.

Georgi Filipov · Accepted Answer · 2014-11-02 19:35:05Z

0

And variant without "join":

SELECT ol.id_cart
      ,ol.products
      ,ol.quantity
      ,ol.invoices
      ,ol.status
  FROM order_list ol
      ,member_carts mc
 WHERE ol.id_cart = mc.id_cart
  AND mc.customer = 'Klaudia'

answered Nov 2, 2014 at 19:35

Georgi Filipov

3322 silver badges18 bronze badges

Comments

Malcolm Kindermans · Accepted Answer · 2014-11-02 20:01:14Z

0

Try this query:

SELECT      * 
FROM        order_list AS ol 
WHERE       ol.id_cart IN   (
                                SELECT  id_cart 
                                FROM    member_carts 
                                WHERE   customer = 'Klaudia'
                            ) 
ORDER BY    id_cart ASC

edited Nov 2, 2014 at 20:01

Malcolm Kindermans

3371 silver badge10 bronze badges

answered Nov 2, 2014 at 19:38

JSB

545 bronze badges

Collectives™ on Stack Overflow

Select table and Loop inside a loop

4 Answers 4

4 Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related