Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2003
    Posts
    2

    Unanswered: How do I display Results 18-29 of 1,680 ?

    Current query:

    SELECT name,address
    FROM some_table
    WHERE name > '$start'
    ORDER BY name
    LIMIT 12;

    1. I need to know the number of rows in the whole table. Can this be done without another query? COUNT(*) will give me just the rows returned from the above query...

    2. I need to know where in set I am, based on what the value of $start is.

    Looking to display: "Results 18-29 of 1,680"


    not knowing mySQL that well, the only way I see is to read the whole table and process the results in a program.

    I'm thinking there has to be a better/faster way!

    Thanks for your help.

  2. #2
    Join Date
    Feb 2001
    Location
    NC, USA
    Posts
    200
    You'll need another query.

    If you're using MyISAM, the overhead for a count(*) is insignificant as the count is stored as meta data.

    If you're using InnoDB, then you might want to maintain your own counter table to avoid the required full index scan.

  3. #3
    Join Date
    Feb 2003
    Posts
    2
    Thank you for your reply.

    I'm using MyISAM...

    Your answer helps with the overall count, but not the "where am I in the results" question...

  4. #4
    Join Date
    Feb 2003
    Posts
    17
    Try using PHP:

    $query1 = mysql_query("SELECT * FROM table LIMIT 17,28");
    $rows = mysql_num_rows($query1);
    Would return 12 rows displaying every field from 18 - 29.

    $query2 = mysql_query("SELECT * FROM table");
    $rows = mysql_num_rows($query2);
    Would return as many rows as you have in the table.

    I can run this on a table with over 30,000+ records and it returns very fast.
    Last edited by progress; 02-06-03 at 00:20.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •