link two tables

[zebedeee]

Hi there,

I am new to this and have a little issue I cannot quite work out.

I have two tables

Table name: gallery
gallery_id, title, description, featured, created

Table name: gallery_images
image_id, gallery_id, image, caption, image_order

I want to list the contents of the gallery table and call the images related to each field from the gallery images table.

$gallery = new DataConn; // pre set to connect to db
$gallery->query("SELECT gallery.gallery_id, title, description, featured, image FROM gallery LEFT JOIN gallery_images ON gallery_images.gallery_id = gallery.gallery_id AND image_order = 0");

while ($rs_gallery = mysql_fetch_array($gallery->result))
{
    
    echo $rs_gallery['title'];
    echo $rs_gallery['image'];
?> 

This displays the title of each entry in the database and only one image associated with it.

How can I loop through the images held in gallery_images and display them under each title?

Any help would be great thank you,

[eval(BadCode)]

Maybe you meant to ORDER BY the order... and not join on it. Also, if the column names are identical for a join you can write USING (column) instead of having to write out super long ON clauses (which can get confusing when you start joining lots of tables).

$gallery = new DataConn; // pre set to connect to db
$gallery->query("
     SELECT 
        gallery.gallery_id, title, description, featured, image 
     FROM gallery 
     LEFT JOIN gallery_images USING (gallery_id)
     ORDER BY gallery_id ASC, image_order ASC");

while ($rs_gallery = mysql_fetch_assoc($gallery->result)) echo $rs_gallery['title'], $rs_gallery['image'];
?> 

I also think if you use USING mysql can prove to itself that the gallery_id is no longer ambiguous and you no longer need to specify which table it belongs to. I might be wrong about that.

Cheers

[zebedeee]

Hi there,

Not sure what you mean by order by as I need to call info from one table that has the same id as a field from the main gallery table.

I managed to fix it and here is the code for all who will find it of some help:-

<h1>gallery</h1>
<?php 
    $gallery = new DataConn;
    $gallery->query("SELECT gallery.gallery_id, title, description, featured, image FROM gallery LEFT JOIN gallery_images ON gallery_images.gallery_id = gallery.gallery_id AND image_order = 0");

    while ($rs_gallery = mysql_fetch_array($gallery->result))
    {
        $id = $rs_gallery['gallery_id'];
        //echo $id;
        $images = new DataConn();
        $images->query("SELECT image, caption FROM gallery_images WHERE gallery_id = ".$id." ORDER BY image_order");
?>
<div class="case-container">
<div id="gallery">
<?php
    $thumbs = '';
    if ($images->numRows() > 0)
    {
        
    ?>
    <a href="../images/featured/<?php echo $rs_gallery['featured']; ?>">
        <div class="magglass"></div>
        <img class="overlayglass" src="../images/featured/thumbs/<?php echo $rs_gallery['featured']; ?>"/>
    </a>
    <?php
        $i = 0;
        while($rs_images = mysql_fetch_array($images->result))
        {
            if ($i == 0)
            ?>
                <a href="../images/gallery/large/<?php echo $rs_images['image']; ?>"></a>
            <?php
            $i++;
        }    
    }
    ?>
</div>
<h2><?php echo $rs_gallery['title']; ?></h2>
<p><?php echo $rs_gallery['description']; ?></p>    
</div>
<?php } ?>