Display all columns in a grid in php MySql

Question

I know to display the values from a table in MySql in a PHP page. This is what I do:

$sql = "SELECT * FROM  table";
$result = mysql_query($sql);
while($row = mysql_fetch_array($result,MYSQL_NUM))
{
//print the values using echo
}

The problem is I have 35 columns in the table I use and I have 4 such tables. Is there a simpler way than just echo-ing each column. Also I have to print the table headers. Tried a few links in google, didn't get a satisfactory answer. Direct answer or link will be helpful. Thanks in advance.

Just loop over the columns. If you get a associative instead of a numerical array you can print the keys when you get the first row. — jeroen
– jeroen, Commented Jul 10, 2014 at 17:53
You got a link or anything ? You mean have associative for the header and numerical for the columns ? — SomeUser
– SomeUser, Commented Jul 10, 2014 at 17:58

Joey · Accepted Answer · 2014-07-10 18:21:31Z

2

If you want the column names of a table from a query, you can do something like this:

$c=0;
$myarray = array();
while ($c < mysql_num_fields($result))
{
     # Get field name
     $fld = mysql_fetch_field($result, $c);

     # Put field name in array
     $myarray[] = $fld->name;

     # Count + 1 for next field
     $c++;
}

echo "<table style='border:1px solid #ccc;'>\n";
echo "<thead>\n";
echo "<tr>\n";
foreach($myarray as $columnheading) {
    echo "<th>".$columnheading."</th>\n";
}
echo "</tr>\n";
echo "</thead>\n";
echo "<tbody>\n";
if (mysql_num_rows($result) > 0) {
    while ($row = mysql_fetch_assoc($result)) {
        echo "<tr>\n";
        foreach($row as $td) {
            echo "<td>".$td."</td>";
        }
        echo "</tr>\n";
    }
}
echo "</tbody>\n";
echo "</table>";

You have the column names in a array. Add print_r($myarray) to see what columns are generated.

EDIT: added full example.

edited Jul 10, 2014 at 18:21

answered Jul 10, 2014 at 18:10

Joey

6775 silver badges20 bronze badges

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

4 Comments

SomeUser Over a year ago

$myarray can be printed in a table header ? And regarding the other part of the question ? Anyway I can reduce the number of times I print the columns ?

Joey Over a year ago

Hi man, i added a full example, hope it helps. Table is really sloppy but it does what you need. You need to tweak it a little.

Joey Over a year ago

Good! Let me know, don't forget to accept if it's ok!

Joey Over a year ago

Note that mysql will be deprecated soon (PHP 5.5), just like @Ollie Jones said, you should use mysqli classes.

Angel Diaz · Accepted Answer · 2014-07-10 18:12:04Z

0

According to PHP documentation here

$result = mysql_query("SHOW COLUMNS FROM sometable");
if (!$result) {
echo 'Could not run query: ' . mysql_error();
exit;
}
if (mysql_num_rows($result) > 0) {
while ($row = mysql_fetch_assoc($result)) {
    print_r($row);
}
}

answered Jul 10, 2014 at 18:12

Angel Diaz

365 bronze badges

Comments

Amey Mahajan · Accepted Answer · 2021-10-07 16:37:35Z

0

@Joey has written an excellent answer, just wanted to add that, mysql_num_fields() function has been deprecated since PHP 7.0 and

mysqli_num_fields();

should be used in it's stead. Deprecation note

answered Oct 7, 2021 at 16:37

Amey Mahajan

393 bronze badges

Comments

Greg · Accepted Answer · 2021-01-14 16:41:08Z

The trick you need is to retrieve the result set's metadata. I refuse to help you do anything with the deprecated and dangerous mysql_ interface, so my answer relates to mysqli_.

Here's documentation.

http://php.net/manual/en/mysqli.quickstart.statements.php

Here's some possible code you could use:

$mysqli = new mysqli("example.com", "user", "password", "database");
if ($mysqli->connect_errno) {
    die "Failed to connect to MySQL: (" .
        $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

$res = $mysqli->query("SELECT * FROM table");

if (!$res) {
    die "Query failed: (" . $res->errno . ") " . $res->error;
}

$rownumber = 0;
echo "<table>\n";
while ($row = $res->fetch_assoc()) {
  if (0 == $rownumber) {
    /* first result set row? look at the keys=column nanes */
    echo "<tr>";
    foreach (array_keys($row) as $colname) {
      echo "<td>$colname</td>";
    }
    echo "</tr>\n";
  }
  $rownumber ++;

  echo "<tr>";
  foreach (array_values($row) as $colval) {
    echo "<td>$colval</td>";
  }
  echo "</tr>\n";
}
echo "</table>\n";
$res->close();

As far as reducing the number of times you call "echo", there's no particularly good reason to do that. But if you must you can do something like this to accumulate an output string.

  $out = "";
  $out .= "<tr>";
  foreach (array_values($row) as $colval) {
    $out .= "<td>$colval</td>";
  }
  $out .= "</tr>\n";
  echo $out;
  $out = "";

Collectives™ on Stack Overflow

Display all columns in a grid in php MySql

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related