Only one select statment instead of two

[raduenea]

Hi,

I want to make a select from mysql to show some data in page.

To show a table with data from mysql but limited to only 5 and this to show total number of rows.

I'm thinking to make two selects.

SELECT count(*) FROM table1 and after that to use mysql_num_rows to count total number in that table.

Then another SELECT name,prename FROM table1 LIMIT 1, 5 to show only first 5 rows.

 

It's there a way to use only one select insted of two but having the same results ?

Thanks

[Barand]

To do pagination successfully you need to know the total number of pages. That means a query at the start to count the number of rows.

You are correct in using SELECT COUNT(*), however using mysql_num_rows after that query will always return "1" as the query will return a single row containing the count. All you need to do is read the count column value returned.

$res = $conn->query("SELECT COUNT(*) FROM table1");       // get the count
$total_recs = $res->fetch_row()[0];                       // read the first column of the returned row

You would use the query with LIMIT clause once you know which page to get.

Edited October 1, 2021 by Barand

[raduenea]

So in the end I must use two select.

First to count the number of all rows:

$res = $conn->query("SELECT COUNT(*) FROM table1");       // get the count
$total_recs = $res->fetch_row()[0];                       // read the first column of the returned row

Second to paginate like:

$res = $conn->query("SELECT name,prename FROM table1 LIMIT 1,5");    // first 5 rows  
$res->execute();
$res->fetchAll(PDO::FETCH_OBJ);

 

I wonder if it's possible to combine this two SELECT statements into one. 

Because using two select I must interact with my table twice, witch is a big table. Maybe combine this into one SELECT may reduce the time to query.

 

[gizmola]

There is no way to combine the queries,  other than to union them, and that has essentially no significant value to you.  You can write the count query in a slightly more compact manner if you like:

$total_recs = $conn->query("SELECT count(*) FROM table1")->fetchColumn();

Your pagination limit variables should use bind variables:

// You'll set your $limit and offset variables from your pagination calculation
$res = $conn->prepare("SELECT * FROM users LIMIT ?,?");
$res->execute([$limit,$offset]); 
$data = $res->fetchAll();

 

[raduenea]

Ok, I get it.

Thank you.