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
![mysql-affected-rows-function-in-php.gif]()
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
![mysql-autocommit-function-in-php.gif]()
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
![default-character-set-name-function-in-php.gif]()
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
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);
?>