Querying a Database (SELECT, INSERT, UPDATE, DELETE)

1. SELECT - Retrieving Data

The SELECT statement is used to retrieve data from a database table. In PHP, you can use the mysqli extension to execute SELECT queries. Here's an example:

<?php
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$database = "your_database";

$connection = new mysqli($servername, $username, $password, $database);

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

// Select data
$sql = "SELECT column1, column2 FROM your_table";
$result = $connection->query($sql);

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo "Column1: " . $row["column1"]. " - Column2: " . $row["column2"]. "<br>";
    }
} else {
    echo "No results found.";
}

$connection->close();
?>

• Replace "your_table", "column1", and "column2" with your table and column names.

2. INSERT - Adding Data

The INSERT statement is used to add new records to a database table. Here's an example in PHP:

<?php
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$database = "your_database";

$connection = new mysqli($servername, $username, $password, $database);

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

// Insert data
$sql = "INSERT INTO your_table (column1, column2) VALUES ('value1', 'value2')";

if ($connection->query($sql) === TRUE) {
    echo "Record added successfully.";
} else {
    echo "Error: " . $sql . "<br>" . $connection->error;
}

$connection->close();
?>

3. UPDATE - Modifying Data

The UPDATE statement is used to modify existing data in a database table. Here's an example in PHP:

<?php
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$database = "your_database";

$connection = new mysqli($servername, $username, $password, $database);

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

// Update data
$sql = "UPDATE your_table SET column1 = 'new_value' WHERE column2 = 'some_condition'";

if ($connection->query($sql) === TRUE) {
    echo "Record updated successfully.";
} else {
    echo "Error: " . $sql . "<br>" . $connection->error;
}

$connection->close();
?>

4. DELETE - Removing Data

The DELETE statement is used to remove records from a database table. Here's an example in PHP:

<?php
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$database = "your_database";

$connection = new mysqli($servername, $username, $password, $database);

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

// Delete data
$sql = "DELETE FROM your_table WHERE column1 = 'some_condition'";

if ($connection->query($sql) === TRUE) {
    echo "Record(s) deleted successfully.";
} else {
    echo "Error: " . $sql . "<br>" . $connection->error;
}

$connection->close();
?>

5. Parameterized Queries

To enhance security and prevent SQL injection, use parameterized queries when handling user input. Parameterized queries ensure that input is properly sanitized before being executed in SQL statements. Consider using prepared statements and placeholders to insert user data securely into your queries.

6. Conclusion

Performing SELECT, INSERT, UPDATE, and DELETE operations in PHP and MySQL is essential for managing and manipulating data in web applications. These basic database operations are at the core of many web development projects, allowing you to retrieve, add, modify, and delete data. Understanding how to execute these operations securely and efficiently is crucial for building robust and reliable applications.