Loop inside of a loop?

[jarv]

Hi,

I have 2 tables of data REVIEWS and RATINGS

I would like to show a list of my reviews and a list of rating for each of my reviews

I have tried putting my second WHILE loop inside teh first one but that doesn't work, can anyone please help?

here is my code:

$SQL = "SELECT
                        *
                    FROM
                        school_reviews
                    WHERE
                        active = 1
                        AND is_deleted = 0
                        AND school_id = '" . $school_id . "'
                        ORDER BY
                        review_date DESC";
    $q->query($DB,$SQL);


    while($review = $q->getrow()):
        $reviewid = $review['review_id'];
        echo $reviewid;
        echo '<h3>'.$review['review'].'</h3>';
        

    endwhile;
    
    echo '<ul>';
    $SQL = "SELECT
                    *
                FROM
                    ratings
                WHERE
                    review_id = '".$reviewid."'";
    $q->query($DB,$SQL);
    while($rating = $q->getrow()):
        echo '<li>'.$rating['rating_name'].': '.$rating['rating'].'</li>';
    endwhile;
    echo '</ul>';

here is an example of the review I get:

ReviewID: 46
Review: testing the reviews
ReviewID: 43
Review: Because I know John is sooo handsome and he works at Cactus
ReviewID: 40
Review: iuhi

Ratings:
    Course / Course content: 5
    Teacher: 5
    Social Programme: 5
    School Facilities: 5
    Atmosphere: 5
    Location: 5
    Accommodation: 4
    Service from Cactus: 2
    Value for money: 3
    Overall Experience: 5

[Ch0cu3r]

Your don't want to use queries within loops. As the data relates, via the review_id in both tables you can use a JOIN to query both tables at the same time.

 

Example

$SQL = "
    SELECT sr.review_id,
           sr.review_date,
           sr.review,

           r.rating_name,
           r.rating
    FROM
        school_reviews sr
    LEFT JOIN 
        ratings r USING(review_id)
    WHERE
        sr.active = 1
        AND sr.is_deleted = 0
        AND sr.school_id = '" . $school_id . "'
        ORDER BY
        sr.review_date DESC";
$q->query($DB,$SQL);

// store results of query in array
$reviews = array();

while($row = $q->getrow())
{
    // get the review id
    $id = $row['review_id'];

    // group reviews by review id
    if(!isset($reviews[$id]))
    {
        $reviews[$id] = array(
            'message' => $row['review'],
            'date' => $row['review_date'],
            'ratings' => array()
        );
    }

    // group ratings by review id
    $reviews[$id]['ratings'][] = array( 
        'name' => $row['rating_name'],
        'value' => $row['rating']
    );
}

// loop over the reviews
foreach($reviews as $review_id => $review)
{
    // output review
    echo "<p>Review ID: $review_id<br />\n"
       . "Posted On: " . $review['date'] . "<br />\n"
       . "Review: " . nl2br($review['message']) . "</p>\n";

    // output ratings list for each review
    echo "<ul>\n";
    foreach($review['ratings'] as $rating)
    {
        echo "\t<li>" . $rating['name'] . ': ' . $rating['value'] . "</li>\n";
    }
    echo "</ul>\n";
}

[jarv]

excellent! thank you very much, I've been using JOINS for a couple of years now but not ARRAYS

 

I've learn a lot from this, thanks.