PHP Script - To Display all mysql tables data from one database problem

ciaramkm · 08-03-2012 11:32pm #1

Hi,

I have multiple tables in one database, each table has a different table name but similiar format for example ("aaaaa_bbbbb_8_03_12"). In each table all the column headings are the same but the data inserted is different. How can I display each table data in a php script. What I can do so far is display it for one table only knowing the table name but what I want is to be able to display each table without having to type in the table name because a new table will be inserted into the database every now and then and it will be available for viewing on a website then.

I can give you an example code I have that is able to display one table only but what I want is to be able to display all the tables data from one database rather than just one table only. I hope this is not confusing??

_______________________________________________

<html>

<?php

$connect = mysql_connect("******", "*****", "******") or

die ("Check your server connection");

mysql_select_db("databasename");

$quey1="select * from tablename";

$result=mysql_query($quey1) or die(mysql_error());

?>

<table border=1 style="background-color:#FFEBCD;">

<caption><EM>Records</EM></caption>

<tr>

<th>Number</th>

<th>Module Code</th>

<th>Room Number</th>

<th>Student ID Number</th>

<th>Date & Time</th>

</tr>

<?php

while($row=mysql_fetch_array($result)){

echo "<tr><td>";

echo $row;

echo "</td><td>";

echo $row;

echo "</td><td>";

echo $row;

echo "</td><td>";

echo $row;

echo "</td><td>";

echo $row;

echo "</td></tr>";

}

echo "</table>";

?>

</html>

FSL · 09-03-2012 12:28pm

Assuming MySQL is compliant to the standards you can get a list of tables from the database.

You can then loop through all the relevant tables.

dlofnep · 09-03-2012 12:31pm

In MySQL 5.0 you can list all the current tables in the current DB.

SELECT table_name FROM information_schema.tables WHERE table_schema='db_name'

amen · 09-03-2012 3:01pm

http://dev.mysql.com/doc/refman/5.0/en/show-tables.html

try this

ciaramkm · 09-03-2012 5:28pm

What I'm looking for is to display all the data from each table into each html table as shown in the php script above. Each table from the database has the exact same column headings/values. I can only do it for one table at a time but I want it to be able to do it for all tables no matter how many there is.

The Corinthian · 09-03-2012 5:42pm

As amen pointed out:

SHOW TABLES FROM myDatabase

Will return a recordset with all the table names in your schema called myDatabase. If you want then the field names for a table called myTable, you can do much the same with:

SHOW COLUMNSFROM myTable

As PHP this would look something like this:
[PHP]$rsTables = mysql_query("SHOW TABLES FROM ".$myDatabase);
while ($table= mysql_fetch_row($rsTables)) {
$rsFields = mysql_query("SHOW COLUMNS FROM ".$table[0]);
while ($field = mysql_fetch_assoc($rsFields)) {
echo $table[0].".".$field["Field"]."\n";
}
}[/PHP]
Which will output to the buffer all the database fields in a tablename.fieldname format. As the name "Field" in the inner loop would suggest, you can also pull out other info (e.g. "Type", "default", etc) for each field.

Based upon the above, you should be able to introduce a further nesting that does a "SELECT *" on each table, or in fact you could dispense with the second nest if all you want to do is spew out the contents without any special handling, especially if each table is identical in structure.

Disclaimer: I've not tested the above and am doing it largely from memory, so don't expect a seamless cut n' paste.

CuAnnan · 11-03-2012 9:37pm

Can I just ask, for my own clarification, if you have multiple tables that are structurally identical?

ciaramkm · 11-03-2012 9:40pm

CuAnnan wrote: »

Can I just ask, for my own clarification, if you have multiple tables that are structurally identical?

Yes all tables are structurally identical. I'm still trying to solve this problem.

CuAnnan · 11-03-2012 9:49pm

ciaramkm wrote: »

Yes all tables are structurally identical. I'm still trying to solve this problem.

If you have, for example, a series of school classes that you want to represent in a database, you should put them all in the one table.

In a second table you have the name of the class and a primary id of an autoincrement, serial or whatever the implementation of sequence your database uses.

The class id then acts as a foreign key in your first table. When you want to see only the students in class "Mrs Kimble's First Year" for example, you can look it up using a join.

I'm pretty sure this is the third normal form, but it's been a while since I studied the naming of the various database theories.

The Corinthian · 11-03-2012 9:57pm

You mean he should normalize his database.

I thought that myself, although there are sometimes perfectly valid reasons for not doing so. Most of the time it's just bad database design though.

CuAnnan · 11-03-2012 9:59pm

The Corinthian wrote: »

You mean he should normalize his database.

I thought that myself, although there are sometimes perfectly valid reasons for not doing so. Most of the time it's just bad database design though.

I would say "rarely" over "sometimes".

ciaramkm · 11-03-2012 10:17pm

It's more complicated in this situation though. I'll give an example to give a better insight about it. After let's say after a class is over, the table will be submitted into the database along with all the details aka id number, room number etc.. This process stays the same for all classes. The only difference about it is that it is that each time a new table is inserted into the database will have a different table name.

I had no problem doing this just for one table as you can see in the code below when I knew what the table name was except now this time I have to do it without knowing the table name and be able to display all table details - id numbers, room number etc into a html table for each table from the database all on one page. I tried following the code examples that ye suggested to me as posted previously..and I seem to be getting no luck still..

<html>

<?php

$connect = mysql_connect("******", "*****", "******") or

die ("Check your server connection");

mysql_select_db("databasename");

$quey1="select * from tablename";

$result=mysql_query($quey1) or die(mysql_error());

?>

<table border=1 style="background-color:#FFEBCD;">

<caption><EM>Records</EM></caption>

<tr>

<th>Number</th>

<th>Module Code</th>

<th>Room Number</th>

<th>Student ID Number</th>

<th>Date & Time</th>

</tr>

<?php

while($row=mysql_fetch_array($result)){

echo "<tr><td>";

echo $row;

echo "</td><td>";

echo $row;

echo "</td><td>";

echo $row;

echo "</td><td>";

echo $row;

echo "</td><td>";

echo $row;

echo "</td></tr>";

}

echo "</table>";

?>

</html>

CuAnnan · 11-03-2012 10:22pm

ciaramkm wrote: »

It's more complicated in this situation though. I'll give an example to give a better insight about it. After let's say after a class is over, the table will be submitted into the database along with all the details aka id number, room number etc.. This process stays the same for all classes. The only difference about it is that it is that each time a new table is inserted into the database will have a different table name.

I'm not seeing why this is anything other than really bad database design.

Having less tables to store the same information is not only more efficient, it's easier to query and can be properly indexed. What you're talking about doing isn't and can't be.

The Corinthian · 11-03-2012 10:41pm

CuAnnan wrote: »

I'm not seeing why this is anything other than really bad database design.

Following his most recent description, I concur. It was designed by someone who does not know what an RDB is.

What you're talking about doing isn't and can't be.

It can, but how it's structured is just a really dumb way of going about it.

OP, all you need to do is use "SHOW TABLES" to list out your table names into an array then iterate through that. The code for this is essentially in my previous post.

CuAnnan · 11-03-2012 11:25pm

The Corinthian wrote: »

It can, but how it's structured is just a really dumb way of going about it.

You'd have to create the indices on the fly, which means that you have a larger database and an inherently less efficient one, because indices cannot be applied to all fields. You can't possibly index practically, with mySQL, if the data is in seperate tables.

The Corinthian · 12-03-2012 2:22pm

CuAnnan wrote: »

You'd have to create the indices on the fly, which means that you have a larger database and an inherently less efficient one, because indices cannot be applied to all fields. You can't possibly index practically, with mySQL, if the data is in seperate tables.

My reading of his requirements is that he has data, kept in multiple, structurally identical tables, where the only unknown is the number and names of said tables. From this he only wants to list out the data in these tables - no particular order or filtering.

On this basis indexes aren't really that essential as all he's doing is treating his database as a text file. For what he's wants all he needs to do is first get a list of all the tables by querying the database with "SHOW TABLES", then loop through that array doing a "SELECT *" on each.

@OP; is this essentially what you want?

PHP Script - To Display all mysql tables data from one database problem

Comments