Alternating results from 2 tables

[TEENFRONT]

Hi Everyone

Im after a lil help with alternating results from 2 tables, I currently display results from just the one.

I display the title from the movie reviews table like this

movie review 1
movie review 2
movie review 3
etc

I would like to alternate movie reviews and dvd reviews table results so it would show like this

movie review 1
dvd review 1
movie review 2
dvd review 2
etc


I know how to display items from the DB so its just the grabbing info from both DVD and Movie tables, THEN alternating the titles like above. Much appreciated if anyone can solve my lil problem.  ;D

[Psycho]

Well, there's many ways to accomplish this. Some are easy and some are hard. But, you haven't given enough details to determine which route to take.

I will make the assumption that there is no more than 1 movie review and no more than 1 dvd review for any single movie title. Given that, all you need to do is use a JOIN in your query. Something like this:

SELECT mr.*, dr.*
FROM movie_reviews mr
INNER JOIN dvd_review
  ON mr.movie_id = dr.movie_id

Your results will have a single record for each movie id with all the data from the movie_review table and the dvd_review tables (if a movie doesn't exist in on of the tables, that data will be NULL).

Of course, if my assumptions are not correct, then this may not work.

[TEENFRONT]

hey thanks for the reply.

however there are more than 1 review for each, they are different titles for the reviews, like its not just
eg - nacho libre movie & nacho libre dvd, its other seperate titles... i missed a bit off from my explanation....

its just the one table with a colum called "type" which contains either dvd or movie, its the actual column that i need to alternate, so movie,dvd,movie,dvd,movie etc. please help

[Psycho]

OK, let me paraphrase as to what I understand now. There is a single table for reviews and the table contains both DVD and movie reviews. When doing a query are you doing it for a single movie or will you display reviews for multiple movies on a page?

Actually that really doesn't matter much as in either case I would probably copy the database results to an array and then manipulate the array to get what I want. If the results are for a single movie, however, it is a much easier operation (assuming a multi-movie list would still be grouped).

Here is the first solution I came up with, there's probably something more efficient. basically sort the records by type so that DVDs are first then movie reviews. Then put all the records into a temporary arry in the same order. Lastly create a new array by adding values from the front and back of the temporary array working from the outside in.
[code]<?php
$sql = "SELECT *
        FROM reviews
        WHERE movie_id = $movieID
        ORDER BY type";
$result = mysql_query($sql);

$resultAry = array();
while ($row = mysql_fetch_assoc($result)) {
    $resultAry[] = $row;
}

$reviewAry = array();
for ($i=0; $i<(count($resultAry)/2); i++) {
    $reviewAry[] = $resultAry[i];
    if ( (count($resultAry)-$i-1) != $i) {
        $reviewAry[] = $resultAry[count($resultAry)-$i-1];
    }
}

print_r($reviewAry);
?>[/code]

[TEENFRONT]

Hi

Sorry i wasnt able to get online for a few days - il test this today, much appreciated! Thanks for taking the time to help.