PHP MySQL Database Connection
Code for Establishing a database connection with MySQL Database using MySqli Extension
<?php
#Establishing a connection with the database
function OpenCon()
{
$dbhost = "localhost";
$dbuser = "root";
$dbpass = "1234";
$db = "satish";
$conn = new mysqli($dbhost, $dbuser,"",$db) or die("Connect failed: %s\n". $conn -> error);
return $conn;
}
function CloseCon($conn)
{
$conn -> close();
}
$conn = OpenCon();
echo "Connected Successfully";
CloseCon($conn);
?>
Output
Code for inserting a record into the MySQL Database
<?php
#inserting a record in a table
function OpenCon()
{
$dbhost = "localhost";
$dbuser = "root";
$dbpass = "1234";
$db = "satish";
$conn = new mysqli($dbhost, $dbuser,"",$db) or die("Connect failed: %s\n". $conn -> error);
return $conn;
}
function CloseCon($conn)
{
$conn -> close();
}
$conn = OpenCon();
echo "Connected Successfully";
$sql = "INSERT INTO employee (eid,name,phone,salary) VALUES ('12456', 'Mathew', 55555,10090)";
if ($conn->query($sql) === TRUE) {
echo "New record created successfully";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
CloseCon($conn);
?>
Output
Code for updating a record in the MySQL Database
<?php
#updating a record using the primary key in the database
function OpenCon()
{
$dbhost = "localhost";
$dbuser = "root";
$dbpass = "1234";
$db = "satish";
$conn = new mysqli($dbhost, $dbuser,"",$db) or die("Connect failed: %s\n". $conn -> error);
return $conn;
}
function CloseCon($conn)
{
$conn -> close();
}
$conn = OpenCon();
echo "Connected Successfully";
$sql = "UPDATE employee SET name='Chris' WHERE eid=12456";
if ($conn->query($sql) === TRUE) {
echo "Record updated successfully";
} else {
echo "Error updating record: " . $conn->error;
}
CloseCon($conn);
?>
Output
Code for selecting records from a MySQL Database
<?php
#Selecting all the records from a table
function OpenCon()
{
$dbhost = "localhost";
$dbuser = "root";
$dbpass = "1234";
$db = "satish";
$conn = new mysqli($dbhost, $dbuser,"",$db) or die("Connect failed: %s\n". $conn -> error);
return $conn;
}
function CloseCon($conn)
{
$conn -> close();
}
$conn = OpenCon();
$sql = "SELECT eid,name FROM employee";
$result = $conn->query($sql);
if ($result->num_rows > 0)
{
// output data of each row
while($row = $result->fetch_assoc())
{
echo "eid: " . $row["eid"]. " - Name: " . $row["name"]."<br>";
}
} else
{
echo "0 results";
}
CloseCon($conn);
?>
Output
Code for searching a record from the database
<?php
#Selecting all the records from a table
function OpenCon()
{
$dbhost = "localhost";
$dbuser = "root";
$dbpass = "1234";
$db = "satish";
$conn = new mysqli($dbhost, $dbuser,"",$db) or die("Connect failed: %s\n". $conn -> error);
return $conn;
}
function CloseCon($conn)
{
$conn -> close();
}
$conn = OpenCon();
$sql = "SELECT eid,name FROM employee where eid=12456";
$result = $conn->query($sql);
if ($result->num_rows > 0)
{
// output data of each row
while($row = $result->fetch_assoc())
{
echo "eid: " . $row["eid"]. " - Name: " . $row["name"]."<br>";
}
} else
{
echo "0 results";
}
CloseCon($conn);
?>
Output
Code for Deleting a record from the MySQL Database
<?php
#Deleting a record from the mysql table
function OpenCon()
{
$dbhost = "localhost";
$dbuser = "root";
$dbpass = "1234";
$db = "satish";
$conn = new mysqli($dbhost, $dbuser,"",$db) or die("Connect failed: %s\n". $conn -> error);
return $conn;
}
function CloseCon($conn)
{
$conn -> close();
}
$conn = OpenCon();
$sql = "delete from employee where eid=12457";
if ($conn->query($sql) === TRUE) {
echo "Record deleted successfully";
} else {
echo "Error deleting record: " . $conn->error;
}
CloseCon($conn);
?>
Output
Code Demonstrating parameters of MySQLi class
<?php
#parameters of mysqli class
function OpenCon()
{
$dbhost = "localhost";
$dbuser = "root";
$dbpass = "1234";
$db = "satish";
$conn = new mysqli($dbhost, $dbuser,"",$db) or die("Connect failed: %s\n". $conn -> error);
return $conn;
}
function CloseCon($conn)
{
$conn -> close();
}
$conn = OpenCon();
echo "Connected Successfully";
echo $conn->host_info; echo "<br>";
echo $conn->protocol_version;echo "<br>";
echo $conn->server_info;echo "<br>";
echo $conn->server_version;echo "<br>";
echo $conn->client_info;echo "<br>";
echo $conn->client_version;echo "<br>";
CloseCon($conn);
?>
Output
PHP Data Objects
Establishing a Database connection using Php Data objects
<?php
#PHP Data Objects
$dbHost="localhost";
$dbName="satish";
$dbUser="root"; //by default root is user name.
$dbPassword=""; //password is blank by default
try{
$dbConn= new PDO("mysql:host=$dbHost;dbname=$dbName",$dbUser,$dbPassword);
Echo "Successfully connected with database Satish";
} catch(Exception $e){
Echo "Connection failed" . $e->getMessage();
}
// this command close the connection.
$dbConn = null;
?>
Output
Inserting a Record using Php Data objects
<?php
#PHP Data Objects
$dbHost="localhost";
$dbName="satish";
$dbUser="root"; //by default root is user name.
$dbPassword=""; //password is blank by default
try{
$dbConn= new PDO("mysql:host=$dbHost;dbname=$dbName",$dbUser,$dbPassword);
Echo "Successfully connected with database Satish";
$query = $dbConn->exec("insert into employee values('124','ram',2333,566);"); // returns number of affected rows
if($query)
{
echo "Row Inserted";
}
} catch(Exception $e){
Echo "Connection failed" . $e->getMessage();
}
// this command close the connection.
$dbConn = null;
?>
Output
Updating a record in the database using PhP Data Objects
<?php
#PHP Data Objects
$dbHost="localhost";
$dbName="satish";
$dbUser="root"; //by default root is user name.
$dbPassword=""; //password is blank by default
try{
$dbConn= new PDO("mysql:host=$dbHost;dbname=$dbName",$dbUser,$dbPassword);
$query = $dbConn->exec("update employee set name='tom' where eid='124';"); // returns number of affected rows
if($query)
{
echo "Row Updated Successfully";
}
} catch(Exception $e){
Echo "Connection failed" . $e->getMessage();
}
// this command close the connection.
$dbConn = null;
?>
Output
Deleting a record from the table using PHP Data Objects
<?php
#PHP Data Objects
$dbHost="localhost";
$dbName="satish";
$dbUser="root"; //by default root is user name.
$dbPassword=""; //password is blank by default
try{
$dbConn= new PDO("mysql:host=$dbHost;dbname=$dbName",$dbUser,$dbPassword);
$query = $dbConn->exec("delete from employee where eid='124';"); // returns number of affected rows
if($query)
{
echo "Row Deleted Successfully";
}
} catch(Exception $e){
Echo "Connection failed" . $e->getMessage();
}
// this command close the connection.
$dbConn = null;
?>
Output
Selecting Records from a table using PHP Data Objects
<?php
#PHP Data Objects - selecting records from a table
$dbHost="localhost";
$dbName="satish";
$dbUser="root"; //by default root is user name.
$dbPassword=""; //password is blank by default
try{
$dbConn= new PDO("mysql:host=$dbHost;dbname=$dbName",$dbUser,$dbPassword);
$query = $dbConn->query("select * from employee;");
foreach($query as $row)
{
echo $row['eid'];
echo $row['name'];
echo $row['phone'];
echo $row['salary'];
echo "<br>";
}
} catch(Exception $e){
Echo "Connection failed" . $e->getMessage();
}
// this command close the connection.
$dbConn = null;
?>
Output
Writing the data from a table as an HTML table to the client using PHP
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Document</title>
<style>
table th{
background-color: aqua;}
tr:nth-child(2n+1)
{
background-color:bisque;
}
</style>
</head>
<body>
</body>
</html>
<?php
#Selecting all the records from a table
function OpenCon()
{
$dbhost = "localhost";
$dbuser = "root";
$dbpass = "1234";
$db = "satish";
$conn = new mysqli($dbhost, $dbuser,"",$db) or die("Connect failed: %s\n". $conn -> error);
return $conn;
}
function CloseCon($conn)
{
$conn -> close();
}
$conn = OpenCon();
$sql = "SELECT * FROM employee";
$result = $conn->query($sql);
if ($result->num_rows > 0)
{
echo "<table border='1'>
<tr>
<th>Employee Id</th>
<th>Name</th>
<th>Phone</th>
<th>Salary</th>
</tr>";
// output data of each row
while($row = $result->fetch_assoc())
{
echo "<tr>";
echo "<td>" . $row['eid'] . "</td>";
echo "<td>" . $row['name'] . "</td>";
echo "<td>" . $row['phone'] . "</td>";
echo "<td>" . $row['salary'] . "</td>";
echo "</tr>";
}
echo "</table>";
} else
{
echo "0 results";
}
CloseCon($conn);
?>
Output