PHP TUTORIALDatabase Programming in PHP
The development of PHP has focused heavily on database access, and as a result, it provides driver support for 15 distinct databases systems.
You may connect to and work with different databases using PHP. Among them, MySQL is the most widely used database platform for PHP.
Establishing a connection to the database :
We require four parameters to connect to the database.
- Hostname (By default "localhost")
- Username (By default "root")
- Password (By default "No Password")
- Database Name
☞ mysqli_connect()
One built-in function in PHP allows us to establish a connection to a database (MySQL) .i.e. mysqli_connect()
format:mysqli_connect("hostname","username","password","databasename")The above function creates a successful connection with Database if you provide valid information. When the connection is not well, we can kill that connection by using die()
format:
die("Can't create connection");
☞ mysqli_close()
mysqli_close() function is used to close or terminate the connection with MySQL database.
format:
mysqli_close(Connection);
☞ An example program to create a connection with the database.
Example: "DbConnection.php"
<?php
$host = 'localhost';
$user = 'root';
$pass = '';
$dbname = 'sampledb';
// to create the connection
$conn = mysqli_connect($host, $user, $pass, $dbname);
if(! $conn )
{
die('Could not connect: ' . mysqli_error());
}
echo 'Connection established successfully';
// to close the connection
mysqli_close($conn);
?>
Output :
Executing simple queries :
Once you've established a connection to the database, you can run/perform a variety of actions on it.
☞ mysqli_query()
PHP includes a built-in function to carry out many database operations, including create, insert, update, and delete. i.e. mysqli_query()
format:
mysqli_query(Connection,Query);
The above function has two parameters, those are
- Connection : Specifies the name of the connection
- Query: Specifies the query
☞ An example program to create to table in the database.
Example: "TblCreation.php"
<?php
$host = 'localhost';
$user = 'root';
$pass = '';
$dbname='sampledb';
$conn = mysqli_connect($host, $user, $pass,$dbname);
if(! $conn )
{
die('Could not connect: ' . mysqli_error());
}
echo 'Connection established successfully'.'<br>';
// sql query to create students table
$query = "CREATE TABLE students(id INT AUTO_INCREMENT,name VARCHAR(20) NOT NULL,Age INT NOT NULL,primary key (id))";
// to execute the query
if(mysqli_query($conn, $query)){
echo "Table students created successfully";
}else{
echo "Could not create table: ". mysqli_error($conn);
}
// to close the connection
mysqli_close($conn);
?>
Output :
The "students" table structure in database :
☞ An example program to insert data into table.
Example: "InsertRecord.php"
<?php
$host = 'localhost';
$user = 'root';
$pass = '';
$dbname='sampledb';
$conn = mysqli_connect($host, $user, $pass,$dbname);
if(! $conn )
{
die('Could not connect: ' . mysqli_error());
}
echo 'Connected successfully'."<br/>";
$query = 'INSERT INTO students VALUES (1,"Amith",30)';
if(mysqli_query($conn, $query))
{
echo 'Record Inserted Successfully';
}
else
{
echo 'Error';
}
mysqli_close($conn);
?>
Output :
The data in the "students" table :
☞ An example program to delete data from the table.
Example: "DeleteRecord.php"
<?php
$host = 'localhost';
$user = 'root';
$pass = '';
$dbname='sampledb';
$conn = mysqli_connect($host, $user, $pass,$dbname);
if(! $conn )
{
die('Could not connect: ' . mysqli_error());
}
echo 'Connected successfully'."<br/>";
$query = 'DELETE FROM students WHERE id=2';
if(mysqli_query($conn, $query))
{
echo 'Records Deleted Successfully';
}
else
{
echo 'Error';
}
mysqli_close($conn);
?>
Output :
Retrieve data from Table
In PHP, mysqli_query() function is used to retrieve data from table. There are two other MySQLi functions used in select query. those are,
- mysqli_num_rows(mysqli_result $result): returns number of rows.
- mysqli_fetch_assoc(mysqli_result $result): returns row as an associative array. Each key of the array represents the name of column. It return NULL if there are no more rows.
☞ An example program to Select/retrive data from the table.
Example: "SelectRecord.php"
<?php
$host = 'localhost';
$user = 'root';
$pass = '';
$dbname='sampledb';
$conn = mysqli_connect($host, $user, $pass,$dbname);
if(! $conn )
{
die('Could not connect: ' . mysqli_error());
}
echo 'Connected successfully'."<br/>";
$query = 'SELECT * FROM students';
$result=mysqli_query($conn, $query);
if(mysqli_num_rows($result) > 0)
{
while($row = mysqli_fetch_assoc($result))
{
echo "Student ID :{$row['id']} <br> ".
"Student NAME : {$row['name']} <br> ".
"Student Age : {$row['age']} <br> ".
"--------------------------------<br>";
} //end of while
}
else
{
echo "0 results";
}
mysqli_close($conn);
?>
Output :
Next Topic :Handling Sessions in PHP