This tutorial shows how to select items randomly from each category in MySQL. So here we will select exactly one item randomly from each category. People who need sometimes to select photo from each photo category and display them on the page for animation purpose will be benefited.
In this example I have used table name starts with item. You can change the name and column names as per your needs and you will get the same results.

Final Output:

random item selection for each category in mysql

First create item category table which holds the category for items

CREATE TABLE item_categories (
    category_id     int(10) unsigned NOT NULL AUTO_INCREMENT,
    category_name   VARCHAR(50),
    category_date   timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (category_id)
) ENGINE = MyISAM DEFAULT CHARACTER SET latin1;

 

Create table items which holds items from each category above

CREATE TABLE items (
    item_id int(10) unsigned NOT NULL AUTO_INCREMENT,
    category_id    int NOT NULL,
    smallimage     VARCHAR(255) ,
    bigimage       VARCHAR(255),
    PRIMARY KEY (item_id)
) ENGINE = MyISAM DEFAULT CHARACTER SET latin1;

 

Insert dummy values to item_categories table

INSERT INTO item_categories VALUES (1, 'cat1', '2014-04-11 08:42:06');
INSERT INTO item_categories VALUES (2, 'cat2', '2014-04-11 08:42:07');
INSERT INTO item_categories VALUES (3, 'cat3', '2014-04-11 08:42:08');
INSERT INTO item_categories VALUES (4, 'cat4', '2014-04-11 08:42:09');
INSERT INTO item_categories VALUES (5, 'cat5', '2014-04-11 08:42:10');

 

Insert dummy values to items table

INSERT INTO items VALUES (1, 1, 'item1', '2014-04-11 08:43:06');
INSERT INTO items VALUES (2, 1, 'item2', '2014-04-11 08:43:07');
INSERT INTO items VALUES (3, 2, 'item3', '2014-04-11 08:43:08');
INSERT INTO items VALUES (4, 2, 'item4', '2014-04-11 08:43:09');
INSERT INTO items VALUES (5, 3, 'item5', '2014-04-11 08:43:10');
INSERT INTO items VALUES (6, 3, 'item6', '2014-04-11 08:43:11');
INSERT INTO items VALUES (7, 4, 'item7', '2014-04-11 08:43:12');
INSERT INTO items VALUES (8, 4, 'item8', '2014-04-11 08:43:13');
INSERT INTO items VALUES (9, 5, 'item9', '2014-04-11 08:43:14');
INSERT INTO items VALUES (10, 5, 'item10', '2014-04-11 08:43:15');

 

Execute the below query

SELECT *
FROM item_categories
LEFT JOIN (SELECT * FROM items ORDER BY RAND())
tmp ON (item_categories.category_id = tmp.category_id)
GROUP BY tmp.category_id
ORDER BY item_categories.category_date;

 

That’s all. Thanks for your reading. Please do not hesitate to ask any question by leaving a comment.

Tags:

I am a professional Web developer, Enterprise Application developer, Software Engineer and Blogger. Connect me on JEE Tutorials | TwitterFacebook Google PlusLinkedin | Reddit | Email Me

Leave a Reply

Your email address will not be published. Required fields are marked *