How To get DATABASE SIZE

[eluser]pmsfo[/eluser]
Hi How can i get the database size using the database library from codeigniter ?

[eluser]johnpeace[/eluser]
What do you mean by 'size'?

The number of records in each table?
The KB file size of the binary files the database stores?
The number of tables in the DB?

This query might be a good start:

Code:

SELECT table_schema "Data Base Name", sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB"
FROM information_schema.TABLES GROUP BY table_schema ;

[eluser]pmsfo[/eluser]
For example i have i database namee "bacula" that as 15 tables, what i what to know is the size of the database, but using the condeigniter library.

For mysql i can do this

mysql_select_db( $dbname );
$result = mysql_query( “SHOW TABLE STATUS” );
$dbsize = 0;
while( $row = mysql_fetch_array( $result ) ) {
$dbsize += $row[ "Data_length" ] + $row[ "Index_length" ];
}


I what to do is for all databases types postgres, mysql, sqlite, using the codeigniter library.

[eluser]johnpeace[/eluser]
[quote author="pmsfo" date="1297283518"]For example i have i database namee "bacula" that as 15 tables, what i what to know is the size of the database, but using the condeigniter library.

For mysql i can do this

mysql_select_db( $dbname );
$result = mysql_query( “SHOW TABLE STATUS” );
$dbsize = 0;
while( $row = mysql_fetch_array( $result ) ) {
$dbsize += $row[ "Data_length" ] + $row[ "Index_length" ];
}


I what to do is for all databases types postgres, mysql, sqlite, using the codeigniter library.[/quote]

I think you'd have to extend the DB class for that functionality.

Alternatively, you could create a helper that has a function to return database filesize for a given DB.

[eluser]pmsfo[/eluser]
I think it's only away, i will try to do it and then i will post the code here so that can be add to the codeigniter library.

http://www.newitperson.com/

[eluser]cbwd[/eluser]
An old thread, but this gets the size of the default db in MB.

Code:

/**
    * Gets the size of the default database
    */
    function getDbSize() {
        
        $CI=&get;_instance();
        $CI->load->database();
        
        $dbName = $CI->db->database;
        
        $dbName = $this->db->escape($dbName);
        
        $sql = "SELECT table_schema AS db_name, sum( data_length + index_length ) / 1024 / 1024 AS db_size_mb
                FROM information_schema.TABLES
                WHERE table_schema = $dbName
                GROUP BY table_schema ;";
        
        $query = $CI->db->query($sql);
        
        if ($query->num_rows() == 1) {
            
           $row = $query->row();
           $size = $row->db_size_mb;
           return $size;
          
        } else {
            
            log_message('ERROR', "*** Unexpected number of rows returned " . ' | line ' . __LINE__ . ' of ' . __FILE__);
            show_error('Sorry, an error has occured.');
            
        }
        
    }