Practical MySQL Basics for PHP Developers: Tables, Records, Queries, Updates, and Joins

Introduction

A PHP application often needs more than variables, arrays, and text files. Once an application must store structured data, search it, update it, and retrieve only the rows that match a condition, a relational database becomes a much better tool.

MySQL is a relational database management system commonly used with PHP applications. A relational database stores data in tables. A table has rows and columns. Each row usually represents one real-world item, such as a member, customer, booking, product, or article. Each column stores one attribute of that item, such as a name, reference number, city, status, or year.

This post explains the practical MySQL commands a PHP developer needs before connecting PHP code to a database. The focus is not advanced database design. The goal is to understand the command patterns that create, change, read, and combine database data safely.

Context and Scope

Imagine a small web application that manages club members. The application needs to store details such as:

• A unique member number
• First name and last name
• Gender code
• Job title
• City of birth
• Year of birth
• Number of children

A relational database table is a natural fit for this data because each member has the same general shape. Each member is one row, and each property is one column.

A simplified table might look like this:

members

member_no | first_name | last_name | gender | job_title  | birth_city | birth_year | children_count
----------|------------|-----------|--------|------------|------------|------------|---------------
1201      | Lena       | Moore     | F      | nurse      | Oslo       | 1994       | 1
1202      | Omar       | Kay       | M      | mechanic   | Berlin     | 1981       | 2
1203      | Nina       | Shah      | F      | developer  | Madrid     | 1990       | 0

The commands in this post cover the main work you will do with that kind of table:

1. Create a database and choose it as the current database.
2. Understand table names, field names, field types, and primary keys.
3. Insert new records.
4. Delete records carefully.
5. Change table structure with ALTER TABLE.
6. Update existing records.
7. Retrieve records with SELECT.
8. Sort, filter, limit, count, and summarize results.
9. Combine related tables with joins.
10. Inspect databases and tables with SHOW and DESCRIBE.

Relational Database Terms You Must Know

Before writing commands, it helps to make the terminology clear.

The most important idea is this: a table is not just a visual grid. MySQL also stores metadata about the table, including the names and types of its fields. That structure controls what kind of data can be stored and how MySQL treats it.

Creating and Selecting a Database

A database is the container where your tables live. Creating an empty database is simple:

CREATE DATABASE club_records

After creating it, you usually select it as the current database. That means later commands can refer to tables directly, without repeating the database name every time.

USE club_records

After this, a command such as SELECT * FROM members means "select from the members table inside the current database".

In a PHP-driven application, selecting the database is usually handled when the PHP code opens the database connection. Still, understanding USE is useful when learning MySQL directly or working in a database administration tool.

Naming Databases, Tables, and Fields

Database names, table names, and field names should be boring and predictable. Stick to letters, digits, underscores, and clear lowercase names when possible.

Good examples:

club_records
members
member_no
first_name
birth_year
children_count

Avoid names that contain spaces, hyphens, or unusual punctuation. MySQL can support names like that when they are quoted with backticks, but they make scripts easier to break and harder to read.

Prefer this:

SELECT first_name, last_name FROM members

Avoid designing tables that force this style:

SELECT `first name`, `last-name` FROM `club members`

There is also a practical case-sensitivity rule to remember. Field names are not case-sensitive in MySQL, so first_name and FIRST_NAME refer to the same field. Database and table name case-sensitivity can depend on the operating system used by the server. A safe habit is to choose one naming style and use it consistently everywhere.

Understanding Field Types

Each field has a type. You do not need every MySQL type to start building useful applications, but you should understand the idea.

For the members example, a table might contain fields like this:

Field types matter because MySQL treats numbers, text, dates, and special values differently. For example, text values normally need quotes in commands, while numeric values usually do not.

Choosing a Primary Key

A primary key is the value that lets MySQL identify one row exactly.

For a members table, last_name would be a poor primary key because many people can share the same last name. first_name plus last_name is better, but still not safe. Two members might have the same full name.

A dedicated member number is safer:

ALTER TABLE members ADD PRIMARY KEY (member_no)

The primary key should be stable, unique, and available for every row. It becomes especially useful when updating or deleting records because you can target one row without accidentally touching several.

If you set the wrong primary key, you can remove it and add a better one:

ALTER TABLE members DROP PRIMARY KEY
ALTER TABLE members ADD PRIMARY KEY (member_no)

In real projects, this kind of change should be handled carefully, especially once the database contains production data.

Inserting Records

The INSERT INTO command creates a new row.

A full insert can name every field explicitly:

INSERT INTO members
(member_no, first_name, last_name, gender, job_title, birth_city, birth_year, children_count)
VALUES
(1201, 'Lena', 'Moore', 'F', 'nurse', 'Oslo', 1994, 1)

This style is clear because the field list explains exactly what each value means. It is also safer when a table has many columns.

If you provide values for every field in the table's default order, MySQL lets you omit the field list:

INSERT INTO members
VALUES (1202, 'Omar', 'Kay', 'M', 'mechanic', 'Berlin', 1981, 2)

That is shorter, but it depends on the exact column order. If the table changes later, this form can become harder to maintain.

You can also insert only selected fields:

INSERT INTO members
(member_no, first_name, last_name, job_title, birth_city)
VALUES
(1203, 'Nina', 'Shah', 'developer', 'Madrid')

Fields not supplied by the command receive their default value, an empty value, or NULL, depending on how the table was defined.

Quotes Around Values

Use straight single quotes around text values:

INSERT INTO members
(member_no, first_name, last_name)
VALUES
(1204, 'Aron', 'Stone')

Do not forget quotes around text values. Without quotes, MySQL may treat the word as a field name rather than as text.

Numbers usually do not need quotes:

INSERT INTO members
(member_no, birth_year, children_count)
VALUES
(1205, 1978, 3)

For text fields that happen to contain digits, keep the quotes because the value is still text.

Inserting Several Records in One Command

When several rows use the same fields, you can insert them together:

INSERT INTO members
VALUES
(1206, 'Maja', 'Eriksen', 'F', 'teacher', 'Copenhagen', 1988, 2),
(1207, 'Leo', 'Grant', 'M', 'designer', 'Dublin', 1995, 0),
(1208, 'Sara', 'Novak', 'F', 'engineer', 'Prague', 1983, 1)

This is more compact than writing one command per row. It is especially convenient for seed data, examples, and controlled imports.

Replacing a Record by Primary Key

REPLACE INTO behaves like INSERT INTO, with one important difference. If a row already exists with the same primary key, MySQL removes the old row and inserts the new one.

REPLACE INTO members
VALUES (1208, 'Sara', 'Novak', 'F', 'systems engineer', 'Prague', 1983, 1)

This can be useful, but it should be used carefully. Replacing is not the same as changing selected fields. It can remove existing values that are not included in the new row. For normal edits, UPDATE is usually more explicit.

Deleting Records Safely

To delete a row, use DELETE FROM with a WHERE clause.

DELETE FROM members
WHERE member_no = 1207

The WHERE clause is the protection. Without it, the command affects every row in the table:

DELETE FROM members

That means "delete all member records". It is rarely what you want during normal application work.

When deleting by primary key, adding LIMIT 1 is a useful habit:

DELETE FROM members
WHERE member_no = 1207
LIMIT 1

Because member_no is the primary key, only one row can match. LIMIT 1 makes that intent clear and prevents MySQL from continuing to search for more matches after the row is found.

Changing Table Structure

ALTER TABLE changes the structure of a table. You can add fields, remove fields, change field names, change field specifications, and change primary keys.

To add a field at the end of a table:

ALTER TABLE members ADD passport_code VARCHAR(12)

To add a field after a specific existing field:

ALTER TABLE members ADD passport_code VARCHAR(12) AFTER job_title

To add a field as the first column:

ALTER TABLE members ADD passport_code VARCHAR(12) FIRST

To remove a field:

ALTER TABLE members DROP passport_code

The word COLUMN can also be used, but it is optional:

ALTER TABLE members DROP COLUMN passport_code

To rename a field and change its type at the same time:

ALTER TABLE members CHANGE children_count child_count TINYINT

A table structure change can affect stored data. For example, changing a text field into an integer field may destroy text values that cannot be converted into numbers. Treat structure changes as significant operations.

Emptying a Table Without Deleting Its Structure

There is a major difference between removing rows and removing the whole table.

To remove all rows but keep the table structure:

TRUNCATE members

To delete the table itself:

DROP TABLE members

Use TRUNCATE when you want an empty table with the same columns and field definitions. Use DROP TABLE only when the table is no longer needed.

Updating Existing Records

UPDATE changes values in existing rows.

To update one member using the primary key:

UPDATE members
SET gender = 'M', job_title = 'chef', birth_year = 1979, child_count = 99
WHERE member_no = 1203
LIMIT 1

The SET part can include one or more field = value pairs separated by commas.

The WHERE clause is as important here as it is for DELETE. Without it, every row in the table will be updated.

For example, this command updates every record:

UPDATE members
SET job_title = 'unknown'

That may be correct when you deliberately want a table-wide change, but it is dangerous if you intended to edit only one row.

You can also update many rows that match a condition:

UPDATE members
SET status_note = 'review'
WHERE gender = 'F' AND child_count > 2 AND child_count != 99

The extra child_count != 99 condition matters if 99 is being used as a special code for "unknown". Without that condition, rows with unknown child counts could be treated as if they had more than two children.

Using Field Values in Updates

The value assigned in an UPDATE command does not have to be a constant. It can be calculated from existing fields.

For example, to store the year after each member was born:

UPDATE members
SET review_year = birth_year + 1

To calculate a simple age-like value for a fixed year:

UPDATE members
SET calculated_value = 2020 - birth_year

To join first and last names into a single text value, MySQL provides CONCAT:

UPDATE members
SET display_name = CONCAT(first_name, ' ', last_name)

This copies the joined value into the field at that moment. It does not behave like a spreadsheet formula. If first_name or last_name changes later, display_name will not automatically change unless you run another update.

Understanding NULL

NULL means there is no value. It is not the same as zero, and it is not the same as an empty string.

• 0 is a real numeric value.
• '' is a real text value with no characters.
• NULL means the value does not exist or is unknown.

You can assign NULL:

UPDATE members
SET status_note = NULL
WHERE birth_year < 1980

To test for it, use IS NULL or IS NOT NULL:

SELECT first_name, last_name
FROM members
WHERE status_note IS NULL

SELECT first_name, last_name
FROM members
WHERE status_note IS NOT NULL

Do not use normal equality for NULL checks. NULL needs its own comparison form.

Reading Data with SELECT

The main reason to store data is to retrieve it later. SELECT returns rows from a table.

To select every field from every row:

SELECT * FROM members

The * means all fields.

In application code, you usually select only the fields you need:

SELECT first_name, last_name, gender, child_count
FROM members

This makes the result smaller and clearer. It also documents which fields the application actually uses.

When MySQL commands are issued from PHP, most non-SELECT commands report success or failure. A valid SELECT command returns a result object from which rows and columns can be extracted. The application can then display those values, format them, email them, or use them in later logic.

Sorting Results with ORDER BY

Do not rely on MySQL returning rows in the order they were inserted. If the order matters, state it explicitly.

Sort by primary key:

SELECT * FROM members
ORDER BY member_no

Ascending order is the default, but you can write it explicitly:

SELECT * FROM members
ORDER BY member_no ASC

Descending order uses DESC:

SELECT * FROM members
ORDER BY member_no DESC

You can sort by multiple fields:

SELECT * FROM members
ORDER BY last_name, first_name DESC

This sorts by last_name first. If two rows have the same last name, those rows are sorted by first_name in descending order.

Custom Sort Orders

Alphabetical order is not always the right order. Suppose a field contains values such as small, medium, and large. Alphabetical sorting would not represent the logical order.

MySQL supports a custom order with FIELD:

SELECT * FROM packages
ORDER BY FIELD(package_size, 'small', 'medium', 'large', 'extra large')

A custom order is useful when the values have a business meaning that does not match alphabetical or numeric order.

Filtering Results with WHERE

A WHERE clause selects only the rows that satisfy a condition.

SELECT last_name, first_name, birth_year
FROM members
WHERE gender = 'M' AND child_count > 0 AND child_count != 99
ORDER BY last_name, first_name

A simple condition follows this pattern:

field operator value

Common comparison operators include:

Conditions can be combined with AND, OR, and NOT:

SELECT * FROM members
WHERE gender = 'F'
AND (birth_year > 1985 OR child_count != 99)
AND NOT (job_title = 'doctor' OR job_title = 'engineer')

Parentheses make the intended logic easier to read and safer to maintain.

Filtering Ranges with BETWEEN

BETWEEN checks whether a value is inside a range.

For numeric or year values:

SELECT * FROM members
WHERE birth_year BETWEEN 1980 AND 2000
ORDER BY birth_year

For text values, the range follows alphabetical ordering:

SELECT * FROM members
WHERE last_name BETWEEN 'Hill' AND 'Stone'
ORDER BY last_name

When combining clauses, remember the order: WHERE comes before ORDER BY.

Selecting Values That Are Not Direct Fields

SELECT can return calculated or combined values, not only stored columns.

For example, this query returns a full name built from two fields:

SELECT CONCAT(first_name, ' ', last_name), gender
FROM members
WHERE child_count > 1 AND child_count != 99
ORDER BY last_name, first_name

This is useful for display output. You can keep data normalized in separate fields but still produce friendly output when needed.

Limiting Results

LIMIT restricts how many rows are returned.

To list the four youngest members by birth year:

SELECT * FROM members
ORDER BY birth_year DESC
LIMIT 4

LIMIT 1 is also useful when selecting by primary key:

SELECT first_name, last_name, birth_year
FROM members
WHERE member_no = 1201
LIMIT 1

If only one row can match, there is no need to continue looking after it is found.

MySQL also supports an offset:

SELECT * FROM members
ORDER BY birth_year DESC
LIMIT 2, 4

The first number is the offset. The second number is how many rows to return. Because counting starts at zero, LIMIT 2, 4 skips the first two rows and then returns four rows.

This pattern is useful for paging through ordered results.

Summarizing Data with Functions

MySQL can summarize field values directly.

Useful functions include:

• MIN, to find the smallest value
• MAX, to find the largest value
• AVG, to calculate an average

Example:

SELECT MIN(birth_year), MAX(birth_year), MIN(last_name), MAX(last_name), AVG(child_count)
FROM members

MIN and MAX can work with numbers and text. With text, they use alphabetical ordering.

Be careful with special codes. If 99 means "unknown" for child_count, including it in an average will produce misleading output. Filter it out:

SELECT MIN(child_count), MAX(child_count), AVG(child_count)
FROM members
WHERE child_count != 99

Counting Rows

To count every row in a table:

SELECT COUNT(*) FROM members

To count rows where a field has a non-null value:

SELECT COUNT(birth_city) FROM members

To count rows that satisfy a condition, you can use COUNT with IF:

SELECT COUNT(IF(birth_year > 1985 AND gender = 'F', 1, NULL))
FROM members

The idea is that matching rows produce a countable value, while non-matching rows produce NULL, which is not counted.

Finding Distinct Values

DISTINCT returns only unique values.

SELECT DISTINCT gender
FROM members

This is useful when exploring data. For example, a title field might contain inconsistent values such as Mrs, Mrs., and MRs. Before cleaning the data, you need to know which values exist.

You can also find distinct combinations:

SELECT DISTINCT gender, child_count
FROM members

This returns each unique pair only once.

Combining Tables with Joins

Relational databases become more useful when information is split into related tables and combined when needed.

Imagine two tables:

pets

pet_id | pet_name | legs | can_fly
-------|----------|------|--------
1      | dog      | 4    | no
2      | parrot   | 2    | yes
3      | salmon   | 0    | no
4      | lizard   | 4    | no

pet_types

type_name | category
----------|----------
dog       | mammal
parrot    | bird
salmon    | fish
rabbit    | mammal

Both tables contain an animal name, but the field names are different: pet_name in one table and type_name in the other.

Inner Join

An inner join returns rows that have matching values in both tables.

SELECT *
FROM pets INNER JOIN pet_types
ON pets.pet_name = pet_types.type_name
ORDER BY pet_name

This returns only animals found in both tables. In this example, dog, parrot, and salmon match. lizard does not appear because it is missing from pet_types. rabbit does not appear because it is missing from pets.

Left Outer Join

A left outer join returns every row from the first table and adds matching data from the second table when available.

SELECT *
FROM pets LEFT OUTER JOIN pet_types
ON pets.pet_name = pet_types.type_name
ORDER BY pet_name

If a pet has no matching type, the fields from pet_types are returned as null values.

Use a left outer join when the first table is your main list and the second table is optional supporting information.

Right Outer Join

A right outer join returns every row from the second table and adds matching data from the first table when available.

SELECT *
FROM pets RIGHT OUTER JOIN pet_types
ON pets.pet_name = pet_types.type_name
ORDER BY type_name

If a type has no matching pet, the fields from pets are returned as null values.

Use a right outer join when the second table is the main list you want to preserve.

Join Checklist

When writing joins, check these points:

• Which table should provide the full list of rows?
• Which fields are being compared?
• Do both compared fields contain the same kind of values?
• Can either side contain duplicates?
• Do you want only matches, all rows from the left table, or all rows from the right table?
• Are field names duplicated across tables? If yes, qualify them with table names.

Auto-Increment Fields

Sometimes a table does not naturally contain a safe primary key. Names, cities, years, and categories can repeat. In that case, an auto-increment field can provide a unique value automatically.

An auto-increment field is usually an integer field. MySQL gives it the next sequential value whenever a new row is inserted. It commonly starts at 1 and increases by 1 for each new record.

A table can have only one auto-increment field. It is usually used as the primary key.

If an auto-increment field is not the last field in the table and you insert a full row, use an empty string placeholder for the value that MySQL should generate:

INSERT INTO members
VALUES ('', 1210, 'Tara', 'Mills', 'F', 'analyst', 'Rome', 1992, 0)

To change the next auto-increment value:

ALTER TABLE members AUTO_INCREMENT = 100

This can be useful when you want generated identifiers to begin at a chosen value.

Inspecting Databases and Tables

MySQL includes commands for inspecting structure and system information.

List available databases:

SHOW DATABASES

List tables in the current database:

SHOW TABLES

List tables in a specific database:

SHOW TABLES IN club_records

Show the fields in a table:

SHOW FIELDS FROM members

Equivalent forms are:

SHOW COLUMNS FROM members

DESCRIBE members

The field description output normally includes information such as:

• Field name
• Field type
• Whether NULL is allowed
• Whether the field is indexed or is a primary key
• Default value
• Extra information, such as auto-increment behavior

To inspect system variables:

SHOW VARIABLES

To display the MySQL version:

SHOW VARIABLES LIKE 'version'

These commands are useful when debugging environment differences or verifying table structure before writing application code.

Renaming and Removing Tables or Databases

To rename a table, you can use either form:

ALTER TABLE members RENAME TO archived_members

RENAME TABLE members TO archived_members

To delete a table:

DROP TABLE archived_members

To delete a database and all its tables:

DROP DATABASE club_records

These commands are destructive. In a real project, use them with care and make sure you understand exactly which database is selected.

Comments in MySQL Commands

MySQL commands can include comments. A comment is ignored by MySQL and is meant for humans.

Single-line comments can start with #:

DROP DATABASE old_test_db # remove unused test database

They can also start with two minus signs followed by a space:

DROP DATABASE old_test_db -- remove unused test database

Block comments use /* and */:

DROP DATABASE /* remove unused test database */ old_test_db

Comments can make maintenance scripts easier to read, but avoid placing destructive commands in shared scripts unless the purpose is completely clear.

Practical Workflow for Building Confidence

When learning these commands, do not start with a production database. Use a small practice database and a table with a few rows.

A safe learning workflow looks like this:

1. Create a practice database.
2. Select it with USE.
3. Work with an existing table structure.
4. Add a primary key.
5. Insert a few records.
6. Run simple SELECT commands.
7. Add WHERE filters.
8. Add ORDER BY clauses.
9. Try LIMIT with and without an offset.
10. Update one row using the primary key.
11. Delete one row using the primary key.
12. Add and remove a temporary column.
13. Use SHOW FIELDS or DESCRIBE to confirm structure.
14. Practice joins with two small related tables.

The goal is to understand how each command changes either the data or the table structure.

Common Mistakes to Watch For

Forgetting WHERE in DELETE or UPDATE

This is the most dangerous beginner mistake.

UPDATE members SET job_title = 'unknown'

This changes every row. Use a WHERE clause when you mean to target selected rows.

Using a non-unique field as a primary key

A surname, city, title, or job is rarely unique. Prefer a stable identifier such as a reference number or an auto-increment field.

Confusing NULL with zero or an empty string

NULL, 0, and '' mean different things. Use IS NULL and IS NOT NULL when testing for missing values.

Assuming row order without ORDER BY

MySQL is not required to return rows in insertion order. Use ORDER BY whenever order matters.

Relying too much on short INSERT commands

INSERT INTO members VALUES (...) is compact, but it depends on column order. Naming fields is often clearer and safer.

Changing field types without thinking about existing data

Converting a text field to a numeric field can destroy values that cannot be converted. Structure changes should be treated as careful operations.

Mixing special codes with real data

Using 99 to mean "unknown" can work in examples, but it can also distort calculations. Always filter special codes out of averages and business rules.

Developer Checklist

Use this checklist when writing MySQL commands for a PHP-backed application:

• Choose clear lowercase names for databases, tables, and fields.
• Use a primary key that uniquely identifies one row.
• Prefer explicit field lists in INSERT INTO commands.
• Quote text values with straight single quotes.
• Do not quote ordinary numeric values.
• Always review DELETE and UPDATE commands before running them.
• Use WHERE for targeted changes.
• Use LIMIT 1 when targeting a single row by primary key.
• Use ORDER BY when output order matters.
• Use DISTINCT to explore inconsistent field values.
• Use COUNT, MIN, MAX, and AVG for summaries, but filter out special codes.
• Use joins only after confirming which table should control the result set.
• Use SHOW FIELDS or DESCRIBE to verify table structure.
• Treat DROP TABLE and DROP DATABASE as destructive commands.

Conclusion

MySQL becomes much easier to understand when you separate its work into two categories: commands that change data or structure, and commands that retrieve information.

INSERT INTO, REPLACE INTO, UPDATE, DELETE, ALTER TABLE, TRUNCATE, and DROP change something. SELECT, SHOW, and DESCRIBE help you read data or inspect structure. Clauses such as WHERE, ORDER BY, and LIMIT make those commands precise.

For PHP developers, this foundation is essential. PHP can send commands to MySQL, receive results, and turn those results into web output. Before adding PHP code, make sure you understand what each MySQL command does on its own. That makes your database-backed applications safer, easier to debug, and easier to maintain.