PHP : MySQL Database
Before you connect PHP with a database, you should know what a database is and how MySQL stores the data. This chapter explains the basics in simple words.
What is MySQL?
MySQL is the most popular open-source Relational Database Management System (RDBMS). It is free to use and it is developed and supported by Oracle Corporation. A very similar and compatible database is MariaDB, which many hosting companies and XAMPP use in place of MySQL. Everything you learn in these chapters works with both.
MySQL is used by many large websites and by most of the popular CMS like WordPress, Joomla and Drupal.
How data is stored
In a relational database the data is stored in tables. A table has columns (the fields, like name or email) and rows (one record each). Usually one column is the primary key, a unique value like an id, that identifies every row.
| id (primary key) | name | mobile | |
|---|---|---|---|
| 1 | Ram | 9898989898 | ram@example.com |
| 2 | Sanjay | 9898969696 | sanjay@example.com |
A group of related tables is called a database. For example, a school website can have one database with the tables students, teachers and courses.
Features of MySQL
- It is free, open source and works on many operating systems.
- It is fast and handles large data well.
- It works with many languages like PHP, Python, Java, C and C++.
- It supports users and permissions, so you can control who can read or change the data.
- PHP and MySQL is the most common combination for web development.
Basic SQL commands
You talk to MySQL using SQL (Structured Query Language). These are the commands that you will use most in PHP.
| Command | Use | PHP chapter |
|---|---|---|
| CREATE DATABASE | Create a new database | Create Database |
| CREATE TABLE | Create a new table | Create Table |
| INSERT INTO | Add a new record | INSERT |
| SELECT | Read records | SELECT |
| UPDATE | Change records | UPDATE |
| DELETE | Remove records | DELETE |
PHP and MySQL
PHP and MySQL work together like this: the visitor opens a PHP page, PHP connects to MySQL, runs a SQL query, gets the data and shows it as HTML. In the next chapter we will connect PHP with MySQL.
| Exercises & Assignments |
|---|
| Display MySQL data in an HTML table using mysqli_fetch_array() |