All geek questions in one place

Create HTML table from sql table

I have a table in an SQL database with the following fields: ID, Name, Email, University, Languages ​​and Experience. I want to create an html table that extracts data from SQL and displays the last 10 results? How can I do it?

Sorry if this is a very simple question, I have very little knowledge in PHP and SQL.

Here is the code that I have right now that just displays the name, not the table:

<html> <head> <title>Last 5 Results</title> </head> <body> <?php $connect = mysql_connect("localhost","root", "root"); if (!$connect) { die(mysql_error()); } mysql_select_db("apploymentdevs"); $results = mysql_query("SELECT * FROM demo"); while($row = mysql_fetch_array($results)) { echo $row['Name'] . "</br>"; ?> </body> </html>
+9
source share
8 answers

Here is something that should help you create a table and gain more knowledge about php and mysql .

You should also move the connection logic and query to the beginning of your process to avoid page loading errors and show a more accurate error than just mysql_error.

Change: If your identifiers increase, then you can add an ORDER BY clause,
change: SELECT * FROM demo LIMIT 10 to: SELECT * FROM demo LIMIT 10 ORDER BY id

 <html> <head> <title>Last 10 Results</title> </head> <body> <table> <thead> <tr> <td>Id</td> <td>Name</td> </tr> </thead> <tbody> <?php $connect = mysql_connect("localhost","root", "root"); if (!$connect) { die(mysql_error()); } mysql_select_db("apploymentdevs"); $results = mysql_query("SELECT * FROM demo LIMIT 10"); while($row = mysql_fetch_array($results)) { ?> <tr> <td><?php echo $row['Id']?></td> <td><?php echo $row['Name']?></td> </tr> <?php } ?> </tbody> </table> </body> </html>
+10
source

An option requiring you to encode data in a database in JSON: http://www.jeasyui.com/documentation/datagrid.php

But it looks a lot more promising: http://phpgrid.com/

+6
source

Call table( $result ); in the results of your query will lead to the creation of an html-based table regardless of the size of the sql array that you feed it. Hope this helps :)

 <?php function table( $result ) { $result->fetch_array( MYSQLI_ASSOC ); echo '<table>'; tableHead( $result ); tableBody( $result ); echo '</table>'; } function tableHead( $result ) { echo '<thead>'; foreach ( $result as $x ) { echo '<tr>'; foreach ( $x as $k => $y ) { echo '<th>' . ucfirst( $k ) . '</th>'; } echo '</tr>'; break; } echo '</thead>'; } function tableBody( $result ) { echo '<tbody>'; foreach ( $result as $x ) { echo '<tr>'; foreach ( $x as $y ) { echo '<td>' . $y . '</td>'; } echo '</tr>'; } echo '</tbody>'; }
+4
source

I found it very unpleasant to paste the same code over and over to create HTML tables from SQL queries.

So, we will move on to a function that takes mysqli results and inserts them together into a regular simple HTML table that has column names according to the data in the database:

 function sql_to_html_table($sqlresult, $delim="\n") { // starting table $htmltable = "<table>" . $delim ; $counter = 0 ; // putting in lines while( $row = $sqlresult->fetch_assoc() ){ if ( $counter===0 ) { // table header $htmltable .= "<tr>" . $delim; foreach ($row as $key => $value ) { $htmltable .= "<th>" . $key . "</th>" . $delim ; } $htmltable .= "</tr>" . $delim ; $counter = 22; } // table body $htmltable .= "<tr>" . $delim ; foreach ($row as $key => $value ) { $htmltable .= "<td>" . $value . "</td>" . $delim ; } $htmltable .= "</tr>" . $delim ; } // closing table $htmltable .= "</table>" . $delim ; // return return( $htmltable ) ; }

Usage example

 $DB = new mysqli("host", "username", "password", "database"); $sqlresult = $DB->query( "SELECT * FROM testtable LIMIT 1 ;" ) ; echo sql_to_html_table( $sqlresult, $delim="\n" ) ;
+3
source

You may need to fetch using mysql_fetch_assoc() (so that you get the data in an associative array: keys => value). The keys indicate your column names; so for each row you can scroll through each column (using array_keys() ) and print its value.

 $results = mysql_query("SELECT * FROM demo"); while($row = mysql_fetch_assoc($results)) { foreach (array_keys($row) as $column) { echo $row[$key] . "</br>"; } }

(After that, you can cache array_keys ($ row) in a variable that is set only once, since its value will not change when passing the results.)

+1
source

Despite the fact that it has been a while, I'm going to put my 2 cents in: use functions (even better - classes). Creating tables from mysql results is a very common task.

 <!DOCTYPE html> <html> <head><title>Create Tables from MySQL using functions</title></head> <body> <?php db_connect(); // assuming you have an auto increment id as the first column $result = mysql_query("SELECT * FROM demo ORDER BY 1 DESC LIMIT 10"); print createTable(array_result($result)); ?> </body> </html> <?php /** * Quick mysql result function * * @param $result * @return array */ function array_result($result) { $args = array(); while ($row = mysql_fetch_assoc($result)) { $args[] = $row; } return $args; } /** * Connect to db * * @param string $db_host * @param string $db */ function db_connect($db_host = "localhost", $db = "apploymentdevs") { $connect = mysql_connect($db_host,"root", "root"); if (!$connect) { die(mysql_error()); } mysql_select_db($db); } /** * Create a table from a result set * * @param array $results * @return string */ function createTable(array $results = array()) { if (empty($results)) { return '<table><tr><td>Empty Result Set</td></tr></table>'; } // dynamically create the header information from the keys // of the result array from mysql $table = '<table>'; $keys = array_keys(reset($results)); $table.='<thead><tr>'; foreach ($keys as $key) { $table.='<th>'.$key.'</th>'; } $table.='</tr></thead>'; // populate the main table body $table.='<tbody>'; foreach ($results as $result) { $table.='<tr>'; foreach ($result as $val) { $table.='<td>'.$val.'</td>'; } $table.='</tr>'; } $table.='</tbody></table>'; return $table; }
+1
source

I hope you know how to make a table in HTML, <table>, <tr> and <td>.

Correct the table with this while loop and use "SELECT * FROM demo LIMIT 10" as an SQL query.

0
source

He asked me to see if my 2 queries in sql can connect to a table in html.

my code is:

 -- mostrar uma tabela com as reclamações da semana atual Select R.Data, C.No, C.Nome, V.IdVendedor, V.Vendnm From dbo.Reclamacoes R Inner Join dbo.PHC_CLIENTES_SAMSYS C On R.IdCliente = C.Id Inner Join dbo.PHC_VENDEDORES_SAMSYS V on R.IdVendedor = V.IdVendedor --Where datepart(ww, getdate()) = datepart(ww,data) -- mostrar uma tabela apenas com a data de conclusão das reclamações eo seu respetivo ID Select R.DataConclusao, C.No, C.Nome, V.IdVendedor, V.Vendnm From dbo.Reclamacoes R Inner Join dbo.PHC_CLIENTES_SAMSYS C on R.IdCliente = C.Id Inner Join dbo.PHC_VENDEDORES_SAMSYS V on R.IdVendedor = V.IdVendedor Where Tratado = 1

Can anybody help me?

0
source

Source: https://habr.com/ru/post/922781/

More articles:


All Articles