Combine multiple SQL columns into a single column of an HTML table using PHP

Question

Is it possible to combine data from multiple columns in a MySQL table into a single column in an HTML table, where each record will be a new row in the HTML table?

In this example, a table in MySQL has two columns (Col1, Col2). The data from Col1 is displayed in the first column in the HTML table. The data from Col2 in MySQL is displayed in the second column in the HTML table. In another words, the HTML table matches the layout of the MySQL table.

<?php
$con = new mysqli('domain', 'username', 'password', 'database');
$sql = "select * from table1";
$sql_query = mysqli_query($con, $sql);

while ($row = mysqli_fetch_array($sql_query)) {
  $col1 = $row['col1'];
  $col2 = $row['col2'];

  echo "<table>";
  echo "<tr>";
  echo "<td> $col1 </td>";
  echo "<td> $col2 </td>";
  echo "</tr>";
  echo "</table>";
}
?>

MySQL Table:

| - - - - | - - - - - - - |
| Col1    |    Col2       |
| - - - - | - - - - - - - |
| Blue    |    Car        |
| Green   |    Truck      |
| Yellow  |    Van        |
| - - - - | - - - - - - - |

HTML Table:

| - - - - | - - - - - - - |
| Column1 |    Column2    |
| - - - - | - - - - - - - |
| Blue    |    Car        |
| Green   |    Truck      |
| Yellow  |    Van        |
| - - - - | - - - - - - - |

If $col1 and $col2 are put inside of a single TD tag, this does get both $col1 and $col2 to display in Col1 in the HTML table. However, both $col1 and $col2 are displayed in the same cell.

<?php
$con = new mysqli('domain', 'username', 'password', 'database');
$sql = "select * from table1";
$sql_query = mysqli_query($con, $sql);

while ($row = mysqli_fetch_array($sql_query)) {
  $col1 = $row['col1'];
  $col2 = $row['col2'];

  echo "<table>";
  echo "<tr>";
  echo "<td> $col1 $col2 </td>";
  echo "</tr>";
  echo "</table>";
}
?>

HTML Table:

| - - - - - - - - - - - - |
| Column1                 |
| - - - - - - - - - - - - |
| Blue Car                |
| Green Truck             |
| Yellow Van              |
| - - - - - - - - - - - - |

Is it possible to echo $Col1 and $Col2 in Column1 of the HTML table and have each record be in its own row in the HTML table?

| - - - - - - - - - - - - |
| Column1                 |
| - - - - - - - - - - - - |
| Blue                    |
| Green                   |
| Yellow                  |
| Car                     |
| Truck                   |
| Van                     |
| - - - - - - - - - - - - |

You want the result like the last figure in your question??

Aju John
– Aju John

2016-05-07 03:52:06 +00:00
Commented May 7, 2016 at 3:52 — Aju John
– Aju John, Commented May 7, 2016 at 3:52

Aju John · Accepted Answer · 2016-05-07 03:55:36Z

1

No need to have two loops. It can be done in single loop. Separate the col1 and col2 values in different variables. and at the end print it.

<?php
$con = new mysqli('domain', 'username', 'password', 'database');
$sql = "select * from table1";
$sql_query = mysqli_query($con, $sql);
$str = "<table>";
$str_col2 = "";
while ($row = mysqli_fetch_array($sql_query)) {
  $col1 = $row['col1'];
  $col2 = $row['col2'];    

  $str .= "<tr><td> $col1 </td></tr>";
  $str_col2 .= "<tr><td> $col2 </td></tr>";
}
echo $str . $str_col2 . "</table>";
?>

answered May 7, 2016 at 3:55

Aju John

2,2441 gold badge14 silver badges27 bronze badges

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

3 Comments

JeremyCanfield Over a year ago

This works perfectly. Thank you very much for the solution Aju!

Aju John Over a year ago

Glad to hear...!! Thanks :)

Aju John Over a year ago

Are you talking about the scenario when result is empty from DB?

Murad Hasan · Accepted Answer · 2016-05-07 03:52:09Z

1

Your HTML is valid, but not correct. This is might be the structure of your resultant table.

Make a new array where you store the col2 value for showing after all the col1 value. After completing the col1 value loop again for the second col2 values for the display.

$col2 = array();
echo "<table>";
while ($row = mysqli_fetch_array($sql_query)) {
  $col1 = $row['col1'];
  $col2[] = $row['col2'];

  echo "<tr>";
  echo "<td> $col1 </td>";
  echo "</tr>";
}
for($i = 0; $ < count($col2); $i++){
  echo "<tr>";
  echo "<td> $col2[$i] </td>";
  echo "</tr>";
}
echo "</table>";

answered May 7, 2016 at 3:52

Murad Hasan

9,5932 gold badges23 silver badges43 bronze badges

2 Comments

JeremyCanfield Over a year ago

This is a fascinating solution. I had not considered adding a "for" loop after the "while" loop. I am going to experiment with this. This actually is going to also be useful for the scenario I am working on, as I actually simplified this issue to make the issue easier to digest :)

Murad Hasan Over a year ago

yes, @AjuJohn has the good answer for this question, you must go with him.

Collectives™ on Stack Overflow

Combine multiple SQL columns into a single column of an HTML table using PHP

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related