CODEWITHSUNDEEP
phpsqlpostgresqlcountrows
Brandon
Brandon

Reputation: 1421

PHP / SQL - Show specific number of rows per query

I apologize if this question has been asked already, I'm sure it has I just was not able to find a similar question/answer that solved my problem.

I am pulling data from PostgreSQL and I have an SQL statement similar to:

$SQL = "SELECT * FROM my_view_table WHERE id='".$my_id."'";

From this I run my query. The request pulls from a view in PostgreSQL which looks through more tables. That is correct. I'm not sure how to limit the number of rows to display. I receive about 1,000 rows of information with about 20 columns of data so it takes an incredible amount of time to query.

Is there a way to ask for rows 0 - 100, then 101 - 200, etc, so I can pull 100 at a time to display? I know I'll have to use a little code to keep track of the count, but I just need some SQL help with querying "x to y" rows.

Thank you for the help with this issue. (If there is another very similar question that has already been answered a link to that would be a sufficient answer!)

I've posted the answer to my question down below.

Upvotes: 3

Views: 1462

Answers (1)

Brandon
Brandon

Reputation: 1421

I found the answer to this question from gnarly's suggestion of LIMIT on SQL

$sql = "SELECT * FROM my_table LIMIT X OFFSET Y";

Where LIMIT only gives the X number of rows you want, and OFFSET gives the Y starting point. So showing rows 0 through 30:

$sql = "SELECT * FROM my_table LIMIT 30 OFFSET 0";

And showing rows 31 through 60:

$sql = "SELECT * FROM my_table LIMIT 30 OFFSET 30";

Upvotes: 2

Related Questions