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
- Log in to MyWeb (DirectAdmin)
- Open File Manager
- Go to:
- public_html
- Create a new file called:
- people.php
- 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 = "testproj1_myDB";
$user = "testproj1_myDB";
$pass = "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 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>
<form method='post' style='display:inline;'>
<input type='hidden' name='delete_id' value='{$row['PersonID']}'>
<input type='submit' value='Delete'
onclick=\"return confirm('Delete this record?');\">
</form>
</td>";
echo "</tr>";
}
?>
</table>
Add New Records (CREATE)
Add this above the table:
<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>Then add this PHP logic near the top of the file:
if (isset($_POST['add'])) {
$stmt = $conn->prepare(
"INSERT INTO Persons (PersonID, FirstName, LastName, Address, City)
VALUES (?, ?, ?, ?, ?)"
);
$stmt->bind_param(
"issss",
$_POST['personid'],
$_POST['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($_POST['delete_id'])) {
$id = (int) $_POST['delete_id'];
$stmt = $conn->prepare("DELETE FROM Persons WHERE PersonID = ?");
$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 = "testproj1_myDB";
$user = "testproj1_myDB";
$pass = "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 Persons (PersonID, FirstName, LastName, Address, City)
VALUES (?, ?, ?, ?, ?)"
);
$stmt->bind_param(
"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($_POST['delete_id'])) {
$id = (int) $_POST['delete_id'];
$stmt = $conn->prepare("DELETE FROM Persons WHERE PersonID = ?");
$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 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>
<form method='post' style='display:inline;'>
<input type='hidden' name='delete_id' value='{$row['PersonID']}'>
<input type='submit' value='Delete'
onclick=\"return confirm('Delete this record?');\">
</form>
</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
Errors and Logs
During building of your PHP application, you may need to view the errors either on the webpage or via the log.
To view errors on your PHP page, add the following at the top of your PHP file:
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);Put them before any other PHP code.
Once fixed, remove the debug lines.
Or you can access the log file from "Site Summary/Statistics/Logs"
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