MySQLi connect Method

PHP

MySQLi connect - Connect to Database

Learn MySQLi connect method. Open a new connection to the MySQL server efficiently using PHP’s MySQLi extension.

Introduction

When working with PHP to interact with MySQL databases, establishing a reliable database connection is the first and most critical step. The MySQLi extension offers a procedural and object-oriented way to connect with MySQL databases. Among its features, the connect() method is essential for opening new connections.

In this tutorial, you will learn how to use the MySQLi connect() method to establish connections with MySQL servers, including setting up persistent or non-persistent connections, handling connection errors, and applying best practices.

Prerequisites

  • Basic knowledge of PHP programming language.
  • MySQL server installed and running.
  • Access credentials to a MySQL database (hostname, username, password, database name).
  • PHP installed with MySQLi extension enabled.

Setup Steps

  1. Ensure MySQL server is running and accessible.
  2. Have your MySQL username, password, hostname (usually localhost), and database name ready.
  3. Verify PHP has MySQLi enabled by running phpinfo(); or checking your PHP configuration.
  4. Create a PHP script file (e.g., connect.php).
  5. Write PHP code using the MySQLi connect() method to open the connection.

Understanding MySQLi connect() Method

The mysqli_connect() function in procedural style or the new mysqli() constructor in object-oriented style creates a new connection to the MySQL database server.

Function Signature (Procedural)

mysqli_connect(host, username, password, database, port, socket);

Constructor Syntax (Object-Oriented)

new mysqli(host, username, password, database, port, socket);

Parameters:

  • host: Hostname of the MySQL server (localhost by default)
  • username: MySQL user
  • password: Password for the MySQL user
  • database: Database name to connect to (optional but recommended)
  • port: MySQL server port (default: 3306)
  • socket: Socket or named pipe

Examples

1. Procedural Style Using mysqli_connect()

 <?php
$host = "localhost";
$user = "root";
$pass = "mypassword";
$dbname = "my_database";

// Create connection
$conn = mysqli_connect($host, $user, $pass, $dbname);

// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";
?>

2. Object-Oriented Style Using new mysqli()

 <?php
$host = "localhost";
$user = "root";
$pass = "mypassword";
$dbname = "my_database";

// Create connection
$conn = new mysqli($host, $user, $pass, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
?>

3. Creating a Persistent Connection

Persistent connections can be made by prepending p: to the host name in the connection:

 <?php
$host = "p:localhost"; // "p:" prefix for persistent connection
$user = "root";
$pass = "mypassword";
$dbname = "my_database";

$conn = new mysqli($host, $user, $pass, $dbname);

if ($conn->connect_error) {
    die("Persistent connection failed: " . $conn->connect_error);
}
echo "Persistent connection established successfully.";
?>

Best Practices

  • Always check for connection errors immediately after attempting to connect.
  • Use mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); in development to throw exceptions on errors.
  • Use environment variables or configuration files to store sensitive credentials, not hard-coded values.
  • Close the connection explicitly using mysqli_close() or by letting the script finish.
  • Prefer prepared statements for queries once the connection is established to protect against SQL injection.
  • If using persistent connections, be cautious as they can lead to resource exhaustion if not managed properly.

Common Mistakes

  • Not checking connection success and proceeding blindly — can cause fatal errors.
  • Misspelling parameter names or passing incorrect parameter order.
  • Using deprecated MySQL extension instead of MySQLi (or PDO) extensions.
  • Hardcoding passwords and credentials without using environment variables.
  • Not closing or reusing connections appropriately which can lead to resource leaks.
  • Forgetting to prepend p: for persistent connections when needed.
  • Assuming connection errors will not happen — always handle exceptions or errors gracefully.

Interview Questions and Answers

Junior Level

  • Q: What is the purpose of the mysqli_connect() method?
    A: It opens a new connection to the MySQL database server using MySQLi procedural style.
  • Q: Which parameters are required for mysqli_connect()?
    A: Hostname, username, password, and optionally the database name.
  • Q: How do you check if the connection was successful?
    A: By testing if mysqli_connect() returned false or checking mysqli_connect_error().
  • Q: Can you use MySQLi to establish a connection in object-oriented style?
    A: Yes, using new mysqli() constructor.
  • Q: What is the default port used by MySQLi connect?
    A: Port 3306.

Mid Level

  • Q: How can you create a persistent MySQL connection using MySQLi?
    A: By prefixing the host with p:, e.g., p:localhost.
  • Q: What are the advantages of using the object-oriented MySQLi connection over procedural?
    A: Better code organization, OOP features like exceptions and methods, and easier to manage multiple connections.
  • Q: How can you handle connection errors gracefully in MySQLi?
    A: Check connect_error property (OO) or mysqli_connect_error() (procedural) and handle accordingly.
  • Q: What is the difference between a persistent and a non-persistent MySQLi connection?
    A: Persistent connections stay open across multiple requests; non-persistent connections close after script execution.
  • Q: Can you specify a socket or port in MySQLi connect? How?
    A: Yes, by passing the 5th parameter as port and 6th as socket to mysqli_connect() or constructor.

Senior Level

  • Q: How do persistent connections affect resource usage and what precautions should be taken?
    A: Persistent connections can lead to resource exhaustion if connections aren’t managed properly; ensure connection limits and monitor usage carefully.
  • Q: Explain how mysqli_report() can aid in managing connection errors during development.
    A: It enables error reporting and throwing exceptions, helping detect and handle connection or query errors more effectively.
  • Q: How does connection error behavior differ between procedural and object-oriented MySQLi methods?
    A: Procedural returns false and requires manual error checking; OO provides connect_error and can throw exceptions if MySQLi error reporting enabled.
  • Q: What security measures should be considered when establishing connections with MySQLi?
    A: Avoid hardcoding credentials, use environment variables or secure vaults, enforce SSL connections if possible, and restrict user privileges.
  • Q: How would you handle failover if the primary MySQL server is unreachable when using MySQLi connect?
    A: Implement retry logic in PHP, attempt connecting to secondary servers, or use load balancers/proxies for high availability.

FAQ

Q1: What does mysqli_connect() return on success and failure?

It returns a MySQLi connection resource/object on success, and false on failure.

Q2: Can I connect to multiple databases using separate MySQLi connections?

Yes, you can instantiate multiple MySQLi connections by calling mysqli_connect() or new mysqli() multiple times with different database credentials.

Q3: How do I close a MySQLi connection?

Use mysqli_close($conn) in procedural style or $conn->close() in object-oriented style.

Q4: Is it recommended to always connect with a database name in the connection step?

Yes, specifying a database during connection helps avoid extra steps and prevents confusion later while running queries.

Q5: What should I do if I get “Access denied” error while connecting?

Verify your hostname, username, password, and database name are correct, and ensure your MySQL user has sufficient privileges.

Conclusion

Establishing a connection to a MySQL database using PHP's MySQLi connect() method is fundamental for building data-driven applications. Whether you choose procedural or object-oriented approach, understanding how to properly use mysqli_connect() or the mysqli constructor ensures stable, secure, and efficient database interactions. Remember to validate connections, handle errors gracefully, and follow best practices like securing credentials and using persistent connections only when appropriate. With this knowledge, you can confidently open, manage, and close MySQL connections within your PHP applications.