Chapter Five
Server Side Scripting
Part II
.....
Objective
Database Programming using PHP
Overview on MySQL database
Creating Database Connection in PHP
Sending Query to MySQL Database using PHP
Processing Query Result.
PHP File Input-Output
PHP MySQL Database
With PHP, you can connect to and manipulate databases.
MySQL is the most popular database system used with
PHP.
The data in a MySQL database are stored in tables.
A table is a collection of related data, and it consists of
columns and rows
What is MySQL?
MySQL is a database system used on the web
MySQL is a database system that runs on a server
MySQL is ideal for both small and large applications
MySQL is very fast, reliable, and easy to use
MySQL uses standard SQL
MySQL compiles on a number of platforms
MySQL is free to download and use
Steps to access MySQL database from
PHP page
1. Create connection
2. Select a database to use
3. Send query to the database
4. Retrieve the result of the query
5. Close the connection
Create Connection to MySQL server
Before we can access data in the MySQL
database, we need to be able to connect to the
server:
The way of connection to the server and
Databse
1. MySQLi Object-Oriented
2. MySQLi Procedural
3. PHP Data Objects (PDO)
MySQLi Object-Oriented
<?php
$servername = "localhost";
$un = "root";
$pass = "";
// Create connection
$conn = new mysqli($servername, $un, $pass);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
?>
Example (MySQLi Procedural)
<?php
$sn = "localhost";
$un = "root";
$pas = "";
// Create connection
$conn = mysqli_connect($sn, $un, $pas);
// Check connection
if (!$conn) {
die("Connection failed: " .
mysqli_connect_error());
}
echo "Connected successfully by using pros";
?>
Clothing connection
<?php
$conn = new mysqli("localhost",‚un",‚pas‚)
if ($mysqli -> connect_error)
{
echo "Failed to connect to MySQL: " . $mysqli -
> connect_error;
exit();
}
$conn -> close();
?>
Creating Database Connection in PHP OOP
• The CREATE DATABASE statement is used to create a database in MySQL.
// Create database
$sql = "CREATE DATABASE Bit";
if ($conn->query($sql) === TRUE)
{
echo "Database created successfully";
} else {
echo "Error creating database: " . $conn-
>error;
}
Example (MySQLi Procedural)
// Create database
$sql = "CREATE DATABASE Bit";
if (mysqli_query($conn, $sql))
{
echo "Database created successfully";
}
else
{
echo "Error creating database: " .
mysqli_error($conn);
}
Drop Database using PHP Script
Note:- While deleting a database using PHP script, it does not prompt you
for any confirmation. So be careful while deleting a MySql database.
Syntax
$sql = 'DROP DATABASE Bit’;
$qury = $mysqli->query ($conn, $sql );
if(! $qury )
{
die('Could not delete database: ' . mysqli_error());
}
13
Selecting MySQL Database Using PHP Script
Once you get connection with MySQL server, it is required to select a
particular database to work with.
This is because there may be more than one database available with
MySQL Server.
PHP provides function mysql_select_db to select a database.
It returns TRUE on success or FALSE on failure.
14
Cont..
Syntax:
mysql_select_db(db_name, connection);
Where
db_name:-Required - MySQL Database name to be
selected
Connection:-Optional - if not specified then last opened
connection by mysql_connect will be used.
Example
<?php
include connection.php’;
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die('Could not connect: ' . mysqli_error());
}
echo 'Connected successfully’;
mysqli_select_db( ‘Bit’ );//data base is selected
16
mysqli_close($conn);
?>
Creating table
Create a MySQL Table Using MySQLi
The CREATE TABLE statement is used to create
a table in MySQL
Example (MySQLi Object-oriented)
// sql to create table
$sql = "CREATE TABLE stud (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50) )";
if ($conn->query($sql) === TRUE)
{
echo "Table Bit created successfully";
}
else {
echo "Error creating table: " . $conn->error;
}
Example (MySQLi Procedural)
/ sql to create table
$sql = "CREATE TABLE Bit (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50), )";
if (mysqli_query($conn, $sql)) {
echo "Table Bit created successfully";
}
else
{
echo "Error creating table: " . mysqli_error($conn);
}
PHP MySQL Insert Data
• After a database and a table have been created, we can start
adding data in them.
• Here are some syntax rules to follow:
INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)
Example (MySQLi Object-oriented)
// Create connection
$conn = new mysqli($servername, $username, $password,
$dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "INSERT INTO stud (firstname, lastname, email)
VALUES (‘Mahe', ‘yose', ‘
[email protected]')";
if ($conn->query($sql) === TRUE) {
echo "New record is inserted successfully";
} else {
echo $conn->error;
}
Example (MySQLi Procedural)
// Create connection
$conn = mysqli_connect($servername, $username, $password,
$dbname);
// Check connection
if(!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES (‘Dave', 'Dane', ‘
[email protected]')";
if (mysqli_query($conn, $sql)) {
echo "New record created successfully";
} else {
echo "Error: ". mysqli_error($conn);
}
Insert the data from HTML form
In real application, all the values will be taken using HTML form
and then those values will be captured using PHP script and
finally they will be inserted into MySql tables.
<form action="" method="POST">
Name <input type="text" name="name">
age <input type="text" name="age">
<input type="submit" name="submit">
</form>
Cont..
<?php
$server = "localhost";
$username = "root";
$password = "";
$database = ‚Bit";
$con = mysqli_connect($server, $username, $password);
if(!$con){
echo "Error : ".mysqli_error();
return;
}
$db = mysqli_select_db($database,$con);
if(!$db)
{echo "Error : ".mysqli_error();
return;}
?>
Cont.
<?php
if(isset($_POST['submit']))
{
$name = $_POST["name"];
$age = $_POST["age"];
mysqli_query("insert into employee (name,age) value
('$name','$age') ")or die(mysql_error());
}
?>
Getting Data From MySql Database
Data can be fetched from MySQL tables by executing SQL SELECT statement through PHP function
mysql_query().
mysql_query() returns a result set of the query if the SQL statement is SELECT
mysql_num_rows($result) : returns the number of rows found by a query
mysql_fetch_row($result): returns a row as an array containing each field in the row. It returns
false when it reaches the end
mysql_fetch_row() and an associative array, with the names of the fields as the keys.
You have several options to fetch data from MySQL.
Getting Data From MySql Database: Example
<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die('Could not connect: ' . mysql_error());
}
$sql = 'SELECT id, name, salary FROM employee';
mysql_select_db(‘Bit');
$result = mysql_query( $sql, $conn );
27
Getting Data From MySql Database: Example
if(! $ result )
{
die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
echo ‚id:{$row[‘id']} <br> ".
‚name: {$row[‘name']} <br> ".
‚salary: {$row['salary']} <br> ".
"------------------------<br>";
}
echo "Fetched data successfully\n";
28
mysql_close($conn);
?>
Getting Data From MySql Database: Example
NOTE: Always remember to put curly brackets when you want to insert
an array value directly into a string.
PHP provides another function called mysql_fetch_assoc() which also
returns the row as an associative array.
29
Getting Data From MySql Database: Example
if(! $result )
{
die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_assoc($result))
{
echo "EMP ID :{$row[‘id']} <br> ".
"EMP NAME : {$row[‘name']} <br> ".
"EMP SALARY : {$row['salary']} <br> ".
"--------------------------------<br>";
}
echo "Fetched data successfully\n";
mysql_close($conn);
?>
30
Getting Data From MySql Database: Example
<?php
include(‚conn.php");
mysql_select_db(‚MyDB‛,$conn);
$sql=‚select * from employee‛;
$result = mysql_query($sql,$conn);
If(!$result)
die(‚Unable to query:‛.mysql_err());
while($row=mysql_fetch_row($result)){
for($i=0;$i<count($row);$i++)
print ‚$row[$i]‛;
print‛<br>‛;
}
mysql_close($link);
?>
Java script
