Foreach loops within a foreach loop

Question

I have a news steam being pulled from a MySQL table displaying things like headline, story and date. These news stories are generated from a PHP foreach loop and works fine.

I have two more MySQL tables, one for images and one for videos. These tables have a column called news_id that link with the news table's unique field. The idea is each news story will automatically display any videos or images attached too it.

The code for this doesn't work correctly though - e.g. if a story has 3 matching images and one video it actually outputs that video 3 times, one next to each picture. Same if there were 5 videos and one picture, it would show that picture 5 times.

The code worked ok when it was just pulling images next to the news but since I added the video table too the problem started. I think I need to rewrite the queries and foreach loop but have no idea how. My guess is that I need two foreach loops within the main foreach - one for images and one for videos.

How I want it to work is:

Headline, Date and Story pulled for news table

any photo_url's pulled from image table

any video_url's pulled from video table

then the next headline and so on. Can anyone help me structure this?

For reference this is the current query used:

$sql = 'SELECT headline, story, DATE_FORMAT(date, "%d-%M-%Y") AS Displaydate, name, logo, tpf_parks.park_id, url, alt, description, credit, location
FROM tpf_news
INNER JOIN tpf_parks ON tpf_news.park_id = tpf_parks.park_id 
LEFT JOIN tpf_images ON tpf_news.news_id = tpf_images.news_id 
LEFT JOIN tpf_videos ON tpf_news.news_id = tpf_videos.news_id 
ORDER BY date DESC' ;
$result = $pdo->query($sql);

and current php used:

$sLastStory = '';
foreach ($result AS $row)
{
    $sStory = $row['headline'] . $row['story'];
    if (strcasecmp($sStory, $sLastStory) != 0)
    {
        if (!empty($sLastStory))
        {
            print('<hr>' . PHP_EOL);
        }
        $sLastStory = $sStory;


        printf('<h2>%s</h2>' . PHP_EOL, $row['headline']);

        printf('<h3><a href="parknews.php?park_id=%s">
        %s</a> - %s</h3>' . PHP_EOL, $row['park_id'], $row['name'], $row['Displaydate']);

        printf('<p>%s</p>' . PHP_EOL, $row['story']);
    }

if(!empty($row['url'])){   
printf('
<a href="/images/%s%s.jpg" rel="lightbox[%s]" title="%s - Credit - %s" >
<img src="/images/%s%s-thumb.jpg" style="max-height: 250px; max-width: 250px" alt="%s"/></a>' . PHP_EOL, $row['url'], $row['alt'], $row['headline'],  $row['description'],$row['credit'], $row['url'], $row['alt'], $row['alt'] );
}

if(!empty($row['location'])){   
printf('<iframe width="640" height="360" src="%s" allowfullscreen></iframe>' . PHP_EOL, $row['location'] );
}

}

How can I make this code work correctly to display the data as I want? But as i said I think it all needs a total rewrite.

GROUP BY story may solve your problem. If you could provide a sample of your table structures here: sqlfiddle.com , it would be much easier to visualize a solution. Your dealing with a MANY-TO-ONE issue which will probably require some re-coding and new schema. — DevlshOne
– DevlshOne, Commented Jul 12, 2013 at 1:53
you can see the problem in action here if you scroll down to the "Vegas To Get World's Tallest Coaster?" themeparkfocus.com/news/parknews.php?park_id=999 its howing the same video 3 times next to each picture. — user2574794
– user2574794, Commented Jul 12, 2013 at 2:07
Won't 'GROUP BY story' end up only showing a max of one image and video per story? — user2574794
– user2574794, Commented Jul 12, 2013 at 2:08

Sean · Accepted Answer · 2013-07-13 00:01:42Z

You could use GROUP_CONCAT. This will return a concatenated string, which you can then explode(). This will make it so you return 1 row per headline, so you can easily group your images and videos.

$sql = 'SELECT headline, story, DATE_FORMAT(date, "%d-%M-%Y") AS Displaydate, 
        name, logo, tpf_parks.park_id, 
        GROUP_CONCAT(url) as url, GROUP_CONCAT(alt) as alt, GROUP_CONCAT(description) as description, 
        GROUP_CONCAT(credit) as credit, GROUP_CONCAT(DISTINCT location) as location
FROM tpf_news
INNER JOIN tpf_parks ON tpf_news.park_id = tpf_parks.park_id 
LEFT JOIN tpf_images ON tpf_news.news_id = tpf_images.news_id 
LEFT JOIN tpf_videos ON tpf_news.news_id = tpf_videos.news_id 
GROUP BY tpf_images.news_id, tpf_videos.news_id 
ORDER BY date DESC' ;
$result = $pdo->query($sql);

Then in your loop, use explode() on each of the columns that were concatenated -

if(!empty($row['url'])){ 
   $url=explode(',',$row['url']);
   $alt=explode(',',$row['alt']);
   $description=explode(',',$row['description']);
   $credit=explode(',',$row['credit']);

   for($i=0;$i<count($url);$i++){
     printf('
        <a href="/images/%s%s.jpg" rel="lightbox[%s]" title="%s - Credit - %s" >
        <img src="/images/%s%s-thumb.jpg" style="max-height: 250px; max-width: 250px" alt="%s"/></a>' . PHP_EOL, $url[$i], $alt[$i], $row['headline'],  $description[$i],$credit[$i], $url[$i], $alt[$i], $alt[$i] );
   }
}

if(!empty($row['location'])){  
   $location=explode(',',$row['location']);
   for($j=0;$j<count($location);$j++){
        printf('<iframe width="640" height="360" src="%s" allowfullscreen></iframe>' . PHP_EOL, $location[$j] );
   }
}

Thank you for this detailed answer. I changed my code with yours but now no images or videos show at all. The news stories show fine, just no pics or videos underneath. Any idea what I would need to change? I'm not sure if it makes a difference but on two lines of your code I had to change } for a ) to stop an error. It was this line "for($i=0;$i<count($url);$i++}{" and this line "for($j=0;$j<count($url);$j++}{". Thanks
Sorry, that was untested. Yes, there were a few errors. I have updated it above, but the errors are: 1- change for($i=0;$i<count($url);$i++}{ to for($i=0;$i<count($url);$i++){. 2- change for($j=0;$j<count($url);$j++}{ to for($j=0;$j<count($location);$j++){. 3- add as url,as alt, as description, as credit, as location after each of the group_concats. Hopefully that will work, if not let me know.
thank you again for being so helpful. Picture and videos are showing up again and it's an improvement on before but still not right. On my original code if a story had 3 images and 1 video it would display image 1, video 1, image 2, video 1, image 3, video 1. Your code now has all 3 images together as it should be but underneath the same video is displaying 3 times! It's the same if there are more videos than images - it duplicates them so there's an equal number of both. Any ideas?
if it helps this shows your code in action themeparkfocus.com/test/parknews.php?park_id=999 scroll to the Vegas To Get World's Tallest Coaster story to see the video duped 3 times. Thank you
Try echoing out $row['location'] to see if it is showing the video three times.

Collectives™ on Stack Overflow

Foreach loops within a foreach loop

1 Answer 1

8 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Related