Member Avatar for Passy

<?php

                            $sql    =" SELECT * FROM `comments` ";
                            $result = mysqli_query($db, $sql);

                            while($row = mysqli_fetch_assoc($result)){
                            $comment_id              = $row['comment_id'];
                            $comment_company_id      = $row['comment_company_id'];
                            $comment_name            = $row['comment_name'];
                            $comment_content         = $row['comment_content'];
                            $comment_email           = $row['comment_email'];
                            $comment_status          = $row['comment_status'];
                            $comment_date            = $row['comment_date'];

                echo "<tr>";
                echo "<td>$comment_id   </td>";
                echo "<td>$comment_name </td>";
                echo "<td>$comment_content</td>";
                echo "<td>$comment_email</td>";
                echo "<td>$comment_status  </td>";

                                $sql           =" SELECT * FROM `waste_collection_companies_info_table` WHERE `company_id`='$comment_company_id'";
                                $result        = mysqli_query($db, $sql);

                                while($row    = mysqli_fetch_assoc($result)){
                                $company_id   = $row['company_id'];
                                $company_name = $row['company_name'];

                                echo "<td><a href ='../post.php?p_id=$company_id'>$company_name</a></td>";
                                }

                echo "<td>$comment_date </td>";

                echo "<td><a href='companies.php?source=edit_companies&p_id='>approve</a></td>";
                echo "<td><a href='companies.php?delete='>disapprove</a></td>";
                echo "<td><a href='companies.php?delete='>delete</a></td>";

                echo "</tr>";
                }

                ?> </tbody> </table> <?php
  • 2 Contributors
  • 1 Reply
  • 117 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by pritaeas

Dani AI

Generated

The behaviour described (three rows exist but only one is printed) is caused by the inner query replacing the outer query resource. called this out: reusing the same mysqli result variable for both loops clobbers the outer fetch. Two practical, safe approaches follow — a minimal fix that keeps the current structure, and a preferred single-query approach that avoids the nested-query N+1 problem.

Use distinct result/row variables and check for query errors. Free the inner result after use and cast IDs when concatenating into SQL to avoid injection:

$commentsRes = mysqli_query($db, $commentsSql);
if (!$commentsRes) { error_log(mysqli_error($db)); }

while ($commentsRow = mysqli_fetch_assoc($commentsRes)) {
    $companySql = "SELECT company_id, company_name FROM waste_collection_companies_info_table WHERE company_id = " . intval($commentsRow['comment_company_id']);
    $companyRes = mysqli_query($db, $companySql);
    $companyName = 'Unknown';
    if ($companyRes) {
        $companyRow = mysqli_fetch_assoc($companyRes);
        if ($companyRow) { $companyName = $companyRow['company_name']; }
        mysqli_free_result($companyRes);
    }
    // output using $commentsRow and $companyName (escape HTML when printing)
}
mysqli_free_result($commentsRes);

A more robust and faster solution is a single JOIN that returns comment and company data together. This eliminates nested queries and the resource-overwrite issue:

$sql = "
SELECT c.comment_id, c.comment_name, c.comment_content, c.comment_email, c.comment_status, c.comment_date,
       w.company_id, w.company_name
FROM comments c
LEFT JOIN waste_collection_companies_info_table w
  ON c.comment_company_id = w.company_id
ORDER BY c.comment_date DESC
";
$res = mysqli_query($db, $sql);
while ($row = mysqli_fetch_assoc($res)) {
    // use $row['company_name'] and escape output with htmlspecialchars(...)
}

Additional notes: avoid SELECT * in production, validate/escape IDs (intval or prepared statements), check mysqli_query return values, and HTML-escape any user content to prevent XSS. Also confirm action links include the intended IDs (use urlencode/intval) so approve/delete links actually target the correct rows.

Member Avatar for pritaeas 
$sql    =" SELECT * FROM `comments` ";
$result = mysqli_query($db, $sql);
while($row = mysqli_fetch_assoc($result))
{
    $sql           =" SELECT * FROM `waste_collection_companies_info_table` WHERE `company_id`='$comment_company_id'";
    $result        = mysqli_query($db, $sql);
    while($row    = mysqli_fetch_assoc($result))
    {
    }
}

The above is a summary of your code. As you can see you are reusing your $result in both while loops. When the inner while is done, the outer while will no longer be valid. Solution: use different variables for your inner and outer while.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.