Mastering mysqli_query: Interact with MySQL in PHP

PHP and MySQL form a powerful duo for web development, providing a robust platform for database management. Using the function mysqli_query in PHP allows operations on MySQL databases, from simple queries to more complex transactions.

Understanding the mysqli_query function

Before we dive into practical examples, let's understand what it is and how it works mysqli_query. This PHP function is essential for sending queries to our MySQL database. The basic structure is as follows:

mysqli_query($connection, $query);

Where $connection is an object that represents an open connection to a MySQL database, and $query is the string that contains the SQL query to be executed.

Connecting to the Database

To perform any operation on the database, we first need to establish a connection. Suppose we have the following database credentials:

• Host: localhost
• User: root
• Password: password
• Database: my_database

The following code snippet shows how to initiate a connection:

$connection = mysqli_connect("localhost", "root", "password", "my_database"); if (!$connection) { die("The connection has failed: " . mysqli_connect_error()); } echo "Connection successful";

Make sure you select your database correctly and handle potential connection errors.

Performing a Simple Query

With the connection already established, let's see how to perform a simple query to get data from a table called users:

$query = &quot;SELECT * FROM users&quot;; $result = mysqli_query($connection, $query); if (!$result) { die(&quot;Query error: &quot; . mysqli_error($connection)); } while ($row = mysqli_fetch_assoc($result)) { echo &quot;Name: &quot; . $row[&quot;name&quot;] . &quot; - Email: &quot; . $row[&quot;email&quot;] . &quot;<br>&quot;; } mysqli_free_result($result);

In the loop while, mysqli_fetch_assoc obtains each row of the result as an associative array and we store it in $row. This allows us to access each column in the row by name.

Don't forget to release the result with mysqli_free_result to free the memory associated with the query result.

Inserting Data into the Database

Next, let's see how we could insert a new user inside our table users:

$name = &quot;John Doe&quot;; $email = &quot;[email protected]&quot;; $query = &quot;INSERT INTO users (name, email) VALUES (&#039;$name&#039;, &#039;$email&#039;)&quot;; if (mysqli_query($connection, $query)) { echo &quot;New record created successfully&quot;; } else { echo &quot;Error: &quot; . $query . &quot;<br>&quot; . mysqli_error($connection); }

This code snippet performs an insert into the database. Always remember to validate and sanitize input to avoid SQL injection attacks.

Updating Existing Records

If we want to update information for a specific user, we use the instruction UPDATE. For example, to change a user's email:

$newEmail = "[email protected]"; 1TP4UserTid = 3; $query = "UPDATE users SET email = '$newEmail' WHERE id = $idUsuario"; if (mysqli_query($connection, $query)) { echo "Record updated successfully"; } else { echo "Error updating record: " . mysqli_error($connection); }

Please note that you must specify a search criteria in the WHERE to not update more records than desired.

Deleting Records from the Database

If you want to delete a record, you would do it as follows:

1TP4UserTid = 4; $query = "DELETE FROM users WHERE id = $idUsuario"; if (mysqli_query($connection, $query)) { echo "Record deleted successfully"; } else { echo "Error deleting record: " . mysqli_error($connection); }

As with updating, it is crucial to make sure you properly specify the WHERE to avoid deleting more data than intended.

Transactions with mysqli_query

To carry out operations that involve multiple steps and require all or none of them to be done, such as a banking transaction, we use transactions. This is how you could do it with mysqli_query:

mysqli_begin_transaction($connection); try { mysqli_query($connection, "UPDATE accounts SET balance = balance - 100 WHERE id = 1"); mysqli_query($connection, "UPDATE accounts SET balance = balance + 100 WHERE id = 2"); mysqli_commit($connection); echo "Transaction completed successfully"; } catch (Exception $e) { mysqli_rollback($connection); echo "An error occurred. Transaction reversed"; }

In this case, we are simulating a transfer of funds from one account to another. If something goes wrong, the catch catches the exception and all operations are rolled back to maintain data integrity.

Conclusion

The use of mysqli_query It is essential in programming with PHP and MySQL. From simple to more complex tasks, this feature is crucial for direct interaction with the database. By mastering this brand of CRUD (Create, Read, Update, Delete) operations, you become a more competent developer capable of creating dynamic and secure web applications.

Don't miss our articles on development and technology, visit my blog. And if you have any questions or need assistance, do not hesitate to contact us through my contact page. Keep coding and learning more every day!