Selecting first in each (SQL / PHP)

Question

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(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

Answer 1

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.