Display all records in mysql table (PHP)

Question

Pretty much the complete noob, the code below is great for printing 1 record from a database table, but what I really want to do is print all the records in the mysql table in a format similar to my code.

IE: The name of the field as a heading for each column of the html table and the record under the heading. Hope this makes 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>"; 

Answer 1

As additional information, you should probably use PDO. It has more features and helps in training the preparation of SQL statements. It will also serve you much better if you ever write more complex code.

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

This example uses objects, not arrays. It doesn’t necessarily matter, but it uses less characters, so I like it. The difference does occur when you delve 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 will probably also want to study prepared statements. It helps against injections. Injection is poor for safety reasons. Here is the page for this.

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

You should probably also consider disinfecting your user. Just a head and not related to your current situation.

Also to get all field names using PDO try this

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

Once you have all the fields in the table, it would be fairly easy to use <div> or even <table> to arrange them as you like using <th>

Happy learning PHP. It's fun.

Answer 2

Thank you guys understand.

  $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"; } 

Answer 3

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

Answer 4

Try the following:

  $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 the new php version, so use mysqli or PDO.

Answer 5

Thanks! I adapted some of these answers to output a table from all records from any table without specifying field names. Just paste this into your .php file and change the connection information:

 <?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 ?>