why i get multiple results in phpmy admin but only get one result display on my page?

[sashavalentina]

This is my ordered items table

This is my products table

This is my user table

This is my seller table

This is my category table

I want to display the order_id, user_fullname and purchase_price, seller_fullname. I wrote the query below i got queried results in the phpmyadmin page, but when i want it to display in my page, the first result does not show, but instead it shows the follwing results after the first one. If there's only one data in that category, there will be no result display in my page. I do not know what is wrong with my query that cause this. This is the sql query that i use

 $sql = "SELECT *, o.order_id , o.purchase_price , u.id, u.user_fullname ,  p.id, p.product_title , c.id , c.category, s.id , s.seller_fullname FROM ordered_items o 
    INNER JOIN users u ON o.user_id = u.id 
    INNER JOIN sellers s ON o.seller_id = s.id 
    INNER JOIN products p ON o.product_id = p.id 
    INNER JOIN category c ON p.product_category = c.id WHERE c.category = '".$category."' ";

The $category, $month and $seller_id are the values that are pass from the previous page. (Which i already check, the value pass are all correct)

These are the codes that i use to display the data in my page

 

<?php
       $category = $_REQUEST['catid'];
        $month = $_REQUEST['month'];
        $sellerid = $_REQUEST['sellerid'];
        
        $sql = "SELECT *, o.order_id , o.purchase_price , u.id, u.user_fullname ,  p.id, p.product_title , c.id , c.category, s.id , s.seller_fullname FROM ordered_items o INNER JOIN users u ON o.user_id = u.id INNER JOIN sellers s ON o.seller_id = s.id INNER JOIN products p ON o.product_id = p.id INNER JOIN category c ON p.product_category = c.id WHERE c.category = '".$category."' ";
        $query = $conn->query($sql);
        $tot_orders = mysqli_num_rows($query);
        if (!mysqli_num_rows($query)) {
          echo '
            <div class="col-12">
              <div class="badge badge-danger">No Orders Found</div>
            </div>
          ';
        } else {
          $row = $query->fetch_assoc();
      ?>

      <div class="col-12">
        <div class="card">

          <div class="card-header">
            <h4 class="card-title"> Report for category: <b> <?php echo $category ?> </b> ( <?php echo $month ?> ) from seller: <b> <?php echo $row['seller_fullname']; ?> </b></h4> 
             <b class="float-left mb-4">Total Records: <?php echo $tot_orders; ?></b>
          </div>
            <div class="table-responsive" style="max-height: 70vh">
              <table class="table">
                <thead class="text-primary">
                  <th style=" position: sticky;top: 0; background: white";>
                    Order Id
                  </th>
                  <th style=" position: sticky;top: 0; background: white";>
                    User Name
                  </th>
                  <th style=" position: sticky;top: 0; background: white";>
                    Total Purchase
                  </th>
                  <th class="text-center" style=" position: sticky;top: 0; background: white";>
                    Action
                  </th>
                   
                </thead>
                <tbody>
                  <?php
                  if (!$tot_orders) {
                    echo '
                      <tr>
                        <td colspan="8" class="badge badge-danger">Nothing Found</td>
                      </tr>
                    ';
                  } else {
                   
                    while ($row = $query->fetch_assoc()) {
                     
                  ?>
                  <tr>
                    <td>
                      #<?php echo $row['order_id']; ?>
                    </td>
                    <td>
                      <?php echo $row['user_fullname']; ?>
                    </td>
                    <td>
                      RM<?php echo  number_format($row['purchase_price'],2); ?>
                    </td>
                     <td>
                          <a style="text-decoration: none;color: #000;" title="View Details" data-toggle="collapse" data-target="#products-details<?php echo $row['id']; ?>">
                              <i class="nc-icon nc-minimal-down" onclick="changeToggleIcon(this)"></i>
                          </a>  
                    </td>
                    <td class="text-center">          
                    </td>
                  </tr>

This is the result i got from php myadmin

This is the result i got from my page

I do not know why the result does not show as the one in phpmy admin in my page. Can i know what mistake did i made? is it the mistake i made in my query or the codes that i use to display it in my page? any help will be appreciated thank you so much

Edited April 19, 2021 by sashavalentina

[kicken]

24 minutes ago, sashavalentina said:

} else {

$row = $query->fetch_assoc();

That's what's happening to your first row.  Every time you call fetch_assoc it returns the next row.  You call it here to return the first row, but don't ever echo that row data out in the table anywhere.  Remove that call and let your loop down below fetch the first row.

Since it looks like you're calling it there so you can get the seller name, you'll have to come up with an alternative way to obtain that information or re-arrange your code so you can get the name and all the row data.

[sashavalentina]

4 minutes ago, kicken said:

That's what's happening to your first row.  Every time you call fetch_assoc it returns the next row.  You call it here to return the first row, but don't ever echo that row data out in the table anywhere.  Remove that call and let your loop down below fetch the first row.

Since it looks like you're calling it there so you can get the seller name, you'll have to come up with an alternative way to obtain that information or re-arrange your code so you can get the name and all the row data.

Can i know what is the other way to obrain my data other than fetch_assoc? Because this is the only method i know.

[mac_gyver]

just fetch all the rows of data into an appropriately named php array variable, then use the contents of that variable in your html document.  if the variable is empty, no rows of data were matched. to display the number of rows of data, use php's count() function. to get a copy of the common values for displaying the one-time heading, just reference the zero'th row. to loop over the data, use a foreach(){} loop.

[Barand]

I see in your query that you are selecting several columns named "id"

       

When you use fetch_assoc() the column names must be unique otherwise $row["id"] will be overwritten by the last one (seller id) therefore you cannot reference the user id or catgory id etc. Use column aliases to differentiate.

              

Now you can reference $row['pid'], $row['cid'] etc

When producing output with various levels of headings and subheadings and subtotals, store your data in an array that reflects the output structure. For example...

$data = [
            2 => [
                    'seller_name' => 'Sasha',
                    'orders' => [       
                                   10002 => [
                                              'lines' =>  [  
                                                              0 =>  [
                                                                        'purchase_price' => 200.00,
                                                                        'uid' => 4,
                                                                        'user_fullname' => 'yeewen'
                                                                        . . .
                                                                    ],
                                                              1 ->  [
                                                                        'purchase_price' => 200.00,
                                                                        'uid' => 4,
                                                                        'user_fullname' => 'yeewen'
                                                                        . . .
                                                                    ]
                                                          ]
                                              'order_total' => 400.00                                 
                                             ]
                                ],
                    'seller_total' = 400.00  

        ];

Then the processing becomes a set of nested loops...

foreach ($data as $seller_id => $seller_data) {
    output seller heading
    foreach ($seller_data['orders'] as $order_no => $order_data) {
        output order heading
        foreach ($order_data['lines']) {
            output line data
        }
        output order subtotal
    }
    output seller subtotal
    accumulate grand total
}
output grand total

 

Edited April 19, 2021 by Barand
typo