Access MySQL from PHP

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.

mysql

Create and Configure Database

Open PHPMyAdmin and click Database tab. It will ask to create new database. The screenshot is,

create_db-300x121

After creating, select your database from left panel ad create tables.

create_table-300x139

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.

  1. Create New Record
  2. Read Data from Table
  3. Update Record
  4. 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);
?>

This MySQL code tutorial was published on April 13, 2013.

↑ Back to Top