Select and Display All Fields from MySQL Table for indefinite amount of columns with PHP

Question

I'm trying to write a PHP function that will output all fields in HTML for many different database tables. The tables vary in rows and also in columns, so I'm trying to come up with a method.

Like the example here - How get all values in a column using PHP?

If I don't know how many columns are in each row in the database, or their names, though, how can I output the data from a "SELECT * FROM $mytable" query when each database varies in columns AND rows?

You want to dump a mysql database to html? why? If you want to visualize your database easy look into phpmyadmin. — Erik, Sep 13 '12 at 19:05
So is the question about outputting a variable number of fields or about formatting HTML? This seems very all encompassing. So why do you need to set variables like `$column1`, `$column2`, etc.? This has nothing to do with format of output. What are you really trying to do? Are you trying to solve this with a shell script or in PHP? — Mike Brant, Sep 13 '12 at 19:06
Typically, the standard way of outputting all data in a MySQL table through PHP is by making a while loop of the array you get through your query, and then displaying each field by calling that field's name. Sorry if what I showed was confusing. I want to simply call a MySQL "Select * FROM $mytablevariable" and then output that as HTML, through PHP. — RCNeil, Sep 13 '12 at 19:08
What exactly is wrong with using the `while` loop? I understand not wanting to use static field names to access row values, given that you may not always know the column names. But I'm at a loss as to why you don't want to use the loop. — Patrick Q, Sep 13 '12 at 19:09
Because my understanding is the while loop requires you to know the column name of your database table, and if I use this function for MULTIPLE database tables, I won't be able to provide that in the code, as they vary — RCNeil, Sep 13 '12 at 19:11
The `while` loop goes through the rows, not the columns. See my answer below for an example of something that should at least get you going in the right direction — Patrick Q, Sep 13 '12 at 19:15
That's exactly my point Patrick Q. The number of rows can be indefinite and using `while` is no problem. The number of COLUMNS in the ROW, however, also varies, and thus how would I output all the columns in each row if I don't know how many there are? — RCNeil, Sep 13 '12 at 19:18

Patrick Q · Accepted Answer · 2016-11-18T13:08:26.200

5

Maybe something like this?

echo "<table>";

while ($row = mysql_fetch_array($query))
{
    echo "<tr>";

    foreach($row as $value)
    {
        echo "<td>".$value."</td>";
    }

    echo "</tr>";

}

echo "</table>";

UPDATE

Since this answer still seems to be getting some attention, I feel that it warrants noting that it since it is based on an example (linked in the original question) that is now outdated, this answer itself is also outdated. This is due to the use of the mysql extension which is now deprecated/removed (depending on your version of PHP). Instead, you should be using PDO or mysqli

edited Nov 18 '16 at 13:08

answered Sep 13 '12 at 19:13

Patrick Q

6,373
2
25
34

2

What the what? Why the downvote? And with no comment giving a reason? Come on people. – Patrick Q Sep 13 '12 at 19:24
1

The example you provide loops through an indefinite amount of ROWS, not columns. This is the traditional way of outputting the results from your query, when you KNOW there is only 1 "value" in this instance. When there are multiple columns in each row, however, this will not work. – RCNeil Sep 13 '12 at 19:26
@RCNeil No, you are incorrect. My answer loops through both the rows AND the columns. That is what the `foreach` is doing. The `while` gets one row at a time and stores it into the `$row` variable. You then loop through each column of the row using the `foreach` loop. – Patrick Q Sep 13 '12 at 19:30
Alright. It ain't working for me, so I'm not 100% sure this is a correct method, but I'll eat it when I'm wrong.... – RCNeil Sep 13 '12 at 19:43
@PatrickQ I'm trying this at the moment, but i get a duplication of every value. The rows output looks correct, just the values duplicating. Any ideas why that would happen? Cheers! – jason3w Jul 02 '14 at 23:28
It's ok i was using the fetch_array not fetch_assoc. All good now cheers! – jason3w Jul 03 '14 at 00:51

score 3 · Answer 2 · edited Feb 10 '16 at 14:03

this builds a table with header row containing the column names. could be more eloquent in places, but down and dirty solution if I understand your requirement.

function tableme($result){
    $header='';
    $rows='';
    while ($row = mysql_fetch_array($result)) { 
        if($header==''){
            $header.='<tr>'; 
            $rows.='<tr>'; 
            foreach($row as $key => $value){ 
                $header.='<th>'.$key.'</th>'; 
                $rows.='<td>'.$value.'</td>'; 
            } 
            $header.='</tr>'; 
            $rows.='</tr>'; 
        }else{
            $rows.='<tr>'; 
            foreach($row as $value){ 
                $rows .= "<td>".$value."</td>"; 
            } 
            $rows.='</tr>'; 
        }
    } 
    return '<table>'.$header.$rows.'</table>';
}

just call it with your $result from your query;

 echo tableme($result);

Excellent, thank you! Very handy for [*quick-and-dirty*](https://niksilver.com/2007/07/10/the-truth-about-quick-and-dirty/) reporting. In my case I just needed to change: . . . `while ($row = mysql_fetch_array($result)) {` to: `while ($row = $result->fetch_assoc()) {` — ashleedawg, Mar 02 '19 at 15:29

score 0 · Answer 3 · answered Sep 06 '13 at 06:51

$query = "SELECT * FROM `ios7_google`";

$result = mysql_query("SHOW COLUMNS FROM `ios7_google`.`graph_geo_table`");
if (!$result) {
    echo 'Could not run query: ' . mysql_error();
    exit;
}
if (mysql_num_rows($result) > 0) {
    while ($row = mysql_fetch_assoc($result)) {
    mssql_field_name($result,0);
    //    print_r($row);
    }
}

Valdorn · Answer 4 · 2014-02-01T15:52:31.887

I'm new to PHP and appreciated the post from Dave (edited by Patrick Q) previously. I have built on their example in the mysqli version below. Heavy commenting provided to help others understand what's going on... As a function I've got it included for every page that has tabular output now.

/*
Function: tabular_output (mysqli_query) as string
Utilization: Given the results of a successful mysqli_query, produce a dynamic table based on the columns identified.
<th> contents are based of the fields of the array.
<td> contents are based on contents associated to the relevant fields.
The table is not limited in size or length and table attributes can be controlled external to the function via CSS

Ref: http://www.php.net/manual/en/mysqli-result.fetch-fields.php
Ref: http://stackoverflow.com/questions/12413064/select-and-display-all-fields-from-mysql-table-for-indefinite-amount-of-columns
*/

function tabular_output($sql_result){
//Initiate header and rowdata for the table output.
$header='';
$rowdata='';

//Initiate header string.
$header='<tr>';
    //Assign the $sql_result field attributes to the $field array to output column headings.
    $field= mysqli_fetch_fields($sql_result);

    //Iterate through the field array to produce the name value in the header.
    foreach ($field as $val) {
        $header.= "<th>".$val->name."</th>";
    }

//Header string complete.
$header.='</tr>';

//Iterate through the data ($row) contained within the passed $sql_result.
while ($row = mysqli_fetch_array($sql_result))
{
    //Assign the $sql_result field attributes to the $field array to dynamically handle data contents.
    $field= mysqli_fetch_fields($sql_result);

    //Initiate the rowdata string.
    $rowdata.='<tr>';

    //Iterate through the field array to produce the associated $rowdata element.
    foreach ($field as $val) {
        $rowdata.= "<td>".$row[$val->name]."</td>";
    }
    $rowdata.='</tr>';

} //Rowdata string complete.
//Return the finished table string to the referring procedure.
return '<table>'.$header.$rowdata.'</table>';
}

score -1 · Answer 5 · answered Sep 13 '12 at 19:12

-1

You can use the SQL command "SHOW TABLES" to get a list of all tables in the database.

Then you can loop through the result and query each table for its fields.

answered Sep 13 '12 at 19:12

JvdBerg

21,777
8
38
55

that wasn't the question. – ashleedawg Mar 02 '19 at 14:54

Select and Display All Fields from MySQL Table for indefinite amount of columns with PHP

5 Answers5

Linked