MySQL: Multiple Photos and Videos for News with Joins

Question

I want multiple photos and multiple videos, the main problem is that I can't get them inline if I don't use joins.

So for example, I get 2 photos a video and again a photo.

I have a parent news table and 2 secondary table news_photos and news_videos and I want to get in one query the photos and videos for the news.

Is this somehow possible?

mysql_query("
    SELECT * 
    FROM news_photos, news_videos 
    FULL JOIN news_videos 
    ON news_id = {$news_id}
    FULL JOIN news_photos
    ON news_id = {$news_id}
");

An image about the structure:

Answer 1

There's actually only a single FULL JOIN in that, since you are not involving the news table at all.

SELECT *  
FROM news_photos  
FULL JOIN news_videos  
ON news_photos.news_id=news_videos.news_id
WHERE news_photos.news_id=... OR news_videos.news_id=...

FULL JOIN is not supported by MySQL. It can be less-efficiently simulated using two LEFT JOINs and a UNION, but it's relatively rare that you actually need to. Assuming every photo and video does belong to a news, you could avoid it and get a more conventional query by bringing the news table into it:

SELECT *
FROM news
LEFT JOIN news_photos ON news_photos.news_id=news.id
LEFT JOIN news_videos ON news_videos.news_id=news.id
WHERE news_id=...

But still, this is almost certainly not what you mean! If there are multiple photos and videos for a news item, you would be effectively creating a cartesian product, where every combination of photo and video produces a row. This is the sort of combinatorial explosion you almost never want!

If you just want one of each photo and video, I suppose you could hack that into a single query using a LEFT JOIN that will always give NULL on the other side:

SELECT * FROM news_photos
LEFT JOIN news_videos ON 0
WHERE news_photos.news_id=...
UNION SELECT * FROM news_photos
RIGHT JOIN news_videos ON 0
WHERE news_videos.news_id=...

But there's really nothing to be gained by this. Don't shoehorn two separate queries (“I'd like the photos for a news, and the videos for a news”) into one. Just do it the trivial way:

SELECT * FROM news_photos
WHERE news_id=...

SELECT * FROM news_videos
WHERE news_id=...

Answer 2

i would do it using a stored procedure that had multiple select statements as follows:

http://pastie.org/1141100

drop procedure if exists list_news_photos_videos;

delimiter #

create procedure list_news_photos_videos
(
in p_news_id int unsigned
)
proc_main:begin

 select n.* from news n where n.news_id = p_news_id;

 select p.* from news_photos p where p.news_id = p_news_id order by photo_id desc;

 select v.* from news_videos v where v.news_id = p_news_id order by video_id desc;

end proc_main #

you would call this in mysql as follows:

call list_news_photos_videos(2);

then you can call the stored procedure from php (1 db call only) using mysqli as follows:

http://pastie.org/1141103

<?php
// quick and dirty demo - needs to be made more robust !!

$db = new Mysqli("localhost", "foo_dbo", "pass", "foo_db");

$sql = sprintf("call list_news_photos_videos(%d)", 2); // get all the news related data in one query

$result = $db->query($sql);

//news item
$row = $result->fetch_assoc();

echo sprintf("<h2>news item</h2>news_id = %d subject = %s <br/>", $row["news_id"], $row["subject"]);

$result->free();

//news photos
$db->next_result();
$result = $db->use_result();

echo "<h2>news photos</h2>";

while ($row = $result->fetch_assoc()){
    echo sprintf("photo_id = %d subject = %s<br/>", $row["photo_id"], $row["subject"]);
}

$result->free();

//news videos
$db->next_result();
$result = $db->use_result();

echo "<h2>news videos</h2>";

while ($row = $result->fetch_assoc()){
    echo sprintf("video_id = %d subject = %s<br/>", $row["video_id"], $row["subject"]);
}

$result->free();
$db->close();

?>