Get name columns above table from database

Question

I've written code that will dynamically generate a table based on the result set of a query. Each field in each row gets placed in its own cell. How can I add the column names as headers over the appropriate column?

My code:

$db = new mysqli("...", "...", "", "...");
$query = "SELECT * from customer ";
if ($result = $db->query($query)) {

    /* Get field information for all columns */
    while ($finfo = $result->fetch_field()) {
        printf("%s\n", $finfo->name);
    }
    $result->close();
}


$db = new mysqli('...', '...', '...', '...');
if($db->connect_errno > 0){
    die('Unable to connect to database [' . $db->connect_error . ']');
}
$sql = "SELECT * from ...";
if(!$result = $db->query($sql)){
    die('There was an error running the query [' . $db->error . ']');
}
echo "<table class='table'>";
while($row = $result->fetch_assoc()){
echo "<tr class='info'>
                <td>" . $row['COLUMN1'] . "</td>
                <td>" . $row['COLUMN2'] . "</td>
               <td>" . $row['COLUMN3'] . "</td>
                <td>" . $row['COLUMN4'] . "</td>
               <td>" . $row['COLUMN5'] . "</td>
               <td>" . $row['COLUMN6'] . "</td>
            </tr>";  
}
echo "</table>";

?>

------------ picture problem solved-------------

You only have 4 `` inside your loop, and in your image there are 6 columns ? — Akshay, Oct 25 '15 at 20:14
Using `SELECT * from customer ` just to get the column names is really bad. Put a LIMIT 1 on the query at least. — Ray, Oct 25 '15 at 20:16
@Akshay, you are correct, i did modify the code to make it shorter but not the picture, sorry. Anyway it works — gigi, Oct 25 '15 at 20:20
@Small Legend, I would like to diplay the column name on the top of the right column and not as now all the column name in a row.... — gigi, Oct 25 '15 at 20:21
@Ray, thanks for the tips, I am now on php so I have notice it was working and I did not change it. Dont think is that the main problem. — gigi, Oct 25 '15 at 20:22

score 1 · Accepted Answer · answered Oct 25 '15 at 20:23

1

You should only need to open 1 DB connection. You can use the fetch_field on the query you ran without iterating over the rows of the resultset. All the whitespace i'm adding is optional.

    $db = new mysqli("...", "...", "", "...");

    if($db->connect_errno > 0){
        die('Unable to connect to database [' . $db->connect_error . ']');
    }


    $sql = "SELECT * from ...";
    if(!$result = $db->query($sql)){
        die('There was an error running the query [' . $db->error . ']');
    }

    echo "
<table class='table'>
    <thead>
        <tr>";
    /* Get field information for all columns */
    while ($finfo = $result->fetch_field()) {
        echo "
        <th>" . $finfo->name . "</th>";
    }
    echo "
        </tr>
    </thead>
    <tbody>";
    while($row = $result->fetch_assoc()){
    echo "<tr class='info'>
            <td>" . $row['COLUMN1'] . "</td>
            <td>" . $row['COLUMN2'] . "</td>
            <td>" . $row['COLUMN3'] . "</td>
            <td>" . $row['COLUMN4'] . "</td>
        </tr>";  
    }
    echo "
    </tbody>
</table>";

answered Oct 25 '15 at 20:23

Your solution is working fine. Thanks to point out i should have used only one connection. Moreover I never thought about formatting the table in that way. Good tips! I uploaded a second picture implementing your code. Trying now to fix it in the senter so its 100% perfect. – gigi Oct 25 '15 at 20:29
@gigi actually, look like the problem is coming from the `id` column. Since you're using `SELECT *`, `fetch_fields` fetches and displays it despite you only wanting to display the other 6 columns. If you don't use the `id` column anywhere, the easiest solution is to change `SELECT *` to `SELECT name, mail, number, price, paymentType, pcname` – Oct 25 '15 at 20:43
I will try out your solution, Idid added the id column in the code and updated the picture solution. Does not seems to fix the problem but i am following this that might help. http://stackoverflow.com/questions/11678298/centering-text-in-a-table-in-twitter-bootstrap – gigi Oct 25 '15 at 20:49
1

@gigi oh i see what you meant by centering issue now. that question/answer does look like the answer to that – Oct 25 '15 at 20:52
As solution i changed the th{text-align:center;} in boostrap.min.css – gigi Oct 25 '15 at 21:51

Get name columns above table from database

1 Answers1