PHP and MySQL: Introduction to Database Integration

Connecting PHP to MySQL – opening a database connection, running basic queries, and keeping things secure.

Understanding PHP and MySQL

PHP runs on the server and generates pages. MySQL stores the data. Together they let you build sites with content pulled from a database rather than hard-coded into every page.

Setting up MySQL

You need a MySQL database before any of this works. Most hosting includes one – create it through your control panel. For local development, XAMPP or MAMP give you PHP and MySQL in one package.

Connecting PHP to MySQL

First step: open a connection. The two common options are mysqli (MySQL Improved) and PDO (PHP Data Objects).

Using mysqli

<?php
$servername = "localhost";
$username = "username";
$password = "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";
?>

Using PDO

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

try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    // Set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully"; 
}
catch(PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}
?>

Creating a database and tables

Before you can store or fetch data, you need a database and at least one table. Create them from PHP or through phpMyAdmin.

Creating database and table using PHP

<?php
// SQL to create database
$sql = "CREATE DATABASE myDatabase";
if ($conn->query($sql) === TRUE) {
    echo "Database created successfully";
} else {
    echo "Error creating database: " . $conn->error;
}

// SQL to create table
$sql = "CREATE TABLE MyGuests (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP
)";

if ($conn->query($sql) === TRUE) {
    echo "Table MyGuests created successfully";
} else {
    echo "Error creating table: " . $conn->error;
}
?>

Inserting data into MySQL database

With the table in place, you can add rows:

Inserting data

<?php
$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('John', 'Doe', 'john@example.com')";

if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}
?>

Retrieving data from MySQL database

Fetching data is the most common operation – read from the database and output it on the page:

Retrieving data

<?php
$sql = "SELECT id, firstname, lastname FROM MyGuests";
$result = $conn->query($sql);

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

Updating and deleting data

You’ll also need to change existing rows and remove ones you no longer want:

Updating data

<?php
$sql = "UPDATE MyGuests SET lastname='Doe Updated' WHERE id=2";

if ($conn->query($sql) === TRUE) {
    echo "Record updated successfully";
} else {
    echo "Error updating record: " . $conn->error;
}
?>

Deleting data

<?php
$sql = "DELETE FROM MyGuests WHERE id=3";

if ($conn->query($sql) === TRUE) {
    echo "Record deleted successfully";
} else {
    echo "Error deleting record: " . $conn->error;
}
?>

Security considerations

Never stitch user input straight into SQL. Use prepared statements to block injection attacks. Example with PDO:

Using prepared statements

<?php
$stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $firstname, $lastname, $email);

// Set parameters and execute
$firstname = "Jane";
$lastname = "Doe";
$email = "jane@example.com";
$stmt->execute();
?>

PHP plus MySQL is the backbone of most dynamic PHP sites. Connect, query, validate input, use prepared statements – that covers the everyday work.