PhpHow-ToBeginner · 3 min read

How to Use MySQLi in PHP: Simple Guide with Examples

Use the mysqli extension in PHP to connect to a MySQL database by creating a mysqli object with host, username, password, and database name. Then use methods like query() to run SQL commands and close() to end the connection.

📐

Syntax

The basic syntax to use mysqli in PHP involves creating a new mysqli object with connection details, running queries, and closing the connection.

new mysqli(host, user, password, database): Connects to the MySQL server.
query(sql): Executes an SQL query.
close(): Closes the database connection.

php

<?php
$mysqli = new mysqli('localhost', 'username', 'password', 'database');

if ($mysqli->connect_error) {
    die('Connect Error (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error);
}

$result = $mysqli->query('SELECT * FROM tablename');

$mysqli->close();
?>

💻

Example

This example connects to a MySQL database, runs a simple SELECT query, and prints the results.

php

<?php
$mysqli = new mysqli('localhost', 'root', '', 'testdb');

if ($mysqli->connect_error) {
    die('Connection failed: ' . $mysqli->connect_error);
}

$sql = 'SELECT id, name FROM users';
$result = $mysqli->query($sql);

if ($result && $result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo 'ID: ' . $row['id'] . ' - Name: ' . $row['name'] . "\n";
    }
} else {
    echo 'No results found.';
}

$mysqli->close();
?>

Output

ID: 1 - Name: Alice ID: 2 - Name: Bob

⚠️

Common Pitfalls

Common mistakes when using mysqli include not checking for connection errors, forgetting to close the connection, and not handling query errors.

Always check if the connection succeeded and if the query returned results before using them.

php

<?php
// Wrong way: no error checks
$mysqli = new mysqli('localhost', 'user', 'pass', 'db');
$result = $mysqli->query('SELECT * FROM table');

// Right way: with error checks
$mysqli = new mysqli('localhost', 'user', 'pass', 'db');
if ($mysqli->connect_error) {
    die('Connection failed: ' . $mysqli->connect_error);
}
$result = $mysqli->query('SELECT * FROM table');
if (!$result) {
    die('Query error: ' . $mysqli->error);
}
$mysqli->close();
?>

📊

Quick Reference

Function/Method	Description
new mysqli(host, user, password, database)	Create a new connection to MySQL
$mysqli->connect_error	Check if connection failed
$mysqli->query(sql)	Run an SQL query
$result->fetch_assoc()	Fetch a row as an associative array
$mysqli->close()	Close the database connection

✅

Key Takeaways

Always create a mysqli object with correct connection details to connect to MySQL.

Check for connection and query errors to avoid runtime issues.

Use query() to run SQL commands and fetch_assoc() to get results row by row.

Close the connection with close() when done to free resources.