Echo MySQL Table as HTML Table

I need to write a simple script to echo out a MySQL table as an HTML table. I have an array, $shortnames, extracted from the URL (GET-method form submission), that contains the names of the HTML columns I wish to show. They correspond to the field 'shortnames' in MySQL.

What is the code I can use to convert the table from MySQL to HTML? I tried with the following code, but it does not work correctly. (I also know I have some security issues; if you can help with that as well, I would be grateful.)

<?php
$shortnames = $_GET['vs'];
include_once('connect.php');
$connection = mysqli_connect($host,$user,$password) or die();
mysqli_select_db($connection,$db);
?>
<table>
<?php
$query = mysqli_query($connection, "SELECT * FROM compare_wearables ORDER BY name") or die(mysqli_error($db));
$spec = mysqli_fetch_array($query);
foreach($spec as $row) {
    echo "\t<tr>";
    foreach($shortnames as $column) {
        echo "<td>cell</td>";
    }
    echo "</tr>\n";
}
mysqli_close($connection);
?>
</table>

PHP:

 

Try below code.

<?php
$shortnames = $_GET['vs'];
include_once('connect.php');
$connection = mysqli_connect($host,$user,$password) or die();
mysqli_select_db($connection,$db);
?>
<table>
<?php
$query = mysqli_query($connection, "SELECT * FROM compare_wearables ORDER BY name") or die(mysqli_error($db));
$spec = mysqli_fetch_array($query);
foreach($spec as $row) {
    echo "\t<tr>";
    foreach($shortnames as $column) {
        echo "<td>".$row[$column]."</td>"; // I updated this line
    }
    echo "</tr>\n";
}
mysqli_close($connection);
?>
</table>

PHP:

 

Warning: Illegal string offset

 

14, the line you edited. And none, because I have no idea what the error means, or what could be causing it.

 

I rewrote some code. Essentially what I want is this, with the table flipped the other way.

<?php
$shortnames = $_GET['vs'];
include_once('connect.php');
$connection = mysqli_connect($host,$user,$password) or die();
mysqli_select_db($connection,$db);
?>
<table>
<?php
$query = mysqli_query($connection, "SELECT * FROM compare_wearables ORDER BY name") or die(mysqli_error($db));
while($spec = mysqli_fetch_assoc($query)) {
    echo "\t<tr>";
    for($columns=1; $columns<count($shortnames); $columns++) {
        echo "<td>".$spec['name']."</td>";
        echo "<td>".$spec['activity_calories']."</td>";
        echo "<td>".$spec['price']."</td>";
    }
    echo "</tr>\n";
}

print_r($spec);

mysqli_close($connection);
?>
</table>

Code (markup):

 

Hi Jason.

Can you show an example of the table that needs to be generated?

 

I think I figured out a solution that will work. I'll turn the lists into loops to clean it up a bit.

<?php
$shortnames = $_GET['vs'];
include_once('connect.php');
$connection = mysqli_connect($host,$user,$password) or die();
mysqli_select_db($connection,$db);
?>
<table>
<?php
$query = mysqli_query($connection, "SELECT * FROM compare_wearables ORDER BY name") or die(mysqli_error($db));

$specs['shortname'] = array();
$specs['name'] = array();
$specs['activity_calories'] = array();
$specs['activity_distance'] = array();
$specs['activity_heartRate'] = array();
$specs['activity_moveReminder'] = array();
$specs['activity_skinTemp'] = array();
$specs['activity_sleep'] = array();
$specs['activity_steps'] = array();
$specs['activity_swim'] = array();
$specs['activity_perspiration'] = array();
$specs['battery_duration'] = array();
$specs['battery_rechargeable'] = array();
$specs['compat_Android'] = array();
$specs['compat_iOS'] = array();
$specs['compat_Mac'] = array();
$specs['compat_PC'] = array();
$specs['connect_Bluetooth'] = array();
$specs['connect_GPS'] = array();
$specs['connect_USB'] = array();
$specs['connect_WiFi'] = array();
$specs['display_size'] = array();
$specs['display_resolution'] = array();
$specs['display_touch'] = array();
$specs['display_type'] = array();
$specs['feature_accelerometer'] = array();
$specs['feature_time'] = array();
$specs['feature_waterResistant'] = array();
$specs['feature_wirelessSync'] = array();
$specs['notif_apps'] = array();
$specs['notif_callerID'] = array();
$specs['notif_email'] = array();
$specs['notif_event'] = array();
$specs['notif_alarm'] = array();
$specs['notif_SMS'] = array();
$specs['price'] = array();

while($spec = mysqli_fetch_assoc($query)) {
    array_push($specs['shortname'], $spec['shortname']);
    array_push($specs['name'], $spec['name']);
    array_push($specs['activity_calories'], $spec['activity_calories']);
    array_push($specs['activity_distance'], $spec['activity_distance']);
    array_push($specs['activity_heartRate'], $spec['activity_heartRate']);
    array_push($specs['activity_moveReminder'], $spec['activity_moveReminder']);
    array_push($specs['activity_skinTemp'], $spec['activity_skinTemp']);
    array_push($specs['activity_sleep'], $spec['activity_sleep']);
    array_push($specs['activity_steps'], $spec['activity_steps']);
    array_push($specs['activity_swim'], $spec['activity_swim']);
    array_push($specs['activity_perspiration'], $spec['activity_perspiration']);
    array_push($specs['battery_duration'], $spec['battery_duration']);
    array_push($specs['battery_rechargeable'], $spec['battery_rechargeable']);
    array_push($specs['compat_Android'], $spec['compat_Android']);
    array_push($specs['compat_iOS'], $spec['compat_iOS']);
    array_push($specs['compat_Mac'], $spec['compat_Mac']);
    array_push($specs['compat_PC'], $spec['compat_PC']);
    array_push($specs['connect_Bluetooth'], $spec['connect_Bluetooth']);
    array_push($specs['connect_GPS'], $spec['connect_GPS']);
    array_push($specs['connect_USB'], $spec['connect_USB']);
    array_push($specs['connect_WiFi'], $spec['connect_WiFi']);
    array_push($specs['display_size'], $spec['display_size']);
    array_push($specs['display_resolution'], $spec['display_resolution']);
    array_push($specs['display_touch'], $spec['display_touch']);
    array_push($specs['display_type'], $spec['display_type']);
    array_push($specs['feature_accelerometer'], $spec['feature_accelerometer']);
    array_push($specs['feature_time'], $spec['feature_time']);
    array_push($specs['feature_waterResistant'], $spec['feature_waterResistant']);
    array_push($specs['feature_wirelessSync'], $spec['feature_wirelessSync']);
    array_push($specs['notif_apps'], $spec['notif_apps']);
    array_push($specs['notif_callerID'], $spec['notif_callerID']);
    array_push($specs['notif_email'], $spec['notif_email']);
    array_push($specs['notif_event'], $spec['notif_event']);
    array_push($specs['notif_alarm'], $spec['notif_alarm']);
    array_push($specs['notif_SMS'], $spec['notif_SMS']);
    array_push($specs['price'], $spec['price']);
}

foreach($specs as $spec) {
    echo "\t<tr>";
    for($column=0; $column<count($shortnames); $column++) {
        echo "<td>".$spec[$column]."</td>";
    }
    echo "</tr>\n";
}

?>
</table>

Code (markup):

http://wearabletechforums.com/compare/specs.php?vs%5B%5D=watch&vs%5B%5D=peak

 

Cleaned up the first list with a loop. Having trouble with the second.

<table>
<?php
$shortnames = $_GET['vs'];

include_once('connect.php');
$connection = mysqli_connect($host,$user,$password) or die();
mysqli_select_db($connection,$db);
$query = mysqli_query($connection, "SELECT * FROM compare_wearables ORDER BY name") or die(mysqli_error($db));

$fields=array(
    'shortname',
    'name',
    'activity_calories',
    'activity_distance',
    'activity_heartRate',
    'activity_moveReminder',
    'activity_skinTemp',
    'activity_sleep',
    'activity_steps',
    'activity_swim',
    'activity_perspiration',
    'battery_duration',
    'battery_rechargeable',
    'compat_Android',
    'compat_iOS',
    'compat_Mac',
    'compat_PC',
    'connect_Bluetooth',
    'connect_GPS',
    'connect_USB',
    'connect_WiFi',
    'display_size',
    'display_resolution',
    'display_touch',
    'display_type',
    'feature_accelerometer',
    'feature_time',
    'feature_waterResistant',
    'feature_wirelessSync',
    'notif_apps',
    'notif_callerID',
    'notif_email',
    'notif_event',
    'notif_alarm',
    'notif_SMS',
    'price'
);
  
foreach($fields as $field) {
    $specs[$field] = array();
}

while($spec = mysqli_fetch_assoc($query)) {
    array_push($specs['shortname'], $spec['shortname']);
    array_push($specs['name'], $spec['name']);
    array_push($specs['activity_calories'], $spec['activity_calories']);
    array_push($specs['activity_distance'], $spec['activity_distance']);
    array_push($specs['activity_heartRate'], $spec['activity_heartRate']);
    array_push($specs['activity_moveReminder'], $spec['activity_moveReminder']);
    array_push($specs['activity_skinTemp'], $spec['activity_skinTemp']);
    array_push($specs['activity_sleep'], $spec['activity_sleep']);
    array_push($specs['activity_steps'], $spec['activity_steps']);
    array_push($specs['activity_swim'], $spec['activity_swim']);
    array_push($specs['activity_perspiration'], $spec['activity_perspiration']);
    array_push($specs['battery_duration'], $spec['battery_duration']);
    array_push($specs['battery_rechargeable'], $spec['battery_rechargeable']);
    array_push($specs['compat_Android'], $spec['compat_Android']);
    array_push($specs['compat_iOS'], $spec['compat_iOS']);
    array_push($specs['compat_Mac'], $spec['compat_Mac']);
    array_push($specs['compat_PC'], $spec['compat_PC']);
    array_push($specs['connect_Bluetooth'], $spec['connect_Bluetooth']);
    array_push($specs['connect_GPS'], $spec['connect_GPS']);
    array_push($specs['connect_USB'], $spec['connect_USB']);
    array_push($specs['connect_WiFi'], $spec['connect_WiFi']);
    array_push($specs['display_size'], $spec['display_size']);
    array_push($specs['display_resolution'], $spec['display_resolution']);
    array_push($specs['display_touch'], $spec['display_touch']);
    array_push($specs['display_type'], $spec['display_type']);
    array_push($specs['feature_accelerometer'], $spec['feature_accelerometer']);
    array_push($specs['feature_time'], $spec['feature_time']);
    array_push($specs['feature_waterResistant'], $spec['feature_waterResistant']);
    array_push($specs['feature_wirelessSync'], $spec['feature_wirelessSync']);
    array_push($specs['notif_apps'], $spec['notif_apps']);
    array_push($specs['notif_callerID'], $spec['notif_callerID']);
    array_push($specs['notif_email'], $spec['notif_email']);
    array_push($specs['notif_event'], $spec['notif_event']);
    array_push($specs['notif_alarm'], $spec['notif_alarm']);
    array_push($specs['notif_SMS'], $spec['notif_SMS']);
    array_push($specs['price'], $spec['price']);
}

$row=0;
foreach($specs as $spec) {
    echo "\t<tr class=\"".$fields[$row]."\">";
    for($column=0; $column<count($shortnames); $column++) {
        echo "<td class=\"".$specs['shortname'][$column]."\">".$spec[$column]."</td>";
    }
    echo "</tr>\n";
    $row++;
}

?>
</table>

PHP:

 

There is a neater and automatic way of doing this without writing very long lines of unreusable code. Have you tried bootstrap? Have a look at this: http://wenzhixin.net.cn/p/bootstrap-table/docs/examples.html

This will save you a lot of time. You can get the table to match the table fields straight from the MySQL results. Not to store the fields. Will require knowledge of JQuery.

 

Thanks! Good to know.

 

Thanks! I believe I'm all set at the moment. You can see it in action by running a comparison here:
http://wearabletechforums.com/compare/

Edit: Has someone disabled my ability to post links? I can't post any—even to Google.

 

does it have to be a table or can it be a stack of information? something Ajax could bring back and display in <div>
then you only need to echo the vars with a pagebreak at end . "<br>" .