Building a HTML table using MySQL/PHP results

Question

I am trying to build a dynamic HTML table (regardless of number of columns or rows) from a MySQL query but I think I might be doing something wrong as it doesn't work at all.

I have experience with Oracle but I am new to MySQL. I have tried to check that the MySQL/PHP functions I am using do exist and I think that is the case.

<?php
$mysqli = new mysqli("localhost", "dbuser", "dbpass", "db");

/* check connection */
if ($mysqli->connect_errno) {
    printf("Connect failed: %s\n", $mysqli->connect_error);
    exit();
}

/* Select queries return a resultset */
if ($result = $mysqli->query("SELECT * FROM user")) {
    /* build html table */
    echo "<table class='table '><thread><tr>";

    $ncols = mysqli_num_rows($result);
    for ($i = 1; $i <= $ncols; $i++) {
        $column_name  = mysqli_field_name($result, $i);
        echo "<th>".$column_name."</th>";
    }

    echo "</tr></thread><tbody>";

    while ($row = mysqli_fetch_array($result, OCI_NUM)) {
        echo "<tr>";
        for ( $ii = 0; $ii < count($row); $ii++ ) {
            echo "<td>" . $row[$ii] . "</td>";
        }
        echo "</tr>";
    }

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

    /* free result set */
    $result->close();
}

$mysqli->close();
?>

Appreciating any help!

Steve

Edit: I added the error reporting as suggested and get:

Fatal error: Call to undefined function mysqli_field_name() in connection.php on line 20

please explain "Doesn't work at all" : do you get an error message, a blank page, a messed-up HTML result,... ? I might already make a first suggestion even if my PHP skills are a bit rusty : I'm quite sure mysqli_num_rows doesn't actually count columns of your table with the query you use... mysqli_field_count seems to be what you need... — Laurent S.
– Laurent S., Commented Jul 9, 2014 at 8:31
Use print_r on $result, $ncols and then on $row inside the loop. You will then see what data you have to play with and can work forward from that. Also add error_reporting(E_ALL); ini_set('display_errors', 1); to the top of your document to display errors to the browser. Let us know what you get. — Matt The Ninja
– Matt The Ninja, Commented Jul 9, 2014 at 8:32
Another thing: $ncols = mysqli_num_rows($result); - you're assigning number of rows, to variable which, from my understanding, should contain number of columns. — Maciej Jaśniaczyk
– Maciej Jaśniaczyk, Commented Jul 9, 2014 at 8:42

TiMESPLiNTER · Accepted Answer · 2014-07-09 09:14:17Z

2

Your basic problem is, that you probably converted from mysql_ functions to mysqli which is great! But there is no function mysqli_field_name() this function does just exist as a mysql_ function.

You need the mysqli_result::fetch_fields() function/method in mysqli to get the field names.

edited Jul 9, 2014 at 9:14

answered Jul 9, 2014 at 8:31

TiMESPLiNTER

5,9193 gold badges31 silver badges68 bronze badges

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

6 Comments

Maciej Jaśniaczyk Over a year ago

variable assignment in conditional statement is perfectly valid. "if ($result = $mysqli->query("SELECT * FROM user"))" is the same as "$result = $mysqli->query("SELECT * FROM user"); if ($result)"

TiMESPLiNTER Over a year ago

@MaciejJaśniaczyk Of course but it doesn't make sense in this case.

Maciej Jaśniaczyk Over a year ago

I don't see anything wrong in this condition. If $mysqli->query will return false, conditional statement won't be executed.

TiMESPLiNTER Over a year ago

@MaciejJaśniaczyk According to the PHP doc mysqli::query() will return false or a mysqli_result object but never true so this if condition will never result in true.

Maciej Jaśniaczyk Over a year ago

" For successful SELECT, SHOW, DESCRIBE or EXPLAIN queries mysqli_query() will return a mysqli_result object". If you pass this object to if statement, it's logical value will be true and code will be executed. Correct me if I'm wrong :)

|

Ralph · Accepted Answer · 2014-07-09 08:32:07Z

0

This should be a boolean operation not an assignment

if ($result = $mysqli->query("SELECT * FROM user"))

answered Jul 9, 2014 at 8:32

Ralph

3,0599 gold badges30 silver badges50 bronze badges

Comments

Pradeep · Accepted Answer · 2014-07-09 09:31:08Z

-1

<?php
$link=mysql_connect("hostname","username","password","dbname");
$query="select * from table_name";
$sql=mysql_query($query,$link);
if(count($sql)>0)
{
?><tr><th></th>
<?php

while($row=mysql_fetch_array($sql))
    { ?> 
           <tr><td><?php $row['columnname']; ?>
   <?php  } }?>

answered Jul 9, 2014 at 9:31

Pradeep

11 silver badge2 bronze badges

2 Comments

TiMESPLiNTER Over a year ago

mysql_* functions are deprecated. Don't use them anymore!

Pradeep Over a year ago

for me working good so only i replied. ok thank you for your feedback

Collectives™ on Stack Overflow

Building a HTML table using MySQL/PHP results

3 Answers 3

6 Comments

Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

2 Comments

Linked

Related