MySQL: The easiest way to display all data from the table by getting TABLE_NAME olny (no column information) on the html page

Question

MySQL db named "db2011" has several tables.

Using php variable $tablename (the tablename is correct, the table exists in the db), how to display the data from "db2011".$tablename on the html page?

Can it be done by performing just 1 query - to select all data by $tablename?

I think it can be done in 2 steps, but I'm asking any better solution (in case if this one is not good):

Step1: Get column names by performing "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='db2011' AND TABLE_NAME='".$tablename."'";
Step2: Build and perform a query with SELECT + list of columns separated by comma FROM $tablename?

P.S. I know the data could be huge to be displayed on the html page. I will limit it by 100 rows.

Any better ways?

Thank you.

I think one fast way of coding it would be to use your initial thought: dynamically generate your sql command using $tablename then pass it to the query executor. Other ways would be elegant-looking, but if you're a no-qualms type of person and you just need your data, go with that.. — Nonym, Nov 18 '11 at 20:34

score 3 · Accepted Answer · answered Nov 18 '11 at 20:54

3

I am assuming you are doing this in PHP. It may not be elegant, but it gets it in one query. I think you want to display the table columns as well as the data in one query.

<?php

$sql = "SELECT * FROM $tablename";
$res = mysql_query($sql);
$firstpass = TRUE;
while($row = mysql_fetch_assoc($res)){
    if($firstpass){
        foreach($row as $key => $value) {
           //store all the column names here ($key)
           $firstpass = FALSE;
        }
    }
    //Collect all the column information down here.
}
?>

answered Nov 18 '11 at 20:54

jjs9534

475
2
7

You could just as easily use mysql_fetch_array as well. – jjs9534 Nov 18 '11 at 20:55
Thank you! A small improvement to your code: $firstpass = FALSE; could be moved 1 row below (below "}"). – Haradzieniec Nov 18 '11 at 23:31

score 2 · Answer 2 · edited Nov 19 '11 at 00:58

2

Why not just SELECT * FROM $tablename limit 100;?

You would get all the column names back in the result set. Unless you also need the column type on your webpage I would go with just that

edited Nov 19 '11 at 00:58

Kev

118,037
53
300
385

answered Nov 18 '11 at 20:48

Andreas Wederbrand

38,065
11
68
78

MySQL: The easiest way to display all data from the table by getting TABLE_NAME olny (no column information) on the html page

2 Answers2