MySQL


  • MySQL is the most popular Open Source SQL database management system.

  • MySQL is developed and supported by Oracle Corporation.

  • MySQL is Relational DataBase System.

  • MySQL is very fast, reliable, scalable, and easy to use DataBase Server.

  • MySQL is named after co-founder Monty Widenius's daughter, My.

  • MySQL connects to our tables using our own fast low-level routines.


MySQL Connect with PHP



mysql_connect and mysqli_connect() function are use to open a new connection to the MySQL server in PHP.

Syntax
mysql_connect(host, user, password);
or

mysqli_connect(host,username,password,dbname);

Parameters Description
host IP Address or host.
user User name of MySQL.
password password of MySQL.
dbname Which Database to be used.

Example1:
<?php

mysql_connect("localhost", "user", "password") or die(mysql_error());

echo "Connected to MySQL <br />";

mysql_close(); //This function use for close the connection.

echo "Connection closed";

?>
Output :
Connected to MySQL
Connection closed

Example2 :
<?php

// Create connection
$con=mysqli_connect("localhost","username","password","php_db");

// Check connection
if (mysqli_connect_errno($con))
{
	echo "Connection Failed " . mysqli_connect_error();
}
else 
{
	echo "Connection Created";
}
mysqli_close($con);//Close Connection.

?>
Output :
Connection Created

Create Database


CREATE DATABASE statement use to create a database table in MySQL.
Example :
<?php
$con=mysqli_connect("localhost","username","password");
// Check connection
if (mysqli_connect_errno())
  {
  echo "Connection Failed" . mysqli_connect_error();
  }

 // Create database
$sql="CREATE DATABASE php_db";
if (mysqli_query($con,$sql))
  {
  echo "Database php_db created successfully";
  }
else
  {
  echo "Error " . mysqli_error($con);
  }
?>
Output :
Database php_db created successfully

Create Table


CREATE TABLE statement use to create a table in MySQL.
Example :
<?php

$con=mysqli_connect("localhost","username","password","php_db");
// Check connection
if (mysqli_connect_errno())
{
	echo "Conection Failed " . mysqli_connect_error();
}

// Create table
$sql="CREATE TABLE Employees(FirstName CHAR(30),LastName CHAR(30),Age INT)";

// Execute query
if (mysqli_query($con,$sql))
{
	echo "Employees table created successfully";
}
else
{
	echo "Error : " . mysqli_error($con);
}
?>
Output :
Employees table created successfully.

Insert Data Into Table In Database


INSERT INTO statement use to add new records to a database table.
Example :
<?php

$con=mysqli_connect("localhost","username","password","php_db");
// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

mysqli_query($con,"INSERT INTO Employees (FirstName, LastName, Age)
VALUES ('fname', 'lname',35)");

mysqli_query($con,"INSERT INTO Employees (FirstName, LastName, Age) 
VALUES ('fname1', 'lname1',33)");

echo "Table Inserted";

mysqli_close($con);

?>
Output :
Table Inserted

Select Data From a Table In Database


SELECT statement use to select data from a database.
Example :
<?php

$con=mysqli_connect("localhost","username","password","php_db");
// Check connection
if (mysqli_connect_errno())
  {
  echo "Connection Failed: " . mysqli_connect_error();
  }

$result = mysqli_query($con,"SELECT * FROM Employees");

while($row = mysqli_fetch_array($result))
  {
  echo $row['FirstName'] . " " . $row['LastName'];
  echo "
"; } mysqli_close($con); ?>
Output :
fname lname
fname1 lname1

WHERE clause


WHERE clause use to extract only those records that fulfill a specified criteria.
Example :
<?php

$con=mysqli_connect("localhost","username","password","php_db");
// Check connection
if (mysqli_connect_errno())
  {
  echo "Conection Failed " . mysqli_connect_error();
  }

$result = mysqli_query($con,"SELECT * FROM Employees
WHERE FirstName='fname'");

while($row = mysqli_fetch_array($result))
  {
  echo $row['FirstName'] . " " . $row['LastName'];
  echo "
"; } ?>
Output :
fname lname

ORDER BY


ORDER BY keyword use to sort the data in a recordset.

The sorting of the records in ascending order by default.
Example :
<?php

$con=mysqli_connect("localhost","username","password","php_db");
// Check connection
if (mysqli_connect_errno())
  {
  echo "onnection Failed: " . mysqli_connect_error();
  }

$result = mysqli_query($con,"SELECT * FROM Employees ORDER BY age");

while($row = mysqli_fetch_array($result))
  {
  echo $row['FirstName'];
  echo " " . $row['LastName'];
  echo " " . $row['Age'];
  echo "
"; } mysqli_close($con); ?>
Output :
fname1 lname1 33
fname lname 35

Update Data In a Database


UPDATE statement use to update existing records in a table.
Example :
<?php

$con=mysqli_connect("localhost","username","password","php_db");
// Check connection
if (mysqli_connect_errno())
  {
  echo "Connection Failed: " . mysqli_connect_error();
  	  }

mysqli_query($con,"UPDATE Persons SET Age=36
WHERE FirstName='fname' AND LastName='lname'");

echo " Data Updated. ";

mysqli_close($con);

?>
Output :
Data Updated.

Delete Data In a Database


DELETE FROM statement use to delete records from a database table.
Example :
<?php

$con=mysqli_connect("localhost","username","password","php_db");
// Check connection
if (mysqli_connect_errno())
  {
  echo "Connection Failed: " . mysqli_connect_error();
  	  }

mysqli_query($con,"DELETE FROM Persons WHERE LastName='lname'");

echo "Data Deleted. ";
mysqli_close($con);

?>
Output :
Data Deleted.