CREATE DEFINER=`dbuser`@`111.111.111.%` PROCEDURE `up_admin_series_list`(IN
seriesType CHAR(6),
page INT,
page_size INT
)
BEGIN
SET @PAGE = page;
SET @PAGE_SIZE = page_size;
SET @SERIES_TYPE = seriesType;
SELECT COUNT(*) INTO @RECORD_COUNT FROM series
WHERE series_type = @SERIES_TYPE;
SET @PAGE_COUNT = CEIL(@RECORD_COUNT / @PAGE_SIZE);
SET @START_LIMIT = (@PAGE – 1) * @PAGE_SIZE;
SET @STR = ‘
SELECT ? AS record_count, ? AS page_count, S.series_id, title, author_name, cover_file, folder_name,
is_completed, install_count, comment_count, view_count, recomm_count, I.install_id,
I.sub_title, I.install_order
FROM series S
LEFT JOIN (
SELECT install_id, sub_title, install_order, series_id FROM installment
WHERE (install_order, series_id)
IN( SELECT MAX(install_order) AS install_order, series_id
FROM installment GROUP BY series_id
)
) I ON I.series_id = S.series_id
WHERE series_type=?
ORDER BY last_updated DESC
LIMIT ?,?;
‘;
PREPARE STMT FROM @STR;
EXECUTE STMT USING @RECORD_COUNT, @PAGE_COUNT, @SERIES_TYPE, @START_LIMIT, @PAGE_SIZE;
END