PHP Database Operation

Database Connection With PHP Database Object (PDO)

database.php

<?php
  $servername = "localhost"; // Database Host Name
  $username = "username"; // Database Username
  $password = "pass"; // Database Password
  $dbname = "database_name"; // Database Name

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

  // Check Connection
  if ($conn->connect_error) {
      die("Error connecting to database: " . $conn->connect_error);
  }
?>

In this step, a connection is established using the necessary information to connect to the MySQL server. This information usually includes the server name, username, password and database name. Once the connection is established, database operations can be performed via the connection variable.

CRUD (Create, Read, Update, Delete) Operation:

Data Adding (Create):

database.php

<?php
  // Get data from form
  $name = "bla";
  $lastname = "blabla";
  $email = "blablabla@bla.com";

  // Prepare the SQL query
  $sql = "INSERT INTO kullanicilar (name, lastname, email) VALUES ('$name', '$lastname', '$email')";

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

In this step, data submitted by the user through a form is retrieved. This data is then used in the SQL query to add a new record to the database. After the added record is added to the database, a success or error message is notified to the user.

Data Reading (Read):

database.php

<?php
  // Prepare the SQL query
  $sql = "SELECT * FROM users";
  $result = $conn->query($sql);

  // Print data
  if ($result->num_rows > 0) {
      while($row = $result->fetch_assoc()) {
          echo "ID: " . $row["id"]. " - Name: " . $row["name"]. " " . $row["lastname"]. " - Email: " . $row["email"]. "<br>";
      }
  } else {
      echo "0 result";
  }
?>

In this step, data is read from the database. The requested data is retrieved from the database using an SQL query and then this data is printed on the screen. This is usually done within an HTML table so that the data is displayed in an orderly manner.

Data Updating (Update):

database.php

<?php
  // Get data from form
  $id = 18;
  $name = "bla";
  $lastname = "blabla";
  $email = "blablabla@bla.com";

  // Prepare the SQL query
  $sql = "UPDATE users SET name='$name', lastname='$lastname', email='$email' WHERE id=$id";

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

This step retrieves the updated data submitted by the user via a form. This data is then used in a SQL query to update the data for a particular record. When the update process is successful, a success message is displayed to the user, otherwise an error message is displayed.

Data Deleting (Delete):

database.php

<?php
  // Get data from form
  $id = 18;

  // Prepare the SQL query
  $sql = "DELETE FROM users WHERE id=$id";

  // Run Query
  if ($conn->query($sql) === TRUE) {
      echo "Record successfully deleted";
  } else {
      echo "Error: " . $sql . "<br>" . $conn->error;
  }
?>

In this step, the ID of a record that the user wants to delete is obtained. An SQL query is created using this credential and the relevant record is deleted from the database. When the deletion is successful, a success message is displayed to the user, otherwise an error message is displayed.