PHP Fundamentals

SQL: Inserting & Selecting Data

13 min Lesson 32 of 45

Inserting Data with INSERT

The INSERT INTO statement adds new rows (records) to a table.

Basic Syntax

INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);

Example: Inserting a Single User

INSERT INTO users (username, email, password)
VALUES ('john_doe', 'john@example.com', 'hashed_password_here');

Important Notes

String values must be enclosed in single quotes: 'text'
Numbers don't need quotes: 25, 19.99
AUTO_INCREMENT columns (like id) are automatically generated - don't include them
Columns with defaults (like created_at) can be omitted

Inserting Without Column Names

If you provide values for ALL columns in the exact order they were defined:

INSERT INTO users
VALUES (NULL, 'jane_doe', 'jane@example.com', 'hashed_password', NOW());
-- NULL for AUTO_INCREMENT id
-- NOW() for timestamp

Best Practice: Always specify column names explicitly. This makes your code more readable and prevents errors if the table structure changes.

Inserting Multiple Rows

Insert multiple records in a single query for better performance:

INSERT INTO users (username, email, password)
VALUES
    ('alice', 'alice@example.com', 'password1'),
    ('bob', 'bob@example.com', 'password2'),
    ('charlie', 'charlie@example.com', 'password3');

Example: Inserting Products

INSERT INTO products (name, description, price, stock, category)
VALUES
    ('Laptop', 'High-performance laptop', 899.99, 15, 'Electronics'),
    ('Mouse', 'Wireless mouse', 19.99, 50, 'Electronics'),
    ('Desk Chair', 'Ergonomic office chair', 199.99, 8, 'Furniture');

Getting the Last Inserted ID

-- In MySQL, after INSERT:
SELECT LAST_INSERT_ID();

-- In PHP (mysqli):
$last_id = $mysqli->insert_id;

-- In PHP (PDO):
$last_id = $pdo->lastInsertId();

Selecting Data with SELECT

The SELECT statement retrieves data from one or more tables.

Select All Columns

SELECT * FROM users;

The * means "all columns". Output:

+----+----------+-------------------+------------------+---------------------+
| id | username | email             | password         | created_at          |
+----+----------+-------------------+------------------+---------------------+
| 1  | john_doe | john@example.com  | hashed_password  | 2024-01-15 10:30:00 |
| 2  | jane_doe | jane@example.com  | hashed_password  | 2024-01-15 11:45:00 |
+----+----------+-------------------+------------------+---------------------+

Select Specific Columns

SELECT username, email FROM users;

Output:

+----------+-------------------+
| username | email             |
+----------+-------------------+
| john_doe | john@example.com  |
| jane_doe | jane@example.com  |
+----------+-------------------+

Performance Tip: Select only the columns you need instead of using SELECT *. This reduces data transfer and improves performance, especially with large tables.

Filtering with WHERE

The WHERE clause filters results based on conditions.

Basic WHERE Syntax

SELECT columns FROM table WHERE condition;

Comparison Operators

-- Equal to
SELECT * FROM products WHERE category = 'Electronics';

-- Not equal to
SELECT * FROM products WHERE category != 'Electronics';
-- OR
SELECT * FROM products WHERE category <> 'Electronics';

-- Greater than
SELECT * FROM products WHERE price > 100;

-- Less than
SELECT * FROM products WHERE stock < 10;

-- Greater than or equal to
SELECT * FROM products WHERE price >= 50;

-- Less than or equal to
SELECT * FROM products WHERE stock <= 20;

Logical Operators (AND, OR, NOT)

-- AND: Both conditions must be true
SELECT * FROM products
WHERE category = 'Electronics' AND price < 500;

-- OR: At least one condition must be true
SELECT * FROM products
WHERE category = 'Electronics' OR category = 'Furniture';

-- NOT: Negates a condition
SELECT * FROM products
WHERE NOT category = 'Electronics';

-- Combining multiple conditions
SELECT * FROM products
WHERE (category = 'Electronics' OR category = 'Furniture')
AND price BETWEEN 50 AND 500;

BETWEEN Operator

-- Select products with price between 50 and 200
SELECT * FROM products
WHERE price BETWEEN 50 AND 200;

-- Equivalent to:
SELECT * FROM products
WHERE price >= 50 AND price <= 200;

IN Operator

-- Select products in specific categories
SELECT * FROM products
WHERE category IN ('Electronics', 'Furniture', 'Books');

-- Equivalent to:
SELECT * FROM products
WHERE category = 'Electronics'
   OR category = 'Furniture'
   OR category = 'Books';

LIKE Operator (Pattern Matching)

-- % matches any sequence of characters
-- _ matches exactly one character

-- Products starting with 'Lap'
SELECT * FROM products WHERE name LIKE 'Lap%';

-- Products ending with 'mouse'
SELECT * FROM products WHERE name LIKE '%mouse';

-- Products containing 'chair'
SELECT * FROM products WHERE name LIKE '%chair%';

-- Products with exactly 5 characters
SELECT * FROM products WHERE name LIKE '_____';

-- Case-insensitive by default in MySQL
SELECT * FROM users WHERE email LIKE '%@gmail.com';

NULL Values

-- Find products without description
SELECT * FROM products WHERE description IS NULL;

-- Find products with description
SELECT * FROM products WHERE description IS NOT NULL;

-- Note: You cannot use = or != with NULL
-- WRONG: WHERE description = NULL
-- CORRECT: WHERE description IS NULL

Sorting with ORDER BY

The ORDER BY clause sorts results in ascending (ASC) or descending (DESC) order.

Single Column Sorting

-- Sort by price (ascending - lowest first)
SELECT * FROM products ORDER BY price ASC;

-- Sort by price (descending - highest first)
SELECT * FROM products ORDER BY price DESC;

-- ASC is default, so this is equivalent:
SELECT * FROM products ORDER BY price;

Multiple Column Sorting

-- Sort by category, then by price within each category
SELECT * FROM products
ORDER BY category ASC, price DESC;

Combining WHERE and ORDER BY

SELECT name, price, stock
FROM products
WHERE category = 'Electronics' AND stock > 0
ORDER BY price DESC;

Limiting Results with LIMIT

The LIMIT clause restricts the number of rows returned.

Basic LIMIT

-- Get first 5 products
SELECT * FROM products LIMIT 5;

-- Get 5 most expensive products
SELECT * FROM products
ORDER BY price DESC
LIMIT 5;

LIMIT with OFFSET (Pagination)

-- Skip first 10 rows, return next 5
SELECT * FROM products LIMIT 5 OFFSET 10;

-- Alternative syntax (older)
SELECT * FROM products LIMIT 10, 5;
-- LIMIT offset, count

-- Example: Page 1 (0-9)
SELECT * FROM products LIMIT 10 OFFSET 0;

-- Page 2 (10-19)
SELECT * FROM products LIMIT 10 OFFSET 10;

-- Page 3 (20-29)
SELECT * FROM products LIMIT 10 OFFSET 20;

Pagination Formula

-- Formula: LIMIT items_per_page OFFSET (page - 1) * items_per_page

-- Page 1, 20 items per page:
SELECT * FROM products LIMIT 20 OFFSET 0;

-- Page 3, 20 items per page:
SELECT * FROM products LIMIT 20 OFFSET 40;

Useful SELECT Functions

COUNT - Count Rows

-- Count all products
SELECT COUNT(*) FROM products;

-- Count products in Electronics category
SELECT COUNT(*) FROM products WHERE category = 'Electronics';

-- Count non-NULL descriptions
SELECT COUNT(description) FROM products;

SUM - Calculate Total

-- Total value of all products in stock
SELECT SUM(price * stock) AS total_inventory_value FROM products;

-- Total stock quantity
SELECT SUM(stock) FROM products;

AVG - Calculate Average

-- Average product price
SELECT AVG(price) FROM products;

-- Average price in Electronics category
SELECT AVG(price) FROM products WHERE category = 'Electronics';

MIN and MAX

-- Cheapest product price
SELECT MIN(price) FROM products;

-- Most expensive product price
SELECT MAX(price) FROM products;

-- Product with highest price
SELECT * FROM products ORDER BY price DESC LIMIT 1;

DISTINCT - Remove Duplicates

-- Get unique categories
SELECT DISTINCT category FROM products;

-- Count unique categories
SELECT COUNT(DISTINCT category) FROM products;

Column Aliases with AS

-- Rename columns in output
SELECT
    name AS product_name,
    price AS cost,
    stock AS quantity_available
FROM products;

-- Use in calculations
SELECT
    name,
    price,
    stock,
    price * stock AS total_value
FROM products;

Practical Examples

Example 1: E-Commerce Product Search

-- Search for laptops under $1000, in stock, sorted by price
SELECT name, price, stock
FROM products
WHERE name LIKE '%laptop%'
  AND price < 1000
  AND stock > 0
ORDER BY price ASC;

Example 2: User Registration Report

-- Get users registered in last 7 days
SELECT username, email, created_at
FROM users
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
ORDER BY created_at DESC;

Example 3: Low Stock Alert

-- Products with stock below 10 units
SELECT name, stock, category
FROM products
WHERE stock < 10 AND stock > 0
ORDER BY stock ASC;

Example 4: Sales Dashboard

-- Summary statistics
SELECT
    COUNT(*) AS total_products,
    SUM(stock) AS total_items_in_stock,
    AVG(price) AS average_price,
    MIN(price) AS cheapest_product,
    MAX(price) AS most_expensive_product,
    SUM(price * stock) AS total_inventory_value
FROM products
WHERE is_active = 1;

Exercise: Product Catalog Queries

Using the products table, write SQL queries for the following:

Insert 3 new products in different categories
Select all products sorted by price (highest to lowest)
Find all products in the "Electronics" category with price between $50 and $500
Get the 10 most recently added products
Count how many products are in each category
Find products whose names contain "pro" (case-insensitive)
Calculate the total value of all inventory
Get the top 5 most expensive products that are in stock

Best Practices

Always use WHERE carefully: Queries without WHERE can accidentally affect all rows
Use LIMIT: When testing queries, add LIMIT to avoid overwhelming results
Index frequently searched columns: Add indexes on columns used in WHERE, ORDER BY, JOIN
Use specific column names: Avoid SELECT * in production code
Sanitize user input: Never put user input directly in SQL (prevents SQL injection)
Use prepared statements: In PHP, use PDO or mysqli prepared statements

SQL Injection Warning

NEVER construct SQL queries by concatenating user input:

// DANGEROUS - NEVER DO THIS!
$query = "SELECT * FROM users WHERE username = '" . $_POST['username'] . "'";

// SAFE - Use prepared statements (covered in later lessons)
$stmt = $pdo->prepare("SELECT * FROM users WHERE username = ?");
$stmt->execute([$_POST['username']]);

Query Execution Order

SQL clauses are written in this order:

SELECT - columns to return
FROM - table to query
WHERE - filter rows
GROUP BY - group rows (covered later)
HAVING - filter groups (covered later)
ORDER BY - sort results
LIMIT - restrict number of rows

But MySQL executes them in a different order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

What's Next?

In the next lesson, you'll learn how to modify existing data with UPDATE and remove data with DELETE statements, along with important safety practices.