UPPhoto gallery using CodeIgniter and Sql

Question

I have 2 tables: photocategories:

id_categorie   name   date

photo:

id_photo  categorie_id  smallimage  bigimage

In my view I need to have the name of the categorie and ONLY one photo random how to do this select?thx

SELECT p.id,p.categorie_id,p.largeimage,p.smallimage,c.id_categorie,c.name FROM photos p, 
photocategories c WHERE c.id_categorie=p.categorie_id ORDER BY c.Date DESC

If I have for example 5 categories I need to show for every categorie only one photo

Answer 1

you can do like this

CREATE TABLE photocategories (
    id_category     int NOT NULL, 
    name     VARCHAR(255) , 
    date       VARCHAR(255) 
) ENGINE = MyISAM DEFAULT CHARACTER SET latin1;

CREATE TABLE photo (
    id_photo int NOT NULL,
    id_category     int NOT NULL, 
    smallimage     VARCHAR(255) , 
    bigimage       VARCHAR(255) 
) ENGINE = MyISAM DEFAULT CHARACTER SET latin1;


INSERT INTO photocategories VALUES (1, "cat1", "2014");
INSERT INTO photocategories VALUES (2, "cat2", "2014");
INSERT INTO photocategories VALUES (3, "cat3", "2014");
INSERT INTO photocategories VALUES (4, "cat4", "2014");
INSERT INTO photocategories VALUES (5, "cat5", "2014");

INSERT INTO photo VALUES (1, 1, "photo1", "2014");
INSERT INTO photo VALUES (2,1, "photo2", "2014");
INSERT INTO photo VALUES (3,2, "photo3", "2014");
INSERT INTO photo VALUES (4,2, "photo4", "2014");
INSERT INTO photo VALUES (5,3, "photo5", "2014");
INSERT INTO photo VALUES (6,3, "photo6", "2014");
INSERT INTO photo VALUES (7,4, "photo7", "2014");
INSERT INTO photo VALUES (8,4, "photo8", "2014");
INSERT INTO photo VALUES (9,5, "photo9", "2014");
INSERT INTO photo VALUES (10,5, "photo10", "2014");

SELECT *
FROM photocategories
LEFT JOIN (SELECT * FROM photo ORDER BY RAND()) 
tmp ON (photocategories.id_category = tmp.id_category)
GROUP BY tmp.id_category
ORDER BY photocategories.date;