PHP and MySQL are two components that are almost always present in every web-based program. Data-driven Web applications can use the MySQL which has the main advantages of open source applications. In each database in MySQL there are a few tables. To learn how to access the data contained in the MySQL database, create a database instance with the name “online-store”. Databases can be created using the SQL command “create database online-store” or using PHPMyadmin. Later in the database table named instance make an “employee” table with the columns in it is the “id”, “first-name”, “last-name”, “address”. You can create table “employee” using SQL command as follows:
CREATE TABLE IF NOT EXISTS `employee` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`first-name` varchar(50) NOT NULL,
`last-name` varchar(50) NOT NULL,
`address` varchar(200) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;
Then fill the table with sample data as follows:
+----+-----------+----------+-------------+
| id | first-name | last-name | address |
+----+-----------+----------+-------------+
| 1 | John | Deep | Ballard Ave |
| 2 | Anita | Swift | Barstow Ave |
| 3 | Linda | Spencer | First Ave |
| 4 | Jeff | Hopkins | Sierra Ave |
+----+-----------+----------+-------------+
Table “employee” can be filled using the facilities on the application or use PHPMyAdmin SQL command as follows:
INSERT INTO `employee` (`id`, `first-name`, `last-name`, `address`) VALUES
(1, 'John', 'Deep', 'Ballard Ave'),
(2, 'Anita', 'Swift', 'Barstow Ave'),
(3, 'Linda', 'Spencer', 'First Ave'),
(4, 'Jeff', 'Hopkins', 'Sierra Ave');
Now, table and your data is ready. Then create a PHP file with the name “listemployee.php” on the web root directory. The first step in accessing the MySQL database is open a connection to that database. Opening a database connection can be done using the function mysql_connect() with 3 parameters in it: username, password and database name. We assume that username is root and password is root. So the first lines in the file “listemployee.php” are as follows:
$password = "root";
$database = "online-store";
// open connection
mysql_connect($username, $password, $database);
Then access “listemployee.php” using web browser. After a successful connection to the MySQL database, the next stage is to prepare the SQL command to retrieve data from the employee table and execute the SQL command using the function mysql_query() with the following example:
$SQL = "select * from employee";
$rs = mysql_query($SQL);
The function mysql_query() will send the SQL command to the MySQL database server. By the MySQL server, the command is executed and the result is a record set will be given to the function caller and then inserted into the variable $rs (short for record set). Then we can process this record set variable using a loop so that we can retrieve content/data.
while($data = mysql_fetch_array($rs))
{
$first-name = $data['first-name'];
}
The above code means that each time looping, one record is retrieved from the record set (which is the data from the employee table) then the data in the column first-name taken and put into variable $first-name. Furthermore, we can do something on this variable tha suit our needs eg for display on a web page like the example below.
while($data = mysql_fetch_array($rs))
{
$first-name = $data['first-name'];
print $first-name;
print "
";
}
This loop can be modified to display the complete data using a display table with the following example:
print "";
print "ID";
print "First Name";
print "Last Name";
print "Address";
while($data = mysql_fetch_array($rs))
{
$id = $data['id'];
$first-name = $data['first-name'];
$last-name = $data['last-name'];
$address = $data['address'];
print "";
print "$id";
print "$first-name";
print "$last-name";
print "$address";
}
print "";
With the above code we’ve managed to get a view of data from employee table in a webpage.
AUTOPOST by BEDEWY VISIT GAHZLY
