PHP dynamic HTML table using SQL

Question

Background

I am using the below code to generate a HTML table based on SQL results.

Code

  $stid = oci_parse($conn, "

    SELECT *
    FROM
    (
     SELECT   orders.order_no, orders.porder_no, orders.date, order_totals.value
     FROM     orders, order_totals
     WHERE    orders.order_no = order_totals.order_no
     AND      orders.account_no = '" . $_SESSION['session_account'] . "'
     ORDER BY orders.order_no DESC
    )
    WHERE ROWNUM <= 15

  ");
  oci_execute($stid);

  echo "<table class='table'>
        <thread>
        <tr>
        <th>Order No</th>
        <th>Purchase Order No</th>
        <th>Date</th>
        <th>Value</th>
        </tr>
        </thread>
        <tbody>";

  while ($row = oci_fetch_array($stid, OCI_NUM)) {
    echo "<tr>";
    echo '<td><a href="view.php?id=' . $row['0'] . '">' . $row['0'] . '</a></td>';
    echo "<td>" . $row['1'] . "</td>";
    echo "<td>" . $row['2'] . "</td>";
    echo "<td>" . $row['3'] . "</td>";
    echo "</tr>";
    unset($row);
  }

  echo "</tbody>
        </table>";

Question

Is it possible to make the HTML table generation part in the code more dynamic, so that if I need to add an additional column for example I can just ammend the SQL part in the code?

I had an idea to set the column headings using AS in SQL and I can amend the SQL to use AS to show the real column headings I want for example

SELECT orders.order_no    AS "Order No"
,      orders.porder_no   AS "Purchase Order No"
,      orders.date        AS "Date"
,      order_totals.value AS "Total"

but what about the HTML table part, is there some method to just print all columns and rows dynamically, like maybe create a function printTable that would handle any table?

bloodyKnuckles · Accepted Answer · 2014-06-27 10:35:14Z

2

The $row var is an array so you can loop over that too. Since you want to treat your first field differently write it out before the loop and start the loop at 1.

while ($row = oci_fetch_array($stid, OCI_NUM)) {
    echo "<tr>";
    echo '<td><a href="view.php?id=' . $row[0] . '">' . $row[0] . '</a></td>';
    for ( $ii = 1; $ii < count($row); $ii++ ) {
        echo "<td>" . $row[$ii] . "</td>";
    }
    echo "</tr>";
}

I don't know what the unset($row) was for, so I left it out.

answered Jun 27, 2014 at 10:35

bloodyKnuckles

12.2k4 gold badges32 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3636943 Over a year ago

Thanks! Is it possible to write the column headings from the SQL also?

Kayvy · Accepted Answer · 2014-06-27 10:34:48Z

0

You can use:

SELECT * FROM {TABLENAME}

Then fetch an array. You can get data with $row['{FIELDNAME}']

answered Jun 27, 2014 at 10:34

Kayvy

1

Comments

Justinas · Accepted Answer · 2014-06-27 10:41:15Z

You can use double loop:

$results = $PDO->fetchAll(PDO::FETCH_ASSOC); // here is all columns and rows from db query.

echo '<table><tr>';

// loop only first row to get header
foreach ($results[0] as $header => $value) {
   echo "<th>{$header}</th>"
}

echo '</tr>';

// loop all rows
foreach ($results as $row) {
   echo '<tr>';
   // and loop any number of columns
   foreach ($row as $cellName => $cell) {

      // If you have only one special case, than use if statement
      switch ($cellName) {
          case 'order_no':
               echo "<td><a href='http://example.com/getOrder?id={$cell}'>{$cell}</a></td>";
               break;
          default: echo "<td>{$cell}</td>";
      }
   }
   echo '</tr>';
}
echo '</table>';

Collectives™ on Stack Overflow

PHP dynamic HTML table using SQL

Background

Question

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Background

Question

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related