I have two tables galleries which contains the number and name of the photo galleries and 'thumnails' the images that are conenected to the galleries.

I am trying to create a 'pick a gallery' screen where it selects all the galleries and then output the first thumbnail image associated with that gallery. The probroblem is only returning one unique image.





--
-- Table structure for table `galleries`
--

CREATE TABLE `galleries` (
`id` int(11) NOT NULL auto_increment,
`display` tinyint(4) NOT NULL default '0',
`galleryorder` int(11) NOT NULL default '0',
`title` mediumtext NOT NULL,
`description` text NOT NULL,
PRIMARY KEY (`id`),
KEY `id` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

--
-- Dumping data for table `galleries`
--

INSERT INTO `galleries` VALUES (7, 1, 1, 'my gallery1', 'my gallery1');
INSERT INTO `galleries` VALUES (8, 0, 1, 'gallery2', 'my gallery2');

-- --------------------------------------------------------

--
-- Table structure for table `thumbnails`
--

CREATE TABLE `thumbnails` (
`id` int(4) NOT NULL auto_increment,
`gallery` int(4) NOT NULL default '0',
`display` tinyint(4) NOT NULL default '0',
`photoorder` int(4) NOT NULL default '0',
`caption` varchar(80) NOT NULL default '',
`description` varchar(200) default NULL,
`bin_data` longblob,
`filename` varchar(50) default NULL,
`filesize` varchar(50) default NULL,
`filetype` varchar(50) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=513 DEFAULT CHARSET=latin1 AUTO_INCREMENT=513 ;



This is what I have so far



<? $dbquery = "SELECT id FROM galleries";
$result = mysql_query($dbquery);
while($row=mysql_fetch_array($result))
{
echo $id=$row['id'];
$dbquery2 = "SELECT * FROM thumbnails where gallery=$id";
$result2 = mysql_query($dbquery2);
while($myimage=mysql_fetch_array($result2)){
echo $myimage['caption'];
}
}

Ross -

In your query you can add a "LIMIT" clause to get just one row in the
result, a la:
SELECT * FROM thumbnails where gallery=$id LIMIT 1

If you want the "first" image, say the one with lowest ID number, you
could do this:
SELECT * FROM thumbnails where gallery=$id ORDER BY id LIMIT 1

Dan


On 11/1/06, Ross Hulford <ross@aztechost.com> wrote:
>
>
> I have two tables galleries which contains the number and name of the photo galleries and 'thumnails' the images that are conenected to the galleries.
>
> I am trying to create a 'pick a gallery' screen where it selects all the galleries and then output the first thumbnail image associated with that gallery. The probroblem is only returning one unique image.
>
>
>
>
>
> --
> -- Table structure for table `galleries`
> --
>
> CREATE TABLE `galleries` (
> `id` int(11) NOT NULL auto_increment,
> `display` tinyint(4) NOT NULL default '0',
> `galleryorder` int(11) NOT NULL default '0',
> `title` mediumtext NOT NULL,
> `description` text NOT NULL,
> PRIMARY KEY (`id`),
> KEY `id` (`id`)
> ) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;
>
> --
> -- Dumping data for table `galleries`
> --
>
> INSERT INTO `galleries` VALUES (7, 1, 1, 'my gallery1', 'my gallery1');
> INSERT INTO `galleries` VALUES (8, 0, 1, 'gallery2', 'my gallery2');
>
> -- --------------------------------------------------------
>
> --
> -- Table structure for table `thumbnails`
> --
>
> CREATE TABLE `thumbnails` (
> `id` int(4) NOT NULL auto_increment,
> `gallery` int(4) NOT NULL default '0',
> `display` tinyint(4) NOT NULL default '0',
> `photoorder` int(4) NOT NULL default '0',
> `caption` varchar(80) NOT NULL default '',
> `description` varchar(200) default NULL,
> `bin_data` longblob,
> `filename` varchar(50) default NULL,
> `filesize` varchar(50) default NULL,
> `filetype` varchar(50) default NULL,
> PRIMARY KEY (`id`)
> ) ENGINE=MyISAM AUTO_INCREMENT=513 DEFAULT CHARSET=latin1 AUTO_INCREMENT=513 ;
>
>
>
> This is what I have so far
>
>
>
> <? $dbquery = "SELECT id FROM galleries";
> $result = mysql_query($dbquery);
> while($row=mysql_fetch_array($result))
> {
> echo $id=$row['id'];
> $dbquery2 = "SELECT * FROM thumbnails where gallery=$id";
> $result2 = mysql_query($dbquery2);
> while($myimage=mysql_fetch_array($result2)){
> echo $myimage['caption'];
> }
> }
>