CODEWITHSUNDEEP
phpmysqlhtml-table
user2063487
user2063487

Reputation: 61

Displaying all records in a mysql table

The code below works fine for printing one record from a database table, but what I really want to be able to do is print all the records in the mysql table in a format similar to my code.

I.E.: Field Name as heading for each column in the html table and the entry below the heading. Hope this is making sense to someone ;)

        $raw = mysql_query("SELECT * FROM tbl_gas_meters");
        $allresults = mysql_fetch_array($raw);
        $field = mysql_query("SELECT * FROM tbl_gas_meters");
        $num_fields = mysql_num_fields($raw);   
        $num_rows = mysql_num_rows($raw);   
        $i = 1;

            print "<table border=1>\n";
            while ($i < $num_fields)
            {
                echo "<tr>";
                echo "<b><td>" . mysql_field_name($field, $i) . "</td></b>";
                //echo ": ";
                echo '<td><font color ="red">' . $allresults[$i] . '</font></td>';
                $i++;
                echo "</tr>";
                //echo "<br>";
            }
            print "</table>";

Upvotes: 4

Views: 22381

Answers (5)

ashleedawg
ashleedawg

Reputation: 21639

Thanks! I adapted some of these answers to draw a table from all records from any table, without having to specify the field names. Just paste this into a .php file and change the connection info:

<?php    

// Authentication detail for connection
$servername = "localhost";
$username =   "xxxxxxxxxx";
$password =   "xxxxxxxxxx";
$dbname =     "xxxxxxxxxx";
$tablename =  "xxxxxxxxxx";
$orderby = "1 DESC LIMIT 500";  // column # to sort & max # of records to display

// Create & check connection
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);  // quit
    }

// Run query & verify success
$sql = "SELECT * FROM {$tablename} ORDER BY {$orderby}";
if ($result = $conn->query($sql)) {
    $conn->close();  // Close table
    $fields_num = $result->field_count;
    $count_rows = $result->num_rows;

    if ($count_rows == 0) {
        die ("No data found in table: [" . $tablename . "]" );  //quit 
        } 
    } else {
    $conn->close();  // Close table
    die ("Error running SQL:<br>" . $sql );  //quit
}

// Start drawing table
echo "<!DOCTYPE html><html><head><title>{$tablename}</title>";
echo "<style> table, th, td { border: 1px solid black; border-collapse: collapse; }</style></head>";
echo "<body><span style='font-size:18px'>Table: <strong>{$tablename}</strong></span><br>";  
echo "<span style='font-size:10px'>({$count_rows} records, {$fields_num} fields)</span><br>";
echo "<br><span style='font-size:10px'><table><tr>";        

// Print table Field Names
while ($finfo = $result->fetch_field()) {
    echo "<td><center><strong>{$finfo->name}</strong></center></td>";
}
echo "</tr>"; // Finished Field Names

/* Loop through records in object array */
while ($row = $result->fetch_row()) {
    echo "<tr>";    // start data row
    for( $i = 0; $i<$fields_num; $i++ ) {
        echo "<td>{$row[$i]}</td>";
    }
    echo "</tr>";   // end data row
}

echo "</table>";  // End table
$result->close();  // Free result set
?>

Upvotes: 0

user2063487
user2063487

Reputation: 61

Thanks guys, got it.

        $table = 'tbl_gas_meters';
        $result = MYSQL_QUERY("SELECT * FROM {$table}");

        $fields_num = MYSQL_NUM_FIELDS($result);

        ECHO "<h1>Table: {$table}</h1>";
        ECHO "<table border='1'><tr>";
        // printing table headers
        FOR($i=0; $i<$fields_num; $i++)
        {
            $field = MYSQL_FETCH_FIELD($result);
            ECHO "<td>{$field->name}</td>";
        }
        ECHO "</tr>\n";
        // printing table rows
        WHILE($row = MYSQL_FETCH_ROW($result))
        {
            ECHO "<tr>";

            // $row is array... foreach( .. ) puts every element
            // of $row to $cell variable
            FOREACH($row AS $cell)
            ECHO "<td>$cell</td>";

            ECHO "</tr>\n";
        }

Upvotes: 2

Prasanth Bendra
Prasanth Bendra

Reputation: 32740

Try this :

    $raw = mysql_query("SELECT * FROM tbl_gas_meters");
    $allresults = mysql_fetch_array($raw);
    $field = mysql_query("SELECT * FROM tbl_gas_meters");
    while($row  = mysql_fetch_assoc($field)){
         echo $row['your field name here'];
    }

Please note that, mysql_* functions are deprecated in new php version , so use mysqli or PDO instead.

Upvotes: 0

Goddard
Goddard

Reputation: 3059

Just as an additional piece of information you should probably be using PDO. It has more features and is helpful in learning how to prepare SQL statements. It will also serve you much better if you ever write more complicated code.

http://www.php.net/manual/en/intro.pdo.php

This example uses objects rather then arrays. Doesn't necessarily matter, but it uses less characters so I like it. Difference do present themselves when you get deeper into objects, but not in this example.

//connection information
$user = "your_mysql_user";
$pass = "your_mysql_user_pass";
$dbh = new PDO('mysql:host=your_hostname;dbname=your_db;charset=UTF-8', $user, $pass);

//prepare statement to query table
$sth = $dbh->prepare("SELECT name, colour FROM fruit");
$sth->execute();
//loop over all table rows and fetch them as an object
while($result = $sth->fetch(PDO::FETCH_OBJ))
{
//print out the fruits name in this case.
  print $result->name;
  print("\n");
  print $result->colour;
  print("\n");
}

You probably also want to look into prepared statements. This helps against injection. Injection is bad for security reasons. Here is the page for that.

http://www.php.net/manual/en/pdostatement.bindparam.php

You probably should look into sanitizing your user input as well. Just a heads up and unrelated to your current situation.

Also to get all the field names with PDO try this

$q = $dbh->prepare("DESCRIBE tablename");
$q->execute();
$table_fields = $q->fetchAll(PDO::FETCH_COLUMN);

Once you have all the table fields it would be pretty easy using <div> or even a <table> to arrange them as you like using a <th>

Happy learning PHP. It is fun.

Upvotes: 5

Taz
Taz

Reputation: 1303

while ( $row = mysql_fetch_array($field) ) {
    echo $row['fieldname'];
    //stuff
}

Upvotes: 0

Related Questions