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', ‘mahe@yahoo.com')";
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', ‘San@gmal.com')";
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