PHP mysqli set_opt() Method

The PHP mysqli::set_opt() / mysqli_set_opt() function is used to set extra connect options and affect behavior for a connection. This function may be called multiple times to set several options. This function should be called after mysqli_init() and before mysqli_real_connect().

This method is an alias of mysqli_options() function.

Syntax

//Object-oriented style
public mysqli::set_opt(option, value)

//Procedural style
mysqli_set_opt(mysql, option, value)

Parameters

`mysql`	`Required.` For procedural style only: Specify a mysqli object returned by mysqli_connect() or mysqli_init().
`option`	`Required.` Specify the option to set. It can be one of the following values: MYSQLI_OPT_CONNECT_TIMEOUT - Connection timeout in seconds MYSQLI_OPT_READ_TIMEOUT - Command execution result timeout in seconds. Available as of PHP 7.2.0. MYSQLI_OPT_LOCAL_INFILE - Enable/disable use of LOAD LOCAL INFILE MYSQLI_INIT_COMMAND - Command to execute after when connecting to MySQL server MYSQLI_SET_CHARSET_NAME - The charset to be set as default. MYSQLI_READ_DEFAULT_FILE - Read options from named option file instead of my.cnf. Not supported by mysqlnd. MYSQLI_READ_DEFAULT_GROUP - Read options from the named group from my.cnf or the file specified with MYSQL_READ_DEFAULT_FILE. Not supported by mysqlnd. MYSQLI_SERVER_PUBLIC_KEY - RSA public key file used with the SHA-256 based authentication. MYSQLI_OPT_NET_CMD_BUFFER_SIZE - Only valid for mysqlnd. The size of the internal command/network buffer. MYSQLI_OPT_NET_READ_BUFFER_SIZE - Only valid for mysqlnd. Maximum read chunk size in bytes when reading the body of a MySQL command packet. MYSQLI_OPT_INT_AND_FLOAT_NATIVE - Convert integer and float columns back to PHP numbers. Only valid for mysqlnd. MYSQLI_OPT_SSL_VERIFY_SERVER_CERT - Whether to verify server certificate or not.
`value`	`Required.` Specify the value for the option.

Return Value

Returns true on success or false on failure.

Example: Object-oriented style

The example below shows the usage of mysqli::set_opt() method.

<?php
$mysqli = mysqli_init();
if (!$mysqli) {
  die('mysqli_init failed');
}

if (!$mysqli->set_opt(MYSQLI_INIT_COMMAND, 'SET AUTOCOMMIT = 0')) {
  die('Setting MYSQLI_INIT_COMMAND failed');
}

//specifying connection timeout
if (!$mysqli->set_opt(MYSQLI_OPT_CONNECT_TIMEOUT, 10)) {
  die('Setting MYSQLI_OPT_CONNECT_TIMEOUT failed');
}

//establishing connection to the database
if (!$mysqli->real_connect("localhost", "user", "password", "database")) {
  die('Connect Error: '. mysqli_connect_error());
}

echo 'Success... ' . $mysqli->host_info . "\n";

//closing the connection
$mysqli->close();
?>

The output of the above code will be:

Success... MySQL host info: localhost via TCP/IP

Example: Object-oriented style when extending mysqli class

Consider the example below where this method is used while extending the mysqli class.

<?php
class acs_mysqli extends mysqli {
  public function __construct($host, $user, $pass, $db) {
    parent::init();

    if (!parent::set_opt(MYSQLI_INIT_COMMAND, 'SET AUTOCOMMIT = 0')) {
      die('Setting MYSQLI_INIT_COMMAND failed');
    }

    if (!parent::set_opt(MYSQLI_OPT_CONNECT_TIMEOUT, 10)) {
      die('Setting MYSQLI_OPT_CONNECT_TIMEOUT failed');
    }

    if (!parent::real_connect($host, $user, $pass, $db)) {
      die('Connect Error: ' . mysqli_connect_error());
    }
  }
}

//establishing connection to the database
$db = new acs_mysqli("localhost", "user", "password", "database");

echo 'Success... ' . $db->host_info . "\n";

//closing the connection
$db->close();
?>

The output of the above code will be:

Success... MySQL host info: localhost via TCP/IP

Example: Procedural style

The example below shows the usage of mysqli_set_opt() function.

<?php
$mysqli = mysqli_init();
if (!$mysqli) {
  die('mysqli_init failed');
}

if (!mysqli_set_opt($mysqli, MYSQLI_INIT_COMMAND, 'SET AUTOCOMMIT = 0')) {
  die('Setting MYSQLI_INIT_COMMAND failed');
}

//specifying connection timeout
if (!mysqli_set_opt($mysqli, MYSQLI_OPT_CONNECT_TIMEOUT, 10)) {
  die('Setting MYSQLI_OPT_CONNECT_TIMEOUT failed');
}

//establishing connection to the database
if (!mysqli_real_connect($mysqli, "localhost", "user", "password", "database")) {
  die('Connect Error: '. mysqli_connect_error());
}

echo 'Success... ' . mysqli_get_host_info($mysqli) . "\n";

//closing the connection
mysqli_close($mysqli);
?>

The output of the above code will be:

Success... MySQL host info: localhost via TCP/IP

❮ PHP MySQLi Reference