How to echo out table rows from the db (php)

Question

i want to echo out everything from a particular query. If echo $res I only get one of the strings. If I change the 2nd mysql_result argument I can get the 2nd, 2rd etc but what I want is all of them, echoed out one after the other. How can I turn a mysql result into something I can use?

I tried:

$query="SELECT * FROM MY_TABLE";
$results = mysql_query($query);
$res = mysql_result($results, 0);

while ($res->fetchInto($row)) {
    echo "<form id=\"$row[0]\" name=\"$row[0]\" method=post action=\"\"><td style=\"border-bottom:1px solid black\">$row[0]</td><td style=\"border-bottom:1px solid black\"><input type=hidden name=\"remove\" value=\"$row[0]\"><input type=submit value=Remove></td><tr></form>\n";
}

See this answer: https://stackoverflow.com/a/56256378/273403 — Paul Feakins, May 22 '19 at 12:03

score 27 · Accepted Answer · edited Jul 08 '23 at 05:08

27

$sql = "SELECT * FROM MY_TABLE";
$result = mysqli_query($conn, $sql); // First parameter is just return of "mysqli_connect()" function
echo "<br>";
echo "<table border='1'>";
while ($row = mysqli_fetch_assoc($result)) { // Important line, returns assoc array
    echo "<tr>";
    foreach ($row as $field => $value) { 
        echo "<td>" . htmlspecialchars($value) . "</td>"; 
    }
    echo "</tr>";
}
echo "</table>";

edited Jul 08 '23 at 05:08

Your Common Sense

156,878
40
214
345

answered Jun 04 '10 at 01:26

VoteyDisciple

37,319
5
97
97

This doesn't show column headings, see this answer: https://stackoverflow.com/a/56256378/273403 – Paul Feakins May 22 '19 at 12:03
1

@PaulFeakins - OR, for column headings, see [my answer immediately below written years earlier](https://stackoverflow.com/a/31442311/199364). – ToolmakerSteve Oct 23 '20 at 20:51

score 17 · Answer 2 · edited Jul 08 '23 at 05:06

17

NOTE: In 2018, long after I wrote this, the accepted answer was significantly improved.

Expanding on the accepted answer:

$query = "SELECT * FROM my_table";
$result = mysqli_query($mysqli, $query);
echo "<table>" ;
$first_row = true;
while ($row = mysqli_fetch_assoc($result)) {
    if ($first_row) {
        $first_row = false;
        // Before displaying first row of data.
        // ---> Display row of column headers, from keys. <---
        echo '<tr>';
        foreach($row as $column_name => $value) {
            echo '<th>' . htmlspecialchars($column_name) . '</th>';
        }
        echo '</tr>';
    }
    echo '<tr>';
    // Display row of data.
    foreach($row as $column_name => $value) {
        echo '<td>' . htmlspecialchars($value) . '</td>';
    }
    echo '</tr>';
}
echo "</table>" ;

Benefits compared to accepted answer's 2018 version:

Shows field names as a header row (row of column headers) of table.

Example Output:

Id      Name
777     Aardvark
50      Lion
9999    Zebra

edited Jul 08 '23 at 05:06

Your Common Sense

156,878
40
214
345

answered Jul 15 '15 at 22:27

ToolmakerSteve

18,547
14
94
196

Can you place this into context of how the selected answer code fits into this? I don't understand what the '...' is supposed to be. – Jozef Pietrzak Jul 08 '23 at 00:01
1

I'll edit the answer to clarify. The important code is inside while loop. Before outputting first row of data, this code outputs a row of column headers. – ToolmakerSteve Jul 08 '23 at 02:25
1

Please read: https://stackoverflow.com/questions/15318368/mysqli-or-die-does-it-have-to-die – Your Common Sense Jul 08 '23 at 05:12
1

Thanks for the modernization, and the link. I stopped programming in php years ago. – ToolmakerSteve Jul 08 '23 at 16:54

score 7 · Answer 3 · answered Jun 04 '10 at 01:25

7

 $result= mysql_query("SELECT * FROM MY_TABLE");
 while($row = mysql_fetch_array($result)){
      echo $row['whatEverColumnName'];
 }

answered Jun 04 '10 at 01:25

Babiker

18,300
28
78
125

5

Actually, this answer only echos a single column from each row. – ToolmakerSteve Jul 15 '15 at 21:58
This "snippet dump" is missing its educational explanation. – mickmackusa Dec 30 '20 at 21:42

score 1 · Answer 4 · answered Dec 24 '18 at 14:39

$sql = "SELECT * FROM YOUR_TABLE_NAME";
$result = mysqli_query($conn, $sql); // First parameter is just return of "mysqli_connect()" function
echo "<br>";
echo "<table border='1'>";
while ($row = mysqli_fetch_assoc($result)) { // Important line !!!
    echo "<tr>";
    foreach ($row as $field => $value) { // If you want you can right this line like this: foreach($row as $value) {
        echo "<td>" . $value . "</td>"; 
    }
    echo "</tr>";
}
echo "</table>";

In PHP 7.x You should use mysqli functions and most important one in while loop condition use "mysqli_fetch_assoc()" function not "mysqli_fetch_array()" one. If you would use "mysqli_fetch_array()", you will see your results are duplicated. Just try these two and see the difference.

Zimba · Answer 5 · 2020-08-22T16:23:20.550

1

Nested loop to display all rows & columns of resulting table:

$rows = mysql_num_rows($result);
$cols = mysql_num_fields($result);
for( $i = 0; $i<$rows; $i++ ) {
   for( $j = 0; $j<$cols; $j++ ) {
     echo mysql_result($result, $i, $j)."<br>";
   }
}

Can be made more complex with data decryption/decoding, error checking & html formatting before display.

Tested in MS Edge & G Chrome, PHP 5.6

edited Aug 22 '20 at 16:23

answered Aug 22 '20 at 15:41

Zimba

2,854
18
26

score 0 · Answer 6 · answered Dec 30 '20 at 21:55

All of the snippets on this page can be dramatically reduced in size.

The mysqli result set object can be immediately fed to a foreach() (because it is "iterable") which eliminates the need to maked iterated _fetch() calls.

Imploding each row will allow your code to correctly print all columnar data in the result set without modifying the code.

$sql = "SELECT * FROM MY_TABLE";
echo '<table>';
    foreach (mysqli_query($conn, $sql) as $row) {
        echo '<tr><td>' . implode('</td><td>', $row) . '</td></tr>';
    }
echo '</table>';

If you want to encode html entities, you can map each row:

implode('</td><td>' . array_map('htmlspecialchars', $row))

If you don't want to use implode, you can simply access all row data using associative array syntax. ($row['id'])

How to echo out table rows from the db (php)

6 Answers6

Linked

Related