In this tutorial, we are going to access MySQL database from PHP. Accessing MySQL from PHP is very simple and easy to implement. PHP contains built-in functions to connect the MySQL database and to access the data. Those functions are,
- mysqli_connect – To connect the database by using the specified configuration.
- mysqli_query – To execute a query to get resource id.
- mysqli_fetch_row – To read row data with the reference of the resource id. There are many MySQL fetch functions in PHP to read table rows.
I have added an example code for accessing MySQL database from PHP. I used the database table named animals on which the create, read, update and delete (CRUD) functionalities are going to be performed.
Create and Configure Database
Open PHPMyAdmin or any other database client to create the database. Select the newly created database and create the table on which the CRUD is going to be performed. After creating the database and tables connect this from PHP by setting the configuration. The following code is used to connect MySQL from PHP. It requires hostname, database name, database username, database password.
<?php
// to be replaced with your own information
$conn = mysqli_connect(localhost,"root","admin","blog_samples");
?>Create New Record
The code shows the INSERT query to add a new row to the database table animals by mapping values to its columns animal_name and animal_color. Since the column values are string data, those are enclosed with single quotes. The mysqli_query() function is used to execute the query.
<?php
mysqli_query($conn,"INSERT INTO animals (animal_name, animal_color) VALUES ('Elephant', 'Grey')");
mysqli_close($conn);
?>Read Data from Table
The SELECT query is used to read database table rows. The following code shows how to read all rows from the animals table. I used mysqli_fetch_assoc() function to get the associative array column fields and values for each row. The code will return a list of animal name and it’s color in an associative array.
<?php
$result = mysqli_query($conn,"SELECT * FROM animals");
while($row=mysqli_fetch_assoc($result)) {
$tblEntries[] = $row;
}
mysqli_close($conn);
?>We can read filtered data by using the WHERE clause. The following SELECT query is used to read the list of animals that are in ‘grey’ color.
<?php
SELECT * FROM animals WHERE animal_color='Grey';
?>Update Table Row
The UPDATE query is used to change the value of the column by setting a new value. It uses the WHERE clause to apply the condition for updating the row data. The following code is used to change the animal_name as ‘Rat‘ for the rows which has the value Grey in the animal_color.
<?php
mysqli_query($conn,"UPDATE animals SET animal_name='Rat' WHERE animal_color='Grey'");
mysqli_close($conn);
?>Deleting the selected row
The DELETE query shown below is to delete all the rows containing animal_color as ‘grey’
<?php
mysqli_query($con,"DELETE FROM animals WHERE animal_color='Grey'");
mysqli_close($conn);
?>