PHP is a popular server-side scripting language used for building dynamic web applications. One common use case for PHP is querying a database to retrieve or manipulate data.
Table of Contents
A PHP Installation
Before querying a database with PHP, you need to establish a connection to the database server. The mysqli extension provides a convenient way to establish a connection using the mysqli_connect() function.
$host = "localhost";
$user = "username";
$password = "password";
$database = "database_name";
$connection = mysqli_connect($host, $user, $password, $database);
if (!$connection) {
die("Connection failed: " . mysqli_connect_error());
}
Executing a Query
After establishing a database connection, you can execute SQL queries using the mysqli_query() function. This function takes two parameters: the database connection and the SQL query to execute.
$query = "SELECT * FROM users";
$result = mysqli_query($connection, $query);
if (mysqli_num_rows($result) > 0) {
while($row = mysqli_fetch_assoc($result)) {
// handle each row of data here
}
} else {
echo "No results found";
}
Retrieving Query Results
After executing a query, you can retrieve the result set using the mysqli_fetch_assoc() function. This function returns an associative array representing a row of data from the result set. You can then loop through the result set to retrieve all the rows of data.
$query = "UPDATE users SET email='[email protected]' WHERE id=1";
$result = mysqli_query($connection, $query);
Updating and Deleting Data
In addition to retrieving data, you can also update or delete data from a database using SQL queries. To update data, you can use the UPDATE statement with the mysqli_query() function. To delete data, you can use the DELETE statement with the mysqli_query() function.
$query = "DELETE FROM users WHERE id=1";
$result = mysqli_query($connection, $query); Closing the Connection
After querying a database with PHP, it’s important to close the database connection using the mysqli_close() function. This frees up resources and prevents potential security vulnerabilities.
mysqli_close($connection); Conclusion
Querying a database with PHP is an essential skill for building dynamic web applications. By using the mysqli extension to establish a database connection, execute SQL queries, and retrieve or manipulate data, you can create powerful applications that interact with a database.
