PHP Inner Join, Using a foreach loop when one table has multiple outputs

Question

I have a situation where I have several tables that I am pulling from with an INNER JOIN. There is a one to many relationship, where the main table has one line for each park, but the photos table could have several lines for some parks. My code works in that a photo is being displayed for each park, but I can only get one to display. I suspect the problem is in the foreach loop, but I'm a little stumped. Here's the code:

try
{
    $sql = 'SELECT parks.id, parks.state, parks.name, parks.description, parks.site, parks.sname, parks.street, parks.city, parks.zip, parks.phone, comments.comment, comments.commentname, events.event, events.date, events.description2, photos.parkid, photos.type, photos.filename, photos.big FROM parks
            INNER JOIN comments INNER JOIN photos INNER JOIN events ON parks.parkid = comments.parkid and parks.parkid = photos.parkid and parks.parkid = events.parkid
            GROUP BY parks.id
            ORDER BY parks.name asc';
    $result = $pdo->query($sql);
}

catch (PDOException $e)
{
    $error = 'Error fetching data: ' . $e->getMessage();
    include 'output.html.php';
    exit();
}

//This is pulling the information from the database for display. On the foreach it will display each
//line until there are no more lines to display.    
foreach ($result as $row)
{
    $datas[] = array ('id' =>$row['id'],
                'parkid' =>$row['parkid'],
                'state' =>$row['state'], 
                'name' =>$row['name'], 
                'description' =>$row['description'], 
                'site' =>$row['site'], 
                'sname' =>$row['sname'],
                'street' =>$row['street'], 
                'city' =>$row['city'], 
                'phone' =>$row['phone'],
                'zip' =>$row['zip'],
                'commentname' =>$row['commentname'],
                'comment' =>$row['comment'],
                'event' =>$row['event'],
                'date' =>$row['date'],
                'description2' =>$row['description2'],
                'type' =>$row['type'],
                'filename' =>$row['filename'],
                'big' =>$row['big']);
}    
include 'writing.html.php';

and

<?php    
foreach ($datas as $name)
{
    if ($name['state'] === 'PA') 
    {
?>  
        <a href="#header" title="return to the top of the page">Back to top</a>
        <input type="hidden" name="id" value="' . $name['id'] . '" />
        <h1 id="name"> <?php echo ($name['name']) ?> </h1>
        <p id="descriptionlist">
            <?php echo ($name['description']) ?>
            <br />
            <ul id="link">
                <li class="l1">
                    <a href=<?php echo $name['site'] ?> target="_blank"> <?php echo $name['sname'] ?> </a>
                </li>
            </ul>
        </p>

        <h2>Location</h2>
        <div class = "loc"> 
            <p class="loct">
                <a class = "fancyImg" href="maps/<?php echo $name['id'] ?>state.gif"> <img src= "maps/<?php echo $name['id'] ?>state.gif"> </a>
                <br />
                <php echo ($name['street']) . ?>
                <br />
                <?php echo ($name['city']) .  
                    ($name['state']) .
                    ($name['zip']) ?>
                <br>
                <?php echo ($name['phone']) ?> 
                <br> <br>
            </p>
        </div>

        <h2>Trail Map</h2>
        <div class = "map">
            <p class = "mapt"> 
                Click to Enlarge
                <a class ="fancyImg" href= "/maps/<?php echo $name['id'] ?>maplink.gif">
                    <img src= "/maps/<?php echo $name['id'] ?>.gif"></a> <br> <br> 
            </p>
        </div>

        <h2>Photos</h2>
        <div class = "pho">
            <p class = "phot">
                <a class = "fancyImg" href= "/assets/indiv/<?php echo $name['big'] ?>.gif"> 
                    <img src= "<?php echo $name['filename'] ?>.gif"></a>**
                Submit <i>your</i> photos of <?php echo ($name['name']) ?> through our <ul id = "link"><li><a href="https://www.facebook.com/Ride4Wheel">Facebook Page!</li></ul></a></h3><p> Or go to our Contact Us page for information on how to e-mail us your favorite pictures! 
            </p>
        </div>

The issue at hand is in the pho div at the end here. I was hoping that $name['big'] would give me all of the items for this loop, but it only gives me the first. I'm missing something fundamental here.

The link is http://www.ride4wheel.com/new_ma.php

Answer 1

The relational database results will be always returned as rows beside the fact that your query has a relationship one to many, I think in your case you will have to loop again using your unique Id and looking for different values for 'big' field.

I don't also think that you need the foreach loop to make you results like associative array, you may need to use this instead : PDOStatement::fetchAll

Answer 2

The problem is in your query. You INNER JOIN photos which would create a row for all photos and the parks repeating for each assigned foto. The GROUP BY distincts the parks again but forces MySQL to select one of the assigned photos.

If a park doesn't have a picture, it will not be in the list at all ( INNNER JOIN = give me all which have a relation in both tables )

You could remove the GROUP BY and replace the INNER JOINs by LEFT JOINs ( give me all parks and attach the images and comments if you got any ) which would require checks in the output loop ( whats my current park, did I display this park already, display curren picture, display current comment )

The cleaner but slower solution would be to remove the joins and field for fetching images and comments so you only get the parks; then in the parks loop fetch the comments and fetch the images for the current park in two extra queries.

EDIT 1:

Just like said in the comments this is not a really good alternative since you'll fire two additional queries and run two more loops for every park you add. I want you to understand the basic problematic of a many to many relationship - db results can be only two-dimensional tables

EDIT 2:

I've fixed your code to what I suggested, watch this

<?php
try
{
    $sql = 'SELECT 
                parks.id, 
                parks.state, 
                parks.name, 
                parks.description, 
                parks.site, 
                parks.sname, 
                parks.street, 
                parks.city, 
                parks.zip, 
                parks.phone, 
                comments.comment, 
                comments.commentname, 
                events.event, 
                events.date, 
                events.description2,                
                photos.type, 
                photos.filename, 
                photos.big 
            FROM 
                parks
                    LEFT JOIN comments ON comments.parkid = parks.id
                    LEFT JOIN photos ON photos.parkid = parks.id
                    LEFT JOIN events ON events.parkid = parks.id
            ORDER BY 
                parks.name ASC';
    $result = $pdo->query($sql);
}
catch (PDOException $e)
{
    $error = 'Error fetching data: ' . $e->getMessage();
    include 'output.html.php';
    exit();
}

//This is pulling the information from the database for display. On the foreach it will display each
//line until there are no more lines to display.    
$datas = array();

foreach ( $result as $row )
{
        // we didn't add this park yet
        if ( !array_key_exists( $row['id'], $datas )
        {
            $datas[$row['id']] = array (
                'id' => $row['id'],                
                'state' => $row['state'], 
                'name' => $row['name'], 
                'description' => $row['description'], 
                'site' => $row['site'], 
                'sname' => $row['sname'],
                'street' => $row['street'], 
                'city' => $row['city'], 
                'phone' => $row['phone'],
                'zip' => $row['zip'],
                'comments' => array(),
                'photos' => array(),
                'events' => array()
            );
        }

        // if there is no comment for this park, this will be null
        if ( $row['comment'] )
        {
            $datas[$row['id']]['comments'][] = array (
                'comment' => $row['comment'],
                'commentname' => $row['commentname']
            );
        }

        // same for photos
        if ( $row['filename'] )
        {
            $datas[$row['id']]['photos'][] = array (                
                'type' => $row['type']
                'filename' => $row['filename']
                'big' => $row['big']
            );
        }

        // same for events
        if ( $row['event'] )
        {
            $datas[$row['id']]['events'][] = array (
                'event' => $row['event'],
                'date' => $row['date'],
                'description2' => $row['description2']
            );
        }
}

include 'writing.html.php';

and writing.html.php

<?php    
foreach ($datas as $park)
{
    // do you only want to display PA? 
    // then add " WHERE state = 'PA' " to your query
    if ($park['state'] === 'PA') 
    {
?>  
        <a href="#header" title="return to the top of the page">Back to top</a>
        <input type="hidden" name="id" value="' . $park['id'] . '" />
        <h1 id="name"> <?php echo ($park['name']) ?> </h1>
        <p id="descriptionlist">
            <?php echo ($park['description']) ?>
            <br />
            <ul id="link">
                <li class="l1">
                    <a href=<?php echo $park['site'] ?> target="_blank"> <?php echo $park['sname'] ?> </a>
                </li>
            </ul>
        </p>

        <h2>Location</h2>
        <div class = "loc"> 
            <p class="loct">
                <a class = "fancyImg" href="maps/<?php echo $park['id'] ?>state.gif"> <img src= "maps/<?php echo $park['id'] ?>state.gif"> </a>
                <br />
                <php echo ($park['street']) . ?>
                <br />
                <?php echo ($park['city']) .  
                    ($park['state']) .
                    ($park['zip']) ?>
                <br>
                <?php echo ($park['phone']) ?> 
                <br> <br>
            </p>
        </div>

        <h2>Trail Map</h2>
        <div class = "map">
            <p class = "mapt"> 
                Click to Enlarge
                <a class ="fancyImg" href= "/maps/<?php echo $park['id'] ?>maplink.gif">
                    <img src= "/maps/<?php echo $park['id'] ?>.gif"></a> <br> <br> 
            </p>
        </div>
        <?php if ( !empty( $park['photos'] ) ): ?>
        <h2>Photos</h2>
            <?php foreach( $park['photos'] as $photo ): ?>
                <div class = "pho">
                    <p class = "phot">
                        <a class = "fancyImg" href= "/assets/indiv/<?php echo $photo['big'] ?>.gif"> 
                            <img src= "<?php echo $photo['filename'] ?>.gif"></a>**
                        Submit <i>your</i> photos of <?php echo ($photo['name']) ?> through our <ul id = "link"><li><a href="https://www.facebook.com/Ride4Wheel">Facebook Page!</li></ul></a></h3><p> Or go to our Contact Us page for information on how to e-mail us your favorite pictures! 
                    </p>
                </div>
            <?php endforeach; ?>
        <?php endif; ?>

        <?php if ( !empty( $park['comments'] ) ): ?>
            <h2>Comments</h2>
            <?php foreach( $park['comments'] as $comment ): ?>
                <?php echo $comment['comment']; ?>
            <?php endforeach; ?>
        <?php endif; ?>

        <?php if ( !empty( $park['events'] ) ): ?>
            <h2>Events</h2>
            <?php foreach( $park['events'] as $event ): ?>
                <?php echo $event['event']; ?>
            <?php endforeach; ?>
        <?php endif; ?>
<?php 
    }
}

EDIT 3:

What you need to understand is, that you can only return a two-dimensional result table from the database. So how would you return 10 photos for one park if your park would not be repeated? Right, that's not ( cleanly ) possible. That's why you have to filter the duplicate rows and only take the photos, comments and events once you got your park.

In the code above I use the unique park id as array index so I can determine wether this park is already in the datas array or not, then add photos, events and comments.

If you do print_r( $results ) and compare this to print_r( $datas ) you'll understand the whole thing