PHP mysqli_query(): Execute SQL Statement

The mysqli_query() function is used to perform a query against a database. This function is important when you want to perform SQL queries in PHP.

Here's a basic tutorial on how to use the mysqli_query() function in PHP:

Syntax:

The syntax of mysqli_query() is:

mysqli_query(mysqli $link, string $query, int $resultmode = MYSQLI_STORE_RESULT): mysqli_result|bool

$link: A mysqli link identifier returned by mysqli_connect().
$query: The query string.
$resultmode: Either MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT depending on the desired behavior. MYSQLI_STORE_RESULT (default) stores the result set in memory and allows you to use other PHP mysqli functions with the result set. MYSQLI_USE_RESULT will return an object that can be used to retrieve rows of data.

Return Value:

For SELECT, SHOW, DESCRIBE, EXPLAIN and other statements returning a result set, mysqli_query() returns a mysqli_result object. For other successful queries, it returns TRUE. Returns FALSE on failure.

Example:

Suppose we have a MySQL database with a table named Users. Let's select all records from this table using mysqli_query():

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

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

$sql = "SELECT id, firstname, lastname FROM Users";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
  // Output data of each row
  while($row = mysqli_fetch_assoc($result)) {
    echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
  }
} else {
  echo "0 results";
}
mysqli_close($conn);
?>

In this example, we first create a connection to the database using mysqli(). We then define a query to select all records from the Users table. We execute this query using mysqli_query(). If the query returns results, we output each row. Otherwise, we output a message saying that there are no results.

Note:

When dealing with user input, you should always validate and sanitize the input to prevent SQL Injection attacks. For example, if you're adding user-supplied data into your query, you should use prepared statements with mysqli_stmt_prepare().

PHP mysqli_query() example code:

Example demonstrating the use of mysqli_query() to execute a SQL statement.

$mysqli = new mysqli("localhost", "username", "password", "database");

$sql = "SELECT * FROM users";
$result = $mysqli->query($sql);

while ($row = $result->fetch_assoc()) {
    // Process each row
}

$mysqli->close();

mysqli_query() vs mysqli_prepare() in PHP:

Compare mysqli_query() for simple queries and mysqli_prepare() for prepared statements.

// Using mysqli_query for a simple query
$result1 = $mysqli->query("SELECT * FROM users");

// Using mysqli_prepare for a prepared statement
$stmt = $mysqli->prepare("SELECT * FROM users");
$stmt->execute();
$result2 = $stmt->get_result();

Error handling with mysqli_query() in PHP:

Implement error handling to manage issues during the execution of a query.

$mysqli = new mysqli("localhost", "username", "password", "database");

$sql = "INVALID SQL"; // This will cause an error
$result = $mysqli->query($sql);

if (!$result) {
    echo "Error: " . $mysqli->error;
} else {
    // Process results
}

$mysqli->close();

PHP mysqli_query() SELECT statement:

Use mysqli_query() to execute a SELECT statement and fetch results.

$mysqli = new mysqli("localhost", "username", "password", "database");

$sql = "SELECT * FROM users";
$result = $mysqli->query($sql);

while ($row = $result->fetch_assoc()) {
    // Process each row
}

$mysqli->close();

Update database with mysqli_query() in PHP:

Use mysqli_query() to execute an UPDATE statement in PHP.

$mysqli = new mysqli("localhost", "username", "password", "database");

$sql = "UPDATE users SET status = 'active' WHERE id = 1";
$result = $mysqli->query($sql);

if ($result) {
    echo "Update successful";
} else {
    echo "Error: " . $mysqli->error;
}

$mysqli->close();

Insert data using mysqli_query() in PHP:

Use mysqli_query() to execute an INSERT statement in PHP.

$mysqli = new mysqli("localhost", "username", "password", "database");

$sql = "INSERT INTO users (username, email) VALUES ('john_doe', 'john@example.com')";
$result = $mysqli->query($sql);

if ($result) {
    echo "Insert successful";
} else {
    echo "Error: " . $mysqli->error;
}

$mysqli->close();

PHP mysqli_query() return value:

Check the return value of mysqli_query() to determine the success of a query.

$mysqli = new mysqli("localhost", "username", "password", "database");

$sql = "SELECT * FROM users";
$result = $mysqli->query($sql);

if ($result) {
    while ($row = $result->fetch_assoc()) {
        // Process each row
    }
} else {
    echo "Error: " . $mysqli->error;
}

$mysqli->close();

Secure mysqli_query() usage in PHP:

Secure the usage of mysqli_query() by validating and sanitizing user input.

$mysqli = new mysqli("localhost", "username", "password", "database");

$userInput = $_POST['input']; // Validate and sanitize user input
$sql = "SELECT * FROM users WHERE username = '$userInput'";
$result = $mysqli->query($sql);

if ($result) {
    while ($row = $result->fetch_assoc()) {
        // Process each row
    }
} else {
    echo "Error: " . $mysqli->error;
}

$mysqli->close();