2

I understand how LIMIT works, but I want to know if there is a way to set the starting point for a database query before LIMIT. Is this possible? And am I making sense with this?

d-_-b
  • 21,536
  • 40
  • 150
  • 256
wordman
  • 581
  • 2
  • 6
  • 20
  • @ wordman can you explain it clearly... – Techy Jan 02 '13 at 04:59
  • Well, I'd like to specify a starting point in the table to begin counting from...does that make any more sense? – wordman Jan 02 '13 at 05:02
  • 2
    @wordman `tell the query where to start selecting BEFORE it gets to LIMIT` why? it is much better to include sample records and desired result on your question to make it more clearer to the readers. – John Woo Jan 02 '13 at 05:08
  • @wordman an example would help understanding the actual question – Hanky Panky Jan 02 '13 at 05:11
  • @JW, I understand that. What I am asking here is if this is even possible. So in other words, can we tell the query to start at row 5, for instance, then from there select all records equal to `red` and limit the result to 27 records? – wordman Jan 02 '13 at 05:12
  • how about this simple example? [SQLFiddle Demo](http://sqlfiddle.com/#!2/15e92/2) It uses subquery. – John Woo Jan 02 '13 at 05:15
  • @JW Thank you for that. Tell me, in the subquery, what if I wanted to change the numbers in `LIMIT 3, 7` to variables such as `$offset` and `$limit`. What would the proper syntax be for that? – wordman Jan 02 '13 at 05:29
  • use PreparedStatements, [How to prevent SQL injection in PHP?](http://stackoverflow.com/questions/60174/how-to-prevent-sql-injection-in-php) and [limit keyword on MySQL with prepared statement](http://stackoverflow.com/questions/10014147/limit-keyword-on-mysql-with-prepared-statement-maybe-still-a-bug) – John Woo Jan 02 '13 at 05:33
  • @JW Yes, I am utilizing prepared statements in my code. Would it be possible for you to show me the proper syntax for inserting the variables Imentioned as they are? It would help me understand things more at the moment. – wordman Jan 02 '13 at 05:34
  • create a query like this `SELECT * FROM ....... WHERE ..... LIMIT :x, :y` see the link i gave you `:D` – John Woo Jan 02 '13 at 05:37
  • @JW Thanks, but I'm not getting any of that. I was looking for more specific info. I appreciate it though, I'll leep researching this elsewhere. – wordman Jan 02 '13 at 05:39
  • @JW I just worked on my code with your subquery suggestion and IT WORKS exactly as I wanted. How can I give you credit here for this answer? Thank you! – wordman Jan 02 '13 at 05:56
  • @wordman great to hear that. i just updated the answer with the fiddle. `:D` – John Woo Jan 02 '13 at 05:57
  • @wordman to give JW credit, simply click that check mark next to his answer below. – d-_-b Jan 02 '13 at 06:01

5 Answers5

5

LIMIT accepts two values, the starting point and the duration.

UPDATE 1

John Woo
  • 258,903
  • 69
  • 498
  • 492
4
SELECT * FROM `table` LIMIT [offset], [count]

So say you had 4 pages (1, 2, 3, and 4), and wanted to show 10 results per page you would do something like:

page_1 (0 - 10)

SELECT * FROM `table` LIMIT  0, 10

page_2 (10 - 20)

SELECT * FROM `table` LIMIT 10, 10

page_3 (20 - 30)

SELECT * FROM `table` LIMIT 20, 10

page_4 (30 - 40)

SELECT * FROM `table` LIMIT 30, 10
Supericy
  • 5,866
  • 1
  • 21
  • 25
2
SELECT * FROM MyTable LIMIT [startpoint], [row_count]

Where start point is optional and is default to the first row. Specifying the start point skips the record n-times and starts counting at [startpoint]

SELECT * FROM MyTable LIMIT 5, 15

That will display 15 records starting from the 5th record.

SELECT * FROM MyTable LIMIT 7 is equivalent to SELECT * FROM MyTable LIMIT 0,7

codingbiz
  • 26,179
  • 8
  • 59
  • 96
0

I will give you a demo learn from this

SELECT * FROM `your_table` LIMIT 0, 10

This will display the first 10 results from the database.

Raab
  • 34,778
  • 4
  • 50
  • 65
Techy
  • 2,626
  • 7
  • 41
  • 88
0

Not sure if I understood correctly, but I will give it a shot. You may want to look into using variable binding in your query, this way your limit is set with variables. Code example using PDO below:

class englishTable {
  private $selectAllWithLimit;

  public function __construct($db) {
    $this->selectWithLimit = $db->prepare('SELECT id, english, french FROM english LIMIT :start, :limit');

  public function selectWithLimit($start, $limit) {
    $this->selectWithLimit->bindValue(':start', $start, PDO::PARAM_INT );
    $this->selectWithLimit->bindValue(':limit', $limit, PDO::PARAM_INT );
    $this->selectWithLimit->execute();
    return $this->selectWithLimit->fetchAll();
  }
}

$start = 5;
$limit = 7;
$words = $englishTable->selectWithLimit($start, $limit);

Hope this helps, happy new-year friend.

Stephane Gosselin
  • 9,030
  • 5
  • 42
  • 65
  • I appreciate that! However, I am asking if the starting point can be set BEFORE WE GET TO `LIMIT`? – wordman Jan 02 '13 at 05:08
  • I realise I did not understand. Limit clause takes 1 0r 2 parameters. If you set this in a variable (as is commonly done), and you set your variables before executing the sql, in this manner would you not agree your limit parameters are set before the limit is called? – Stephane Gosselin Jan 02 '13 at 17:48
  • This post was exploratory but it led me to two solutions, one of which is marked as the answer above. I was asking for something different that obviously caused a lot of confusion, but it inadvertently led to two solutions for me. Many thanks! – wordman Jan 02 '13 at 18:28