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.
精彩评论