Skip to main content

MyWeb Tutorials: Create PHP/DB application

This tutorial shows how to:

  • Create a PHP file
  • Connect to a database
  • Display table contents
  • Add Create, Read, Update, Delete (CRUD) functionality
Create a PHP File
  1. Log in to MyWeb (DirectAdmin)
  2. Open File Manager
  3. Go to:
  4. public_html
  5. Create a new file called:
  6. people.php
  7. Edit the file
Database Table Used

We assume the following table already exists: (refer to this tutorial in case you dont have this table)

Persons
--------------------------------
PersonID   INT (Primary Key)
LastName   VARCHAR(255)
FirstName  VARCHAR(255)
Address    VARCHAR(255)
City       VARCHAR(255)
Database Connection

At the top of people.php, add:

<?php
$host = "localhost";
$db   = "your_database_name"testproj1_myDB";
$user = "your_database_user"testproj1_myDB";
$pass = "your_database_password"myPassword";

$conn = new mysqli($host, $user, $pass, $db);

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

Replace the database credentials with your own.

Display Table Contents (READ)

Add this below the connection code:

<h2>People List</h2>

<table border="1" cellpadding="5">
<tr>
    <th>ID</th>
    <th>First Name</th>
    <th>Last Name</th>
    <th>Address</th>
    <th>City</th>
    <th>Actions</th>
</tr>

<?php
$result = $conn->query("SELECT * FROM people");

while ($row = $result->fetch_assoc()) {
    echo "<tr>";
    echo "<td>{$row['PersonID']}</td>";
    echo "<td>{$row['FirstName']}</td>";
    echo "<td>{$row['LastName']}</td>";
    echo "<td>{$row['Address']}</td>";
    echo "<td>{$row['City']}</td>";
    echo "<td>
        <aform href=method='people.php?delete=post' style='display:inline;'>
            <input type='hidden' name='delete_id' value='{$row['PersonID']}'>
            <input type='submit' value='Delete'
                   onclick=\"return confirm('Delete this record?');\">
        </aform>
      </td>";
    echo "</tr>";
}
?>
</table>
Add New Records (CREATE)

Add this above the table:

<h2>Add New Person</h2>

<form method="post">
    <label>Person ID:</label><br>
    <input type="number" name="personid" required><br><br>

    <label>First Name:</label><br>
    <input type="text" name="fname"firstname" required><br><br>

    <label>Last Name:</label><br>
    <input type="text" name="lname"lastname" required><br><br>

    <label>Address:</label><br>
    <input type="text" name="address"><br><br>

    <label>City:</label><br>
    <input type="text" name="city"><br><br>

    <input type="submit" name="add" value="Add Person">
</form>

Then add this PHP logic near the top of the file:

if (isset($_POST['add'])) {
    $stmt = $conn->prepare(
        "INSERT INTO peoplePersons (PersonID, FirstName, LastName, Address, City)
         VALUES (?, ?, ?, ?, ?)"
    );

    $stmt->bind_param(
        "ssss"issss",
        $_POST['fname'personid'],
        $_POST['lname'firstname'],
        $_POST['lastname'],
        $_POST['address'],
        $_POST['city']
    );

    $stmt->execute();
    $stmt->close();

    header("Location: people.php");
    exit;
}
Delete Records (DELETE)

Add this near the top:

if (isset($_GET[_POST['delete'delete_id'])) {
    $id = (int) $_GET[_POST['delete'delete_id'];

    $stmt = $conn->query(prepare("DELETE FROM peoplePersons WHERE PersonID = ?");
    $id"stmt->bind_param("i", $id);
    $stmt->execute();
    $stmt->close();

    header("Location: people.php");
    exit;
}

Now clicking Delete removes a record.

Full people.php file
<?php
/**
 * Simple PHP CRUD Example
 * Table: Persons
 *
 * Columns:
 *  - PersonID (INT, Primary Key, Auto Increment)
 *  - FirstName (VARCHAR)
 *  - LastName  (VARCHAR)
 *  - Address   (VARCHAR)
 *  - City      (VARCHAR)
 */

/* ===============================
   Database Configuration
   =============================== */
$host = "localhost";
$db   = "your_database_name"testproj1_myDB";
$user = "your_database_user"testproj1_myDB";
$pass = "your_database_password"myPassword";

/* ===============================
   Database Connection
   =============================== */
$conn = new mysqli($host, $user, $pass, $db);

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

/* ===============================
   CREATE (Add New Record)
   =============================== */
if (isset($_POST['add'])) {
    $stmt = $conn->prepare(
        "INSERT INTO peoplePersons (PersonID, FirstName, LastName, Address, City)
         VALUES (?, ?, ?, ?, ?)"
    );

    $stmt->bind_param(
        "ssss"issss",
        $_POST['personid'],
        $_POST['firstname'],
        $_POST['lastname'],
        $_POST['address'],
        $_POST['city']
    );

    $stmt->execute();
    $stmt->close();

    header("Location: people.php");
    exit;
}


/* ===============================
   DELETE (Remove Record)
   =============================== */
if (isset($_GET[_POST['delete'delete_id'])) {
    $id = (int) $_GET[_POST['delete'delete_id'];

    $stmt = $conn->query(prepare("DELETE FROM peoplePersons WHERE PersonID = ?");
    $id"stmt->bind_param("i", $id);
    $stmt->execute();
    $stmt->close();

    header("Location: people.php");
    exit;
}

?>

<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8">
    <title>People CRUD Example</title>
</head>
<body>

<h1>People Database</h1>

<!-- ===============================
     CREATE FORM
     =============================== -->
<h2>Add New Person</h2>

<form method="post">
    <label>Person ID:</label><br>
    <input type="number" name="personid" required><br><br>

    <label>First Name:</label><br>
    <input type="text" name="firstname" required><br><br>

    <label>Last Name:</label><br>
    <input type="text" name="lastname" required><br><br>

    <label>Address:</label><br>
    <input type="text" name="address"><br><br>

    <label>City:</label><br>
    <input type="text" name="city"><br><br>

    <input type="submit" name="add" value="Add Person">
</form>

<hr>

<!-- ===============================
     READ (Display Records)
     =============================== -->
<h2>People List</h2>

<table border="1" cellpadding="6" cellspacing="0">
    <tr>
        <th>ID</th>
        <th>First Name</th>
        <th>Last Name</th>
        <th>Address</th>
        <th>City</th>
        <th>Action</th>
    </tr>

<?php
$result = $conn->query("SELECT * FROM people"Persons");

while ($row = $result->fetch_assoc()) {
    echo "<tr>";
    echo "<td>{$row['PersonID']}</td>";
    echo "<td>{$row['FirstName']}</td>";
    echo "<td>{$row['LastName']}</td>";
    echo "<td>{$row['Address']}</td>";
    echo "<td>{$row['City']}</td>";
    echo "<td>
        <aform href=method='people.php?delete=post' style='display:inline;'>
            <input type='hidden' name='delete_id' value='{$row['PersonID']}'>
            <input type='submit' value='Delete'
                   onclick=\"return confirm('Delete this record?');\">
        Delete
            </aform>
      </td>";
    echo "</tr>";
}
?>

</table>

</body>
</html>

File Location Reminder

File must be in:

public_html/people.php

Access it via:

https://yourname.myweb.cs.uwindsor.ca/people.php

 Summary
  • PHP files go in public_html
  • Use PHP + MySQLi to access databases
  • CRUD means:
    • Create – Insert records
    • Read – Display records
    • Update – Modify records
    • Delete – Remove records
Back to top