Hello,

Does anyone know the command line over SSH or on PHP how to see and list out how many rows there are in a specific table on all available SQL databases in a server?

eg.

DB 1
- table 1
- table 2
- table 3

DB 2
- table 2
- table 4

DB 3
- table 2
- table16

I want to see the Database name with the number of rows in "table 2"
And getting the commandline or php script to generate something like

DB1 has 6 rows in "table 2"
DB2 has 12 rows in "table 2"
DB3 has 0 rows in "table 2"

Is it possible?

<?php

$query = "SELECT * FROM `table`";
$res = mysql_query($query);
if(!$res) { die($query."<br />".mysql_error()); }
echo "Table has ".mysql_num_rows($res)." rows";
?>


If you want to get a number from all tables in all databases, you would need to login with a user that has rights to all databases (typically only the MySQL "root" user).

If you want to just see a listing of all tables in all databases that your current user can access, the following should help :


<?php

//Connection code here
$query = "SHOW TABLES";
$res = mysql_query($query);
while($tables = mysql_fetch_assoc($res))
{
//Get all records in each table
list(,$tablename) = each($tables);
$query = "SELECT * FROM ".$tables[$tablename];
echo "In table $tablename there are ".mysql_num_rows(mysql_query($query))." rows<br />";
}
?>

When I created a php file with the below line

<?php
$query = "SELECT * FROM `tablename`";
$res = mysql_query($query);
if(!$res) { die($query."<br />".mysql_error()); }
echo "Table has ".mysql_num_rows($res)." rows";
?>

I get this error

SELECT * FROM `tablename`
No Database Selected

You have to open a connection and use mysql_select_db() to select a database first.

Galacnet,

If you read fyrestrtr's code he has the following: //Connection code here In this section you should put
mysql_connect($server,$username,$password);
mysql_select_db($databaseName);


... With their proper values of course :)

Regards,
Patrick

Would there be a command to select all DBs and display wach DB's name and number of rows in a specified Table at once?

http://www.php.net/manual/en/function.mysql-list-dbs.php

Read that...now I'm getting the feeling you want us to do your work for you. I believe my example and that link would be enough.

Opps did not see he edited the thread :P

But I am still getting the error

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/sites/site2/web/test.php on line 8


Where my coding in php is


<?php
mysql_connect("localhost", "Username", "password") or die("Could not connect.");
mysql_select_db($databaseName);

$query = "SHOW TABLES";
$res = mysql_query($query);
while($tables = mysql_fetch_assoc($res))
{
//Get all records in each table
list(,$tablename) = each($tables);
$query = "SELECT * FROM ".$tables[$tablename];
echo "In table $tablename there are ".mysql_num_rows(mysql_query($query))." rows<br />";
}

?>


Is there something I forgot to change?

Looks like you might have forgotten to supply a database name...


<?php

$server = "localhost";
$username = "root";
$password = "";
$database = "my_database";

$cxn = mysql_connect($server, $username, $password) or die("Could not connect.");
mysql_select_db($database,$cxn);

$query = "SHOW TABLES";
$res = mysql_query($query,$cxn);
while($tables = mysql_fetch_assoc($res))
{
//Get all records in each table
list(,$tablename) = each($tables);
$query = "SELECT * FROM ".$tables[$tablename];
echo "In table $tablename there are ".mysql_num_rows(mysql_query($query))." rows<br />";
}

?>


I also added a link-identifier for the MySQL connection.

Regards,
Patrick

Originally posted by fyrestrtr
http://www.php.net/manual/en/function.mysql-list-dbs.php

Read that...now I'm getting the feeling you want us to do your work for you. I believe my example and that link would be enough.

Nope I am not. I just can't get the DB to display a specified Table and the rows in the Table to load...
Where the pointer you showed me just loads the DB name.

$databaeName <--- what is this supposed to be?

Fyrestrtr,

Tired-typing for $databaseName to select :)

Regards,
Patrick

I want to make it display all databases.... not a specific database. in the commandline its with the --all-database command I think but I don't know what for PHP....

READ THIS ENTRY (http://www.php.net/manual/en/function.mysql-list-dbs.php)

Especially the example. I really don't know what else you want -- other than for us to write you the entire thing.

What I wanted to ask was if it was possible to get a script or under SSH to generate an output that displays the Database name and the number of rows in a specified table.

Because I only know how to get it to either display all the databases or just the databes and its tables in the Command line.

But I am unable to get it to do so to display something like


DB1 has 6 rows in "table 2"
DB2 has 12 rows in "table 2"
DB3 has 0 rows in "table 2"


Where I only want to display "table2" and the number of rows in table 2 but for all the databases in the SQL server

from what I was doing perviously over at the command line was to use
"mysqlshow" or through the "mysql" command line but these only shows me the databes, tables or columns.... but I am not able to get it to tell me how many columns are there in a specific table in all the databases on the server because the --all-databeses command and wildcards don't seem to work....

Thats why I thought maybe php is able to do it.

If it is, then because of my limited knowledge in php I would engage a programmer to help me solve this issue.
I just need to know if it is possible to display these because the programmer would be more suited than me to go and write the coding.....

Okay the answer to your question is yes. It is possible to write a PHP script to do this, and have this PHP script run form the command line to show you the results.

You just need to find someone that knows more about PHP. Try posting a request in the Related offers and requests forum.

Have actually got it to display all databases' tables and the amount of rows in it.
Now I need to find out how to make it display only specific rows.

Thanks for the help.