SQL, how to pick portion of list of items via SELECT, WHERE clauses?

Question

I'm passing three parameter to URL: &p1=eventID, &p2=firstItem, &p3=numberOfItems. first parameter is a column of table. second parameter is the first item that I'm looking for. Third parameter says pick how many items after firstItem.

for example in first query user send &p1=1, &p2=0, &p3=20. Therefore, I need first 20 items of list. Next time if user sends &p1=1, &p2=21, &p3=20, then I should pass second 20 items (from item 21 to item 41).

PHP file is able to get parameters. currently, I'm using following string to query into database:

public function getPlaceList($eventId, $firstItem, $numberOfItems) {
        $records = array();
        $query = "select * from {$this->TB_ACT_PLACES} where eventid={$eventId}";

        ...
}

Result is a long list of items. If I add LIMIT 20 at the end of string, then since I'm not using Token then result always is same.

how to change the query in a way that meets my requirement? any suggestion would be appreciated. Thanks

=> update: I can get the whole list of items and then select from my first item to last item via for(;;) loop. But I want to know is it possible to do similar thing via sql? I guess this way is more efficient way.

possible duplicate of http://stackoverflow.com/questions/11728704/use-limit-to-paginate-results-in-mysql-query — DragonZero, Nov 23 '13 at 13:42
Thanks, I just looked at but I think that solution is based on pagination that I'm not using it. — Hesam, Nov 23 '13 at 13:46
ahh, gotcha, see this link, instructions on using the second argument with the limit keyword. http://stackoverflow.com/questions/15950871/select-all-records-using-mysql-limit-and-offset-query — DragonZero, Nov 23 '13 at 13:48
I think this tends to be database specific. Please tell us which db and add that tag — Sam Hartman, Nov 23 '13 at 14:07

score 1 · Answer 1 · answered Nov 24 '13 at 05:29

I would construct the final query to be like this:

SELECT <column list> /* never use "select *" in production! */
FROM Table
WHERE p1ColumnName >= p2FirstItem
ORDER BY p1ColumnName
LIMIT p3NumberOfItems

The ORDER BY is important; according to my reading of the documentation, PostGreSql won't guarantee which rows you get without it. I know Sql Server works much the same way.

Beyond this, I'm not sure how to build this query safely. You'll need to be very careful that your method for constructing that sql statement does not leave you vulnerable to sql injection attacks. At first glance, it looks like I could very easily put a malicious value into your column name url parameter.

+1, Thanks Joel for your valuable information. Although it was not my direct answer, I found it valuable since this is my first attempt to learn SQL. I found my answer here: http://stackoverflow.com/a/8242764/513413 — Hesam, Nov 24 '13 at 07:24

score 0 · Answer 2 · answered Nov 23 '13 at 14:29

0

If you are using mysql, then use the LIMIT keyword.

SELECT *
 FROM Table
 LIMIT $firstItem, $numberOfItems

See the documentation here (search for LIMIT in the page).

answered Nov 23 '13 at 14:29

Andreas

1,751
2
14
25

score 0 · Accepted Answer · edited May 23 '17 at 10:31

0

I found my answer here: stackoverflow.com/a/8242764/513413

For other's reference, I changed my query based on what Joel and above link said:

$query = "select places.title, places.summary, places.description, places.year, places.date from places where places.eventid = $eventId order by places.date limit $numberOfItems offset $firstItem";

edited May 23 '17 at 10:31

Community

1
1

answered Nov 24 '13 at 07:28

Hesam

52,260
74
224
365

SQL, how to pick portion of list of items via SELECT, WHERE clauses?

3 Answers3