PHP x MariaDB Forum (using infinityFree)
PHP version 8.3
MariaDB version 10.6
This is a personal project of mine to learn PHP. It is to be expected that:
- There are bugs
- Things are not optimized
- There are missing features
- It is unsafe
- There can be massive changes at any point
This project is currently under heavy development.
| Name | Description | URL path | File path |
|---|---|---|---|
| Home | List of all topics. | / |
/index.php |
| Topic | List of all threads within a given topic. | /topic/{slug}/{page} |
/topic.php |
| Thread | List of posts within a given thread | /thread/{slug}/{page} |
/thread.php |
| Profile page | Public user profile page | /user/{userhandle} |
/user.php |
| ACCOUNT | |||
| Settings | Allows the user to change various account settings | /profile or /profile/settings |
/profile/settings |
| Moderation | Allows users to moderate their own content (or moderators also other's content) | /profile/moderation |
/profile/moderation |
| Notifications | List of notifications that this user has received | /profile/notifications |
/profile/notifications.php |
The .config.php file is not included for obvious reasons. This file has to be manually added and shall contain all relevant information to connect to the database. Here the pattern used in my project:
<?php
return array(
"servername" => "{servername}",
"username" => "{username}",
"password" => "{password}",
"dbname" => "{dbname}",
);
The deleted columns of posts, threads and users adhere to the following logic:
| Value | Binary | Meaning |
|---|---|---|
| 0 | 0000 | Not deleted |
| 1 | 0001 | User deleted |
| 2 | 0010 | Mod deleted or Auto deleted (empty thread) |
| 4 | 0100 | Thread deleted |
| 8 | 1000 | Ban deleted/Self account deleted |
All soft deleted rows will be permenantly deleted after 60 days. This is executed for posts, threads and users with a 1% chance every time there a new row in the mod_history table is created.
This project uses geojs to figure out the origin of a country given a particular IP.
handle may only contain A-z 0-9 _ . and - and must be unique and between 4 and 16 characters (inclusive)
img_dir (img in the schematic) contains the path to the image directory, stored in src/images/profiles/. Be careful to make sure include this directory when making backups, as the images are not saved in the database itself.
clearance level is an integer.
| Value | Name | Powers (always includes all of the previous ones) |
|---|---|---|
| 0 | User | Can create threads and posts |
| 1 | Moderator | Can delete and restore posts |
| 2 | Moderator | Can delete and restore threads |
| 3 | Admin | Can ban and restore users |
| 4 | Admin | Can promote and demote up to 0 <= val < 4 |
| 5 | Super Admin | Can promote and demote up to 0 <= val < 5 and view all deleted items |
Note: Restoration of deleted posts/threads or banned users can only be done if your moderation level is higher or equal to the moderator that deleted the post/thread or banned the user in the first place.
Note: The Super Admin has a few more special rights, like pinning threads.
The id can be of any post, thread or user (indicated by the "type"). To avoid having polymorphic table queries, the summary column will contain a description (e.g. thread name or first 64 characters of the culprit's post) which, when clicked, shall reveal further information (dynamically generated via PHP).
The type column encodes for the following:
| Value | Meaning |
|---|---|
| 0 | post |
| 1 | thread |
| 2 | user |
The judgement column encodes for the follows:
| Value | Meaning |
|---|---|
| 0 | reported - unread |
| 1 | reported - read |
| 2 | deleted |
| 3 | deleted with threads (for banned accounts) |
| 4 | restored |
| 5 | restored with threads (for banned accounts) |
| 6 | demoted |
| 7 | promoted |
The reason column encodes for the follows:
| Value | Meaning |
|---|---|
| 0 | Spam |
| 1 | Inappropriate |
| 2 | Copyright |
| 3 | Other |
| 4 | Restored |
Slugs are automatically generated for threads (for categories they need to be manually configured) and can only be edited with database access. This is to avoid SEO issues.
The subscribed table enables users to subscribe or unsubscribe form threads. If there is no entry yet, users will be auto-subscribed if they post for the first time on a thread.
Notifications are created upon:
- Posting in a thread
- User promotion
- User Demotion
And deleted/updated upon:
- Moderation action
- Moderation undo
- Self deleted post
- Expired
For clarification, users will not be notified when:
- They post was deleted by a moderator
- Their thread was deleted by a moderator
- A thread they were a part of was deleted by a moderator
The types are:
| Value | Meaning |
|---|---|
| 0 | Post |
| 6 | Demotion |
| 7 | Promotion |
post_id and mod_id are stored in the assoc_id column.
The deleted makes sure that when moderators are changing the visibility of posts with undo/redo, it won't re-generate notifications but instead preserve the old one.
Notifications will be automatically deleted after 60 days. This is checked by 1% chance every time a notification is updated to be flagged as deleted or not.
Values 1 - 5 are reserved for future use if needed.
(These may not be up to date and will be modified in the future)
For all:
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";CREATE TABLE `users` (
`username` varchar(24) NOT NULL,
`handle` varchar(16) NOT NULL,
`image_dir` varchar(64) NOT NULL,
`posts` mediumint(8) UNSIGNED NOT NULL DEFAULT 0,
`threads` smallint(5) UNSIGNED NOT NULL DEFAULT 0,
`appearance` tinyint(1) NOT NULL DEFAULT 0,
`about_me` TEXT NOT NULL DEFAULT '' AFTER `appearance`,
`user_id` varchar(33) NOT NULL,
`password` text NOT NULL,
`clearance` tinyint(1) UNSIGNED NOT NULL DEFAULT 0,
`created` datetime NOT NULL,
`deleted_datetime` datetime NOT NULL DEFAULT current_timestamp(),
`deleted` tinyint(2) UNSIGNED NOT NULL DEFAULT 0
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;ALTER TABLE `users`
ADD PRIMARY KEY (`user_id`),
ADD KEY `username` (`username`),
ADD KEY `handle` (`handle`);
COMMIT;CREATE TABLE `sessions` (
`user_id` varchar(33) DEFAULT NULL,
`ip` text DEFAULT NULL,
`user_agent` text DEFAULT NULL,
`session_id` varchar(88) DEFAULT NULL,
`datetime` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;CREATE TABLE `categories` (
`name` varchar(32) NOT NULL,
`slug` varchar(32) NOT NULL,
`id` varchar(33) NOT NULL,
`description` varchar(128) NOT NULL,
`created` datetime NOT NULL DEFAULT current_timestamp(),
`threads` mediumint(9) UNSIGNED DEFAULT 0,
`posts` mediumint(8) UNSIGNED DEFAULT 0
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;ALTER TABLE `categories`
ADD PRIMARY KEY (`id`),
ADD KEY `slug` (`slug`);
COMMIT;CREATE TABLE `threads` (
`name` varchar(64) NOT NULL,
`slug` varchar(32) NOT NULL,
`id` varchar(33) NOT NULL,
`user_id` varchar(33) NOT NULL,
`category_id` varchar(33) NOT NULL,
`created` datetime NOT NULL DEFAULT current_timestamp(),
`deleted` tinyint(2) UNSIGNED NOT NULL DEFAULT 0,
`deleted_datetime` datetime NOT NULL DEFAULT current_timestamp(),
`posts` mediumint(8) UNSIGNED NOT NULL DEFAULT 0
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;ALTER TABLE `threads`
ADD PRIMARY KEY (`id`),
ADD KEY `threadcategory` (`category_id`);
COMMIT;CREATE TABLE `posts` (
`user_id` varchar(33) NOT NULL,
`post_id` varchar(33) NOT NULL,
`content` text NOT NULL,
`created` datetime NOT NULL,
`thread_id` varchar(33) NOT NULL,
`edited` tinyint(1) NOT NULL DEFAULT 0,
`deleted` tinyint(2) UNSIGNED NOT NULL DEFAULT 0,
`deleted_datetime` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;ALTER TABLE `posts`
ADD PRIMARY KEY (`post_id`),
ADD KEY `created` (`created`),
ADD KEY `thread` (`thread_id`);
COMMIT;CREATE TABLE `subscribed` (
`thread_id` varchar(33) NOT NULL,
`user_id` varchar(33) NOT NULL,
`subscribed` tinyint(1) NOT NULL DEFAULT 1
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;CREATE TABLE `mod_history` (
`mod_id` varchar(33) NOT NULL,
`culp_id` varchar(33) NOT NULL,
`id` varchar(33) NOT NULL,
`summary` varchar(64) NOT NULL,
`type` tinyint(3) UNSIGNED NOT NULL,
`judgement` tinyint(1) NOT NULL DEFAULT 0,
`sender_id` varchar(33) NOT NULL,
`reason` tinyint(3) UNSIGNED NOT NULL DEFAULT 0,
`message` text NOT NULL DEFAULT 'GENERIC',
`created` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;ALTER TABLE `mod_history`
ADD PRIMARY KEY (`mod_id`),
ADD KEY `id` (`id`),
ADD KEY `recent` (`type`,`id`,`judgement`,`created`);
COMMIT;CREATE TABLE `notifications` (
`notification_id` varchar(33) NOT NULL,
`read` tinyint(1) NOT NULL DEFAULT 0,
`sender_id` varchar(33) NOT NULL,
`receiver_id` varchar(33) NOT NULL,
`type` int(1) NOT NULL DEFAULT 0,
`thread_id` varchar(33) DEFAULT '0',
`assoc_id` varchar(33) NOT NULL,
`deleted` tinyint(1) NOT NULL DEFAULT 0,
`datetime` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;