Connecting to a database via PHP is an extremely important step because if our script cannot connect to its database, queries to the database will fail. There are two methods to connect to a MySQL database using PHP: MySQLi and PDO. MySQLi stands for MySQL Improved. It is a MySQL-exclusive extension that adds new features to a MySQL database’s interface.
Earlier versions of PHP used the MySQL extension. However, this extension was deprecated in 2012.
PHP provides mysql_connect() function to open a database connection. We can disconnect from the MySQL database anytime using another PHP function mysql_close(). Since PHP 5.5, mysql_connect() extension is deprecated. Now it is recommended to use one of the 2 alternatives.
- mysqli_connect()
- PDO::__construct()
Connection to MySQL using MySQLi:
PHP mysqli_connect() function is used to connect with MySQL. This is an internal PHP function to establish a new connection to a MySQL server.
Syntax:
mysqli_connect (servername, username, password)
PHP mysqli_close()
PHP mysqli_close() function is used to disconnect with MySQL database. It returns true if connection is closed or false.
Syntax:
mysqli_close(resource $resource_link)
PHP MySQL Connect Example:
<?php
$servername=’localhost’;
$username=’root’;
$password=’ ‘;
// Create connection
$conn = mysqli_connect($servername, $username, $password);
// Check connection
if(!$conn)
{
die(‘Could not connect: ‘.mysqli_error());
}
echo ‘Connected successfully’;
mysqli_close($conn);
?>
Output:
Connected successfully
PHP MySQL Create Database
A database consists of one or more tables. The CREATE DATABASE statement is used to create a database in MySQL.
Since PHP 4.3, mysql_create_db() function is deprecated. Now it is recommended to use one of the 2 alternatives.
- mysqli_query()
- PDO::__query()
PHP MySQLi Create Database Example:
The following examples create a database named “mydb”:
<?php
$servername=’localhost’;
$username=’root’;
$password=’ ‘;
// Create connection
$conn = mysqli_connect($servername, $username, $password);
// Check connection
if(!$conn)
{
die(‘Could not connect: ‘.mysqli_error());
}
// Create database
$sql = ‘create database mydb’;
if(mysqli_query( $conn,$sql)) {
echo “Database mydb created successfully.”;
}
else {
echo “Error creating database “.mysqli_error($conn);
}
mysqli_close($conn);
?>
Output:
Database mydb created successfully.
PHP MySQL Create Table
A database table has its own unique name and consists of columns and rows. The CREATE TABLE statement is used to create a table in MySQL.
PHP mysql_query() function is used to create table. Since PHP 5.5, mysql_query() function is deprecated. Now it is recommended to use one of the 2 alternatives.
- mysqli_query()
- PDO::__query()
PHP MySQLi Create Table:
Example-1:
<?php
$servername=’localhost’;
$username=’root’;
$password=’ ‘;
$dbname = ‘mydb’;
// Create connection
$conn = mysqli_connect($servername, $username, $password,$dbname);
// Check connection
if(!$conn)
{
die(‘Could not connect: ‘.mysqli_error());
}
// Create table
$sql = “create table student(roll int AUTO_INCREMENT,name VARCHAR(20) NOT NULL, marks INT NOT NULL,primary key (roll))”;
if(mysqli_query($conn, $sql)) {
echo “Table student created successfully”;
}
else {
echo “Could not create table: “. mysqli_error($conn);
}
mysqli_close($conn);
?>
Output:
Table student created successfully
Example-2:
<?php
$servername=”localhost:3308″;
$username=”root”;
$password=” “;
$dbname=”mydb”;
$conn=mysqli_connect($servername,$username,$password,$dbname);
if(!$conn)
{
die(“Connection failed”.mysql_connect_error());
}
$sql=”create table emp(id int auto_increment,name varchar(20) not null,emp_salary int not null, primary key(id))”;
if(mysqli_query($conn,$sql))
{
echo “Table emp created successfully”;
}
else
{
echo “Could not craete table”.mysqli_error($conn);
}
mysqli_close($conn);
?>
Output:
Table emp created successfully
PHP MySQL Insert Record:
After a database and a table have been created, we can start adding data in them. The INSERT INTO statement is used to add new records to a MySQL table.
PHP mysql_query() function is used to insert record in a table. Since PHP 5.5, mysql_query() function is deprecated. Now it is recommended to use one of the 2 alternatives.
- mysqli_query()
- PDO::__query()
PHP MySQLi Insert Record:
Example-1:
<?php
$servername=’localhost’;
$username=’root’;
$password=’ ‘;
$dbname = ‘mydb’;
// Create connection
$conn = mysqli_connect($servername, $username, $password,$dbname);
// Check connection
if(!$conn)
{
die(‘Could not connect: ‘.mysqli_error());
}
//insert a record
$sql = ‘INSERT INTO student values(1,”Eliza”, 80)’;
if(mysqli_query($conn, $sql)) {
echo “Record inserted successfully”;
}
else {
echo “Could not insert record: “. mysqli_error($conn);
}
mysqli_close($conn);
?>
Output:
Record inserted successfully
Example-2:
<?php
$servername=”localhost”;
$username=”root”;
$password=” “;
$dbname=”mydb”;
$conn=mysqli_connect($servername,$username,$password,$dbname);
if(!$conn)
{
die(“Connection failed”.mysql_connect_error());
}
$sql=”insert into emp(name,emp_salary) values(‘Binoy’,10000)”;
if(mysqli_query($conn,$sql))
{
echo “Record inserted successfully”;
}
else
{
echo “could not insert record”.mysqli_error($conn);
}
mysqli_close($conn);
?>
Output:
Record inserted successfully
PHP MySQL Insert Multiple Records:
Multiple SQL statements must be executed with the mysqli_multi_query() function.
Note that each SQL statement must be separated by a semicolon.
Example:
The following examples add three new records to the “student” table:
<?php
$servername=’localhost’;
$username=’root’;
$password=’ ‘;
$dbname = ‘mydb’;
// Create connection
$conn = mysqli_connect($servername, $username, $password,$dbname);
// Check connection
if(!$conn)
{
die(‘Could not connect: ‘.mysqli_error());
}
//insert multiple records
$sql =’insert into student values(2,”Raja”,70);’;
$sql .=’insert into student values(3,”Rajiv”,78);’;
$sql .=’insert into student values(8,”samuel”,75)’;
if (mysqli_multi_query($conn, $sql)) {
echo “New records created successfully”;
}
else {
echo “Error: ” .$sql . “<br>” . mysqli_error($conn);
}
mysqli_close($conn);
?>
Output:
New records created successfully
PHP MySQL Select Data
PHP mysql_query() function is used to execute select query. Since PHP 5.5, mysql_query() function is deprecated. Now it is recommended to use one of the 2 alternatives.
- mysqli_query()
- PDO::__query()
There are two other MySQLi functions used in select query.
- 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 column name of the table. It return NULL if there are no more rows.
Select Data From a MySQL Database
The SELECT statement is used to select data from one or more tables:
SELECT column_name(s) FROM table_name;
or we can use the * character to select ALL columns from a table:
SELECT * FROM table_name;
PHP MySQLi Select Query
Example-1:
The following example selects the roll, name and marks columns from the student table and displays it on the page:
<?php
$servername=’localhost’;
$username=’root’;
$password=’ ‘;
$dbname = ‘mydb’;
// Create connection
$conn = mysqli_connect($servername, $username, $password,$dbname);
// Check connection
if(!$conn)
{
die(‘Could not connect: ‘.mysqli_error());
}
// Select record
$sql = “SELECT * FROM student”;
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
// output data of each row
while($row = mysqli_fetch_assoc($result)) {
echo “roll: ” . $row[“roll”]. ” – Name: ” . $row[“name”]. ” – Marks: ” . $row[“marks”]. “<br>”;
}
}
else {
echo “0 results”;
}
mysqli_close($conn);
?>
Output:
Roll: 1 – Name: Eliza – Marks: 80
Roll: 2 – Name: Rajiv – Marks: 75
Roll: 3 – Name: Samuel – Marks: 75
Roll: 4 – Name: Raja – Marks: 78
Roll: 5 – Name: Pratima – Marks: 78
Roll: 6 – Name: Sulagna – Marks: 75
Roll: 7 – Name: Sulagna – Marks: 75
Roll: 8 – Name: Yash – Marks: 75
Example-2:
<?php
$servername=”localhost:3308″;
$username=”root”;
$password=” “;
$dbname=”mydb”;
$conn=mysqli_connect($servername,$username,$password,$dbname);
if(!$conn)
{
die(“Connection failed”.mysql_connect_error());
}
$sql=”select * from emp”;
if($result=mysqli_query($conn,$sql))
{
if(mysqli_num_rows($result)>0)
{
echo “<table border=1>”;
echo “<tr>”;
echo “<th>Name</th>”;
echo “<th>Emp_Salary</th>”;
echo “</tr>”;
while ($row=mysqli_fetch_array($result))
{
echo “<tr>”;
echo “<td>”.$row[‘name’].”</td>”;
echo “<td>”.$row[’emp_salary’].”</td>”;
echo “</tr>”;
}
echo “</table>”;
mysqli_free_result($result);
}
else
{
echo “No matching recordsare found.”;
}
}
else
{
echo “ERROE: Could not able to execute $sql.”.mysqli_error($conn);
}
mysqli_close($conn);
?>
Output:
Select and Filter Data From a MySQL Database:
The WHERE clause is used to filter records.
The WHERE clause is used to extract only those records that fulfill a specified condition.
Syntax:
SELECT column_name(s) FROM table_name WHERE column_name operator valueÂ
The following example selects the roll, name and marks columns from the student table where the name is “Eliza”, and displays it on the page:
<?php
$servername=’localhost’;
$username=’root’;
$password=’ ‘;
$dbname = ‘mydb’;
// Create connection
$conn = mysqli_connect($servername, $username, $password,$dbname);
// Check connection
if(!$conn)
{
die(‘Could not connect: ‘.mysqli_error());
}
$sql = “SELECT roll, name, marks FROM student WHERE name=’Eliza'”;
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
// output data of each row
while($row = mysqli_fetch_assoc($result)) {
echo “roll: ” . $row[“roll”]. ” – Name: ” . $row[“name”]. ” – Marks ” . $row[“marks”]. “<br>”;
}
}
else {
echo “0 results”;
}
mysqli_close($conn);
?>
Output:
roll: 1 – Name: Eliza – Marks 80
PHP MySQL Update Record
PHP mysql_query() function is used to update record in a table. Since PHP 5.5, mysql_query() function is deprecated. Now it is recommended to use one of the 2 alternatives.
- mysqli_query()
- PDO::__query()
The UPDATE statement is used to update existing records in a table:
UPDATE table_name
SET column1=value, column2=value2,…valueN
WHERE some_column=some_value;
Note: The WHERE clause specifies which record or records that should be updated. If we omit the WHERE clause, all records will be updated.
PHP MySQLi Update Record Example
The following examples update the record with roll=2 in the “student” table:
<?php
$servername=’localhost’;
$username=’root’;
$password=’ ‘;
$dbname = ‘mydb’;
// Create connection
$conn = mysqli_connect($servername, $username, $password,$dbname);
// Check connection
if(!$conn)
{
die(‘Could not connect: ‘.mysqli_error());
}
// update a record
$sql = “UPDATE student SET name=’Ankit’ WHERE roll=2”;
if (mysqli_query($conn, $sql)) {
echo “Record updated successfully”;
}
else {
echo “Error updating record: ” . mysqli_error($conn);
}
mysqli_close($conn);
?>
Output:
Record updated successfully
PHP MySQL Delete Record
PHP mysql_query() function is used to delete record in a table. Since PHP 5.5, mysql_query() function is deprecated. Now it is recommended to use one of the 2 alternatives.
- mysqli_query()
- PDO::__query()
The DELETE statement is used to delete records from a table.
Syntax:
DELETE FROM table_name
WHERE some_column = some_value
Note: The WHERE clause specifies which record or records that should be deleted. If we omit the WHERE clause, all records will be deleted.
PHP MySQLi Delete Record Example:
The following examples delete the record with roll=3 in the “student” table.
<?php
$servername=’localhost’;
$username=’root’;
$password=’ ‘;
$dbname = ‘mydb’;
// Create connection
$conn = mysqli_connect($servername, $username, $password,$dbname);
// Check connection
if(!$conn)
{
die(‘Could not connect: ‘.mysqli_error());
}
// delete a record
$sql = “DELETE FROM student WHERE roll=8”;
if (mysqli_query($conn, $sql)) {
echo “Record deleted successfully”;
}
else {
echo “Error deleting record: ” . mysqli_error($conn);
}
mysqli_close($conn);
?>
Output:
Record deleted successfully
I’ve read several good stuff here. Definitely worth bookmarking for revisiting. I surprise how much effort you put to make such a magnificent informative site.
Thanks for commenting..I like your kind words…