Run A Second Query Inside A Foreach Loop?

Need to run 2 queries inside a foreach but cant do it without errors. So, i have this for showing comments: $query = 'SELECT * FROM comments WHERE updatepostid = '' . $postID . '''

Solution 1:

Use a single query with a join. Also, since you're using PDO, you should use parametrized queries rather than concatenating strings.

$query = "SELECT * FROM comments c
          JOIN users u ON u.id = c.userID
          WHERE updatepostid = :updatepostid";
try {
    $stmt = $db->prepare($query);
    $stmt->execute(array(':updatepostid' => $postID));
    }
catch (PDOException $ex) 
    {
        die("Failed to run query: " . $ex->getMessage());
    }

Solution 2:

You can join the tables in select as mentioned:

<?php$query = "SELECT * FROM comments c
          JOIN users u ON u.usercommentID = c.userID
          WHERE updatepostid = :updatepostid";
    try {
        $stmt = $db->prepare($query);
        $stmt->execute();
        $countcomments = $stmt->rowCount();
        }
        catch (PDOException $ex) 
        {
            die("Failed to run query: " . $ex->getMessage());
        }

$rows = $stmt->fetchAll();
foreach ($rowsas$row):

$commentID       = $row['commentID'];
$usercommentID   = $row['userID'];
$commentusername = ucfirst($row['commentusername']);
$comment         = ucfirst($row['comment']);
$updatepostid    = $row['updatepostid'];

if ($rights = '1') {
    $rightscommentcolor = 'userrights1';
} elseif ($rights = '5') {
    $rightscommentcolor = 'userrights5';
}

echo"<div class="textcomment"><a class='$rightscommentcolor'>$commentusername:</a>&nbsp; $comment</div>";

endforeach;

?>

You also could insert the second loop within the first and assign separate variables to that second loop so that it does not conflict with the first as below, but joining would be a better option:

<?php$query = 'SELECT * FROM comments WHERE updatepostid = "' . $postID . '"';
    try {
        $stmt = $db->prepare($query);
        $stmt->execute();
        $countcomments = $stmt->rowCount();
        }
        catch (PDOException $ex) 
        {
            die("Failed to run query: " . $ex->getMessage());
        }

$rows = $stmt->fetchAll();
foreach ($rowsas$row):

$commentID       = $row['commentID'];
$usercommentID   = $row['userID'];
$commentusername = ucfirst($row['commentusername']);
$comment         = ucfirst($row['comment']);
$updatepostid    = $row['updatepostid'];

$query2 = 'SELECT * FROM users WHERE usercommentID = "' . $usercommentID . '"';
    try {
        $stmt2 = $db->prepare($query2);
        $stmt2->execute();
        }
        catch (PDOException $ex2) 
        {
            die("Failed to run query: " . $ex2->getMessage());
        }

$rows2 = $stmt2->fetchAll();
foreach ($rows2as$row2):
$rights = $row2['rights'];

if ($rights = '1') {
    $rightscommentcolor = 'userrights1';
} elseif ($rights = '5') {
    $rightscommentcolor = 'userrights5';
}

endforeach;

echo"<div class="textcomment"><a class='$rightscommentcolor'>$commentusername:</a>&nbsp; $comment</div>";

endforeach;

?>