Jump to content

Only one select statment instead of two


raduenea
Go to solution Solved by gizmola,

Recommended Posts

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

Link to comment
Share on other sites

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 by Barand
Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

  • Solution

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();

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.