MySQL Database Operations

Overview of MySQL Operations

This section covers essential operations for managing a MySQL database using PHP, including creating a database, connecting to it, creating tables, and performing CRUD operations.

1. MySQL Database

Creating and managing a MySQL database is the foundation of working with data. Use the CREATE DATABASE command to create a new database.

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

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

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

// Create database
$sql = "CREATE DATABASE MyDB";
if ($conn->query($sql) === TRUE) {
    echo "Database created successfully";
} else {
    echo "Error creating database: " . $conn->error;
}

$conn->close();
?>

2. MySQL Connect

Establish a connection to the MySQL server using MySQLi or PDO.

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

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

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

3. MySQL Create DB

Use the CREATE DATABASE SQL statement to create a new database in MySQL.

<?php
// Assuming connection is already established

$sql = "CREATE DATABASE MyNewDB";
if ($conn->query($sql) === TRUE) {
    echo "Database MyNewDB created successfully";
} else {
    echo "Error creating database: " . $conn->error;
}
?>

4. MySQL Create Table

Create tables within a database using the CREATE TABLE statement.

<?php
$sql = "CREATE TABLE Users (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(30) NOT NULL,
    email VARCHAR(50),
    reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)";

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

5. MySQL Insert Data

Add data to your table using the INSERT INTO statement.

<?php
$sql = "INSERT INTO Users (username, email)
VALUES ('JohnDoe', 'john@example.com')";

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

6. MySQL Get Last ID

Retrieve the last inserted ID using LAST_INSERT_ID().

<?php
$last_id = $conn->insert_id; // Get the last inserted ID
echo "Last inserted ID is: " . $last_id;
?>

7. MySQL Insert Multiple

Insert multiple records at once using a single INSERT INTO statement.

<?php
$sql = "INSERT INTO Users (username, email) VALUES
('JaneDoe', 'jane@example.com'),
('BobSmith', 'bob@example.com')";

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

8. MySQL Prepared Statements

Use prepared statements to execute SQL queries securely.

<?php
$stmt = $conn->prepare("INSERT INTO Users (username, email) VALUES (?, ?)");
$stmt->bind_param("ss", $username, $email);

// Set parameters and execute
$username = "CharlieBrown";
$email = "charlie@example.com";
$stmt->execute();

echo "New record created successfully";
$stmt->close();
?>

9. MySQL Select Data

Retrieve data from a table using the SELECT statement.

<?php
$sql = "SELECT id, username, email 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"]. " - Name: " . $row["username"]. " - Email: " . $row["email"]. "<br>";
    }
} else {
    echo "0 results";
}
?>

10. MySQL Where

Use the WHERE clause to filter results based on specific conditions.

<?php
$sql = "SELECT * FROM Users WHERE username='JohnDoe'";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        echo "id: " . $row["id"]. " - Name: " . $row["username"]. " - Email: " . $row["email"]. "<br>";
    }
} else {
    echo "0 results";
}
?>

11. MySQL Order By

Use the ORDER BY clause to sort results by specified columns.

<?php
$sql = "SELECT * FROM Users ORDER BY username ASC";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        echo "id: " . $row["id"]. " - Name: " . $row["username"]. " - Email: " . $row["email"]. "<br>";
    }
} else {
    echo "0 results";
}
?>

12. MySQL Delete Data

Remove records from a table using the DELETE FROM statement.

<?php
$sql = "DELETE FROM Users WHERE id=1";

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

13. MySQL Update Data

Modify existing records in a table using the UPDATE statement.

<?php
$sql = "UPDATE Users SET email='john_new@example.com' WHERE username='JohnDoe'";

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

14. MySQL Limit Data

Use the LIMIT clause to restrict the number of records returned from a query.

<?php
$sql = "SELECT * FROM Users LIMIT 5"; // Get only the first 5 records
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        echo "id: " . $row["id"]. " - Name: " . $row["username"]. " - Email: " . $row["email"]. "<br>";
    }
} else {
    echo "0 results";
}
?>

Conclusion

Understanding how to manage MySQL databases using PHP is essential for building robust web applications. This guide provides a solid foundation for performing various operations such as creating databases, managing tables, and manipulating data efficiently.