Retrieve
multiple records from MySQL database using php
Before you start this lesson, I assume that you finished reading
the following tutorials:
- install phpmyAdmin, mysql , php
- Create Database by using phpMyAdmin
- Working with forms
- Insert Form Data into Database
- Retrieve data from
Database
In this lesson, we will learn how to retrieve multiple records from
MySQL database and display them in a table.
PHP Retrieve data from Mysql
from the lesson Retrieve
data from Database, we already know how to use mysql functions
in PHP the following 3 things:
- connect to database mysql_connect(localhost,$username,$password);
- Query the database mysql_query(query string)
- Get resultmysql_result(result
resource,int row,[column name]).
Now we need learn how to display multiple records.
Counting how many rows in MySQL query result
Before we do the loop through the data in retrieved result variable,
we need know how many records(rows) the results have. We use the
command the count how many records in the results.
$numrows=mysql_numrows($result);
Using while loop
Once we got the records count, we could set up a loop to go through
the results like following:
$row=0;
while($row<$numrows)
{
//get result.
$row++;//increase by 1;
}
Note that: PHP count records from 0. 0 is the first record, 1 is
the second and so on.
Put result into table
We may use the above loop to put records in to HTML table
$row=0;
echo "<table border=1 >";
while($row<$numrows)
{
//get name and phone number from results
$name=mysql_result($result,$row,"name");
$phone=mysql_result($result,$row,"phonenumber");
echo "<tr><td>$name</td><td>$phone</td></tr>";
$row++;//increase by 1;
}
echo "</table>";
Put together
We put all code together as following.
<html>
<body>
<?php
$username="root";
$password="123456"; //input your password here.
$database="phonebook";
//connect to database
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die("<b>Unable to specified
database</b>");
//query databae
$query = "select * from contact";
$result=mysql_query($query) or die('Error, insert query failed');
$row=0;
$numrows=mysql_num_rows($result);
echo "<table border=1>";
while($row<$numrows)
{
$name=mysql_result($result,$row,"name");
$phone=mysql_result($result,$row,"phonenumber");
echo "<tr><td>$name</td><td>$phone</td></tr>";
$row++;
}
echo "</table>";
?>
</body>
</html>
Save above code to .php file in your web root or any other virtual
directory. Launch this php file in your browser like http://localhost/mycode.php.
You should be able to see a table of your records. |
