Learn quickly, PHP and MySQL create, read, update, and delete (CRUD)

How deal with server, database and table

PHP stands for Hypertext Preprocessor. PHP was originally created by Rasmus Lerdorf in 1994. It was initially known as Personal Home Page and it is an open source language.

PHP code is executed on the server, and the result is returned to the browser as plain HTML. So you can generate pages and files dynamically as well you can create, open, read, write and close files on the server.

Try following steps, lets jump to learning basics of php operations.

1. install local server (Xampp or Wamp for Windows users, Mamp for mac or other solution)

2. Test by running localhost/phpmyadmin web browser (here you will see databases and tables)

3. Make new folder for project in htdocs or www folder

4. open created project in VS Code

5. Create index.php
(Note : use this file to try following tasks and edit file as accordingly)

6. Do some basics stuffs of PHP(operators, echo or print html elements)

<?php // Displaying HTML code print
$myName = "Malisha";
echo "<h1>Hi I'm $myName.</h4>"; 
print "<h4 style='color: red;'>This is heading with style.</h4>"; ?>

7. Make server connection (Do it and test it using following code)

<?php
$servername = “localhost”;
$username = “username”;
$password = “password”;
// Create connection
$conn_server = new mysqli($servername, $username, $password);
// Check connection
if ($conn_server->connect_error) {
die(“Connection failed: “ . $conn_server->connect_error);
}
echo “Connected successfully”;
?>

8. Create database using php and mysql

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

Note after you create a database, you have to make new connection as step 9.

9. Make connection to database

// Create connection to database
$dbname = "myDB";
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conndb->connect_error) {
  die("Connection failed: " . $conndb->connect_error);
}

Use above connection ($conn) when you do below tasks.

10. Create Table using php and mysql

// sql to create table
$sql = "CREATE TABLE MyStudents (
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 MyStudents created successfully";
} else {
  echo "Error creating table: " . $conn->error;
}

11. then insert data into table you create

$sql = "INSERT INTO MyStudents (firstname, lastname, email)
VALUES ('Kamal', 'Perera', 'perera@example.com')";

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

12. Select data from table

$sql = "SELECT id, firstname, lastname FROM MyStudents";
$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";
}

13. Select and Filter Data With MySQLi

$sql = "SELECT id, firstname, lastname FROM MyStudents WHERE lastname='Perera'";
$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";
}

14. Update data

$sql = "UPDATE MyStudents SET lastname='Doe' WHERE id=1";

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

15. Delete Data

// sql to delete a record
$sql = "DELETE FROM MyStudents WHERE id=1";
if ($conn->query($sql) === TRUE) {
  echo "Record deleted successfully";
} else {
  echo "Error deleting record: " . $conn->error;
}

Next steps : you have to make separate files for separated PHP codes. Build HTML forms and use to take user inputs by name and get them to relevant php file via POST or GET methods. Save these values to php variables and used them for these discussed crud operations such as INSERT, SELECT, DELETE and UPDATE. we will discuss this later.

Thank you!