Jump to content

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


sashavalentina
 Share

Recommended Posts

This is my ordered items table

image.thumb.png.d7eb2587e0397062251ccba2a4719be5.png

This is my products table

image.png.f7c22c94dd639fe79995529010cf6c46.png

This is my user table

image.png.bec82d421ee74fad29b7ddace4598dd4.png

This is my seller table

image.png.eefaeaa6b8d43b8321b7cd69dbb3cded.png

This is my category table

image.png.bd2fbe5695bd360ebd86ee9ba117cff7.png

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

image.png.18a88ee0e14a94300893477207c2a9f0.png

This is the result i got from my page

image.png.34954f3845d6880ff695df8f8a819b53.png

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 by sashavalentina
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

        image.png.59732352526934433d3c20b670b385d9.png

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.

        image.png.407e9d4cac496f0c938012485b0c2139.png      

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 by Barand
typo
  • Like 1
Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.