php crud operations code with database connectivity

CRUD operations (Create, Read, Update, Delete) are fundamental for working with databases in PHP. Let?s explore some examples of how to perform these operations using PHP and MySQL:

Create (Insert) Operation:

• To add data to your database, use an INSERT query.
• Here?s an example of inserting a new record into a table named employees:

<?php

include 'config.php'; // Include your database connection details



$name = 'John Doe';

$address = '123 Main St';

$salary = 50000;



$sql = "INSERT INTO employees (name, address, salary) VALUES ('$name', '$address', $salary)";

if ($conn->query($sql)) {

    echo "Record added successfully";

} else {

    echo "Error: " . $sql . "<br>" . $conn->error;

}



$conn->close();

?>

• Replace the database connection details (config.php) with your actual credentials.

Read (Select) Operation:

• To retrieve data from the database, use a SELECT query.
• Example:

<?php

include 'config.php';



$sql = "SELECT * FROM employees";

$result = $conn->query($sql);



if ($result->num_rows > 0) {

    while ($row = $result->fetch_assoc()) {

        echo "Name: " . $row['name'] . " | Salary: " . $row['salary'] . "<br>";

    }

} else {

    echo "No records found.";

}



$conn->close();

?>

Update Operation:

• To modify existing data, use an UPDATE query.
• Example:

<?php

include 'config.php';



$newSalary = 55000;

$employeeId = 1; // Assuming you want to update the record with ID 1



$sql = "UPDATE employees SET salary = $newSalary WHERE id = $employeeId";

if ($conn->query($sql)) {

    echo "Record updated successfully";

} else {

    echo "Error: " . $sql . "<br>" . $conn->error;

}



$conn->close();

?>

Delete Operation:

• To remove data, use a DELETE query.
• Example:

<?php

include 'config.php';



$employeeIdToDelete = 2; // Assuming you want to delete the record with ID 2



$sql = "DELETE FROM employees WHERE id = $employeeIdToDelete";

if ($conn->query($sql)) {

    echo "Record deleted successfully";

} else {

    echo "Error: " . $sql . "<br>" . $conn->error;

}



$conn->close();

?>

Remember to replace the table name (employees) and field names (name, address, salary) with your actual database schema. Also, consider using prepared statements or PDO for better security.

Creating a Database Connection File: 'config.php'

Creating a database connection in PHP is essential for interacting with databases. Below, We are providing examples of how to establish connections using both MySQLi (both object-oriented and procedural) and PDO (PHP Data Objects):

MySQLi (Object-Oriented):

<?php

$servername = "localhost";

$username = "your_username";

$password = "your_password";

$dbname = "your_database_name";



// Create connection

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



// Check connection

if ($conn->connect_error) {

    die("Connection failed: " . $conn->connect_error);

}



echo "Connected successfully!";

?>

MySQLi (Procedural):

<?php

$servername = "localhost";

$username = "your_username";

$password = "your_password";

$dbname = "your_database_name";



// Create connection

$conn = mysqli_connect($servername, $username, $password, $dbname);



// Check connection

if (!$conn) {

    die("Connection failed: " . mysqli_connect_error());

}



echo "Connected successfully!";

?>

PDO (PHP Data Objects):

<?php

$servername = "localhost";

$username = "your_username";

$password = "your_password";

$dbname = "your_database_name";



try {

    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);

    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    echo "Connected successfully!";

} catch (PDOException $e) {

    echo "Connection failed: " . $e->getMessage();

}

?>

Remember to replace the placeholders (your_username, your_password, your_database_name) with your actual database credentials. Additionally, choose either MySQLi or PDO based on your preferences and project requirements.