Echo Out MySQL Data into a Table

I’m looking to write a very simple script to echo out MySQL data into an HTML table using the following query. I haven’t coded in PHP in about a decade, however, and I’ve forgotten a lot. Would you please assist?

The query:

$sql = mysql_query(“SELECT * FROM compare_wearables WHERE shortname in $shortnames”) or die(mysql_error());

…where $shortnames is an array of 2 to 5 names, most likely. The number of columns is unknowable.

To clarify, I’d like $shortnames to be the table column headings, and the MySQL columns to be the table rows.

Thanks.

MySQL can’t access PHP arrays, for the IN clause, in brackets you’ll need to list the possibilities.

Where are the values for $shortnames coming from? Any user submitted data should never be trusted, it should be sanitized and escaped. The modern way of escaping data is to use prepared statements. Also you need to be aware that the mysql_* extension has been deprecated as of version 5.5 of PHP.

You might want to consider having a read of an up to date book such as PHP Novice To Ninja (by Kevin Yank - published by SitePoint) or do one of the PHP courses over at Learnable (https://learnable.com/home). You’ll probably find that a lot has changed with PHP over the last decade

You should be able to include PHP vars in the query, put it in quotes.
This is a simple example of a table from SQL:-

<table>
    <tr>
        <th>Col 1</th>
        <th>Col 2</th>
        <th>Col 3</th>
    </tr>
    <?php
            include_once "/home/user/.private/connect_db_rom.php";
            $strSQL = "SELECT * FROM dbname ORDER BY '$thisvar'" or die(mysql_error($db));
            $rs = mysqli_query($db, $strSQL);
            while($row = mysqli_fetch_array($rs)) {
                echo "\t<tr><td>".$row['col1data']."</td><td>".$row['col2data']."</td><td>".$row['col3data']."</td></tr>\n";
            }
            mysqli_close($db);
    ?>
</table>

[quote=“SpacePhoenix, post:2, topic:107468, full:true”]MySQL can’t access PHP arrays, for the IN clause, in brackets you’ll need to list the possibilities.[/quote]Each of the 30+ possibilities? What is the syntax?

[quote]You might want to consider having a read of an up to date book such as PHP Novice To Ninja (by Kevin Yank - published by SitePoint) or do one of the PHP courses over at Learnable (https://learnable.com/home). You’ll probably find that a lot has changed with PHP over the last decade[/quote]Thanks.

[quote=“SamA74, post:3, topic:107468”]You should be able to include PHP vars in the query, put it in quotes.
This is a simple example of a table from SQL:-[/quote]Thanks, although in your example I need to already know the number of table columns. Is this possible where the number of table columns is unknown?

Sorry, somehow overlooked that part about columns.
You will want to create the th’s within a ‘foreach’ to loop through the array.

Ok, I’m not exactly sure we’re on the same page. $shortnames is an array probably containing 2-5 values—the quantity may vary. I don’t know the number of table columns because I don’t know the number of values in the array. So I don’t know how many < td > tags across to have. Unfortunately I’ve forgotten too much PHP :frowning: Here’s where I’m at. I’ll also need to, as noted above, consider the security issue.

[code]<?php
$shortnames = $_GET[‘vs’];

include_once(‘connect.php’);
$connection = mysqli_connect($host,$user,$password) or die();
mysqli_select_db($connection,$db);
?>

<?php $query = mysqli_query($connection, "SELECT * FROM compare_wearables ORDER BY name") or die(mysqli_error($db)); while($row = mysqli_fetch_array($query)) { if(in_array($row['shortname'],$shortnames)) { echo "\t"; foreach($row as $column) { echo ""; } echo "\n"; } } mysqli_close($connection); ?>
".$column."
[/code]

This is the best I’ve been able to do so far. However, there are twice as many table rows as necessary, and I’m not sure what to put in place of the word “cell.” Please assist. Thank you.

[code]<?php $shortnames = $_GET['vs']; include_once('connect.php'); $connection = mysqli_connect($host,$user,$password) or die(); mysqli_select_db($connection,$db); ?>

<?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"; foreach($shortnames as $column) { echo ""; } echo "\n"; } mysqli_close($connection); ?>
cell
[/code]

As I read it, you pass in an array called $shortnames which is a list of columns that you want to display. If that’s the case, wouldn’t this do it, returning only the columns specified in your array?

$col = "";
foreach ($shortnames as $cn)
  {
  if ($col != "") $col .= ", ";
  $col .= $cn;
  }
// so now $col should contain a list of your column names
$query = "select $col from compare_wearables";

Or did I misunderstand the question?

Hm, yeah. I think I’m not being clear. I think part of the confusion is that MySQL “columns” are HTML table “rows.” i.e. MySQL field names are HTML column headings, yes?

Let me explain this way. I have this spreadsheet I am filling out and that I will be recreating in MySQL using PHPMyAdmin. The specs on the left will by my MySQL field names.

compare-specs.pdf (27.4 KB)

$shortnames includes the short names of the products (Apple Watch, etc.). I want the end result to be an HTML table that looks like my spreadsheet.

This is some old and very poor code I dug up, but it might be of some help for you

.......
      $query = "SELECT * FROM $tblname";
      $result = @mysql_query($query, $connection);
      $numrows = mysql_numrows($result);
      $numflds =  mysql_num_fields($result);
      if (($format == "csv")&&($output == "toScreen")){// CSV - SCRREEN - process request
         echo "<table><tr>";
         for ($f=0;$f<$numflds;$f++){
            if ($f < $numflds-1){
?>
<td><?php echo mysql_field_name($result, $f); ?>|</td>
<?php
            } elseif ($f == $numflds-1)  {
?>
<td><?php echo mysql_field_name($result, $f); ?></td>
<?php
            }
         }
         echo "</tr>";
         for ($r=0;$r<$numrows;$r++){
            for ($f=0;$f<$numflds;$f++){
               if ($f==0){
                  echo "<tr>";
               } elseif ($f < ($numflds-1)){
?>
<td><?php echo mysql_result($result,$r,$f); ?>|</td>
<?php
               } elseif ($f==($numflds-1)){
?>
<td><?php echo mysql_result($result,$r,$f); ?></td></tr>
<?php
               }
            }
         }               
         echo "</table>";

Ringing endorsement. :smile:
Anyone have any better code? This has got to be a script that developers use all the time.

It does work, just that mysql_ functions should be replaced with mysqli_ or PDO
And I don’t like the use of the @ error suppression
Nor do I like the “in and out of PHP” very much

(at the time mysql_ was all there was and I was a newbie unfamilliar with error and exception handling)

But you could use the logic to get what you’re after. If you’re wanting something you can copy-paste, sorry, I’m too busy to spend time fixing up something I haven’t needed to use for several years.

Ooookay. Well, using your code, I’m still not all the way there.
Catchable fatal error: Object of class stdClass could not be converted to string in on line 18

[code]<?php $shortnames = $_GET['vs']; include_once('connect.php'); $connection = mysqli_connect($host,$user,$password) or die(); mysqli_select_db($connection,$db); ?>

<?php $query = "SELECT * FROM compare_wearables ORDER BY name"; $result = mysqli_query($connection, $query) or die(mysqli_error($db)); $number_rows = mysqli_num_rows($result); $number_fields = mysqli_num_fields($result); for ($field = 0; $field < $number_fields; $field++) { if ($field < $number_fields-1) { ?> <?php } elseif ($field == $number_fields-1) { ?> <?php } } for ($row=0; $row < $number_rows; $row++){ for ($field = 0; $field<$number_fields; $field++) { if ($field==0) { echo "\t"; } elseif ($field < ($number_fields-1)) { ?> <?php } elseif ($f==($number_fields-1)) { ?> <?php } } } mysqli_close($connection); ?>
<?php echo mysqli_fetch_field_direct($result, $field); ?><?php echo mysqli_fetch_field_direct($result, $field); ?>
<?php echo mysqli_result($result,$row,$field); ?><?php echo mysqli_result($result,$row,$field); ?>
[/code]

According to this http://php.net/manual/en/mysqli-result.fetch-field-direct.php that function returns an object, so your error message is because you can’t echo an object. I haven’t done anything with mysqli so I can’t say more than than, examples on there though.

A few things abut the code below

  • I’m not familiar with using prepared statements with mysqli_ so the example uses PDO.
  • I didn’t spend to long in writing it so there may be errors.
<?php
list($headings, $rows) = getData($_GET['vs']);

echo table($headings, $rows);

function getData(array $shortnames)
{
    $headings= [''];
    $rows = [];
    $hash = [];

    try {
        $host = '<HOST>';
        $user = '<USERNAME>';
        $password = '<PASSWORD>';
        $database = '<DATABASE>';
        
        $dbh = new PDO("mysql:host=$host;dbname=$database", $user, $password);
    } catch (PDOException $e) {
        echo "Error!: {$e->getMessage()}<br/>";
        die();
    }        
        
    $in = implode(',', array_fill(0, count($shortnames), '?'));
    $stmt = $dbh->prepare("SELECT * FROM `compare_wearables` WHERE `shortname` IN($in) ORDER BY name");
    
    if ($stmt->execute($shortnames)) {
        while ($wearable = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $headings[] = $wearable['name'];
            
            /**
             * Don't want to include these two fields in our HTML table.
             */
            unset($wearable['shortname']);
            unset($wearable['name']);
            
            foreach ($wearable as $field => $value) {
                if (!isset($hash[$field])) {
                    $hash[$field] = [];
                }
                $hash[$field][] = $value;
            }
        }
    }
    
    foreach ($hash as $key => $values) {
        array_unshift($values, $key);
        $rows[] = $values;
    }

    return [$headings, $rows];
}

function table(array $headings, array $rows)
{
     $html = '<table><thead><tr>';
     foreach ($headings as $heading) {
        $html .= '<th>'.htmlentities($heading, ENT_QUOTES).'</th>'; 
     }
     $html .= '</tr></thead><tbody>';
     foreach ($rows as $cells) {
        $html .= '<tr>';
        foreach ($cells as $cell) {
            $html .= '<td>'.htmlentities($cell, ENT_QUOTES).'</td>'; 
        }
        $html .= '</tr>';
     }     
     $html .= '</tbody></table>';
     return $html;
}

Looks good David. I would think you’d need a $cells count to set colspan on those <th> tags. It was never really clear what columns this table has and the use of * over specific column names is a little ambiguous where specific columns could be displayed more properly, e.g. name or title in bold or <h> tags, id’s as links, image, description, price etc. I would think instead of each field in a cell, the details of the item could be presented in a single <td> with set formatting if those fields were known.

hm.
Error!: SQLSTATE[HY000] [2005] Unknown MySQL server host ‘’ (0)

I rewrote some code. Essentially what I’m trying to do is this, except I want the table flipped the other way.

[code]<?php $shortnames = $_GET['vs']; include_once('connect.php'); $connection = mysqli_connect($host,$user,$password) or die(); mysqli_select_db($connection,$db); ?>

<?php $query = mysqli_query($connection, "SELECT * FROM compare_wearables ORDER BY name") or die(mysqli_error($db)); $columns=count($shortnames); while($spec = mysqli_fetch_assoc($query)) { echo "\t"; for($column=1; $column<$columns; $column++) { echo ""; echo ""; echo ""; } echo "\n"; }

print_r($spec);

mysqli_close($connection);
?>

".$spec['name']."".$spec['activity_calories']."".$spec['price']."
[/code]

I think I figured out a solution that will work. Thank you, everyone.