Selecting first in each (SQL / PHP)

Ok, I tried holding off and looking for answers, but I need your guys' help! I have three tables- category, project, and images. I have it set up so that images are linked to a specific project, and each project has a category. I use two inner joins to do this (is this correct?) What I'm looking to do eventually is have PHP echo out the first image from each project, and then I'll use ajax to load in the rest of the images per project. I've looked at LIMIT and I'm sure there's a way to do it easily with PHP and a for loop, but I'm just stumped! How can I echo out just the first "filename" per project? Thanks in advance!

Tables:

CREATE TABLE `category` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

CREATE TABLE `project` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar开发者_JAVA技巧(50) NOT NULL,
  `caption` text NOT NULL,
  `category` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

CREATE TABLE `images` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `filename` varchar(255) NOT NULL,
  `project` int(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;

Query:

SELECT i.filename as filename,
          i.project as project_id,
          p.name as project_name,
          c.name as category

FROM images i

INNER JOIN project p
ON i.project = p.id

INNER JOIN category c
ON p.category = c.id

Generally, you can group your query's results by a specific column / columns.

This query without a GROUP BY works better for finding the first image within a project:

SELECT @pid := `prj_id`, 
   (SELECT MIN(`id`) FROM `images` `i` WHERE `i`.`prj_id` = @pid) `first_img`
FROM `projects`

This'll also select projects having no images at all (i.e. first_img being NULL). A HAVING clause filtering these might be necessary.

Try DISTINCT at some point:

SELECT i.filename as filename,
          i.project as project_id,
          DISTINCT(p.name) as project_name,
          c.name as category

FROM images i

INNER JOIN project p
ON i.project = p.id

INNER JOIN category c
ON p.category = c.id

I think this will only work in MySQL and will give you one (random) filename per project:

SELECT i.filename as filename
     , i.project as project_id
     , p.name as project_name
     , c.name as category

  FROM images i
    INNER JOIN project p
      ON i.project = p.id
    INNER JOIN category c
      ON p.category = c.id

  GROUP BY p.id
;

ANd this will select the filename with lowest id. I'm sure it can be improved in terms of performance. I definitely need some sleep...

SELECT i.filename as filename
     , im.project_id
     , im.project_name
     , im.category

  FROM images i
    INNER JOIN 
    ( SELECT MIN(i.id) as id
           , i.project as project_id
           , p.name as project_name
           , c.name as category
        FROM images i
          INNER JOIN project p
            ON i.project = p.id
          INNER JOIN category c
            ON p.category = c.id
        GROUP BY p.id
    ) AS im
    ON i.id= im.id
;

You can use this :

foreach ($Result as $r)
{
echo $r['picture'];
break;
}

but that wouldn't be nice. You should use LIMIT in SQL, is there anything wrong with LIMIT?