MySQLi Procedural func3ons
MySQLi (MySQL Improved) provides procedural and object oriented interface to data and its
management. The i extension MySQL func3ons allows the user to access its database servers.
The MySQL improved extension is specially designed to work with MySQL version 4.1.13 and
new versions.
1. mysqli_connect():
As you know, before doing any database related opera3ons, you need to establish a
connec3on to the MySQL database server. If the connec3on is established successfully,
then it returns a database connec3on resource iden3fier. If the connec3on encounters
failure, then it just throws an error.
<?php
// Database configuration
$host = "localhost";
$dbuser = "root";
$dbpass = "";
$dbname = "test_ESST";
// Create database connection
$conn = mysqli_connect($host, $dbuser, $dbpass, $dbname);
// Check connection
if(mysqli_connect_error())
{
echo "Connection establishing failed! <br >";
}
else
{
echo "Connection established successfully. <br >";
}
?>
2. mysqli_connect_error():
The MySQLi func3on throws an error when the connec3on is not made successfully
and the func3on stores the error in previous call to mysqli_connect(). If no error is
encountered, it returns NULL. If any error is encountered , then it returns an error
message.
Note:
• To test mysqli_connect_error(), stop the MySQL server in XAMPP control panel and
then call the above PHP code having mysqli_connect().
• If display_errors are enabled in PHP configuration, you can see an error of
mysqli_connect_error() which returns the following message.
3. mysqli_select_db():
This mySQLi func3on is used to change the default database for making a connec3on.
<?php
// Database configuration
$host = "localhost";
$dbuser = "root";
$dbpass = "";
$dbname = "test";
$dbtest = "ESST";
// Create database connection
$conn = mysqli_connect($host, $dbuser, $dbpass, $dbname);
//write some code for database "test"
// Change database to "ESST"
mysqli_select_db($conn,$dbtest);
// PHP code for database "GFG_TEST"...
mysqli_close($conn);
?>
4. mysqli_close():
This MySQLi func3on is used to close a previously connected database. This func3on
will return TRUE on successful closing, otherwise it will return FALSE.
<?php
// Database configuration
$host = "localhost";
$dbuser = "root";
$dbpass = "";
$dbname = "test";
// Create database connection
$conn = mysqli_connect($host, $dbuser, $dbpass, $dbname);
//some php code
if(mysqli_close($conn))
echo "Connection closed successfully.";
?>
5. mysqli_prepare():
The above MySQLi func3on is used to prepare a MySQL query for execu3on. It returns
a statement object for further opera3ons and returns FALSE if some error occurs.
<?php
if (file_exists('Config/[Link]'))
{
require 'Config/[Link]';
}
else {
echo "File not found";
die();
}
$pwd_hash = password_hash($pwd,PASSWORD_DEFAULT);
// prepare the mysql query statement and bind parameters
$query = mysqli_prepare($conn,"INSERT INTO user
(email,pass,first_name,last_name) VALUES (?,?,?,?)");
$query->bind_param("ssss", $usr, $pwd_hash, $fnm, $lnm);
$fnm = $_POST["fname"];
$lnm = $_POST["lname"];
$usr = $_POST["email"];
$pwd = $_POST["pass"];
$query->execute();
echo "New record inserted successfully";
mysqli_close($link);
6. mysqli_query():
This MySQLi func3on performs or executes the query on the given database.
<?php
if (file_exists('Config/[Link]'))
{
require 'Config/[Link]';
}
else {
echo "File not found";
die();
}
mysqli_query($link,"INSERT INTO user
(email,pass,first_name,last_name) VALUES ('$usr', '$pwd_hash',
'$fnm', '$lnm')");
echo "Inserted successfully";
mysqli_close($link);
7. mysqli_fetch_row():
The above MySQLi func3on is used to fetch one row from the result-set as an
enumerated array. Each call to the above func3on will return the next row from the
result set. If no rows are fetched, then it returns FALSE.
<?php
if (file_exists('Config/[Link]'))
{
require 'Config/[Link]';
}
else {
echo "File not found";
die();
}
$query = "SELECT first_name,last_name from user";
if ($result=mysqli_query($link,$query))
{
// Fetch one and one row
while ($row=mysqli_fetch_row($result))
{
echo " First name :".$row[0]." , ";
echo " Last name : ".$row[1];
echo nl2br (" \n ");
}//end while
// Free result set
mysqli_free_result($result);
}// end if
mysqli_close($link);
8. mysqli_field_count():
The above MySQLi func3on is used to return the number of columns for the most
recent query. It returns total number of columns in the result set.
<?php
if (file_exists('Config/[Link]'))
{
require 'Config/[Link]';
}
else {
echo "File not found";
die();
}
$query = "SELECT * from user";
mysqli_query($link,$query);
$total_columns = mysqli_field_count($link);
echo $total_columns.nl2br (" \n ");
mysqli_close($link);
9. mysqli_fetch_array():
The above MySQLi func3on is used to fetch a row as an associa3ve, numeric array or
both types of array from the result set.
<?php
if (file_exists('Config/[Link]'))
{
require 'Config/[Link]';
}
else {
echo "File not found";
die();
}
$query = "SELECT first_name,last_name from user";
$result=mysqli_query($link,$query);
// Gets the Numeric array
$row=mysqli_fetch_array($result,MYSQLI_NUM);
echo " First name (Num) :".$row[0];
echo ",";
echo " Last name (Num) : ".$row[1];
echo nl2br (" \n ");
// Gets the Associative array
$row=mysqli_fetch_array($result,MYSQLI_ASSOC);
echo " First name (Array) :".$row["first_name"];
echo ",";
echo " Last name (Array): ".$row["last_name"];
echo nl2br (" \n ");
// Free the result set
mysqli_free_result($result);
mysqli_close($link);
10. mysqli_fetch_all():
The MySQLi func3on fetches all rows and return the result set as an associa3ve array,
a numeric array, or both.
<?php
if (file_exists('Config/[Link]'))
{
require 'Config/[Link]';
}
else {
echo "File not found";
die();
}
$query = "SELECT first_name from user";
$result = mysqli_query($link,$query);
$rowcount=mysqli_num_rows($result);
// Gets the Associative array
$row = mysqli_fetch_all($result,MYSQLI_ASSOC);
print_r($row);
for($i=0;$i<$rowcount;$i++)
{
echo "<br> ".$row[$i]['first_name'];
}
// Free the result set
mysqli_free_result($result);
mysqli_close($link);
11. mysqli_free_result():
The above MySQLi func3on free the memory of the fetched rows of the result set.
12. mysqli_num_rows():
The above MySQLi func3on is used to return the number of rows of the result set.
13. mysqli_affected_rows():
The above MySQLi func3on is used to return the total number of affected rows from
the previous MySQL SELECT, INSERT, UPDATE, DELETE or REPLACE query.
14. mysqli_get_server_info():
The above MySQLi func3on is used to return the MySQL server version.
15. mysqli_fetch_fields():
The above MySQLi func3on returns an array of objects which contains the informa3on
of columns of the result set.
<?php
if (file_exists('Config/[Link]'))
{
require 'Config/[Link]';
}
else {
echo "File not found";
die();
}
$query = "SELECT first_name,last_name FROM user";
if ($result=mysqli_query($link,$query))
{
// Get the fields
$fields=mysqli_fetch_fields($result);
print nl2br("\n");
print_r($fields);
print nl2br("\n");
foreach ($fields as $value)
{
echo "Column name : ".$value->name."<br> ";
echo "Table name : ".$value->table."<br> ";
echo "Maximum length : ".$value-
>max_length."<br> ";
echo nl2br (" \n ");
}
// Free result set
mysqli_free_result($result);
}
mysqli_close($link);