counting all records in tables containing 'windows_'

[87dave87]

What is the code to count all records in tables with the table name beginning with 'windows_'?

I have tables beginning with 'windows_' 'mac_' and 'linux_'.

[shoz]

You can use "SHOW TABLES LIKE 'windows_%' to retrieve the tables and then create a query that looks similar to the following

MYSQL 4.1 or higher
[code]
SELECT (SELECT COUNT(*) FROM table1) + (SELECT COUNT(*) FROM table2) AS total
[/code]

Post the version of MYSQL you're using (SELECT VERSION()) and the code you've written if you have any problems.

[HuggieBear]

This should also give you what you're after, and maybe slightly easier than what Shoz posted, but it does involve more queries.

[code]<?php
// This will give you a list of tables
$sql = "SHOW TABLES LIKE 'windows_%'";
$result = mysql_query($sql) or die ("Couldn't execute $sql: " . mysql_error());
while ($table = mysql_fetch_array($result, MYSQL_NUM)){
  $tables[] = $table[0]; // Put each table into an array
}

// Loop through the tables getting the totals for each
foreach ($tables as $t){
  $sql = "SELECT COUNT(*) FROM $t";
  $result = mysql_query($sql) or die ("Couldn't execute $sql: " . mysql_error());
  $total += mysql_result($result, 0); // add the count to the running total
}

// Echo the total posts
echo $total;
?>[/code]

Regards
Huggie

[shoz]

I forgot about "TABLE STATUS". You can also use

[code]
SHOW TABLE STATUS LIKE 'windows_%'
[/code]

The column "Rows" will give you the number that you can sum.

[HuggieBear]

Shoz,

That will work so long as the table type is that of MyISAM or ISAM.  If it's InnoDB then it will only be an estimated row count.

Regards
Huggie

[shoz]

[quote author=HuggieBear link=topic=124416.msg515447#msg515447 date=1170001020]
Shoz,

That will work so long as the table type is that of MyISAM or ISAM.  If it's InnoDB then it will only be an estimated row count.

Regards
Huggie
[/quote]

You're right. Good call.

[boo_lolly]

[url=http://php.net/mysql_num_rows]mysql_num_rows()[/url]???

[HuggieBear]

[quote author=boo_lolly link=topic=124416.msg515491#msg515491 date=1170006562]
[url=http://php.net/mysql_num_rows]mysql_num_rows()[/url]???
[/quote]

Boo, that's not really appropriate in this situation.

Regards
Huggie