Java Techies- Solution for All
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.