3 Replies - 2113 Views - Last Post: 31 December 2012 - 09:21 AM

#1 scottyadam  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 116
  • Joined: 03-December 07

one query to count all results and return limited results

Posted 29 December 2012 - 05:53 PM

I have 2 queries that will now query a database, one that will count all the results (so I can get the paging and limits:

SELECT COUNT(id) as num FROM testinfo_tbl


and then another one that will do the limit to only return the results that I actually want:

SELECT id,name,blah1,blah2,blah3 FROM testinfo_tbl


Is there a way to do this in 1 mysql query, as I am trying to reduce the amount of queries I send to the database...slowing things down...

Is This A Good Question/Topic? 0
  • +

Replies To: one query to count all results and return limited results

#2 scottyadam  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 116
  • Joined: 03-December 07

Re: one query to count all results and return limited results

Posted 29 December 2012 - 06:56 PM

figured it out...just needed a little more research

http://dev.mysql.com...tion_found-rows
Was This Post Helpful? 1
  • +
  • -

#3 BerndB  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 31-December 12

Re: one query to count all results and return limited results

Posted 31 December 2012 - 09:09 AM

Hello,

the "Select ...." Query give you only a Resultset, NOT THE RESULTS.

So after you query the Database you can get with get_num_rows($res)

the number of Rows from the Resultset.

Here ist a Sample in PHP. It works also in each other Language

http://php.net/manua...ql-num-rows.php


<?php

$link = mysql_connect("localhost", "mysql_user", "mysql_password");
mysql_select_db("database", $link);

$result = mysql_query("SELECT * FROM table1", $link);
$num_rows = mysql_num_rows($result);

echo "$num_rows Zeilen\n";

?>


Bernd

This post has been edited by Atli: 31 December 2012 - 09:16 AM
Reason for edit:: Please use [code] tags when posting code.

Was This Post Helpful? -1
  • +
  • -

#4 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3730
  • View blog
  • Posts: 6,017
  • Joined: 08-June 10

Re: one query to count all results and return limited results

Posted 31 December 2012 - 09:21 AM

BerndB, please note that the code you posted is a horrible example. You fetch ALL the data from the table, only to count the number of rows it returns. It's extremely wasteful. In such a situation, using the COUNT() function in an SQL query, like scottyadam does in his first post, is a FAR better solution.
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1