PHP database operations

Question

Answers ( 1 )

    0
    2024-01-11T18:05:26+00:00

    PHP is a widely-used server-side scripting language, particularly for web development, and database operations are a common task in this context.

    To perform database operations in PHP, you commonly interact with a database management system like MySQL. Here’s a general overview of how to perform basic operations such as connecting to a database, creating a table, inserting data, querying data, updating data, and deleting data.

    1. Connecting to a Database

    First, you need to connect to your database. PHP offers different ways to do this, but the most common are using MySQLi (MySQL Improved) and PDO (PHP Data Objects).

    Using MySQLi:

    $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);
    }
    

    Using PDO:

    $servername = "localhost";
    $username = "username";
    $password = "password";
    $dbname = "myDB";
    
    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();
    }
    

    2. Creating a 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 DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    )";
    
    if ($conn->query($sql) === TRUE) {
        echo "Table MyGuests created successfully";
    } else {
        echo "Error creating table: " . $conn->error;
    }
    

    3. Inserting Data

    $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;
    }
    

    4. Querying Data

    $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";
    }
    

    5. Updating Data

    $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;
    }
    

    6. Deleting Data

    $sql = "DELETE FROM MyGuests WHERE id=3";
    
    if ($conn->query($sql) === TRUE) {
        echo "Record deleted successfully";
    } else {
        echo "Error deleting record: " . $conn->error;
    }
    

    Closing the Connection

    It's important to close your database connection after your operations are done.

    $conn->close();
    

    Remember, this is a basic guide. Real-world applications often require more complex operations and considerations, such as prepared statements for security, error handling, and efficient query writing. Always ensure to sanitize and validate user inputs to prevent SQL injection attacks when dealing with databases.

Leave an answer