Professional Web Applications Themes

how to get the search result with count - MySQL

I am relatively new to mysql and sql itself I have about 1000 results for a particular search condition but i only want to get the first 100 search results. How can i get the first 100 search results with total number of search results in a sql sentences or do i have to repeat the similar sql sentences twice like the followings or there is another way? 1. to get the total number of search result select count(*) from table where id=1 and 2. to get the first 100 actual search results select id,name from table where id = ...

  1. #1

    Default how to get the search result with count

    I am relatively new to mysql and sql itself

    I have about 1000 results for a particular search condition
    but i only want to get the first 100 search results.
    How can i get the first 100 search results with total number of
    search results in a sql sentences or do i have to repeat the similar
    sql sentences twice like the followings or there is another way?

    1. to get the total number of search result

    select count(*) from table where id=1

    and

    2. to get the first 100 actual search results

    select id,name from table where id = 1 limit 0, 100

    Thanks in advance.
    MD



    M Guest

  2. #2

    Default Re: how to get the search result with count

    select (select count(*) from table where id =1) as total, name from
    table where id = 1 limit 0,100

    -cheers,
    Manish

    Manish Guest

  3. #3

    Default Re: how to get the search result with count

    Arigato( Thank you very much in Japanese )!!

    M.D

    "Manish Pandit" <com> wrote in message
    news:googlegroups.com... 


    M Guest

  4. #4

    Default Still: how to get the search result with count

    I have tried with your idea but seems like it doesn't work with
    my database(MySQL 4.0.25) any idea?



    SELECT (SELECT count( * ) FROM table ) AS total, id, name
    FROM table LIMIT 0 , 10

    #1064 - You have an error in your SQL syntax. Check the manual that
    corresponds to your MySQL server version for the right syntax to use near
    'select count( * ) from table ) as total , id , name


    M Guest

  5. #5

    Default Re: how to get the search result with count


    M D wrote: 
    Checkout FOUND_ROWS() on the page:
    http://dev.mysql.com/doc/refman/5.0/en/information-functions.html

    Captain Guest

  6. #6

    Default Re: Still: how to get the search result with count

    I hope your table name is not *table* for real.

    "table" is a keyword in mysql, try keeping a name like table1 or
    t_entity for your table.

    -cheers,
    Manish

    Manish Guest

  7. #7

    Default Re: Still: how to get the search result with count


    Manish Pandit wrote: 

    The OP's guess is correct. Subselects don't work pre 4.1. Got to find
    another way.
    Hmm, tricky.

    strawberry Guest

Similar Threads

  1. problems paging a recordset search result
    By CharitiesOnline in forum ASP Database
    Replies: 1
    Last Post: July 3rd, 10:21 AM
  2. loop count down until result is achieved
    By AirJar in forum Macromedia ColdFusion
    Replies: 2
    Last Post: March 21st, 01:54 PM
  3. search result
    By Phpu in forum PHP Development
    Replies: 2
    Last Post: September 21st, 10:42 PM
  4. [PHP] search result highlighting / regexp voodoo
    By Lee O'Mara in forum PHP Development
    Replies: 0
    Last Post: September 3rd, 07:15 AM
  5. Replies: 1
    Last Post: July 2nd, 12:22 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139