mysqli_connect() - Open MySQL Connection

PHP Modules Tutorials - Herong's Tutorial Examples

∟mysqli_connect() - Open MySQL Connection

This section provides a tutorial example on how to open a MySQL connection by calling the mysqli_connect() function.

MySQLi module offers 2 interfaces: object-oriented interface and procedural interface. The procedural interface provides almost identical functions as the original MySQL module, except that MySQLi function names are prefixed with "mysqli." instead of "mysqli_".

The function interface seems to be easier to use, so let's look at the mysqli_connect() function first:

 
$con = mysqli_connect($host, $user, $pass, $base, $port);

where: 
  $host - Host name or IP of the MySQL server 
  $user - User name of the account on the MySQL server   
  $pass - Password  of the account on the MySQL server
  $base - Database name on the MySQL server
  $port - Port number on the MySQL server, if TCP/IP is used
  $con - Object representing the connection to the MySQL server

Note that all parameters are optional. Default values are provided as mysqli.default_* directives in the .ini file.

Also note that if "localhost" is specified as the host, connection will be created through Unix pipe instead of TCP/IP protocol.

Ther are 3 other functions related to mysqli_connect():

mysqli_close($con) - Closes the given connection 
mysqli_connect_error() - Returns the connection error message 
mysqli_connect_errno() - Returns the connection error number

In order to test the mysqli_connect() function, I wrote the folloing PHP script:

<?php
#  mysqli_connect.php
#- Copyright 2009-2026 (c) HerongYang.com. All Rights Reserved.

  $host = null; # default to mysqli.default_host in .ini 
  $user = null; # default to mysqli.default_user in .ini 
  $pass = null; # default to mysqli.default_pw in .ini 
  $base = null; # default to no database selected
  $port = null; # default to mysqli.default_port in .ini 

  $cnt = count($argv);
  if ($cnt>5) $port = $argv[5];
  if ($cnt>4) $base = $argv[4];
  if ($cnt>3) $pass = $argv[3];
  if ($cnt>2) $user = $argv[2];
  if ($cnt>1) $host = $argv[1];

  print("Calling mysqli_connect($host, $user, $pass, $base, $port)\n");
  $con = mysqli_connect($host, $user, $pass, $base, $port);
  if ($con) {
    print("MySQL server info = ".mysqli_get_server_info($con)."\n");

    # connection parameters received on the server
    $sql = "SELECT @@bind_address, USER(), DATABASE(), @@port";
    $res = mysqli_query($con, $sql);
    $row = mysqli_fetch_row($res);
    print("Server side parameters: "
      .$row[0].", ".$row[1].", " 
      .$row[2].", ".$row[3]."\n");
    mysqli_free_result($res);

    mysqli_close($con);
  } else {
    $errno = mysqli_connect_errno();
    $error = mysqli_connect_error();
    print("Connection failed: $errno - $error\n");
  }
?>

I also created a remote MySQL server testing environment with 2 computers:

On my macOS computer (10.0.0.222): 
  Running MySQL server listening to all TCP/IP interface on 3306 port 

On my Ubuntu computer (10.0.0.111): 
  Running PHP with MySQLi module with:
    mysqli.default_host => no value => no value
    mysqli.default_port => 3306 => 3306
    mysqli.default_pw => no value => no value
    mysqli.default_socket => no value => no value
    mysqli.default_user => no value => no value

Here are some tests using the mysqli_connect.php script on Ubuntu computer.

1. Calling mysqli_connect() with default parameters:

herong$ php mysqli-connection.php 

Calling mysqli_connect(, , , , )
Connection failed: 2002 - No such file or directory

The error message tells me that MySQLi was tring the Unix pipe connection no the local host, and failed to open the device file representing the pipe.

2. Calling mysqli_connect() with "localhost":

herong$ php mysqli-connection.php localhost

Calling mysqli_connect(localhost, , , , )
Connection failed: 2002 - No such file or directory

The error message confirms that "localhost" defaults to the Unix pipe connection.

3. Calling mysqli_connect() with "127.0.0.1":

herong$ php mysqli-connection.php 127.0.0.1

Calling mysqli_connect(127.0.0.1, , , , )
Connection failed: 2002 - Connection refused

The error message tells me that TCP/IP connection is used when a non-"localhost" host parameter is specified. If failed, because the MySQL server is not running on the local host represented by "127.0.0.1".

4. Calling mysqli_connect() with "10.0.0.222":

herong$ php mysqli-connection.php 10.0.0.222 

Calling mysqli_connect(10.0.0.222, , , , )
Connection failed: 2054 - The server requested 
  authentication method unknown to the client

The error message tells me that the TCP/IP connection was created correctly. But the MySQL server failed to authenticate the client, because username was not provided.

5. Calling mysqli_connect() with "10.0.0.222" and user name:

herong$ php mysqli-connection.php 10.0.0.222 herong

Calling mysqli_connect(10.0.0.222, herong, , , )
Connection failed: 1045 - Access denied for user 'test'@'10.0.0.111' 
  (using password: NO)

The error message tells me that no password is provided for user name "herong".

6. Calling mysqli_connect() with IP, user name and password:

herong$ php mysqli-connection.php 10.0.0.222 herong TopSecret

Calling mysqli_connect(10.0.0.222, herong, TopSecret, , )
MySQL server info = 8.0.17
Server side parameters: *, herong@10.0.0.222, , 3306

Finally, I have a successful MySQL connection. The "*" value of the bind_address variable on the server side indicates that the server is listening on all TCP/IP interfaces.

7. Calling mysqli_connect() with IP, user name, password and database name:

herong$ php mysqli-connection.php 10.0.0.222 herong TopSecret test

Calling mysqli_connect(10.0.0.222, herong, TopSecret, test, )
MySQL server info = 8.0.17
Server side parameters: *, herong@10.0.0.222, test, 3306