Instantly share code, notes, and snippets.

Embed
What would you like to do?
MySQL Procedure displaying random banner at nixmash.com
CREATE PROCEDURE `p_current_site_image_get`()
BEGIN
DECLARE existing_site_image_id BIGINT;
DECLARE new_site_image_id BIGINT;
DECLARE active_image_count INT;
DECLARE random_number INT;
SELECT site_image_id
INTO existing_site_image_id
FROM site_images
WHERE is_current = 1 AND day_of_year = DAYOFYEAR(NOW());
IF existing_site_image_id IS NULL
THEN
UPDATE site_images
SET is_current = 0, day_of_year = DAYOFYEAR(NOW());
SELECT count(*)
INTO active_image_count
FROM site_images
WHERE is_active = 1 AND banner_image = 1;
CREATE TEMPORARY TABLE t_site_images (
id INT AUTO_INCREMENT PRIMARY KEY,
site_image_id BIGINT NOT NULL,
is_current TINYINT(1) DEFAULT 0
);
INSERT INTO t_site_images (site_image_id) SELECT site_image_id
FROM site_images
WHERE is_active = 1 AND banner_image = 1;
SELECT FLOOR(RAND() * active_image_count) INTO random_number;
UPDATE t_site_images SET is_current = 1 WHERE id = random_number + 1;
SELECT site_image_id INTO new_site_image_id FROM t_site_images WHERE is_current = 1;
UPDATE site_images SET is_current = 1 WHERE site_image_id = new_site_image_id;
DROP TABLE t_site_images;
END IF;
SELECT * from site_images where is_current = 1 LIMIT 1;
END
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment