PHP mysqli_query Function

November 12, 2022 | by Arround The Web | No comments

“In this post, we will discuss how to use the mysqli_query function in PHP to perform a given query to a MySQL database.”

Let us explore.

Function Syntax

The following snippet below illustrates the function syntax in procedural format.

mysqli_query(mysqli $mysql, string $query, INT $result_mode = MYSQLI_STORE_RESULT): mysqli_result|bool

The function accepts the following parameters:

mysql – this defines a mysqli object returned from the mysqli_connect or mysqli_init() functions.
query – this defines a query string executed on the target database. PHP recommends using parameterized prepared statements if the query contains any variable input. Alternatively, the input must be properly formatted, and strings escaped to prevent SQL Injections.
result_mode – this parameter defines the result mode defining how the result will be returned from the server. The accepted parameters include the following:
1. MYSQLI_STORE_RESULT – returns the result as a mysqli_result object with a buffered result set.
2. MYSQLI_USE_RESULT – returns the result as an unbuffered mysqli_result object.
3. MYSQLI_ASYNC – this performs the defined query asynchronously, and no result is returned immediately. Instead, the function performs the query and then uses the mysqli_poll() function to return all the results from the query.

The function returns false on failure. If successful, the function performs the actions defined in the query, such as SELECT, SHOW, DESCRIBE, etc.

Example Function Usage

The following shows an example of usage of the mysqli_query function. Start by creating a sample database, table, and data as shown in the queries below:

CREATE DATABASE src;
USE src;
DROP TABLE IF EXISTS stack_mapping;
CREATE TABLE stack_mapping(
id INT AUTO_INCREMENT PRIMARY KEY,
server_name VARCHAR(50) NOT NULL,
address VARCHAR(100) NOT NULL,
installed_version VARCHAR(50),
tool_id INT
);
INSERT INTO stack_mapping(server_name, address, installed_version, tool_id)
VALUES ('SQL Server', 'localhost:1433', '15.0', 1),
('Elasticsearch', 'localhost:9200', '8.4', 2),
('Redis', 'localhost:6379', '6.0', 3),
('PostgreSQL', 'localhost:5432', '14.5', 4),
('MySQL', 'localhost:3306', '8.0', NULL);

Example – Using PHP mysqli_query() Function to Fetch Records

The example below illustrates how to use the function to fetch the records from the table:

<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$conn = mysqli_connect('localhost', "root", "", "src");

$query = "SELECT * FROM stack_mapping";
$result = mysqli_query($conn, $query);
printf("Returned rows: %d \n", mysqli_num_rows($result));
?>

In this case, the function above connects to the database and then uses the mysqli_function to fetch the rows from the stack_mapping table.

Finally, we determine the number of returned rows using the mysqli_num_rows function. The resulting output is as shown:

Returned ROWS 5

NOTE: If you have a large amount of data to fetch, use the MYSQLI_USE_RESULT result mode.

Conclusion

In this post, we discussed how to use the mysqli_fetch function in PHP to perform a given query to a database.

Thanks for reading!!

Source: linuxhint.com