Display MySQL Data in HTML Table dynamically using PHP 5+ with custom column headings

Question

I am new to PHP. I have around 20 columns in a MySQL table. I want to let users select the columns they want to view, so I have made them clickable via checkboxes and gather the selected columns in an array. Somehow I have managed to build the query. But the problem is that I have custom column headings. For example if my column name in MySQL table is student_name I want to display it as "Student Name" (without the quotes). Along with that I want to display only those columns that were selected by the user.

For example I have this MySQL table: student

<table>
<tr><td>student_id</td><td>student_name</td><td>student_roll_no</td></tr>
<tr><td>1 </td><td>           Mr.Red    </td><td>     17</td></tr>
<tr><td>2  </td><td>          Mr.Green  </td><td>     20  </td></tr>    
<tr><td>3  </td><td>          Mr.Orange  </td><td>    21</td></tr>
<tr><td>4   </td><td>         Mr.Red     </td><td>    22</td></tr>
</table>

Then any of the columns student_id, student_name, student_roll_no (one, two or all of them) can be selected by the user with the given checkboxes.

I then build a query like

Select student_id, student_name from student where student_name='Mr.Red';

When the query is run I want to display its result like this:

<table>
<tr><th>Student ID </th><th>     Student Name</th><tr>
 <tr><td>   1   </td><td>           Mr.Red</td><tr>
 <tr><td>   4     </td><td>         Mr.Red</td><tr>
</table>

How can I achieve this?

Note that I want to use the improved MySQL functions. Thanks in advance.

http://stackoverflow.com/questions/17902483/show-values-from-a-mysql-database-table-inside-a-html-table-in-a-page — Abhik Chakraborty, Mar 13 '14 at 07:35
Thanks for your attempt. But I want the rows to be printed dynamically, like... using a loop, also print the column headers which not same as MySQL table column names. — Abhijit, Mar 13 '14 at 14:26

score 0 · Answer 1 · answered Mar 14 '14 at 00:47

You should take a look at mysqli_fetch_fields.

Basically the example #1 shows what you have to do to achieve your desired result:

$query = "SELECT Name, SurfaceArea from Country ORDER BY Code LIMIT 5";

if ($result = $mysqli->query($query)) {

    /* Get field information for all columns */
    $finfo = $result->fetch_fields();

    foreach ($finfo as $val) {
        printf("Name:     %s\n", $val->name);
        /* [...] */
    }
    $result->close();
}

You have to mix in appropriate HTML code for the table headings. And insert your loop to output the actual rows above $result->close();.

Display MySQL Data in HTML Table dynamically using PHP 5+ with custom column headings

1 Answers1