CODEWITHSUNDEEP
phpmysqlsqlpdoforeach
Lovelock
Lovelock

Reputation: 8075

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 . '"';
    try {
        $stmt = $db->prepare($query);
        $stmt->execute();
        $countcomments = $stmt->rowCount();
        }
        catch (PDOException $ex) 
        {
            die("Failed to run query: " . $ex->getMessage());
        }

$rows = $stmt->fetchAll();
foreach ($rows as $row):
$commentID       = $row['commentID'];
$usercommentID   = $row['userID'];
$commentusername = ucfirst($row['commentusername']);
$comment         = ucfirst($row['comment']);
$updatepostid    = $row['updatepostid'];

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


<?php endforeach; ?>

I then however want to run another query on the users database to check what rights the user has and then set the class of the comments username to that class.

That query would be e.g.

    <?php

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

$rows = $stmt->fetchAll();
foreach ($rows as $row):
$rights = $row['rights'];

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

?>
<?php endforeach; ?>

How is the proper way to go about this?

P.S. i understand that the above code will probably make people cry.

Upvotes: 0

Views: 2602

Answers (2)

Barmar
Barmar

Reputation: 780861

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());
    }

Upvotes: 4

Ray Andison
Ray Andison

Reputation: 284

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 ($rows as $row):

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

if ($rights = '1') {
    $rightscommentcolor = 'userrights1';
} else if ($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 ($rows as $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 ($rows2 as $row2):
$rights = $row2['rights'];

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

endforeach;

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

endforeach;

?>

Upvotes: -2

Related Questions