MySQL: displaying the page containing the entry

Question

Assume the following entries:

ID Value ========= 1. No 2. No 3. No 4. No 5. No 6. No 7. Yes 8. No 9. No 10. No 

Each "page" of my records-records contains 5 records (therefore, page 1 has records 1-5, page 2 has records 6-10, etc.). I want to display a page containing an entry with a value of Yes . Keep in mind that I really don't know where this Yes is in the entries.

How do I request this?

Answer 1

How about something like this:

 /* Work out the page */ SELECT @MatchID = ID FROM tbl WHERE Value = 'Yes' ORDER BY ID ASC LIMIT 1; SELECT @Page = CEIL(COUNT(*) / 5) FROM tbl WHERE ID <= @MatchID; /* Select the items on that page */ SET @Offset = (@Page - 1) * 5; SELECT * FROM tbl ORDER BY ID ASC LIMIT @Offset, 5; 

Note. The above does not support @MatchID, which is not found.

I'm not sure MySQL limits should be constant, so if they do, you will have to calculate the offset in PHP or any programming language that you use to connect to MySQL. Alternatively, perhaps this would work instead of the last SELECT statement in the above example:

 SET @selectSQL = CONCAT('SELECT * FROM tbl ORDER BY ID ASC LIMIT ', @Offset, ', 5'); PREPARE stmt FROM @selectSQL; EXECUTE stmt; 

Answer 2

 SELECT ID FROM tbl WHERE Value = "Yes" Order by ID ASC LIMIT 1; 

divide the identifier through "entries per page" (in your case: 5). Then you get a “page” where the first yes entry exists.

Assuming your id is the primary key (auto_increment) and no spaces.

Answer 3

Ok, I think I got it now. select all records on the same page as the record with value = "YES"

 SET @c:=0; SELECT tbl.* FROM tbl WHERE CEIL((@c: =@c +1) / 5) = (SELECT CEIL(COUNT(temp_table.id) / 5) FROM tbl temp_table WHERE temp_table.id <= (SELECT temp2.id FROM tbl temp2 WHERE temp2.value = 'YES')) ORDER BY tbl.id ASC; 

Answer 4

 SELECT Value, ID / 5 AS Page WHERE Value = "Yes" 

EDIT

 SELECT Value, ID DIV 5 + 1 AS Page WHERE Value = "Yes"