This are my db table:

But my query only get 1 row for each table like this:

As you can see, there are 2 tables for 1003 because it has 2 rows. It should be only one (1) table of 1003 with 2 rows. How do I fix this? EXPECTED RESULT:

            // Attempt select query execution
            $query = "SELECT model, brand_code FROM smartphone GROUP BY model";
            if($result = mysqli_query($db, $query))
            {
                if(mysqli_num_rows($result) > 0)
                {
                    while($row = mysqli_fetch_array($result))
                    {
                    ?>
                    <?php echo $row["brand_code"]?>
                    <table id="table_stock" >
                      <thead>
                          <tr>
                              <th>Model</th>
                          </tr>
                      </thead>
                      <tbody>
                          <tr>
                              <td><?php echo $row["model"]?></td>
                          </tr>
                      </tbody>
                    </table><br>
                    <?php 
                    }
                    /// Free result
                    mysqli_free_result($result);
                } 
                else
                {
                    echo "<td class='no_record' colspan='7'>No records found.</td>";
                }
            } 
            else
            {
                echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
             }
            

CodePudding user response：

you need additional loop. Also in the first query you need to use group by codes.

        $query = "SELECT model, brand_code FROM smartphone GROUP BY brand_code";
        if($result = mysqli_query($db, $query))
        {
            if(mysqli_num_rows($result) > 0)
            {
                while($row = mysqli_fetch_array($result))
                {
 ?>
                <?php echo $row["brand_code"]?>
                <table id="table_stock" >
                  <thead>
                      <tr>
                          <th>Model</th>
                      </tr>
                  </thead>
                  <tbody>

 <?php
                    if ($result1 = mysqli_query($db, "SELECT DISTINCT model, brand_code FROM smartphone WHERE brand_code={$row["brand_code"]}")) 
                    {
                       while ($row1 = mysqli_fetch_array($result1))
                       {
                ?>
                      <tr>
                          <td><?php echo $row1["model"]?></td>
                      </tr>
                <?php 
                      }
                     mysqli_free_result($result1);
                  }
 ?>
                  </tbody>
                </table><br>
 <?php
               }

                /// Free result
                mysqli_free_result($result);
            } 
            else
            {
                echo "<td class='no_record' colspan='7'>No records found.</td>";
            }
        } 
        else
        {
            echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
         }

CodePudding user response：

You have at least 5 problems here,

[edit: problem 1 removed & changed sample based on extended answer]

Inside your while { ... } loop, you're printing an entire table, when you should only be printing the <tr>...</tr> part there. This is what causes additional table(s).

And 3rd problem: your "no_record" line is a loose <td>. Not only isn't it inside the table (which is covered in problem #2), it's also not wrapped with a <tr>.

4th problem: You're randomly printing the echo $row["brand_code"] outside of the table.

5th problem: you're printing raw data from the database as if it is valid html, it more than likely is not. it has to be probably encoded with htmlentities/htmlspecialchars.

Quick & dirty fixed version:

function tableOpen($row) {
    printf(  '<h1>%s</h1>', htmlentities($row["brand_code"]) );
    echo '<table id="table_stock" >';
    echo '<thead>';
    echo '<tr>';
    echo '<th>Model</th>';
    echo '</tr>';
    echo '</thead>';
    echo '<tbody>';
}
function tableClose() {
    echo '</tbody>';
    echo '</table><br>';
}
// Attempt select query execution
$query = "SELECT model, brand_code FROM smartphone ORDER BY brand_code";
$lastBrand = null;
if ($result = mysqli_query($db, $query)) {
    if (mysqli_num_rows($result) > 0) {
        if ($lastBrand !== $row["brand_code"] && !is_null($lastBrand)) tableClose();
        if ($lastBrand !== $row["brand_code"]) tableOpen($row);
        $lastBrand = $row["brand_code"];
        while ($row = mysqli_fetch_array($result)) {
            echo   '<tr>';
            printf(  '<td>%s</td>', htmlentities($row["model"]) );
            echo   '</tr>';
        }
        tableClose();
        /// Free result
        mysqli_free_result($result);
    } else {
        echo '<p >No records found.</p>';
    }
} else {
    echo "ERROR: Not able to execute \$query: <br>" . htmlentities($query) . '<br>' . htmlentities(mysqli_error($link));
}