MySQL is widely used database server for php platform. It is very easy to configure MySQL database to access it from PHP.
First we have to create new database and its tables in MySQL server. I am using PHPMyAdmin client to manage database.
Create and Configure Database
Open PHPMyAdmin and click Database tab. It will ask to create new database. The screenshot is,
After creating, select your database from left panel ad create tables.
This code shows how to configure database. It requires host name, database name, database username, database password.
<?php
// to be replaced with your own information
$conn = mysqli_connect(localhost,"root","admin","animals");
?>
Now we are ready to perform the following CRUD operations with our new databar.
- Create New Record
- Read Data from Table
- Update Record
- Delete Record
Create New Record
INSERT query is used to add new record. The syntax is,
INSERT INTO table_name VALUES (value1, value2, value3,...)
The following code is used to create new row into the database table.
<?php
//connect mysql
...
mysqli_query($conn,"INSERT INTO animals (animal_name, animal_color) VALUES ('Elephant', 'Grey')");
mysqli_close($conn);
?>
In this code, the values of the column is enclosed with single quotes, since values are string data type. mysql_query() function is used to execute the query.
Read Data from Table
SELECT query is used to read database table rows and the syntax is,
SELECT column FROM table_name
We can also select one or more columns separated by commas (column1,column2…). If we want to select the entire row, then * will be used. That is,
SELECT * FROM table_name
The following code is used to select list of rows from animals table.
<?php
$result = mysqli_query($conn,"SELECT * FROM animals");
while($row=mysqli_fetch_assoc($result)) {
$tblEntries[] = $row;
}
mysqli_close($conn);
?>
Code will return list of animal name and it’s color in the form of associative array.
We can read data with some conditions by using WHERE clause. If we want to pick animals in gery color, then the code is,
<?php
SELECT * FROM animals WHERE animal_color='Grey';
?>
Updating the selected row
We can edit any record using UPDATE query. The syntax is,
UPDATE table_name SET column=value WHERE someother_column=someother_value
The following code is used to update record with WHERE condition.
<?php
mysqli_query($conn,"UPDATE animals SET animal_name='Rat' WHERE animal_name='Grey'");
mysqli_close($conn);
?>
Deleting the selected row
Code show the syntax to the DELETE query.
DELETE FROM table_name WHERE column = value
This code will remove database records which contains animal_color = “grey”
<?php
mysqli_query($con,"DELETE FROM animals WHERE animal_color='Grey'");
mysqli_close($conn);
?>
MySQL is widely used database server for php platform. It is very easy to configure MySQL database to access it from PHP.
