This article explains how you can access and manipulate MySQL database using MySQLi Object-oriented interface in PHP.
This article covers the following MySQL statements with code examples. The code examples are pasted in the textarea so you can easily copy then edit as per your requirements.

We will use PHP and Mysqli object oriented extension in this tutorial to perform different types of tasks to retrieve , create and
update records on a MySQL database.

Note :-
The codes used here are just for educational purposes and to teach you how to implement a db connection for read and right access. You can copy and paste these codes on your server for testing and learning.

Setting up a secure connection to MySQL database

To connect to MySQL database with our code examples, you must have the following things ready :

• Your MySQL host name
• Your MySQL username
• Your MySQL database password
• Database name

You will get these informations on your hosting Cpanel. Create a new database and copy the db informations from there.

For testing and learning purposes, create a new directory in your document root and name it as db_project .
Remember that the db_project directory is for learning purposes only and it must be reachable by visiting yoursite.com/db_project/foobar .

Now inside that directory , create a new file named
connection.php with the contents shown below.

connection.php

<?php
//Setting up a secure connection to database
$db_host="localhost";
$db_user="foobar";
$db_pass="123456";
$db_name="my_db";

$conn = new mysqli($db_host,$db_user,$db_pass,$db_name);

// Check the connection
if ($conn -> connect_errno) {
  echo "Could not connect to MySQL: " . $mysqli -> connect_error;
  exit();
}
else {echo "Connected to MySQL database!";}
?>

The connection.php sets up a secure connection with your MySQL database so that you can access the database . You can use this single file to execute multiple Mysqli queries on different pages by including this file.

Add your server informations in this file and test it on your server. If everything is ok you will get a text output “Connected” on your page.

Okay cool, now that you have successfully established the connection with your MySQL server , it’s time to create a table. See the next step below to create a table .

Creating a table on MySQL database

A table on MySQL database can hold multiple columns and rows. In this article , we are going to create a table named MyUsers with 5 unique columns :

1. ID
2. username
3. password
4. email
5. reg_date

We will use this table to store user informations.
Create a new file named create_table.php in your db_project directory with the following contents :

create_table.php

<?php

//include the connection file
include "connection.php";


// sql code to create table
//Creating table MyGursts with 5 fields
//----------------------
// ID
//Username
//Password
//Email
//reg_date

$sql = "CREATE TABLE MyGuests (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
username VARCHAR(30) NOT NULL,
password VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)";
//if the query is successful
if ($conn->query($sql) === TRUE) {
  echo "Table MyGuests created successfully";
}
//If something goes wrong

 else {
  echo "Error creating table: " . $conn->error;
}

$conn->close();
?>

Edit this file if you need to change anything or add more columns and run it on your server . This will create a new table in your MySQL database.

Renaming MySQL table

If you created a new table on MySQL and you want
to change the table name , you can use MySQL RENAME
statement to change the table name.

The code below changes table name from MyUsers to MyNewUsers .

You will only need to use this code if you are not happy with the existing table name and you
want to change it something else.
For learning and testing purpose , create a new file named rename_table.php and put the following contents in that file :

<?php

//include the connection file
include "connection.php";


// sql code to rename table
$sql = "RENAME TABLE MyUsers TO MyNewUsers";
//if the query is successful
if ($conn->query($sql) === TRUE) {
  echo "Table MyUsers renamed successfully";
}
//If something goes wrong

 else {
  echo "Error renaming table: " . $conn->error;
}

$conn->close();
?>

Adding records to MySQL database with PHP

To add a record to MYSQL db we use the INSERT INTO statement. With this statement we can add a record (row) to db.

add-record.php

<?php
// Include the connection file
include "connection.php";

//Add records to database now
//Adding new records to 3 columns
$user_name="Neha";
$pass="123456";
$email="[email protected]";

$sql = "INSERT INTO MyUsers (username, password, email)
VALUES ('$user_name', '$pass', '$email')";
//If the query is successful
if ($conn->query($sql) === TRUE) {
  echo "New record added successfully";
//If something goes wrong
} else {
  echo "Error: " . $sql . "
" . $conn->error;
}

$conn->close();
?>

Select data from MySQL database

To select data from the “MyUsers” table we will use the following code.

Create a new file named select.php in your MySQL test directory and put the following file contents in that file.

select.php

<?php
// Include the db connection file
include "connection.php";

//Select specific columns from MyUsers table
$sql = "SELECT id, username, password, email, reg_date FROM MyUsers";
$result = $conn->query($sql);
// If result rows are more then 0
if ($result->num_rows > 0) {
  // output data of each row in html table

   echo "<table>";
   echo "<tr>";
   echo "<th>ID</th><th>Username</th>
   <th>Password</th>
<th>Email</th>
<th>Registered on</th>
    </tr>";
  while($row = $result->fetch_assoc()) {


    echo "<tr>";
echo '<td>'.$row["id"];
echo '<td>'.$row["username"];
echo '<td>'.$row["password"];
echo '<td>'.$row["email"];
echo '<td>'.$row["reg_date"];
echo "</tr>";

  }
echo "";
} 
// Else print an error
else {
  echo "No results found";
}
$conn->close();
?>

The code above will select all the specific columns from the database.
We have requested to select all the columns from the table. If you just need to get a single column ,ie : username you can then modify the select statement like so :

$sql = "SELECT username FROM MyUsers";

This will now return all the rows for a specific column.

Display database records on a webpage

There are many ways to display MySQL records on a webpage. You can either show the data in tabular format using html table or use any block level elements to show the record. The following is a basic example of showing MySQL records on an HTML webpage. It shows the retrieved data in HTML table element.

<!DOCTYPE html>
<html>
<head>
<title>My db records</title>
</head>
<body>
<!--php code that retrieves records from database-->
<?php
// Include the db connection file
include "connection.php";

//Select specific columns from MyUsers table
$sql = "SELECT id, username, password, email, reg_date FROM MyUsers";
$result = $conn->query($sql);
// If result rows are more then 0
if ($result->num_rows > 0) {
  // output data of each row in html table

   echo "<table>";
   echo "<tr>";
   echo "<th>ID</th><th>Username</th>
   <th>Password</th>
<th>Email</th>
<th>Registered on</th>
    </tr>";
  while($row = $result->fetch_assoc()) {


    echo "<tr>";
echo '<td>'.$row["id"];
echo '<td>'.$row["username"];
echo '<td>'.$row["password"];
echo '<td>'.$row["email"];
echo '<td>'.$row["reg_date"];
echo "</tr>";

  }
echo "";
} 
// Else print an error
else {
  echo "No results found";
}
$conn->close();
?>
</body>
</html>