开发者

MySQL return to first record using LIMIT

开发者 https://www.devze.com 2023-01-19 22:21 出处:网络
Suppose: $sql= \" SELECT * FROM `artwork` WHERE approved = \'1\' \"; if($filter) $sql.= \" AND $filter = \'$filter_value\' \";

Suppose:

            $sql     = " SELECT * FROM `artwork` WHERE approved = '1' ";
if($filter) $sql    .= " AND $filter = '$filter_value' ";
            $sql    .= " ORDER BY subdate DESC, id DESC
                         LIMIT 30,30";

If I were to introduce a starting point (eg. WHERE id > 50) and that stipulation affected my LIMIT such that it only returned 10 results. I want 30 results, remember. Is there a way to start from record 1 and continue the selection?

edit: I realize I'm asking for id > 50 in this example and most certainly the first record if we were to rewind would have a lower ID. In my 开发者_运维知识库scenario that's okay.

Thanks, Jason.


If there are 100 records and you're only selecting 10 rows (#90 - #100), you want to get 20 more rows (#1 - #20)

If those are your constraints, I don't think you will be able to get the desired result set from a single query.

Here's a stored procedure which creates a temp table to get the desired result:

DELIMITER //
DROP PROCEDURE IF EXISTS TMP_TABLE_ARTWORK//
CREATE PROCEDURE TMP_TABLE_ARTWORK (_offset INT, _count INT)
BEGIN
DECLARE res_total INT DEFAULT 0;
SELECT COUNT(*) INTO res_total FROM artwork;
CREATE TEMPORARY TABLE IF NOT EXISTS artwork_tmp ( t_pseudo INT, t_old INT, t_subdate DATETIME );
INSERT INTO artwork_tmp ( t_pseudo, t_old, t_subdate ) SELECT artwork.id, artwork.id, artwork.subdate FROM artwork ORDER BY artwork.subdate DESC;
INSERT INTO artwork_tmp ( t_pseudo, t_old, t_subdate ) SELECT ( artwork.id + res_total ), artwork.id, artwork.subdate FROM artwork ORDER BY artwork.subdate DESC;
PREPARE STMT FROM "SELECT * FROM artwork_tmp ORDER BY t_pseudo LIMIT ?,?";
SET @offset = _offset;
SET @count = _count;
EXECUTE STMT USING @offset, @count;
DROP TABLE artwork_tmp;
END //
DELIMITER ;

You'll probably need to modify it to get it to do what you want (and apparently the prepared statement workaround is no longer required if you're running a newer version of MySQL).


If I understand your question.
You should use LIMIT 0, 30 to get first 30 records which match your query.

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号