将两个不同的查询组合成一个合并的foreach

Question

I'll try and explain this best I can. I have two queries pulling data from MySQL, one pulls data on park reviews, the other pulls data on ride reviews. Both queries are sorted by Review Date.

What I want on the web page is one list of reviews sorted by Review Date so park reviews and ride reviews will be mixed together in the one list.

Almost like two foreach loops running, one for each query but taking it in turns based on the date.

These are the two queries: For Rides

$query4= 'SELECT review_id, review, tpf_reviews_rides.user, DATE_FORMAT(date_ridden, "%d %M %Y") AS Date_Ridden, DATE_FORMAT(review_date, "%d %M %Y") AS Review_Date, tpf_rides.name AS ride_name, rating, tpf_parks.name  AS park_name, country
FROM tpf_reviews_rides  

INNER JOIN tpf_rides ON tpf_reviews_rides.ride_id = tpf_rides.ride_id
INNER JOIN tpf_ratings_rides ON tpf_reviews_rides.rating_link = tpf_ratings_rides.rating_id  
INNER JOIN tpf_parks ON tpf_reviews_rides.park_id = tpf_parks.park_id

ORDER BY review_date DESC, review_id DESC LIMIT '.$start_from.' , '.$limit.'';

For Parks:

$query5= 'SELECT review_id, review, tpf_reviews_parks.user, DATE_FORMAT(date_visited, "%d %M %Y") AS Date_Visited, DATE_FORMAT(review_date, "%d %M %Y") AS Review_Date, rating, tpf_parks.name, country
FROM tpf_reviews_parks  

INNER JOIN tpf_ratings_parks ON tpf_reviews_parks.rating_link = tpf_ratings_parks.rating_id  
INNER JOIN tpf_parks ON tpf_reviews_parks.park_id = tpf_parks.park_id

ORDER BY review_date DESC, review_id DESC LIMIT '.$start_from.' , '.$limit.'';

and this is how separately I display the results: For Rides:

foreach ($res4 AS $row4) { 
            printf('
            <h2 style="display:inline;">%s</h2> <h3 style="display:inline;">- %s, %s</h3>
            <h3>Rating - %sStars</h3>
            <h4>Submitted by %s on %s</h4>
            <p>%s</p>' . PHP_EOL, $row4['ride_name'], $row4['park_name'], $row4['country'], $row4['rating'], (htmlspecialchars($row4['user'], ENT_QUOTES, 'UTF-8')), $row4['Review_Date'], nl2br(htmlspecialchars($row4['review'], ENT_QUOTES, 'UTF-8')));

            if(!empty($row4['Date_Ridden'])){
            printf('<p>Date ridden: %s</p>' . PHP_EOL, $row4['Date_Ridden']);
            }

            printf('<a class="bloglink" href="#top">Back to Top</a>
            <hr>');

        }


    For PArks:

foreach ($res5 AS $row5) { 
    printf('
    <h2 style="display:inline;">%s</h2> <h3 style="display:inline;">- %s</h3>
    <h3>Rating - %sStars</h3>
    <h4>Submitted by %s on %s</h4>
    <p>%s</p>' . PHP_EOL, $row5['name'], $row5['country'], $row5['rating'], (htmlspecialchars($row5['user'], ENT_QUOTES, 'UTF-8')), $row5['Review_Date'], nl2br(htmlspecialchars($row5['review'], ENT_QUOTES, 'UTF-8')));

    if(!empty($row4['Date_Ridden'])){
    printf('<p>Date Visited: %s</p>' . PHP_EOL, $row4['Date_Visited']);
    }

    printf('<a class="bloglink" href="#top">Back to Top</a>
    <hr>');

}

To make it trickier this page is paginated. Looking at it, because the two queries are so similar if there is a way to combine at query level it would be best and help keep the paginate code in tact. Anyone have any suggestions on how I can achieve this?

Thanks

Answer 1

You can use UNION to merge together two select statements.

Note that if your tables are so similar, it may be better to merge them (not in the query, but the actual tables). I do not see any fields that are not completely the same in your tpf_reviews_rides compared to tpf_reviews_parks. Add a column denoting the review type (ride or park) and you should be set. It will simplify your queries and your processing logic.

SELECT
    type,
    review_id,
    review,
    user,
    Date_Event,
    Review_Date,
    ride_name,
    rating,
    park_name,
    country
FROM (
    SELECT
        'ride' as type,
        review_id,
        review,
        tpf_reviews_rides.user,
        DATE_FORMAT(date_ridden, "%d %M %Y") AS Date_Event,
        DATE_FORMAT(review_date, "%d %M %Y") AS Review_Date,
        tpf_rides.name AS ride_name,
        rating,
        tpf_parks.name AS park_name,
        country
    FROM
        tpf_reviews_rides  
        INNER JOIN tpf_rides ON tpf_reviews_rides.ride_id = tpf_rides.ride_id
        INNER JOIN tpf_ratings_rides ON tpf_reviews_rides.rating_link = tpf_ratings_rides.rating_id  
        INNER JOIN tpf_parks ON tpf_reviews_rides.park_id = tpf_parks.park_id

    UNION

    SELECT
        'park' as type,
        review_id,
        review,
        tpf_reviews_parks.user,
        DATE_FORMAT(date_visited, "%d %M %Y") AS Date_Event,
        DATE_FORMAT(review_date, "%d %M %Y") AS Review_Date,
        NULL as ride_name,
        rating,
        tpf_parks.name AS park_name,
        country
    FROM
        tpf_reviews_parks  
        INNER JOIN tpf_ratings_parks ON tpf_reviews_parks.rating_link = tpf_ratings_parks.rating_id  
        INNER JOIN tpf_parks ON tpf_reviews_parks.park_id = tpf_parks.park_id
) AS reviews
ORDER BY
    review_date DESC,
    review_id DESC
LIMIT ' . $start_from . ', ' . $limit;