CODEWITHSUNDEEP
phphtmlmysql
Al Kasih
Al Kasih

Reputation: 886

Select table and Loop inside a loop

[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>

Upvotes: 2

Views: 208

Answers (4)

krynio
krynio

Reputation: 2492

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>

Upvotes: 2

JSB
JSB

Reputation: 54

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

Upvotes: 0

cuSK
cuSK

Reputation: 829

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...

Upvotes: 1

Georgi Filipov
Georgi Filipov

Reputation: 332

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'

Upvotes: 0

Related Questions