MySQL Database Guide for Web Developers: Basic Commands and Benefits

Every web developer eventually encounters MySQL. It’s a database management system that stores information in a structured way, enabling quick retrieval and modification, even when dealing with millions of records.

In this article, I’ll explain the purpose of databases, why MySQL is so popular, how it compares to simple files, and walk you through the basic commands for working with it using PHP.

What Is a Database and Why Do You Need One?

Imagine you have a box where you store all your notes.

• When there are only a few notes, you can find what you’re looking for in a minute.
• But when the notes pile up into the thousands, you start getting confused, losing sheets, and wasting hours searching for a specific one.

A database is like a filing cabinet with labeled drawers. You can quickly open the right drawer, pull out only what you need, and return everything when you’re done.

MySQL is one of the most popular “filing cabinets” on the web. It runs on a server, understands a special query language, and can handle huge amounts of information. When you visit a website and see a product list, read comments under an article, or check your message in a chat—there’s almost always a MySQL query behind the scenes. It’s invisible to the user, but without it, the modern web would be nothing more than a bunch of static pages.

What makes MySQL special is that it doesn’t just store data—it lets you link it together.

One user can have many orders, each order can contain many products, and products belong to categories. With a single query, you can retrieve all orders from a specific user, without manually scanning thousands of rows.

This is called a relational database, and MySQL is one of the easiest and fastest to get started with.

Introduction

Think of a typical Excel spreadsheet—rows and columns. That’s essentially what a table is. Each column represents a specific type of information, like “Name,” “Age,” or “Email.” Each row is a record of a single entity, like a person’s data.

In MySQL, all data is stored in such tables.

A database is just a collection of related tables. For example, an online store might have a “Users” table, a “Products” table, and an “Orders” table. Together, they form the store’s database.

When you make a query (like “show all products under $100”), MySQL quickly looks through the “Products” table, checks the “Price” column, and returns rows that match the condition.

The purpose is clear: to quickly find specific information among thousands or millions of records.

Here’s what a “Users” table might look like:

• Columns (fields) are: ID, Name, Email, Age.
• Rows (records) are individual users with their data.
• A database is a collection of such tables, and there can be many.

To visualize, you can think of a database as a filing cabinet. Each drawer is a table, and each card in the drawer (row) contains fields (columns) with data. When you need to find something, you open the right drawer (table) and look through the cards (rows).

How and When MySQL Was Created

MySQL’s story begins in the mid-90s when the Swedish company MySQL AB decided to create an alternative to expensive databases.

At the time, Oracle, DB2, and Informix were powerful but complex and very expensive. The founders wanted to build a system that was free, fast, and easy to use for web developers. They based it on SQL and added their own layer on top—that’s how MySQL was born.

The first stable version came out in 1996, and it quickly gained attention from website creators. This marked the beginning of the “LAMP” stack—Linux, Apache, MySQL, and PHP—which remains the standard for millions of projects today. In 2008, Sun Microsystems acquired MySQL, and later it was bought by Oracle. Despite initial concerns, MySQL stayed free and continues to evolve. Today, there are two main versions: the classic MySQL and its fork MariaDB, created by the same developers due to uncertainty about the license.

Why is MySQL so loved? Because it’s simple. You can install it on a regular computer, write a few lines of PHP code, and have a working application within an hour. It works on all hosting providers, is suitable for small blogs, and can scale for large projects like Facebook or Wikipedia. While competitors like PostgreSQL and SQLite exist, MySQL remains the gold standard for starting most web projects.

Why MySQL Beats JSON, XML, TXT, or Arrays in Files

Beginners often try to store data in simple files like JSON, XML, CSV, or even PHP arrays saved in files.

For small projects (like website configurations), this is fine. But as the data grows, the file-based approach becomes a nightmare. Every time you need to find one record, you have to read the entire file. It’s slow, doesn’t allow easy deletion or protection against simultaneous writes by multiple users.

Here’s why MySQL is better than storing data in files:

1. Speed: MySQL uses indexes, like the alphabetical index in a book. Even with a million rows, it can search in fractions of a second.
2. Data Integrity: You can set relationships between tables, like preventing the deletion of a product if there are orders attached to it. This isn’t possible with files without a lot of manual code.
3. Multi-User Access: MySQL handles locking to prevent two users from modifying the same record simultaneously. With files, this leads to conflicts and data loss.
4. Security: MySQL has user access controls. You can restrict access to specific tables or set read-only permissions. In files, anyone who has folder access can see everything.
5. Standardization: SQL is a language understood by millions of developers. You don’t need to create your own formats or parsers.

Here’s a comparison table of key features:

Files are easy to read and edit manually. But in real web development, where speed, reliability, and multi-user capabilities are crucial, databases are unmatched.

PhpMyAdmin and Can You Manage MySQL Without It?

PhpMyAdmin is a web interface for managing MySQL. You open it in your browser, see a list of databases and tables, run SQL queries, import dumps, and modify structures.

It’s a great tool for beginners who don’t want to memorize commands. However, it’s not essential. You can work with MySQL entirely through PHP, issuing queries directly from your code. In fact, in production environments, PhpMyAdmin is often disabled for security reasons, leaving only console access.

You can create databases, tables, and modify structures through PHP using special SQL commands like CREATE DATABASE, CREATE TABLE, and ALTER TABLE. You don’t need PhpMyAdmin to manage your database. But for quick data viewing, debugging, or manual fixes, it’s very convenient. Many developers use it on local servers but rely on the console in production.

The main downside of PhpMyAdmin is its slowness with large data sets and the security vulnerabilities that sometimes arise. If you’re comfortable with SQL, you can completely bypass the web interface and manage everything through PHP scripts or the MySQL console.

For learning and small projects, PhpMyAdmin is a good starting point, but don’t think it’s necessary for every project.

Basic MySQL Commands in PHP Environment

All examples below assume you have access to a database and have the MySQLi or PDO extension installed. I will be using MySQLi. However, here’s a small clarification:

Standard MySQL is exactly the method that everyone has used for years.
There used to be the mysql_ extension (which is now outdated and removed).
It has been replaced by mysqli_ (the “i” stands for “improved”).
Essentially, it’s the same “old MySQL”, just with new features.

I will write all examples using mysqli_, and it’s available on any paid hosting plan.

Connecting to the Database

The first thing you need to do in any PHP script that works with MySQL is establish a connection.

$mysqli = new mysqli("localhost", "username", "password", "database_name");
if ($mysqli->connect_error) {
die("Connection failed: " . $mysqli->connect_error);
}

Creating a Database

$sql = "CREATE DATABASE IF NOT EXISTS my_first_db";
if ($mysqli->query($sql) === TRUE) {
echo "Database created or already exists";
} else {
echo "Error: " . $mysqli->error;
}

Checking if the Database Exists

$result = $mysqli->query("SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'my_first_db'");
if ($result->num_rows > 0) {
echo "Database exists";
} else {
echo "Database not found";
}

Creating a Table

$sql = "CREATE TABLE IF NOT EXISTS users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)";
if ($mysqli->query($sql) === TRUE) {
echo "Table 'users' created";
} else {
echo "Error: " . $mysqli->error;
}

Checking if the Table Exists

$result = $mysqli->query("SHOW TABLES LIKE 'users'");
if ($result->num_rows > 0) {
echo "Table 'users' exists";
} else {
echo "Table doesn't exist";
}

Inserting a Record into a Table

$name = "Alex Morrison";
$email = "morrison@example.com";
$stmt = $mysqli->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
$stmt->bind_param("ss", $name, $email);
if ($stmt->execute()) {
echo "Record added, ID: " . $stmt->insert_id;
} else {
echo "Error: " . $stmt->error;
}
$stmt->close();

Reading Data from a Table

$result = $mysqli->query("SELECT id, name, email FROM users");
if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
echo "ID: {$row['id']}, Name: {$row['name']}, Email: {$row['email']}<br>";
}
} else {
echo "No records found";
}

These commands are enough to get started with MySQL in PHP. After this, it’s all about practice and learning more complex queries, table joins, and transactions. But the key takeaway here is that you now understand the logic and can create your first database-powered application.

Breakdown of a Basic Example

Below is a full PHP script that does the following: creates a database Test_DB, tables colors and materials, populates them with data, and outputs the combination “green linoleum”.

<?php
// Connection parameters (user with rights to create DB)
$host = 'localhost';
$user = 'root'; // your MySQL user
$password = 'Password'; // MySQL user password (DB password if you create it)

// Create connection without specifying a database
$mysqli = new mysqli($host, $user, $password);

// Check connection
if ($mysqli->connect_error) {
die("Connection failed: " . $mysqli->connect_error);
}

// 1. Create the Test_DB database if it doesn't exist
$dbname = 'Test_DB';
$sql = "CREATE DATABASE IF NOT EXISTS `$dbname`";
if ($mysqli->query($sql) === TRUE) {
echo "Database $dbname created or already exists.<br>";
} else {
die("Error creating DB: " . $mysqli->error);
}

// Select the newly created database
$mysqli->select_db($dbname);

// 2. Create the colors table
$sql_colors = "CREATE TABLE IF NOT EXISTS colors (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL
)";
if ($mysqli->query($sql_colors) === TRUE) {
echo "Table 'colors' created.<br>";
} else {
die("Error creating colors table: " . $mysqli->error);
}

// 3. Create the materials table
$sql_materials = "CREATE TABLE IF NOT EXISTS materials (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL
)";
if ($mysqli->query($sql_materials) === TRUE) {
echo "Table 'materials' created.<br>";
} else {
die("Error creating materials table: " . $mysqli->error);
}

// 4. Populate the colors table (green, red, blue)
$colors = ['green', 'red', 'blue'];
foreach ($colors as $color) {
// Check if the color already exists to prevent duplication on re-runs
$check = $mysqli->query("SELECT id FROM colors WHERE name = '$color'");
if ($check->num_rows == 0) {
$stmt = $mysqli->prepare("INSERT INTO colors (name) VALUES (?)");
$stmt->bind_param("s", $color);
$stmt->execute();
$stmt->close();
}
}
echo "Colors table populated.<br>";

// 5. Populate the materials table (tile, linoleum, laminate)
$materials = ['tile', 'linoleum', 'laminate'];
foreach ($materials as $material) {
$check = $mysqli->query("SELECT id FROM materials WHERE name = '$material'");
if ($check->num_rows == 0) {
$stmt = $mysqli->prepare("INSERT INTO materials (name) VALUES (?)");
$stmt->bind_param("s", $material);
$stmt->execute();
$stmt->close();
}
}
echo "Materials table populated.<br>";

// 6. Retrieve data and output the "green linoleum" combination
// Get the ids for 'green' and 'linoleum'
$color_result = $mysqli->query("SELECT id FROM colors WHERE name = 'green'");
$color_row = $color_result->fetch_assoc();
$color_id = $color_row['id'];

$material_result = $mysqli->query("SELECT id FROM materials WHERE name = 'linoleum'");
$material_row = $material_result->fetch_assoc();
$material_id = $material_row['id'];

// Get names based on ids (though we already know them, but for universality)
$color_name = $mysqli->query("SELECT name FROM colors WHERE id = $color_id")->fetch_assoc()['name'];
$material_name = $mysqli->query("SELECT name FROM materials WHERE id = $material_id")->fetch_assoc()['name'];

// Output result
echo "Result: " . $color_name . " " . $material_name; // "green linoleum"

// Close connection
$mysqli->close();
?>

• First, we connect to the MySQL server with a user who has rights to create databases (typically root).
• The CREATE DATABASE IF NOT EXISTS command creates the Test_DB database if it doesn’t exist.
• Then we create two tables with fields id (auto-increment) and name (name).
• We populate the tables, checking to avoid duplicates (so data isn’t repeated when the script runs again).
• We fetch the identifiers for the rows we want (“green” and “linoleum”) and print their names together.

Important: The example uses the password Password for the MySQL user. This is the password for the account used to connect PHP to the server, not for the database itself. If you want to create a separate user specifically for this database, you’ll need to run additional SQL commands like CREATE USER and GRANT PRIVILEGES.

So, in the line $password = 'Password';, you specify the password that will be used for the script to connect to the database. This password must match the password of the MySQL user account.

For simple examples, usually one user with full rights is used.

Ethan Carter

I’m Ethan Carter, an American developer and technical writer with more than 20 years of experience in systems and application programming. My core specialty is low-level development in Assembler: 22 years of hands-on work, including deep experience in code optimization, CPU architecture, and performance-critical solutions. I also hold a PhD in Assembler and have spent more than 18 years working with ASP.NET, building enterprise web systems, APIs, and scalable backend solutions.

In addition, I have 9 years of experience in C++ and C#, along with 7 years of hands-on microcontroller programming in Assembler. Thanks to this mix of academic background and practical engineering experience, I can write about software architecture, low-level optimization, and modern development in a way that makes complex technical topics clear for a professional audience.