RE: Table statistics
May the gods of mySQL please forgive me. I just joined the list last night, and thought, in seeing the question, I could provide a possible solution. I was unaware that there was only one acceptable answer, and that all posts had to be pre-checked. In the future I shall not make such a mistake again. BTW: I've heard of, and use quite frequently, mysql_free_result(). In this case, I cut-and-pasted (or is that cutted-and-pasted) existing code in an existing file and at 12:30 a.m. I'm not my sharpest. And Nick, it is PHP (4), not PERL. I don't know PERL, never written a line of code in PERL and don't have any immediate plans to learn PERL. Again I ask forgiveness of the lords and warlocks of the mySQL realm. If a sacrifice is required, please advise as to the form. Doug Bishop -Original Message- From: Georg Richter [mailto:[EMAIL PROTECTED]] Sent: Thursday, April 04, 2002 12:31 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Table statistics On Thursday, 4. April 2002 08:12, Doug Bishop wrote: > mysql_connect("localhost", "user", "password"); > $query = "SHOW TABLES;"; > $result = mysql_db_query("databasename", $query); > $i = 0; > while ($row = mysql_fetch_array($result)) > { > $tableNames[$i] = $row[0]; > $i++; > } > for ($i = 0; $i <= count($tableNames); $i++) > { > $query = "SELECT COUNT(*) FROM " . $tableNames[$i] . ";"; > $result = mysql_db_query("databasename", $query); > $row = mysql_fetch_array($result); > echo "Table " . $tableNames[$i] . " contains " . $row[0] . " rows.\n"; > } > ?> I think Andy asked for a general solution, not for a solution in PHP. Not all people are working with PHP. There are a lot of other languages like C, Perl, Python, ... which all supports MySQL. Finally a little note about your PHP Code: Did you ever heard about mysql_free_result ?! Regards Georg - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Table statistics
On Thu, 4 Apr 2002, Georg Richter wrote: > On Thursday, 4. April 2002 10:42, andy thomas wrote: > > > Yes but this is PHP code that can only be run through a suitable web > > server, browser, etc - I want a command line solution like mysqlshow ... > > or from within the mysql client itself. > > You can also run PHP from the commandline without any webbrowser. I did ask about this recently over in the php-general mailing list and someone said it was possible to do this in the Windows implementation and building a stand-alone PHP for Unix was documented in the INSTALL file. But I couldn't find it. Andy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Table statistics
On Thu, 4 Apr 2002, denonymous wrote: > From: "andy thomas" <[EMAIL PROTECTED]> > > > Is there a command I can give in the mysql client to find the number of > > rows in a table or, better still, the number of rows in all the tables in > > a database? > > > To return the # of rows in a table: > SELECT COUNT(*) FROM table_name; Thanks, this works fine! I keep apache access logs for web servers in MySQL databases and was wondering why the database for March's logs on a particular server was about a third of the size of February's even though the access stats looked about the same for those months. Hence the need to compare the table sizes. cheers, Andy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Table statistics
On Thursday, 4. April 2002 10:42, andy thomas wrote: > Yes but this is PHP code that can only be run through a suitable web > server, browser, etc - I want a command line solution like mysqlshow ... > or from within the mysql client itself. You can also run PHP from the commandline without any webbrowser. Regards Georg - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Table statistics
* andy thomas > Is there a command I can give in the mysql client to find the number of > rows in a table or, better still, the number of rows in all the > tables in a database? The "normal" way: SELECT COUNT(*) FROM table To get all tables in one statement... this is a hack, I'm not sure it will _allways_ give the correct answer, but try this: DESC SELECT * FROM table1,table2,table3,table4,table5 The row count is returned in the coulmn 'rows'. -- Roger query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Table statistics
On Wed, 3 Apr 2002, Nick Arnett wrote: > > > > -Original Message- > > From: Doug Bishop [mailto:[EMAIL PROTECTED]] > > Sent: Wednesday, April 03, 2002 10:13 PM > > To: [EMAIL PROTECTED]; [EMAIL PROTECTED] > > Subject: RE: Table statistics > > > > > > Try: > > > > > mysql_connect("localhost", "user", "password"); > > ... > > Might be helpful to the original poster to mention that this is Perl. He > didn't specify a language, so he may not comprehend what you've offered. This is actually PHP which I use a lot myself. But I wanted to know if there was a simple command for this within mysql, or some option to mysqlshow. It's far quicker to type somethinmg on the command line than fire up a web browser, etc. And there must be a lot of MySQL installations on servers which aren't also web servers, or don't have PHP installed, etc. Actually, it would be very nice if MySQL had a shell API - you can do this to some extent by piping a command file into it but it involves a fair amount of work to get the same functionality you get with, say, the C or PHP interfaces. > I don't mean this as a criticism, but it's interesting that we seem to often > assume that Perl is THE language for scripting MySQL. Not true, of course. > In fact, I'm finding Python easier, except for the blasted printf type > formatting, whose syntax was driving me batty yesterday until... well, I > could go on, but I won't. Try PHP? ;-) cheers, Andy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Table statistics
On Thu, 4 Apr 2002, Doug Bishop wrote: > Try: > > mysql_connect("localhost", "user", "password"); > $query = "SHOW TABLES;"; > $result = mysql_db_query("databasename", $query); > $i = 0; > while ($row = mysql_fetch_array($result)) > { > $tableNames[$i] = $row[0]; > $i++; > } > for ($i = 0; $i <= count($tableNames); $i++) > { > $query = "SELECT COUNT(*) FROM " . $tableNames[$i] . ";"; > $result = mysql_db_query("databasename", $query); > $row = mysql_fetch_array($result); > echo "Table " . $tableNames[$i] . " contains " . $row[0] . " rows.\n"; > } > ?> Yes but this is PHP code that can only be run through a suitable web server, browser, etc - I want a command line solution like mysqlshow ... or from within the mysql client itself. Andy > -Original Message- > From: andy thomas [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, April 03, 2002 11:23 PM > To: [EMAIL PROTECTED] > Subject: Table statistics > > > Is there a command I can give in the mysql client to find the number of > rows in a table or, better still, the number of rows in all the tables in > a database? > > Andy > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Table statistics
On Thursday, 4. April 2002 08:12, Doug Bishop wrote: > mysql_connect("localhost", "user", "password"); > $query = "SHOW TABLES;"; > $result = mysql_db_query("databasename", $query); > $i = 0; > while ($row = mysql_fetch_array($result)) > { > $tableNames[$i] = $row[0]; > $i++; > } > for ($i = 0; $i <= count($tableNames); $i++) > { > $query = "SELECT COUNT(*) FROM " . $tableNames[$i] . ";"; > $result = mysql_db_query("databasename", $query); > $row = mysql_fetch_array($result); > echo "Table " . $tableNames[$i] . " contains " . $row[0] . " rows.\n"; > } > ?> I think Andy asked for a general solution, not for a solution in PHP. Not all people are working with PHP. There are a lot of other languages like C, Perl, Python, ... which all supports MySQL. Finally a little note about your PHP Code: Did you ever heard about mysql_free_result ?! Regards Georg - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Table statistics
Nick Arnett wrote: > > > -Original Message- > > From: Doug Bishop [mailto:[EMAIL PROTECTED]] > > Sent: Wednesday, April 03, 2002 10:13 PM > > To: [EMAIL PROTECTED]; [EMAIL PROTECTED] > > Subject: RE: Table statistics > > > > > > Try: > > > > > mysql_connect("localhost", "user", "password"); > > ... > > Might be helpful to the original poster to mention that this is Perl. He > didn't specify a language, so he may not comprehend what you've offered. > > I don't mean this as a criticism, but it's interesting that we seem to often > assume that Perl is THE language for scripting MySQL. Not true, of course. > In fact, I'm finding Python easier, except for the blasted printf type > formatting, whose syntax was driving me batty yesterday until... well, I > could go on, but I won't. > > Nick Actually, Nick; I don't think you're being presumptious, at all. Personally, I prefer the ASS language since it allows me to access both XML and quasi-PHP APIs in the same IDE-hole and across hardware/os environments; but hey that's me. What do I know? Van -- = Linux rocks!!! http://www.dedserius.com/ = - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Table statistics
Hi. The simple way is use "SHOW TABLE STATUS FROM DATABASE_NAME" Like this in php script (you also put this SQL into any application that you need) "; echo "Table nameRecords"; while ($row=mysql_fetch_array($rs)) { echo "",$row['Name'],"",number_format($row['Rows']),""; } echo ""; mysql_close ($conn); ?> you will see the result in table (tag html) format with the name of data table and it's number of records. Kittiphum Worachat,M.T. www.hatyailab.com - Original Message - From: "Doug Bishop" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Thursday, April 04, 2002 1:12 PM Subject: RE: Table statistics > Try: > > mysql_connect("localhost", "user", "password"); > $query = "SHOW TABLES;"; > $result = mysql_db_query("databasename", $query); > $i = 0; > while ($row = mysql_fetch_array($result)) > { > $tableNames[$i] = $row[0]; > $i++; > } > for ($i = 0; $i <= count($tableNames); $i++) > { > $query = "SELECT COUNT(*) FROM " . $tableNames[$i] . ";"; > $result = mysql_db_query("databasename", $query); > $row = mysql_fetch_array($result); > echo "Table " . $tableNames[$i] . " contains " . $row[0] . " rows.\n"; > } > ?> > > Doug > > -Original Message- > From: andy thomas [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, April 03, 2002 11:23 PM > To: [EMAIL PROTECTED] > Subject: Table statistics > > > Is there a command I can give in the mysql client to find the number of > rows in a table or, better still, the number of rows in all the tables in > a database? > > Andy > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Table statistics
> -Original Message- > From: Doug Bishop [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, April 03, 2002 10:13 PM > To: [EMAIL PROTECTED]; [EMAIL PROTECTED] > Subject: RE: Table statistics > > > Try: > > mysql_connect("localhost", "user", "password"); ... Might be helpful to the original poster to mention that this is Perl. He didn't specify a language, so he may not comprehend what you've offered. I don't mean this as a criticism, but it's interesting that we seem to often assume that Perl is THE language for scripting MySQL. Not true, of course. In fact, I'm finding Python easier, except for the blasted printf type formatting, whose syntax was driving me batty yesterday until... well, I could go on, but I won't. Nick - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Table statistics
Try: "; } ?> Doug -Original Message- From: andy thomas [mailto:[EMAIL PROTECTED]] Sent: Wednesday, April 03, 2002 11:23 PM To: [EMAIL PROTECTED] Subject: Table statistics Is there a command I can give in the mysql client to find the number of rows in a table or, better still, the number of rows in all the tables in a database? Andy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Table statistics
From: "andy thomas" <[EMAIL PROTECTED]> > Is there a command I can give in the mysql client to find the number of > rows in a table or, better still, the number of rows in all the tables in > a database? To return the # of rows in a table: SELECT COUNT(*) FROM table_name; Not sure about all rows from all tables, though. -- denonymous www.coldcircuit.net - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Table statistics
Is there a command I can give in the mysql client to find the number of rows in a table or, better still, the number of rows in all the tables in a database? Andy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php