PHP MySQL Database
Understanding MySQL databases with PHP
🗄️ What is MySQL Database?
MySQL is a popular open-source relational database management system that stores data in organized tables. PHP can easily connect to MySQL to store, retrieve, and manage data for dynamic web applications.
<?php
// Simple MySQL connection example
$servername = "localhost";
$username = "root";
$password = "";
// Create connection
$conn = new mysqli($servername, $username, $password);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully!";
?>
Output:
Connected successfully!
Key MySQL Concepts
Database
Container for organizing tables
CREATE DATABASE mydb;Tables
Store data in rows and columns
CREATE TABLE users;Primary Key
Unique identifier for each row
id INT PRIMARY KEYQueries
Commands to interact with data
SELECT * FROM users;🔹 Why Use MySQL with PHP?
MySQL and PHP work together seamlessly to create dynamic, data-driven websites. PHP handles the logic while MySQL stores and manages the data efficiently.
Benefits of MySQL:
- Free & Open Source: No licensing costs
- Fast Performance: Handles large amounts of data quickly
- Reliable: Proven stability for web applications
- Easy to Learn: Simple SQL syntax for beginners
- Wide Support: Works with all major hosting providers
🔹 MySQL Database Structure
Understanding how MySQL organizes data helps you design better applications:
<?php
// Example: Database hierarchy
/*
Server (localhost)
└── Database (mywebsite)
└── Table (users)
├── Column (id)
├── Column (name)
├── Column (email)
└── Rows (actual data)
*/
// Connecting to a specific database
$conn = new mysqli("localhost", "root", "", "mywebsite");
if ($conn->connect_error) {
die("Connection failed!");
}
echo "Connected to mywebsite database!";
?>
Output:
Connected to mywebsite database!
🔹 Common MySQL Data Types
MySQL supports various data types for different kinds of information:
Text Data Types:
- VARCHAR(255): Variable-length text (up to 255 characters)
- TEXT: Long text content (articles, descriptions)
Number Data Types:
- INT: Whole numbers (age, quantity)
- DECIMAL(10,2): Decimal numbers (prices, ratings)
Date/Time Data Types:
- DATE: Date values (YYYY-MM-DD)
- DATETIME: Date and time combined
-- Example table with different data types
CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(100),
description TEXT,
price DECIMAL(10,2),
stock INT,
created_at DATETIME
);🔹 Basic MySQL Operations
The four fundamental operations you'll use most often:
<?php
// CREATE - Add new data
$sql = "INSERT INTO users (name, email) VALUES ('John', '[email protected]')";
// READ - Retrieve data
$sql = "SELECT * FROM users WHERE id = 1";
// UPDATE - Modify existing data
$sql = "UPDATE users SET name = 'Jane' WHERE id = 1";
// DELETE - Remove data
$sql = "DELETE FROM users WHERE id = 1";
?>