Calculating number of items in different tables.

[jmoore]

I have a problem that I have been trying to solve all day long. Everytime I think I am getting somewhere, I end up with another problem. I know there is a solution to this, I just don't know how to solve it. After spending all day trying to figure it out and searching for possible answers, I have decided to come here and see if someone can help me with this problem.

I am going to try my best to give you as much information as I can in order to obtain a possible solution.

Problem: I have several tables inside a database that automatically get created when added from a php script. Each table is uniquelly named so they stand out from the rest of the tables in the database. Inside of these tables hold entries for multiple items. I want to be able to calculate the amount of items in each table and display them on a web page.

Example of Database Tables:

- store_cElectronics
- store_cFurniture
- store_cClothing

"store_c" is what uniquelly seprates these tables from the rest of the tables in the database.

What I need to do is have the script go through each unique table and calculate the total number of items, which each item is seperated by rows in each table. After it calculates the total number of entries, I want it to give me some variables that I can use to place on a web page, along with the table's field name, which is stored in another table called "store_lists" with the field name of "Name".

An example of what I want displayed on the page would look like this:

$TableName: $total

Which would produce these results:

Electronics: 20
Furniture: 10
Clothing: 30

Each table name and total would need to be automatically generated by the script since a new table could be created at any time, and would have follow the set format. I am sorry for such a hard question, I am not sure if this can even be accomplised, but I know some mySQL guru will be able to come up with something. Also, it would be nice to see a sample code to do the above, since I am very clueless on how it should be programmed. I can get the table names to automatically display by using a code like this, the other, I am clueless:

$cid = mysql_connect($host,$user,$pass);
mysql_select_db($dbname); 
if (mysql_error()) {print "Database ERROR: " . mysql_error(); }
$sql = "SELECT t_name, Name FROM store_lists";
$rs = mysql_query($sql, $cid);
if (mysql_error()) {print "Database Error: $sql " . mysql_error(); }
while ($row = mysql_fetch_array($rs)) {

$TableName = $row["Name"];

print "$TableName<br>";
}

Thank you in advance for any solutions that you may come up with.

[Ramiro S]

Because you are creating tables on the fly youll need to use functions like mysql_list_tables adn for each result query that table... then you can store the result of each table in an array, something that will give:

$table['TableName1']=20
$table['TableName2']=143
$table['TableName2']=65

etc.