MySQL, PHP: Display MySQL table fields and data

8 Comments / PHP Tips & Tutorials, SQL / MySQL Tips and Tutorials / By /

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.

Related Posts

8 thoughts on “MySQL, PHP: Display MySQL table fields and data”

  1. Farrukh Shahzad

    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:
    =====================================

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

  2. Farrukh Shahzad

    wooopppsssss php code gets removed here is another try….

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

    < ? php <<

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

  4. Chreong Sambath

    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…..!

    1. miru

      <?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();
      ?>

  5. Aneeq

    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

Comments are closed.

Scroll to Top