MySQLi Function in PHP: Part 11

Introduction

In this article I describe the PHP MySQLI functions mysqli_real_escape_string, mysqli_refresh, mysqli_rollback, mysqli_select_db and mysqli_set_charset. To learn some other MySQLI functions, go to:

PHP mysqli_real_escape() Function

The PHP MySQLi "mysqli_real_escape_string" function escapes special characters in a string for use in an SQL statement and this function returns an escaped string.

Syntax

mysqli_real_escape_string(connection,escapeString);

Parameters for the mysqli_real_escape function

The parameters of the function are:

Parameter	Description
string	It specifies the MySQL connection to use.
escapeString	It specifies the string to be escaped and characters encoded are nul( ASCII 0), \N,\r,\ and control z.

Example

An example of the function is:

<?php

$con=mysqli_connect("localhost","root","","mysql");

if (mysqli_connect_errno($con))

{

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

}

$names="G'Gupta";

// This query will fail, cause we did not escape $newpers

mysqli_query($con,"INSERT into emp (name) VALUES ('$names')");

$newpers=mysqli_real_escape_string($con,$newpers);

// This query will work, cause we escaped $newpers

mysqli_query($con,"INSERT into emp (name) VALUES ('$names')");

mysqli_close($con);

PHP mysqli_refresh() Function

The PHP MySQLi "mysqli_refresh" function refreshes tables or caches and this function returns true if the refresh was a success otherwise it returns false.

Syntax

mysqli_refresh(connection,options);

Parameters for the mysqli_refresh function

The parameters of the function are:

Parameter	Description
connection	It specifies the mysql connection to use.
options	It specifies the option to refresh and the options are: MYSQLI_REFRESH_GRANT: It refreshes the grant tables. MYSQLI_REFRESH_LOG: It flushes the logs. MYSQLI_REFRESH_TABLES: It flushes the table cache. MYSQLI_REFRESH_HOSTS: It flushes the host cache. MYSQLI_REFRESH_STATUS: It resets the status variables. MYSQLI_REFRESH_THREADS: It flushes the thread cache. MYSQLI_REFRESH_SLAVE: It resets the master server info, and restarts the slave. MYSQLI_REFRESH_MASTER: It removes the binary log files in the binary log index, and truncates the index file.

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();

}

mysqli_refresh($con,MYSQLI_REFRESH_GRANT);

mysqli_close($con);

PHP mysqli_rollbak() Function

The PHP MySQLi "mysqli_rollback" function rolls back the current transaction and this function returns true on success or false on failure..

Syntax

mysqli_rollback(connection);

Parameter for the mysqli_rollbak 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 "Unable to connect: " . mysqli_connect_error();

}

// Set autocommit to off

mysqli_autocommit($con,FALSE);

// Insert some values

mysqli_query($con,"INSERT INTO emp(id,name,salary)VALUES (110,'Vikram',30)");<br>

//without commit it does not save the records

// Rollback transaction

mysqli_rollback($con);

mysqli_close($con);

PHP mysqli_select_db() Function

The PHP MySQLi "mysqli_select_db" function selects the default database for database queries.

Syntax

mysqli_select_db (connection, dbname);

Parameters for the mysqli_select_db function

The parameters of the function are:

Parameter	Description
connection	It specifies the MySQL connection to use.
dbname	It specifies the default database to be used.

Example

An example of the function is:

<?php

$con=mysqli_connect("localhost","root","","mysql");
if (mysqli_connect_errno($con))

{

echo "Unable to connect: " . mysqli_connect_error();

}

//write PHP code foe "mysql" database

mysqli_select_db($con,"test");

// write PHP code foe "test" database

mysqli_close($con);

PHP mysqli_set_charset() Function

The PHP MySQLi "mysqli_real_escape_string" function sets the default client character set and this function returns true on success or false on failure.

Syntax

mysqli_set_charset(connection,charset);

Example

An example of the function is:

<?php

$con=mysqli_connect("localhost","root","","mysql");

if (mysqli_connect_errno($con))

{

echo "Unable to connect: " . mysqli_connect_error();

}

mysqli_set_charset($con,"utf8");

mysqli_close($con);