PHP MySQL Introduction

MySQL is a giant organized cabinet of tables where a website stores its information, like a school's list of students. PHP is the messenger that asks the cabinet for data or adds new entries.

In this page:

Connecting with PDO
Connecting with MySQLi
Selecting Data Safely
Inserting Data Safely
Closing Connections

Syntax

php

$pdo = new PDO("mysql:host=localhost;dbname=database_name", "user", "password");
$conn = mysqli_connect("localhost", "user", "password", "database_name");

Connecting with PDO

MySQL is a relational database that stores data in structured tables with defined columns, and PHP connects to it to save and retrieve the persistent data a web application needs beyond a single page request.

Example: Connecting with PDO

php

<?php
// Create a new `SQLite3` instance with ':memory:', stored in `$db`
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)");
// Print "Table created -- structured rows and columns, just like MySQL" to the output
echo "Table created -- structured rows and columns, just like MySQL";
?>

Connecting with MySQLi

PHP offers two main ways to talk to MySQL: the mysqli extension (MySQL Improved) and PDO (PHP Data Objects), with PDO offering the added benefit of working with several different database systems through one consistent API.

Example: Connecting with MySQLi

php

<?php
// SQLite3 stands in for MySQL here -- on your own server: new mysqli(...) or new PDO(...)
$db = new SQLite3(':memory:');
echo "Connected (PDO offers one API across multiple database systems)";
?>

Selecting Data Safely

The original mysql_* function family was removed entirely in PHP 7, so any modern PHP code should use mysqli or PDO — encountering the old mysql_* functions in a codebase is a strong signal it needs updating.

Example: Selecting Data Safely

php

<?php
// The old mysql_connect()/mysql_query() functions were removed in PHP 7 -- use mysqli or PDO instead
$db = new SQLite3(':memory:');
echo "Using a modern database extension";
?>

Inserting Data Safely

A typical database interaction follows three steps: open a connection, execute a query (ideally with parameters, not raw string concatenation), and process or close the result — a pattern this chapter walks through with both mysqli and PDO.

Example: Inserting Data Safely

php

<?php
// Create a new `SQLite3` instance with ':memory:', stored in `$db`
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)");
$db->exec("INSERT INTO users (name) VALUES ('Alice')");
// Declare `$result`, set to `$db->query("SELECT * FROM users")`
$result = $db->query("SELECT * FROM users");
// Print a human-readable dump of `$result->fetchArray()`
print_r($result->fetchArray());
?>

Closing Connections

Never build a SQL query by directly concatenating user input into the query string, since that's the classic root cause of SQL injection — prepared statements, covered later in this chapter, are the standard defense.

Example: Closing Connections

php

<?php
$db = new SQLite3(':memory:');
$name = "Robert'); DROP TABLE users; --";
// Never do this: $db->exec("INSERT INTO users (name) VALUES ('$name')");
echo "Use a prepared statement instead of concatenating: $name";
?>

Chapter Quiz — Complete all 21 topics to unlock

0/21 topics done

Complete these topics first:

← Previous Lesson PHP Form Complete Next Lesson → PHP MySQL Connect