PHP and MySQL Database Using Object-Oriented Approach

The mysqli extension offers all of the functionality provided by its predecessor, in addition to new features that have been added as a result of MySQL’s evolution into a full-featured database server.

1. Create a New Connection

Before you can access data in the MySQL database, you need to connect to the server.

$servername = 'localhost';
$username = 'username';
$password = 'password';

$conn = new mysqli($servername, $username, $password);

$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) 
{
 die('Connection failed: ' . $conn->connect_error);
} 
echo 'Connected successfully';

Once the connection has been made, you can start interacting with the database.

2. Close the Connection

The connection is closed automatically when the script ends. To close the connection before, use the following:

$conn->close();

3. Creating New Database

A database consists of one or more tables. The CREATE DATABASE statement is used to create a database in MySQL. When you create a new database, you have to only specify the first three arguments to the mysqli object (servername, username and password).

$sql = "CREATE DATABASE myDB";
if ($conn->query($sql) === TRUE) 
{
 echo "Database created successfully";
} else 
{
 echo "Error creating database: " . $conn->error;
}

4. Creating Tables

A database table consists of columns and rows. The CREATE TABLE statement is used to create a table in MySQL.

$sql = "CREATE TABLE MyGuests (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
name VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP
)";

if ($conn->query($sql) === TRUE) 
{
 echo "Table MyGuests created successfully";
} 
else 
{
 echo "Error creating table: " . $conn->error;
}

5. Inserting Data into Tables

After you have created database and table, you can start adding or inserting data in the table. The INSERT INTO statement is used to add new records to a MySQL table.

If a column is AUTO_INCREMENT (like the "id" column) or TIMESTAMP (like the "reg_date" column), there is no need to be specified in the SQL query because MySQL will automatically add the value.

$sql = "INSERT INTO MyGuests (name, email)
VALUES ('John Doe', 'john@example.com')";

if ($conn->query($sql) === TRUE) 
{
 echo "New record created successfully";
} 
else 
{
 echo "Error: " . $sql . "<br>" . $conn->error;
}

6. Get ID of Last Record

If you perform an INSERT or UPDATE on a table with an AUTO_INCREMENT field, you can easily get the ID of the last inserted or updated record immediately.

$last_id = $conn->insert_id;

7. Getting Data from Tables

The SELECT statement is used to select data from one or more tables. First, you set up an SQL query that selects some columns from the table. Then, you run the query and store the resulting data into a variable called $result.

The function num_rows() checks if there are more than zero rows returned. If there are more than zero rows returned, the function fetch_assoc() puts all the results into an associative array that you can loop through. The while() loop loops through the result set and outputs the data

$sql = "SELECT id, name FROM MyGuests";
$result = $conn->query($sql);

if ($result->num_rows > 0) 
{
 // output data of each row
 while($row = $result->fetch_assoc()) 
 {
  echo "id: " . $row["id"]. " - Name: " . $row["name"]. " <br>";
 }
} 
else 
{
 echo "0 results";
}

MySQL provides a LIMIT clause that is used to specify the number of records to return. Returning a large number of records can impact on performance. The LIMIT clause makes it easy to code multi page results or pagination with SQL, and is very useful on large tables. 

$sql = "SELECT * FROM MyGuests LIMIT 50";

8. Deleting Data from Tables

The DELETE statement is used to delete records from a table. The WHERE clause specifies which record or records that should be deleted. If you omit the WHERE clause, all records will be deleted.

$sql = "DELETE FROM MyGuests WHERE id=3";

if ($conn->query($sql) === TRUE) 
{
 echo "Record deleted successfully";
} 
else 
{
 echo "Error deleting record: " . $conn->error;
}

9. Updating Data in Tables

The UPDATE statement is used to update existing records in a table. The WHERE clause specifies which record or records that should be updated. If you omit the WHERE clause, all records will be updated.

$sql = "UPDATE MyGuests SET name='Doe' WHERE id=2";

if ($conn->query($sql) === TRUE) 
{
 echo "Record updated successfully";
} 
else 
{
 echo "Error updating record: " . $conn->error;
}