Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Senior Coder
    Join Date
    Aug 2005
    Posts
    1,119
    Thanks
    2
    Thanked 1 Time in 1 Post

    Number of Rows in DB

    Im looking for a simple way to find the number of rows in a db without having to loop through each table counting the rows up.

    Is there a mysql function that does this?

  • #2
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,642
    Thanks
    0
    Thanked 649 Times in 639 Posts
    You can get a COUNT(*) of the number of rows in a table. The rows in different tables are not normally related in a way that makes sense to accumulate a total. There is also a matter of which tables you count if counting all the rows in the database as the only tables visible from a particular userid are those that the particular user has access to and will never be all of the tables in the database.
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

  • #3
    New to the CF scene
    Join Date
    Jun 2007
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    juse use count(*)

    yes use count(*)

    e.g: select count(*) as count from tablename;

  • #4
    Senior Coder
    Join Date
    Aug 2005
    Posts
    1,119
    Thanks
    2
    Thanked 1 Time in 1 Post
    I dont want to just find the number of rows in 1 table though. I want to find all the rows in every table in a database (that the current mysql user has access too.)

  • #5
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,642
    Thanks
    0
    Thanked 649 Times in 639 Posts
    To do that you will have to do the same call for each table since the user probably doesn't have access to read the table that contains the list of tables that would be required in order to set up the select that would return the total count.
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

  • #6
    New Coder
    Join Date
    Jul 2007
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by thesavior View Post
    Im looking for a simple way to find the number of rows in a db without having to loop through each table counting the rows up.

    Is there a mysql function that does this?
    Quote Originally Posted by dhomba View Post
    yes use count(*)

    e.g: select count(*) as count from tablename;
    Quote Originally Posted by thesavior View Post
    I dont want to just find the number of rows in 1 table though. I want to find all the rows in every table in a database (that the current mysql user has access too.)

    This might do it. Might not be exactly what you want but you can modify it: If you take out the first echo I made it will only show the total number of rows in your entire database.

    I took dhomba's suggestion with the count() and used the show tables as an array, so your not really looping as I see you didn't want to do.



    PHP Code:
    $sql "show tables"
    $result mysql_query($sql); 
    while (
    $rows mysql_fetch_array($result)) 

    $NUMROWS mysql_query("select count(*) from $rows[0]");
    $COUNTER mysql_fetch_array($NUMROWS);

    // this will show the number of rows next to each table. //
    echo "<br> $rows[0] $COUNTER[0]";

    $running_total $running_total $COUNTER[0];

    echo (
    "<br>Total rows = $running_total"); 
    Last edited by phpandmysql; 07-07-2007 at 01:32 AM.
    PHP and MYSQL
    $string = "3Ip*hKEpanKI#8dmUys&*KqlIJ*P8D";
    $new_string = ereg_replace("[^a-z]", "", $string);
    echo "<a href=http://www.$new_string.org>$new_string</a>";


  •  

    Posting Permissions

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