MySQLi Function in PHP: Part 1

Introduction

The MySQLi function allows you to access the MySQL database server and that function only works with MySQL 4.2 and above, so in this article I describe some of them like mysqli_affected_rows, mysqli_autocommit, mysqli_change_user, mysqli_character_set_name and mysqli_close  functions.

PHP mysqli_affected_rows() function

The PHP MySQLi "mysqli_affected_rows" function determines the number of affected rows in a previous MySQL operation or you can say it returns the number of affected rows in the previous SELECT, INSERT, UPDATE, REPLACE, or DELETE query.

Syntax

mysql_affected_rows(connection)

Parameter in mysqli_affected_rows function

The parameter of the function is:

Parameter	Description
connection	It specifies the MySQL connection to use.

Example

An example of the function is:

<?php
$con=mysqli_connect("localhost","root","","mysql");
if (mysqli_connect_errno($con))
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }
  /*........For Fetch data of Emp Table<br>
  .......*/
//mysql_select_db("mysql", $con);
print "<h2>MySQL: Data of Emp table</h2>";
$result =mysqli_query($con,"SELECT * FROM emp");
echo "<table border='1'>
<tr>
<th>Id</th>
<th>Name</th>
<th>Salary</th>
</tr>";
while($rowval = mysqli_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $rowval['id'] . "</td>";
  echo "<td>" . $rowval['name'] . "</td>";
  echo "<td>" . $rowval['salary'] . "</td>";
  echo "</tr>";
  }

echo "</table>";

/*........For Fetch data of Emp Table

  .......*/

print "<h2>Result of mysqli_affected_rows function with SELECT command!.</h2>";

 

// Perform queries and print out affected rows

mysqli_query($con,"SELECT * FROM emp");

echo "Affected rows: " . mysqli_affected_rows($con);

print "<h2>Result of mysqli_affected_rows function with DELETE command!.</h2>";

mysqli_query($con,"DELETE FROM emp WHERE id<102");

echo "Affected rows: " . mysqli_affected_rows($con);

 

mysqli_close($con);

?>

Output



PHP mysqli_autocommit() Function

The PHP MySQLi "mysqli_autocommit" function turns on or off the auto-committing of database modification and it returns true on success and false on failure.

Parameter in mysqli_autocommit function

The parameters of the function are:

Parameter	Description
connection	It specifies the MySQL connection to use.
mode	If set to TRUE, then it turns auto-commit on and if set to FALSE then it turns auto-commit off.

Example

An example of the function is:

In this example when you set the mode parameters to false then it does not commit your data into the "emp" table of the MySQL database.

<?php
$con=mysqli_connect("localhost","root","","mysql");
if (mysqli_connect_errno($con))
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

mysqli_autocommit($con,FALSE); 

mysqli_query($con,"INSERT INTO emp VALUES (106, 'Atul', 656456)");
echo "Affected rows: " . mysqli_affected_rows($con);
 /*........For Fetch data of Emp Table
  .......*/
//mysql_select_db("mysql", $con);
mysqli_close($con);
$con=mysqli_connect("localhost","root","","mysql");
print "<h2>MySQL: Data of Emp table</h2>";
$result =mysqli_query($con,"SELECT * FROM emp");
echo "<table border='1'>
<tr>
<th>Id</th>
 
<th>Name</th>
<th>Salary</th>
</tr>";
while($rowval = mysqli_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $rowval['id'] . "</td>";
  echo "<td>" . $rowval['name'] . "</td>";
  echo "<td>" . $rowval['salary'] . "</td>";
  echo "</tr>";
  }
echo "</table>";
/*........For Fetch data of Emp Table
  .......*/
mysqli_close($con);
?>

Output

 

PHP mysqli_change_user() Function

The PHP MySQLi "mysqli_change_user" function changes the user of the specified database connection and it returns true on success and false on failure.

Syntax

 

mysqli_change_user(connection,username,password,dbname)

Parameters in the mysqli_change_user function

The parameters of the function are:

Parameter	Description
connection	It specifies the MySQL connection to use.
username	It specifies the MySQL username
password	It specifies the MySQL password.
dbname	It specifies the database to change to.

Example

An example of the function is:

<?php

$con=mysqli_connect("localhost","userName","Password","DataBaseName");

if (mysqli_connect_errno($con))

  {

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

  }

mysqli_change_user($con,"userName","Password","DataBaseName");

mysqli_close($con);

?>

PHP mysqli_character_set_name() Function

The PHP MySQLi "mysqli_character_set_name" function returns the default character set for the database connection, in other words it returns the default set for the current connection.

Syntax

mysqli_character_set_name(connection)

Parameter of the mysqli_character_set_name function

The parameter of the function is:

Parameter	Description
connection	It specifies the MySQL connection to use.

Example

An example of the function is:

<?php
$con=mysqli_connect("localhost","root","","mysql");
if (mysqli_connect_errno($con))
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }
$mysqlsetname=mysqli_character_set_name($con);
echo "Defaiulr charachet set is : ". $mysqlsetname;
mysqli_close($con);
?>

Output

 

PHP mysqli_close() Function

The PHP MySQLi "mysqli_close" function closes a previously opened database connection and returns true on success or false on failure.

Syntax

mysqli_close(connection)

Parameter of the mysqli_close  function

The parameter of the function is:

Parameter	Description
connection	It specifies the MySQL connection to close.

Example

An example of the function is:

<?php
$con=mysqli_connect("localhost","root","","mysql");
if (mysqli_connect_errno($con))
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

/*.......write PHP code here

.......*/

mysqli_close($con);

?>