MySQL Function in PHP

MySQL Function in PHP

In PHP Scripting language many functions are available for MySQL Database connectivity and executing SQL queries.

MySQLi is extension in PHP scripting language which gives access to the MYSQL database. MySQLi extension was introduced version 5.0.0,

The MySQLi extension contains the following important functions which are related to MySQL database connectivity and management.

● Mysqli_connect() Function

● Mysqli_close() Function

● Mysqli_query()Function

Database Connections:

Before accessing MySQL Database, connect to Database Server machine via PHP scripting language using Mysqli_connect() Function.

Syntax:

mysqli_connect(“Server Name “,”User Name”,”Password”,”DB Name”);

This function requires four parameters to connect to database server. Database Server name, Database username, password and Database Name.

Managing Database Connections

The below code describes managing database connection methods and features.

<?php

$servername = “localhost”;

$username = “username”;

$password = “password”;

$DB_name = “School_DB”;

$conn = mysqli_connect($servername, $username, $password,$DB_name);

if (!$conn) {

die(“Connection failed: “ . mysqli_connect_error());

}

echo “Connected successfully”;

?>

In the above code snippet, four variables are used to connect to the Database server. They are

● $servername -> Database Server Server IP address

● $username -> Database Server User Name

● $password -> Database Server Password

● $DB_Name -> Database Name

The mysqli_connect function uses these variables to connect Database server to PHP. If connection gets fail, output will be printed with MySQL error code. Otherwise connection is success.

Performing Queries

The main goal of MySQL and PHP connectivity is to retrieve and manipulate the data from MySQL database server. The SQL query statements help in PHP MySQL extension to achieve the objective of MySQL and PHP connection. “mysqli_query” is a function, that helps to execute the SQL query statements in PHP scripting language.

Syntax:

mysqli_query(“Connection Object”,”SQL Query”)

Example:

$con=mysqli_connect(“localhost”,”my_user”,”my_password”,”Student_DB “);

$sql=”SELECT student_name,student_age FROM student”;mysqli_query($con,$sql);

Closing Connection:

mysqli_close() Function is used to close an existing opened database connection between PHP scripting and MySQL Database Server.

Syntax:

mysqli_close(“Connection Object”);

Example:

<?php

$con=mysqli_connect(“localhost”,”$user”,”$password”,”SCHOOL_DB”);

mysqli_close($con);

?>

Example of PHP and MySQL Program:

<?php

$servername = “localhost”;

$username = “username”;

$password = “password”;

$dbname = “school_DB”;

$connection = mysqli_connect(‘$servername ‘, ‘$username’, ‘$password’,’$dbname’);

if (mysqli_connect_errno())

{

echo “Failed to connect to MySQL: “ . mysqli_connect_error();

}

sql_stmt = “SELECT * FROM my_contacts”; //SQL select query

$result = mysqli_query($connection,$sql_stmt);//execute SQL statement

$rows = mysqli_num_rows($result);// get number of rows returned

if ($rows)

{

while ($row = mysqli_fetch_array($result))

{

echo ‘ID: ‘ . $row[‘id’] . ‘<br>’;

echo ‘Full Names: ‘ . $row[‘full_names’] . ‘<br>’;

echo ‘Gender: ‘ . $row[‘gender’] . ‘<br>’;

echo ‘Contact No: ‘ . $row[‘contact_no’] . ‘<br>’;

echo ‘Email: ‘ . $row[‘email’] . ‘<br>’;

echo ‘City: ‘ . $row[‘city’] . ‘<br>’;

echo ‘Country: ‘ . $row[‘country’] . ‘<br><br>’;

}

}

mysqli_close($connection); //close the database connection

?>

In the above code the SQL query retrieves two records from student table in school database. These records are populated into client browser using PHP scripting language.