Home

Your premium source for custom modification services for phpBB

  logo

HomeForumsBlogMOD ManagerFAQSearch Log in

The phpBB Doctor is currently available for private clients only. At this time we are not accepting any new work until we can clear out our current backlog. Thank you for your patience.

  
phpBB Glossary
Common phpBB Terms
ACP
Avatar
BBCode
Board
Category
Forum
Topic
Post
Announcement
Sticky
Poll
Locked

Board Operations
Registration
Search
Moderators
Prune

Permission System
Overview
View
Read
Post
Reply
Edit
Delete
Sticky
Announcement
Create Poll
Vote

What is SQL?
SQL
DDL
DML
SELECT
INSERT
UPDATE
DELETE

Database Terminology
Entity
Relationship
Constraint
Cardinality
Primary Key
Foreign Key
Null
UNIX_TIMESTAMP

phpBB Database Techniques
DBAL
$db->
$db->sql_db()
$db->sql_query()
$db->sql_fetchrow()
$db->sql_fetchrowset()
$db->sql_nextid()
$db->sql_freeresult()
$db->sql_close()

 

Common phpBB Terms
ACP
Admin Control Panel aka where you configure your board.
Back to top
Avatar
A small icon displayed under the poster name on each post they have made.
Back to top
BBCode
An HTML-like set of tags for providing formatting for your posts. For more details, see the provided BBCode FAQ.
Back to top
Board
The highest level of organization. Your board is essentially your web site. Each board will have its own set of database tables. Note: you can run more than one board off of the same database as long as you use a different table prefix. There is an article in the Prescriptions forum that details how to do that.
Back to top
Category
The first level of organization: your board is divided into Categories. A category will contain forums, and is often used to group similar forums together. For example, at www.phpbb.com they have a Mods category and a Styles category. Each of these contains forums where folks can discuss different aspects of modding or styling their board.
Back to top
Forum
Forums provide the second level of organization for your board. Forums are the next more specific level within a category, and will contain the actual topics / posts / user activity on your board. For example, within the Mods forum at www.phpbb.com there is a Mods Request forum, a Mods Development forum, and a forum for Mod Authors. Each of these is supposed to be different enough that users will not have any difficulty figuring out where to post. It does not always work.
Back to top
Topic
Each line of discussion within a forum is a Topic. The first post defines the topic, and further posts are used to discuss the topic. The first post in a topic can determine the topic type (Announcement, Sticky, or Post) and whether or not the topic includes a Poll. In theory, the only limits to the number of topics on a board are database space and the precision (largest value allowed) for topic_id.
Back to top
Post
A post is where the user posts his or her thoughts on the topic. In theory, the only limits to the number of posts in a topic are database space and the precision (largest value allowed) for post_id.
Back to top
Announcement
A special type of post that appears at the top of every page of topics for a forum. An announcement is not subject to pruning.
Back to top
Sticky
A special type of post that appears in viewforum.php before standard posts. In other words, they are "sticky" because they stick to the top of the topic list. Sticky posts may be deleted by pruning.
Back to top
Poll
A poll is a topic that contains a number of selections to vote on. The permission to vote can be restricted, as can the ability to create a poll. A user can only vote once in any particular poll.
Back to top
Locked
A topic or forum can be locked, meaning no further activity can take place.
Back to top

Board Operations
Registration
By default anyone can read and post on your board. In order to manage your board better you may opt to require registration. A user can register by filling out a short form. At your option, you may require the user to confirm their email address prior to becoming active, or you may require a board administrator to activate the user account. Version 2.0.11 introduced a back-port of the visual confirmation system used in Olympus (phpBB 3.x) as an additional option for board administrators.
Back to top
Search
Getting people to post is great. Making those posts available to other user via a search process is even better. The phpBB search system is somewhat controversial, as it can put a heavy load on a server for larger boards. There will soon be an article on the phpBB Anatomy forum that details how the search process works, and some ideas on how to optimize it.
Back to top
Moderators

Back to top
Prune
The operation where older posts are removed from the board to save space. This operation may be done manually on the ACP, or automatically by configuring Auto Prune. Pruning does not reduce a user post count, although it does reduce the number of posts (topics) reported as board activity.
Back to top

Permission System
Overview
Options includes View, Read, Post, Reply, Edit, Delete, Sticky, Announcement, Create Poll, Vote, and Attach. Note that Attach is not used in the base phpBB installation. Permissions are based on a combination of Forums and Groups. Permissions are not based on an individual user in phpBB 2.x, although you can work around that by creating individual groups for each user. For more details, please see the phpBB Anatomy forum.
Back to top
View
User can see the forum listed. They cannot read any posts, but they can see the forum on the main index.
Back to top
Read
User can read the contents of the forum. View permissions are a prerequisite for Read.
Back to top
Post
User can post new topics or posts in the forum. Note that View + Read are prerequisites for posting.
Back to top
Reply
User can reply to topics or posts in the forum. Note that View + Read are prerequisites for Reply. Post, interestingly enough, is not. As a board administrator, you can create a forum where registered users may reply to existing topics but cannot post new ones. The Free MOD Support forum here at the phpBBDoctor is configured that way.
Back to top
Edit
With this setting enabled, users can edit their own posts. If there is no following reply, then no audit log is kept to record the edit. Once someone has replied to a post a normal user can no longer edit it without being logged. There are two fields in the phpbb_users table that record the last edit time (in unix_timestamp format) and the total number of edits.
Back to top
Delete
With this setting enabled a user can delete their own post as long as it is the last post in a topic. If it is the only post in the topic then the entire topic is deleted. Deleting posts (topics) reduces a user post count.
Back to top
Sticky
Generally only certain users (moderators or administrators) have the ability to post Sticky topics.
Back to top
Announcement
Generally only certain users (moderators or administrators) have the ability to post Announcements.
Back to top
Create Poll
Polls may be enabled for different user levels or different forums. Note that the ability to create a poll is separate that the ability to vote in a poll.
Back to top
Vote
By default you must be REGistered to vote in a poll. The standard phpBB code does not allow anonymous users (guests) to vote, as it records the user_id during the voting process in order to prevent multiple votes from the same person.
Back to top

What is SQL?
SQL
SQL stands for Structured Query Language and is pronounced "sequel" or optionally using the letters. This is the language used to work with relational databases. There are two types of language: DDL and DML.
Back to top
DDL
DDL is Data Definition Language and includes things like CREATE TABLE or CREATE INDEX. DDL is not commonly used in day-to-day operations of your board.
Back to top
DML
DML is Data Manipulation Language and includes the basic operations of SELECT, INSERT, UPDATE, and DELETE. If you interact with a database you are almost certainly using one of these four operations.
Back to top
SELECT
A SELECT statement is the workhorse of DML. Anytime you retrieve data from a database you are using this command. Selecting from multiple tables at the same time requires a JOIN clause that links the tables together. Joins are generally made using primary or foreign key values.
Back to top
INSERT
To add new records to your database you use an INSERT statement. Some modern databases are getting more sophisticated, and will allow you to insert into multiple tables using a single INSERT statement. Our practice is to avoid this technique, and only use INSERT statements on one table at a time.
Back to top
UPDATE
If you need to change a value in the database you UPDATE the data. For example when a new post is made the topic record is updated with the last post information. Each time a user posts their user record is updated with the new post count. Each time a vote is recorded for a poll the poll result is incremented.
Back to top
DELETE
Yes, occasionally you want to remove items from a database. Pruning removes topics and posts using the DELETE command. Some databases do not support an Undo option, so be very careful how you use this command.
Back to top

Database Terminology
Entity
An entity is an object or something that we record information about in our database. Entities in phpBB include Users, Forums, Posts, Topics... essentially anything that you can identify. Even words within a post are considered entities.
Back to top
Relationship
A relationship is something that we use to put entities together. A category contains forums. A forum contains topics. A user makes posts. A user is a member of a group. A user has a rank. These are all relationships between entities.
Back to top
Constraint
A constraint is a way for the database to maintain rules about relationships or about column data. For example, it would be impossible to track which forum a post belongs to if the forum_id was missing. So the database constraint forces that column to have a value. If supported, complex constraints can also be used to ensure that a post has an owning user, that a forum has a containing category, and so on.
Back to top
Cardinality
A relationship (see above) has a cardinality. The most frequently found cardinality in database designs is one to many, meaning that one of these has many of those. The \"these\" and \"those\" are the entities, and the \"has\" is the relatioship. A simple example? A user can have many posts. A user can also have zero posts. A Category has one or more forums. A forum has one or more topics. And a topic - by allowing a shadow to remain during a move - can actually have two forums. But this is accomplished by having two topic rows, each one assigned to a single forum.

Understanding cardinality and relationships is critical to doing any sort of database work.
Back to top

Primary Key
A unique value that identifies a row, often generated using a unique sequence of values that are system generated. The user_id, topic_id, post_id, forum_id... all are examples of a system generated primary key. This key value is also used to show relationships. For example, a user enters a post. To record that fact, their user_id is stored on the post record in the database. In a similar fashion, a forum belongs to a category, so each forum has a category_id to show its containing board category.
Back to top
Foreign Key
A value used to represent a relationship between entities. A user has a user_id as their primary key. As long as they are a member of the board, nobody else will ever have their user_id. That user_id can then be used to show \"ownership\" of other items. A user enters a topic, so their user_id is stored on that topic. A user enters a post, so their user_id is also stored there. When a primary key is stored with other information, it is called a foreign key.
Back to top
Null
When a table column allows undefined or missing values, it is said to be NULL. A null value is missing, therefore it cannot be equal to or different from any known value. It is simply NULL. For example, a forum must have a name, therefore the forum_name is NOT NULL. But a user is not required to have a signature, so the phpbb_users.user_sig can be missing. You may have noticed that if you do not have a signature defined, you do not have the ability to attach it to a post.
Back to top
UNIX_TIMESTAMP
Date/time values are stored in int(11) fields, which contain the date/time in a unix timestamp format. This format is defined as the number of seconds since January 1, 1970, and you may hear it called \"epoch time\" as well. By storing the number of seconds since a particular date date math becomes trivial. This format is also portable across essentially all databases, whereas date fields are often treated differently. In MySQL you can encode a date using the unix_timestamp() function and decode it using from_unixtime().
Back to top

phpBB Database Techniques
DBAL
phpBB uses a DBAL or Database Abstraction Layer so that they can write specific SQL syntax for all supported databases without too much code in the main php files.
Back to top
$db->
The DBAL is an object that is loaded and called by nearly every phpBB file. If you need to interact with the database, you will see this object being used. The next few FAQ entries will detail the various operations. Be sure to check out the phpBB Anatomy forum for a more detailed description of how to build and execute a query.
Back to top
$db->sql_db()
This function connects to the database based on the information stored in config.php. You should never have to call this function as the database connection is established in includes/db.php which is included in common.php which is included at the top of every phpBB php program.
Back to top
$db->sql_query()
This function prepares a query and validates the syntax. Note that for INSERT, UPDATE, and DELETE queries it also executes the query.
Back to top
$db->sql_fetchrow()
For SELECT queries this method is used to fetch the next row for the cursor.
Back to top
$db->sql_fetchrowset()
For SELECT queries this method is used to fetch the entire rowset for the cursor. Be very careful with this method. A large rowset will chew up quite a bit of server memory.
Back to top
$db->sql_nextid()
This method is used to retrieve the auto_increment value assigned by the database. In this way you can INSERT a new row, get the associated ID, and then insert other dependent rows. For example, you can get the topic_id and then assign that value to the associated posts.
Back to top
$db->sql_freeresult()
This method is not required. In fact, it will throw an error on MySQL 4.x on anything other than a SELECT statement.
Back to top
$db->sql_close()
This method closes the database connection and is called from includes/page_tail.php. Be very careful if you call this prior to then; you may shut off the connection before you are really finished.
Back to top

All times are GMT - 5 Hours

Jump to:  

HomeForumsMOD ManagerFAQSearch Register Log in
Not endorsed by or affiliated with the phpBB Group
Powered by phpBB © phpBB Group
Terms of Service
Web design by MomentsOfLight.com logo