PHP MySQLi Functions: mysqli_query & mysqli

⚡ Smart Summary

MySQL, PHP, and other database access methods let a PHP script connect to a database, run queries, and read results. This walkthrough covers the core mysqli functions, full CRUD examples, prepared statements for security, and the alternative PDO and ODBC access methods.

🔌 Connecting: mysqli_connect opens a link to the MySQL server, and mysqli_select_db chooses the database to work with.
🗃️ Running Queries: mysqli_query executes INSERT, SELECT, UPDATE, and DELETE statements against the selected database.
📄 Reading Results: mysqli_num_rows counts returned rows, and mysqli_fetch_array reads each row into an array for output.
🔄 CRUD Operations: Combined, these functions create, read, update, and delete records, the four core database operations.
🛡️ Prepared Statements: Binding values with prepared statements stops SQL injection, unlike building queries by concatenating user input.
🧩 PDO and ODBC: PDO offers one interface across many database engines, while ODBC connects to sources such as Microsoft Access.
🤖 AI Assist: AI tools can convert legacy mysqli code to PDO with prepared statements and debug connection errors.

PHP has a rich collection of built-in functions for manipulating MySQL databases.

PHP mysqli_connect function

The PHP mysqli_connect function is used to connect to a MySQL database server.

It has the following syntax.

<?php
$db_handle = mysqli_connect($db_server_name, $db_user_name, $db_password);
?>

HERE,

“$db_handle” is the database connection resource variable.
“mysqli_connect(…)” is the function for the PHP database connection
“$db_server_name” is the name or IP address of the server hosting the MySQL server.
“$db_user_name” is a valid user name in the MySQL server.
“$db_password” is a valid password associated with a user name in the MySQL server.

PHP mysqli_select_db function

The mysqli_select_db function is used to select a database.

It has the following syntax.

<?php
mysqli_select_db($db_handle, $database_name);
?>

HERE,

“mysqli_select_db(…)” is the database selection function that returns either true or false
“$db_handle” is the server connection link
“$database_name” is the name of the database

PHP mysqli_query function

The mysqli_query function is used to execute SQL queries.

The function can be used to execute the following query types:

Insert
Select
Update
Delete

It has the following syntax.

<?php
mysqli_query($db_handle, $query);
?>

HERE,

“mysqli_query(…)” is the function that executes the SQL queries.
“$db_handle” is the server connection link
“$query” is the SQL query to be executed

PHP mysqli_num_rows function

The mysqli_num_rows function is used to get the number of rows returned from a select query.

It has the following syntax.

<?php
mysqli_num_rows($result);
?>

HERE,

“mysqli_num_rows(…)” is the row count function
“$result” is the mysqli_query result set

PHP mysqli_fetch_array function

The mysqli_fetch_array function is used to fetch row arrays from a query result set.

It has the following syntax.

<?php
mysqli_fetch_array($result);
?>

HERE,

“mysqli_fetch_array(…)” is the function for fetching row arrays
“$result” is the result returned by the mysqli_query function.

PHP mysqli_close function

The mysqli_close function is used to close an open database connection.

It has the following syntax.

<?php
mysqli_close($db_handle);
?>

HERE,

“mysqli_close(…)” is the PHP function that closes the connection
“$db_handle” is the server connection resource

PHP MySQL CRUD Examples

Let us look at practical examples that take advantage of these functions. This tutorial assumes knowledge of MySQL and SQL; if these terms are unfamiliar to you, refer to our MySQL and SQL tutorials.

We will create a simple database called my_personal_contacts with one table only. Connect to MySQL using your favorite access tool such as MySQL Workbench or phpMyAdmin, create a database named my_personal_contacts, then execute the script shown below to create the table and insert some dummy data.

CREATE TABLE IF NOT EXISTS `my_contacts` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`full_names` varchar(255) NOT NULL,
`gender` varchar(6) NOT NULL,
`contact_no` varchar(75) NOT NULL,
`email` varchar(255) NOT NULL,
`city` varchar(255) NOT NULL,
`country` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

INSERT INTO `my_contacts` (`id`, `full_names`, `gender`, `contact_no`, `email`, `city`, `country`) VALUES
(1, 'Zeus', 'Male', '111', 'zeus@olympus.mt.co', 'Agos', 'Greece'),
(2, 'Anthena', 'Female', '123', 'anthena@olympus.mt.co', 'Athens', 'Greece'),
(3, 'Jupiter', 'Male', '783', 'jupiter@planet.pt.co', 'Rome', 'Italy'),
(4, 'Venus', 'Female', '987', 'venus@planet.pt.co', 'Mars', 'Italy');

Reading records from the database

We will now create a program that prints the records from the database.

<?php
$dbh = mysqli_connect('localhost', 'root', 'melody'); // connect to MySQL server
if (!$dbh)
    die("Unable to connect to MySQL: " . mysqli_connect_error());

if (!mysqli_select_db($dbh, 'my_personal_contacts'))
    die("Unable to select database: " . mysqli_error($dbh));

$sql_stmt = "SELECT * FROM my_contacts"; // SQL select query
$result = mysqli_query($dbh, $sql_stmt); // execute SQL statement

if (!$result)
    die("Database access failed: " . mysqli_error($dbh));

$rows = mysqli_num_rows($result); // get number of rows returned

if ($rows) {
    while ($row = mysqli_fetch_array($result)) {
        echo 'ID: ' . $row['id'] . '<br>';
        echo 'Full Names: ' . $row['full_names'] . '<br>';
        echo 'Gender: ' . $row['gender'] . '<br>';
        echo 'Contact No: ' . $row['contact_no'] . '<br>';
        echo 'Email: ' . $row['email'] . '<br>';
        echo 'City: ' . $row['city'] . '<br>';
        echo 'Country: ' . $row['country'] . '<br><br>';
    }
}
mysqli_close($dbh); // close the database connection
?>

Executing the above code returns the results shown in the diagram below.

Inserting new records

Let us now look at an example that adds a new record into our table. The code below shows the implementation.

<?php
$dbh = mysqli_connect('localhost', 'root', 'melody'); // connect to MySQL server
if (!$dbh)
    die("Unable to connect to MySQL: " . mysqli_connect_error());

if (!mysqli_select_db($dbh, 'my_personal_contacts'))
    die("Unable to select database: " . mysqli_error($dbh));

$sql_stmt = "INSERT INTO `my_contacts` (`full_names`,`gender`,`contact_no`,`email`,`city`,`country`)";
$sql_stmt .= " VALUES(7,8,9,10,11,12)";

$result = mysqli_query($dbh, $sql_stmt); // execute SQL statement
if (!$result)
    die("Adding record failed: " . mysqli_error($dbh));

echo "Poseidon has been successfully added to your contacts list";
mysqli_close($dbh); // close the database connection
?>

Executing the above code outputs “Poseidon has been successfully added to your contacts list”. Go back to the select query example and retrieve your contacts again to see if Poseidon has been added to your list.

Updating records

Let us now look at an example that updates a record in the database. Let us suppose that Poseidon has changed his contact number and email address.

<?php
$dbh = mysqli_connect('localhost', 'root', 'melody'); // connect to MySQL server
if (!$dbh)
    die("Unable to connect to MySQL: " . mysqli_connect_error());

if (!mysqli_select_db($dbh, 'my_personal_contacts'))
    die("Unable to select database: " . mysqli_error($dbh));

$sql_stmt = "UPDATE `my_contacts` SET `contact_no` = 8, `email` = 9";
$sql_stmt .= " WHERE `id` = 5"; // SQL update query

$result = mysqli_query($dbh, $sql_stmt); // execute SQL statement
if (!$result)
    die("Updating record failed: " . mysqli_error($dbh));

echo "ID number 5 has been successfully updated";
mysqli_close($dbh); // close the database connection
?>

Deleting records

Let us now look at an example that removes records from the database. Let us suppose that Venus has a restraining order against us, and we must remove her contact info from our database.

<?php
$dbh = mysqli_connect('localhost', 'root', 'melody'); // connect to MySQL server
if (!$dbh)
    die("Unable to connect to MySQL: " . mysqli_connect_error());

if (!mysqli_select_db($dbh, 'my_personal_contacts'))
    die("Unable to select database: " . mysqli_error($dbh));

$id = 4; // Venus's ID in the database
$sql_stmt = "DELETE FROM `my_contacts` WHERE `id` = $id"; // SQL delete query

$result = mysqli_query($dbh, $sql_stmt); // execute SQL statement
if (!$result)
    die("Deleting record failed: " . mysqli_error($dbh));

echo "ID number $id has been successfully deleted";
mysqli_close($dbh); // close the database connection
?>

Preventing SQL Injection with Prepared Statements

The CRUD examples above build queries by concatenating strings. When any part of a query comes from user input, this opens the door to SQL injection. Prepared statements solve this by sending the query and the data separately, so input can never change the query structure.

<?php
$dbh = mysqli_connect('localhost', 'root', 'melody');
$stmt = mysqli_prepare($dbh, "INSERT INTO my_contacts (full_names, email) VALUES (?, ?)");
mysqli_stmt_bind_param($stmt, "ss", $name, $email);
$name = 'Poseidon';
$email = 'poseidon@sea.oc';
mysqli_stmt_execute($stmt);
mysqli_stmt_close($stmt);
mysqli_close($dbh);
?>

Here the question marks are placeholders, and bind_param safely binds the name and email values. The database treats them strictly as data, which is why prepared statements, available in both mysqli and PDO, are the recommended way to run any query that uses user input.

PHP Data Access Object (PDO)

PDO is a class that allows us to manipulate different database engines such as MySQL, PostgreSQL, and MS SQL Server using the same interface.

The code below shows the database access method using the PDO object.

Note: the code below assumes knowledge of the SQL language, arrays, exception handling, and the foreach loop.

<?php
try {
$pdo = new PDO("mysql:host=localhost;dbname=my_personal_contacts", 'root', 'melody');

$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->exec('SET NAMES "utf8"');

$sql_stmt = "SELECT * FROM `my_contacts`";
$result = $pdo->query($sql_stmt);
$result->setFetchMode(PDO::FETCH_ASSOC);
$data = array();
foreach ($result as $row) {
$data[] = $row;
}
print_r($data);
}
catch (PDOException $e) {
echo $e->getMessage();
}
?>

HERE,

“try{…}catch{…}” is the exception handling block
“$pdo = new PDO(“mysql…” creates an instance of the PDO object and passes the database driver, server and database names, user id, and password.
“$pdo->setAttribute…” sets the PDO error mode to throw exceptions
“$pdo->exec(‘SET NAMES…” sets the encoding format

PHP ODBC Data Access

ODBC is the acronym for Open Database Connectivity. It has the following basic syntax.

<?php
$conn = odbc_connect($dsn, $user_name, $password);
?>

HERE,

“odbc_connect” is the PHP built-in function
“$dsn” is the ODBC data source name.
“$user_name” is optional and is used for the ODBC user name
“$password” is optional and is used for the ODBC password

The example below assumes you are using Windows and have created an ODBC link to the northwind Microsoft Access database named northwind.

<?php
$dbh = odbc_connect('northwind', '', '');
if (!$dbh) {
exit("Connection Failed: " . $dbh);
}
$sql_stmt = "SELECT * FROM customers";
$result = odbc_exec($dbh, $sql_stmt);
if (!$result) {
exit("Error accessing records");
}
while (odbc_fetch_row($result)) {
$company_name = odbc_result($result, "CompanyName");
$contact_name = odbc_result($result, "ContactName");
echo "<b>Company Name (Contact Person):</b> $company_name ($contact_name) <br>";
}
odbc_close($dbh);
?>

FAQs

mysqli works only with MySQL and offers both procedural and object styles. PDO works with many databases through one interface and supports named placeholders. Both support prepared statements; PDO is more portable across engines.

No. The original mysql_* extension was removed in PHP 7. Use mysqli or PDO instead. Both are actively maintained, support prepared statements, and protect against the injection risks of the old extension.

Catch the error and show the user a generic message while logging the detail privately. Do not echo mysqli_error output to visitors, since it can reveal table names and structure useful to an attacker.

Yes. Paste the mysqli script, and AI can rewrite it using PDO, replace concatenated queries with bound parameters, and add try-catch error handling. Review the placeholders and test each query afterward.

Yes. Share the error and your connection code, and AI can spot wrong credentials, a bad host or port, a missing database, or a disabled driver, then suggest the corrected settings to try.

PHP MySQLi Functions: mysqli_query & mysqli_connect

PHP mysqli_connect function

PHP mysqli_select_db function

PHP mysqli_query function

PHP mysqli_num_rows function

PHP mysqli_fetch_array function

PHP mysqli_close function

PHP MySQL CRUD Examples

Reading records from the database

Inserting new records

Updating records

Deleting records

Preventing SQL Injection with Prepared Statements

PHP Data Access Object (PDO)

PHP ODBC Data Access

FAQs

Summarize this post with:

Sign up for the newsletter

PHP mysqli_connect function

PHP mysqli_select_db function

PHP mysqli_query function

PHP mysqli_num_rows function

PHP mysqli_fetch_array function

PHP mysqli_close function

RELATED ARTICLES

PHP MySQL CRUD Examples

Reading records from the database

Inserting new records

Updating records

Deleting records

Preventing SQL Injection with Prepared Statements

PHP Data Access Object (PDO)

PHP ODBC Data Access

FAQs

Summarize this post with:

Sign up for the newsletter