MySQL, PHP: Display MySQL table fields and data

by Yang Yang on June 6, 2009

While this may seem simple, but it’s more complicated than it may appear because you have to first decide which data (rows, fields, etc.) to be extracted or selected and in what way the data from the MySQL table are presented.

But to simply read from a MySQL table for the fields and data:

SELECT * FROM sometable

Which would select all records (rows) on all fields (columns) from the table named `sometable`. The asterisk * indicates all fields. To select just some of the fields, you will have to specify them one by one explicitly in the SQL:

SELECT id, title, count FROM sometable

Returns all records (rows) from the table `sometable`, but only 3 fields, namely `id`, `title`, and `count` of each record are included. To get the data or records from table `sometable` with id <= 10:

SELECT * FROM sometable WHERE id <= 10

In PHP, you can use mysql_query(‘SELECT … ‘) to execute a SQL query so that the MySQL database system returns certain results set catering to the demand. The returned subject is of result resource type in PHP and you will further extract the original data by fetching the data rows one by one and possibly put them in a native array:

$results = mysql_query('SELECT * FROM sometable');
$results_array = array();
while ($row = mysql_fetch_array($results)) {
	$results_array[$row['id']] = $row;
}

Now you would have all the data you need in $results_array. You can then display the data in a HTML table:

<table>
<?php foreach ($results_array as $id => $record) { ?>
	<tr>
		<td><?php echo $id;?></td>
		<td><?php echo $record['title'];?></td>
		<td><?php echo $record['count'];?></td>
	</tr>
<?php } ?>
</table>

At last, you have a HTML table containing all the data that was originally in a MySQL database table.

Farrukh Shahzad February 11, 2010 at 3:51 pm

This is a very very bad way of fetching records and displaying/generating the HTML code as the database table can be very large in data so php allowed memory might get exhausted:

while ($row = mysql_fetch_array($results)) { $results_array[$row[‘id’]] = $row; }

instead you just fetch the data and display directly instead of storing to the array then display by foreach loop. Below is the example:
=====================================

==============================================

Farrukh Shahzad February 11, 2010 at 3:54 pm

wooopppsssss php code gets removed here is another try….

=====================================
< ? php << <<< php end tag

< ? php <<

=================================

Farrukh Shahzad February 11, 2010 at 3:55 pm

sorry readers i think site is not allowing me to post the php code for your read…

Yang Yang February 12, 2010 at 12:35 pm

You can first translate them into html entities and then post them here. 😉 A nice html entities encoders online: http://www.dan.co.jp/cases/javascript/encode_entities.html

Chreong Sambath February 8, 2011 at 10:33 pm

I am a beginner in PHP:
I want to display a record in database into HTML table
just like output below:
Name Abc
Sex M
Date of birth anywhere
Please help me…..!

miru June 17, 2011 at 10:14 pm

<?php
$host="localhost"; // Host name
$username="username"; // Mysql username
$password="password"; // Mysql password
$db_name="name"; // Database name
$tbl_name="table_name"; // Table name

// Connect to server and select databse.
mysql_connect("localhost", "username")or die("cannot connect");
mysql_select_db("base_name")or die("cannot select DB");

$user_result = "select * from tablename;";
$qry = mysql_query($user_result) OR die(mysql_error());
$user_array = mysql_fetch_assoc($qry);
echo "”;
echo “”;
echo “”;
echo “”.$user_array[‘column_name’].””;
echo “”.$user_array[‘column_name’].””;
echo “”.$user_array[‘column_name’].””;
echo “”.$user_array[‘column_name’].””
;

echo “”;
echo “”;
mysql_close();
?>

Aneeq April 26, 2012 at 3:54 pm

This is the simplest code to select and display records from MySQL database table and display in PHP.

$cn=mysql_connect($db_host,$db_user,$db_password) or die(mysql_error());
mysql_select_db($db_name,$cn) or die(mysql_error());

$sql = “SELECT field_name FROM table_name”;
$rs = mysql_query($sql) or die(mysql_error());

while($row = mysql_fetch_array($rs)){

echo $field_name = $row[“field_name”];
echo “”;

}
mysql_free_result($rs);

Source:
http://phphelp.co/2012/04/26/how-to-select-and-display-mysql-records-data-in-php/

OR

http://addr.pk/acfd

name February 27, 2013 at 9:22 am

<?php

<table>
<tr>
<td> testing html entity
</td>
</tr>
</table>
?>

Comments on this entry are closed.

Previous post:

Next post: