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

Your foreach loop would not skip results. Your problem has to be the SQL query. Have you tested the query in phpmyadmin? Do you get the right results? It could be group by id that's messing you up. If you are ordering by the park name, I see no reason for the group by at all. — Codeguy007, Dec 01 '12 at 00:21
The group by is necessary because of the structure of the output: I had some help with it, so I can't explain it very well. But if I take it out, then the output just shows information for the first park in each of the sections in the output. — user1483042, Dec 01 '12 at 00:38
I'm sorry but that makes no sense. Group by is used when you want to sum the totals for different items and you have multiple entries for each item. It groups them into one sum for each item. I don't see how that would apply here and could be you problem. — Codeguy007, Dec 01 '12 at 00:55

Mehdi Karamosly · Answer 1 · 2012-12-01T00:33:10.263

2

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

edited Dec 01 '12 at 00:33

answered Dec 01 '12 at 00:28

Mehdi Karamosly

5,388
2
32
50

So, writing a second foreach loop inside the first makes sense to me, but I'm not sure how I would write it. The first foreach loop does 'big' =>$row['big']) so what would I write inside that loop (inside another loop) that would capture the second, or third, or fourth big? – user1483042 Dec 01 '12 at 00:35
If you are seeing only one result that should be because you are using a group by like @Codeguy007 mentioned, if you would like to do what I suggested you have two choices, either get rid of the group by then do the processing as I told you first foreach to go through all record inside of it another foreach in which you will populate an array where the key is the id and the value is another array that contains all 'big', (this is probably the hard way). a second way to do it (create a second query,(which is a tweak of the first one), where based on the unique id you get all different 'big') – Mehdi Karamosly Dec 03 '12 at 15:59

Michel Feldheim · Answer 2 · 2012-12-01T01:54:05.717

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

I like your explanations about why inner join may not be what he wants, but I don't like the alternative you suggest - an additional query on comments and photos for each park smacks of [the select n+1 problem](http://stackoverflow.com/questions/97197/what-is-the-n1-selects-problem) — Daniel Miladinov, Dec 01 '12 at 00:38
fixed your code to what I suggested first place, didn't run this so don't be too sad when you get horrible parse errors - this should give you an idea of what I meant — Michel Feldheim, Dec 01 '12 at 01:32
OK, I think I may have the fundamental of this now. If I take out the group by, then in my output I need to limit the display to only show the park once. From there, I can get the photos I need. But how do I do that? I'm really new at this. — user1483042, Dec 01 '12 at 01:39
just run and **read ;)** the above code, it does all you need ( you forgot to paste the comment / event part of your writing.php.html so I didn't add markup for those but just printed out one field as a example — Michel Feldheim, Dec 01 '12 at 01:47

Daniel Miladinov · Answer 3 · 2012-12-01T01:58:08.543

Unless every one of the parks has at least one comment and one photo, your query won't return rows for all parks. This is how INNER JOIN works, it only takes the intersection of all the rows that match according to the join criteria you specify.

If you want to display all parks, regardless of whether or not they have comments and/or photos, may I suggest you use LEFT JOIN instead:

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
    LEFT JOIN comments ON (parks.parkid = comments.parkid)
    LEFT JOIN photos ON (parks.parkid = photos.parkid)
    LEFT JOIN events ON (parks.parkid = events.parkid)
ORDER BY
    parks.name asc

EDIT:

I removed the GROUP BY clause since I'm pretty sure it's not what you want; you'll probably want to see every photo or comment associated with a park, not have them all arbitrarily reduced to a single row for each park.

EDIT:

Using this query should solve any problems you might have had with the original query not returning the rows you were expecting for comments and photos. You had said that the relationship was 1 park to many comments and many photos. This query should return every comment and photo associated with each park, but there will be multiple rows returned for each park. You'll need to adjust your code to compensate for this, perhaps by creating an array of park data, where each element in the array corresponds to a single park. Each element should also contain an array of comments and photos, for which there might be more than one for each park.

Each iteration over the result set should be prepared to create a new element in the park data array, or recognize that the current result set row corresponds to a park whose data you've already begun to build. Additionally, if there are any comments or photos in the current iteration, they should be added to the current (new or existing) park's array of comments and/or photos. Here's an example foreach loop of how to do this:

$parks = array();

foreach ($result as $row) {
    // Create a new element in $parks because it's not in the $parks array yet
    if (! array_key_exists($row['id'], $parks)) {
        $park = array();
        $park['id'] = $row['id'];
        $park['state'] = $row['state'];
        $park['name'] = $row['name'];
        $park['description'] = $row['description'];
        $park['site'] = $row['site'];
        $park['sname'] = $row['sname'];
        $park['street'] = $row['street'];
        $park['city'] = $row['city'];
        $park['zip'] = $row['zip'];
        $park['phone'] = $row['phone'];

        $park['comments'] = array();
        $park['photos'] = array();

        $parks[$row['id']] = $park;
    } else {
        // Otherwise, this is a park we've already seen
        $park = $parks[$row['id']];
    }

    // If there are comments in this result set row, add them
    if ($row['comment'] || $row['commentname']) {
        $park['comments'][] = array(
            'comment' => $row['comment'],
            'commentname' => $row['commentname']
        );
    }

    // If there are photos in this result set row, add them
    if ($row['type'] || $row['filename'] || $row['big']) {
        $park['photos'][] = array(
            'type' => $row['type'], 
            'filename' => $row['filename'], 
            'big' => $row['big']
        );
    }
}

This code isn't that pretty or well-factored, and it could certainly be improved. I just hastily threw it together to give you an example of how to build such a structure.

Relational data doesn't always map cleanly to hierarchical object graphs, this is the object-relational impedance mismatch.

Every park has an entry for every field. So that's not the problem. If I use LEFT JOIN, will it solve the problem of multiple photos, or is it only solving the problem of blank fields? — user1483042, Dec 01 '12 at 00:49
I've updated my answer to further address your concerns as I have understood them. — Daniel Miladinov, Dec 01 '12 at 01:06
I've read your explanation, and it looks terrific. I'm tired and burnt out. I'm going to take a break and try it again later. I'll let you know how it goes. — user1483042, Dec 01 '12 at 01:11
I tried it. But when I took GROUP BY out, even with your code above, the results were that for parks that had more than one photo, the entire park listing was duplicated - each with its own photo. That's not right. — user1483042, Dec 01 '12 at 01:36
You addressed this in the edit, but I'm really confused. How do I structure this array? — user1483042, Dec 01 '12 at 01:36
I've updated my answer to show you one possible way to do it. — Daniel Miladinov, Dec 01 '12 at 01:58
Danny: Before I dive into this I have a question. In trying different things I did take out the GROUP BY a couple of times. Whenever I did that, whether I changed anything else or not, the entire process slowed down to the point that it took nearly a full minute to display the page. Do you know why it is doing that? — user1483042, Dec 01 '12 at 02:49
That's probably because there are many more rows being returned in the result set when not grouping by park. Is this a database of all parks in the U.S.? If you only need to display parks from Pennsylvania, you should really consider adding `WHERE parks.state = "PA"` to the query to speed it up. — Daniel Miladinov, Dec 01 '12 at 03:01
@user1483042, if you found my answer and comments useful, would you please mark my answer as accepted? — Daniel Miladinov, Dec 03 '12 at 04:22

score 0 · Answer 4 · answered Dec 01 '12 at 00:33

0

To get all rows in one array instead of the first foreach loop try using <?$datas = $pdo->fetchAll();?>

answered Dec 01 '12 at 00:33

Tomi

245
1
8

It seems like I really am missing some fundamentals. My husband gave me an early Christmas present - a new subscription to Lynda.com! So I'm going back and taking the basic PHP fundamentals course. Then I'll come look at this again once I figure out what fundamental principal I am misunderstanding. Thanks all for your help. – user1483042 Dec 02 '12 at 14:32

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

4 Answers4