Databases and working with MySQL

1. Introduction

In this article, we are learning about databases and how we can connect to databases using PHP programs.
Databases are used as information storage components in software systems. We can connect to databases using different technologies and programming language.
Let’s first understand the concept of databases thoroughly and then we will connect to it using PHP programming language.

2. RDBMS Concepts

RDBMS stands for Relational Database Management Systems. To interact with RDBMS, we use SQL. SQL stands for Structural Query Language. SQL is used with all modern database systems like MySQL, MS SQL Server, IBM DB2, Oracle, and others.

In RDBMS, we store data in objects and we call them tables. This table is a collection of related data entries, and it consists of numerous columns and rows. These tables are logically related to each other using some connector known as Foreign keys

3. Using SQL for performing actions on DB

To interact with databases, we use SQL and in SQL there are two types of queries – data definition queries and data manipulation queries. We use data definition queries to define the database schema, table structures etc and to insert, update or delete we use data manipulation queries. To keep this article simple and focused, we will just connect to MySQL using PHP and create a new table in the DB.

4. Program to connect to MySQL

To connect to MySQL using PHP, we use PDO. PDO stands for PHP Data Objects. Here is the code:

$servername = "localhost";
$username = "username";
$password = "password";

try {
    $db= new PDO("mysql:host=$servername;dbname=myDB", $username, $password);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully"; 
}catch(PDOException $e){
    echo "Connection failed: " . $e->getMessage();
}

5. Creating a table

As we have successfully connected to DB, let’s try creating a table in DB. Below is the complete code to create table –

$servername = "localhost";
$username = "username";
$password = "password";

try {

    $db = new PDO("mysql:host=$servername;dbname=myDB", $username, $password);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //Error Handling

    $sql ="CREATE table USERS(
              ID INT( 11 ) AUTO_INCREMENT PRIMARY KEY,
              Name VARCHAR( 250 ) NOT NULL
           );" ;

    $db->exec($sql);
    print("Created Table.\n");

} catch(PDOException $e) {
    echo $e->getMessage();
}

6. Conclusion

In this article, we are learning about what is an RDBMS, and it is used to store data in software applications. SQL is used to interact with RDBMS DB to access and manipulate the data stored in RDBMS DB. We also connected to MySQL RDBMS using PHP technology using PDO technology. In the upcoming article of this series, let’s learn about errors and exceptions in PHP.