Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New to the CF scene
    Join Date
    Feb 2013
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query is creating one record for every image attached to a row in for each loop

    I have this query which pulls a list of rides attached to a particular theme park and shows their details and any attached photos (the photos are pulled from a separate table and connected by 'ride_id'). Below is the query

    Code:
        $park_id = $_GET['park_id'];
        $query2="SELECT * FROM  `tpf_rides` LEFT JOIN tpf_images
        ON tpf_rides.ride_id=tpf_images.ride_id 
        WHERE tpf_rides.park_id = $park_id AND `type` LIKE '%Roller Coaster%' ORDER BY `name` ASC";
        $result2 = $pdo->query($query2);
    The problem is when I list the results pulled from this query it creates duplicate rows for a ride, one for each image.

    What is want is:

    Code:
        Ride 1 - Type <br>  
        Manufactured by xxx, Opened xxxx <br>
        image1 image2 
        
        Ride 2 - Type <br>  
        Manufactured by xxx, Opened xxxx <br>
        image1 image2 image3 etc
    but what I currently have is


    Code:
        Ride 1 - Type <br>  
            Manufactured by xxx, Opened xxxx <br>
            image1 
        
        Ride 1 - Type <br>  
            Manufactured by xxx, Opened xxxx <br>
            image2 
        
        Ride 2 - Type <br>  
            Manufactured by xxx, Opened xxxx <br>
            image1 
    
        Ride 2 - Type <br>  
            Manufactured by xxx, Opened xxxx <br>
            image2
    
        Ride 2 - Type <br>  
            Manufactured by xxx, Opened xxxx <br>
            image3


    Below is the for each loop I'm using. What would I need to alter on the query or loop to have this work correctly?

    Code:
        <?php foreach ($result2 as $row2): ?>
        
        <h2 style="display:inline;"><?php echo $row2['name']; ?></h2><h3 style="display:inline;"> - <?php echo $row2['type']; ?></h3> 
        <h3>Manufactured by <?php echo $row2['make']; ?>, Opened <?php echo $row2['opened']; ?> </h3>
        <img border="0" src="<?php echo $row2['url']; ?>" style="max-height:250px; max-width:250px;" >
        
        <br>
        
        <?php endforeach; ?>
    I tried grouping the results by name by that meant only one image was showing up. I'm still at the early stages of understanding PHP and MySQL so a detailed answer would really help. Thank you.

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    This is normal behaviour with the joined records. MySQL itself does have a group_concat operation, but it isn't standard SQL so I don't recommend its use. Instead, simply control the data based on what *has* been displayed:
    PHP Code:
    $sLastRide '';
    foreach (
    $result2 AS $row)
    {
        
    $sRide $row['name'] . $row['type'];
        if (
    strcasecmp($sRide$sLastRide) != 0)
        {
            if (!empty(
    $sLastRide))
            {
                print(
    '</div>' PHP_EOL);
            }
            
    $sLastRide $sRide;
            print(
    '<div>' PHP_EOL);
            
    printf('<h2 style="display:inline;">%s</h2><h3 style="display:inline;"> - %s</h3>' PHP_EOL$row['name'], $row['type']);
            
    printf('<h3>Manufactured by %s, Opened %s</h3>' PHP_EOL$row['make'], $row['opened']);
        }
        
    printf('<img src="%s" style="max-height: 250px; max-width: 250px" alt=""/>' PHP_EOL$row['url']);
    }
    print(
    '</div>'); 
    I don't know what PK you are using, so I just made one up given what you have here. I assumed that ride name and type were unique together (maybe even just the name or the type is unique).
    When I run it with a simple array such as:
    PHP Code:
    $result2 = array(
        array(
    'name' => 'Ride 1''type' => 'Type''make' => 'xxx''opened' => 'xxx''url' => 'image1'),
        array(
    'name' => 'Ride 1''type' => 'Type''make' => 'xxx''opened' => 'xxx''url' => 'image2'),
        array(
    'name' => 'Ride 2''type' => 'Type''make' => 'xxx''opened' => 'xxx''url' => 'image1'),
        array(
    'name' => 'Ride 2''type' => 'Type''make' => 'xxx''opened' => 'xxx''url' => 'image2'),
        array(
    'name' => 'Ride 2''type' => 'Type''make' => 'xxx''opened' => 'xxx''url' => 'image3'),
    ); 
    then I get the results of:
    Code:
    <div>
    <h2 style="display:inline;">Ride 1</h2><h3 style="display:inline;"> - Type</h3>
    <h3>Manufactured by xxx, Opened xxx</h3>
    <img src="image1" style="max-height: 250px; max-width: 250px" alt=""/>
    <img src="image2" style="max-height: 250px; max-width: 250px" alt=""/>
    </div>
    <div>
    <h2 style="display:inline;">Ride 2</h2><h3 style="display:inline;"> - Type</h3>
    <h3>Manufactured by xxx, Opened xxx</h3>
    <img src="image1" style="max-height: 250px; max-width: 250px" alt=""/>
    <img src="image2" style="max-height: 250px; max-width: 250px" alt=""/>
    <img src="image3" style="max-height: 250px; max-width: 250px" alt=""/>
    </div>
    which appear to match what you are looking for.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #3
    New to the CF scene
    Join Date
    Feb 2013
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Smile

    Thank you so much. I am really new to PHP and MySQL and would never have worked that out. The code you wrote works perfectly and does exactly what I wanted. Thanks again!


  •  

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •