Topic 16 of 48 · Full Stack Essentials

Introduction to PHP and MySQL

Lesson TL;DRTopic 1: Introduction to PHP and MySQL 📖 14 min read · 🎯 beginner · 🧭 Prerequisites: CSS Introduction (Internal & External — Inline), Introduction to JavaScript Why this matters Up until now, every...
14 min read·beginner·php · mysql · server-side · xampp

Topic 1: Introduction to PHP and MySQL

📖 14 min read · 🎯 beginner · 🧭 Prerequisites: CSS Introduction (Internal & External — Inline), Introduction to JavaScript

Why this matters

Up until now, everything you've built — your HTML, your CSS, your JavaScript — runs inside the browser. The user's machine does all the work. But here's the thing: that's only half the web. When you log into a website, post a comment, or place an order, something has to remember that. That's where PHP and MySQL come in. PHP runs on the server — it processes your request before any HTML even reaches the browser. MySQL is where the data lives: usernames, passwords, posts, orders — all of it stored, organized, and ready to query. Together, they're the engine behind every dynamic website you've ever used.

What You'll Learn

  • What PHP is and the key features that make it a go-to server-side language
  • What MySQL is and why a relational database management system matters
  • How to install and start XAMPP to create a local development environment
  • How to write and run your first PHP script
  • How to connect PHP to a MySQL database using mysqli and retrieve data from a table

The Analogy

Think of your website as a restaurant. HTML and CSS are the dining room — tables, chairs, menus, all beautifully arranged for guests. JavaScript is the attentive waiter who responds instantly to what guests do at the table. PHP is the kitchen: it receives the order, does the real work — chopping, cooking, combining ingredients — and sends out a finished plate. MySQL is the walk-in pantry where every ingredient is labeled, sorted, and ready to be fetched on demand. Guests never see the kitchen or the pantry, but without them, all you can serve is a static, uneditable menu printed once and never changed.

Chapter 1: What Is PHP?

PHP stands for Hypertext Preprocessor. It is a server-side scripting language designed specifically for web development. Unlike JavaScript, which runs inside the visitor's browser, PHP code executes on the web server. The server processes the PHP, then sends the resulting plain HTML to the client's browser — the visitor never sees a line of PHP code.

Key features of PHP:

  • Easy to Learn — PHP has a gentle learning curve, making it accessible for beginners who already know HTML.
  • Open Source — PHP is free to use and backed by a large, active community of developers.
  • Platform Independent — PHP scripts run on Windows, Linux, and macOS without modification.
  • Integration — PHP integrates easily with many databases, most notably MySQL, but also PostgreSQL, SQLite, and others.

Chapter 2: What Is MySQL?

MySQL is an open-source relational database management system (RDBMS). It uses SQL (Structured Query Language) to manage and manipulate data stored in tables. Tables are organized rows and columns — like a spreadsheet, but queryable at scale.

Key features of MySQL:

  • Performance — MySQL is engineered for fast reads and writes, even under heavy load.
  • Scalability — It handles large databases and supports many concurrent users simultaneously.
  • Security — MySQL provides robust access-control and authentication features to protect stored data.
  • Flexibility — It supports various storage engines and suits applications from small personal projects to enterprise-scale platforms.
flowchart LR
    Browser["Browser (Client)"] -->|HTTP Request| Apache["Apache Web Server"]
    Apache -->|Runs| PHP["PHP Engine"]
    PHP -->|SQL Query| MySQL["MySQL Database"]
    MySQL -->|Result Set| PHP
    PHP -->|HTML Response| Apache
    Apache -->|HTTP Response| Browser

Chapter 3: Setting Up Your Environment — XAMPP

To develop PHP and MySQL applications locally, you need three pieces of software running together: a web server (Apache), PHP, and MySQL. Tools like XAMPP, WAMP, and MAMP bundle all three into a single installer.

Step 1: Install XAMPP

  1. Go to the XAMPP website and download the version for your operating system (Windows, macOS, or Linux).
  2. Run the installer and follow the on-screen instructions. This installs Apache, PHP, and MySQL on your machine.
  3. Open the XAMPP Control Panel and click Start next to both the Apache and MySQL modules.

Step 2: Create a Database in MySQL via phpMyAdmin

  1. Open your browser and navigate to http://localhost/phpmyadmin.
  2. Click the New button in the left sidebar.
  3. Enter a database name — for example, mydatabase — and click Create.

Your local server is now ready to serve PHP pages and talk to a MySQL database.

Chapter 4: Writing Your First PHP Script

PHP files use the .php extension. PHP code lives inside <?php ... ?> tags, which can be mixed with HTML or stand alone in a pure PHP file.

Step 1: Create the file

Open a text editor and create a new file named index.php. Save it inside the htdocs directory of your XAMPP installation — for example:

C:\xampp\htdocs\index.php

Step 2: Write the code

<?php
echo "Hello, World!";
?>

Step 3: Run it

Open your browser and go to http://localhost/index.php. You should see:

Hello, World!

The echo statement is PHP's way of outputting content. Apache detected the .php file, handed it to the PHP engine, which ran the script and returned Hello, World! as plain text to the browser.

Chapter 5: Connecting PHP to MySQL

Knowing both PHP and MySQL individually is useful, but their real power appears when they talk to each other. PHP provides the mysqli extension (MySQL Improved) for this purpose.

Step 1: Create the database table

Navigate to http://localhost/phpmyadmin, select your mydatabase database, click the SQL tab, and run:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    password VARCHAR(255) NOT NULL
);

This creates a users table with three columns:

  • id — an auto-incrementing integer that uniquely identifies each row
  • username — a string up to 50 characters
  • password — a string up to 255 characters

Step 2: Insert sample data

Still in the SQL tab, run:

INSERT INTO users (username, password) VALUES ('admin', 'admin123'), ('user', 'user123');

This populates the table with two rows.

Step 3: Create connect.php

Create a new file named connect.php in C:\xampp\htdocs\ and write:

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "mydatabase";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

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

echo "Connected successfully";
?>

Step 4: Run it

Open http://localhost/connect.php in your browser. You should see:

Connected successfully

Breaking down the connection code:

VariableValuePurpose
$servername"localhost"The MySQL host — same machine as PHP
$username"root"Default XAMPP MySQL admin user
$password""Empty by default in XAMPP
$dbname"mydatabase"The database you created

new mysqli(...) opens the connection. If connect_error is set, die() halts execution and prints the error message — essential for debugging.

Chapter 6: Retrieving Data from MySQL

A database connection that only confirms it exists is a starting point, not a finish line. Let's extend connect.php to actually query the users table and display its contents.

Update connect.php:

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "mydatabase";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

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

echo "Connected successfully<br>";

// SQL query to retrieve data
$sql = "SELECT id, username FROM users";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // Output data of each row
    while ($row = $result->fetch_assoc()) {
        echo "id: " . $row["id"] . " – Username: " . $row["username"] . "<br>";
    }
} else {
    echo "0 results";
}

$conn->close();
?>

Open http://localhost/connect.php and you should see:

Connected successfully
id: 1 – Username: admin
id: 2 – Username: user

What each piece does:

  • $conn->query($sql) — sends the SQL string to MySQL and returns a result object
  • $result->num_rows — the number of rows returned; we check it is greater than 0 before looping
  • $result->fetch_assoc() — fetches one row at a time as an associative array, where keys are column names ("id", "username")
  • $conn->close() — releases the database connection when we're done

🧪 Try It Yourself

Task: Add a third user to the users table and display all three users on the page.

  1. Open phpMyAdmin at http://localhost/phpmyadmin, select mydatabase, open the SQL tab, and run:
INSERT INTO users (username, password) VALUES ('council_member', 'pass456');
  1. Open http://localhost/connect.php in your browser.

Success criterion: You should see three rows printed, with id: 3 – Username: council_member appearing as the last line. No changes to the PHP file are needed — the query fetches all rows automatically.

Bonus: Try changing the SELECT query in connect.php from SELECT id, username FROM users to SELECT * FROM users and reload the page. Then print $row["password"] too and observe what happens.

🔍 Checkpoint Quiz

Q1. PHP stands for Hypertext Preprocessor and is described as a server-side language. What does "server-side" mean in practice?

A) The PHP code is downloaded to the browser and run by JavaScript
B) The PHP code runs on the web server; the browser only receives the HTML output
C) PHP requires a special browser plugin to execute
D) PHP runs on the client's machine after installation

Q2. Given this PHP snippet, what will the browser display if connect_error is set?

<?php
$conn = new mysqli("localhost", "root", "", "mydatabase");
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
?>

A) Both "Connection failed: ..." and "Connected successfully"
B) "Connected successfully" regardless of the error
C) Nothing — die() suppresses all output
D) Only "Connection failed: " followed by the error message; execution stops there

Q3. In the data-retrieval script, $result->fetch_assoc() is called inside a while loop. What would happen if you called it once outside a loop instead?

A) It would return all rows as a single string
B) It would return only the first row as an associative array and stop
C) It would throw a fatal error
D) It would return all rows as a nested array

Q4. You want to store blog posts in MySQL alongside your existing users table. Both tables need to share a relationship — each post belongs to a user. What column would you add to a posts table to link it back to the users table?

A) A post_title column of type VARCHAR
B) A user_id column of type INT referencing users.id
C) A duplicate username column copied from users
D) No additional column — MySQL links tables by table name automatically

A1. B — The PHP engine on the web server processes the code before anything reaches the browser. Visitors see only the resulting HTML, never the PHP source.

A2. D — die() outputs its argument and immediately halts script execution, so "Connected successfully" is never reached.

A3. B — fetch_assoc() advances an internal pointer and returns one row per call. Called once, it returns only row 1. The while loop keeps calling it until the pointer is exhausted and it returns null.

A4. B — A user_id INT column in posts that stores the id value from the users table is a foreign key relationship, the standard way relational databases link records across tables.

🪞 Recap

  • PHP (Hypertext Preprocessor) is a free, open-source, platform-independent server-side scripting language built for web development.
  • MySQL is an open-source RDBMS that uses SQL to store, query, and manage structured data in tables.
  • XAMPP bundles Apache, PHP, and MySQL into a single local development environment; phpMyAdmin provides a browser-based GUI for MySQL at http://localhost/phpmyadmin.
  • The mysqli extension in PHP opens a database connection with new mysqli($host, $user, $pass, $db) and always checks connect_error before proceeding.
  • $conn->query($sql) runs a SQL statement, $result->num_rows tells you how many rows came back, and $result->fetch_assoc() iterates through them one row at a time as an associative array.

📚 Further Reading

Like this topic? It’s one of 48 in Full Stack Essentials.

Block your seat for ₹2,500 and join the next cohort.

Course detailsBlock your seat →