ocPortal Tutorial: Manually editing your database with phpMyAdmin
Written by Chris Graham, ocProductsocPortal stores most of its data in a database, as almost all web applications do. This allows it to efficiently read and write data to a predefined structure, without requiring complex customised file formats to do so.
If something goes wrong with ocPortal/mySQL/the-server that leaves your database in a corrupted/intermediate state, you may need to manually edit the database contents to resolve the problem. Note that it is very rare for this to occur, as ocPortal is designed to be fault tolerant in common situations, but it can never fully protect against all situations such as those that might be triggered by a power-cut at a critical time, or a faulty disk.
Table of contents
phpMyAdmin as found in the Plesk hosting control panel
phpMyAdmin is powerful web application for managing databases and database contents.
Note that if it looks like corruption has happened due to a physical disk problem, then it is absolutely crucial you back up ocPortal (and anything else on the server) as soon as humanely possible and make sure the server gets a disk scan, and it necessary, a new hard disk. Disk issues tend to spread, and files that touch the damaged area are 'scarred': an initially small problem could quickly irreparably destroy all your data. ocProducts can not accept any responsibility for repairing problems resulting from hardware issues.
Browsing the database
Choosing a table to edit rows of
Choosing a row to edit
- an unknown value
- a non-calculated value
Editing a row
To find the ID for some ocPortal content, the best way is usually to find an ocPortal URL that points to a page that is set to view or edit that ID; the ID will be included as a part of the URL.
For example, in the URL "index.php?page=news&type=view&id=30", the ID is 30. By convention, when IDs like this are being used as keys, they are always given the field name 'id' in the database.
phpMyAdmin supports very user friendly features to browse the database tables, and to make changes. To browse a table, click the table icon to the left of the table names in the left hand frame, you can then browse and sort the table contents, and select rows for modification.
Choosing to execute an SQL query
To run a query, you need to click the 'SQL' tab once you have chosen a database to view/edit. You then type in the query. In phpMyAdmin, it is often easier to use the interface to make changes, rather than working out what query to type. Occasionally ocProducts might suggest a query that could help solve a problem, as it is easier for us to give the query, than to explain all the mouse-clicks required. The screen-shots shown an example for executing a query to delete an item of news.
Typing in the SQL query to execute
- INSERT INTO <table-name> (<field-name>,…) VALUES (<field-value>,…)
- UPDATE <table-name> SET <field-name>=<field_value>, … WHERE <key-field>=<key-value>
- DELETE FROM <table-name> WHERE <key-field>=<key-value>
- SELECT * FROM <table-name> WHERE <key-field>=<key-value>
ocPortal database structureThis is an advanced section, and you may wish to skip it.
At the time of writing, ocPortal uses 111 tables, or 123 when official add-ons are installed.
For the technically inclined, the database table structure is mostly in 4NF form, with the main exception being fields that are for caching purposes (such as post count) and other fields that remove the need for complex and slow "JOIN"'s or "EXIST"'s clauses.
A common cause of confusion is where text is located. ocPortal was redesigned in its second version to support content translatable into multiple languages; this feature was dropped, as it was used by few, gave efficiency problems, and primarily because it was very difficult for us to beta test (and we will not release any feature that has not been very thoroughly tested). Text is located in the 'translate' table, and linked into other places by language ID. The 'translate' table is also used to store parsed Comcode, which works out very cleanly. If an entry in the 'translate' table is being edited by hand, and has Comcode, then setting the text_parsed field to NULL will cause the Comcode to be re-parsed on demand.
Since ocPortal 2, ocPortal has been designed to work with many different databases, not just mySQL. We dropped this feature, because like multiple language content, we could not thoroughly beta test it, especially due to very strange and varying limitations and differences in different database systems. However, ocPortal still avoids using mySQL-specific features wherever possible. Instead of using highly specialist (if they exist at all) queries to analyse database table structure, for systems such as backup or OCF-member-id-migration, ocPortal actually stores database structure in the 'db_meta' table. ocPortal's own installation techniques for creating and changing database tables will properly update this table, and if modifications are being made, it is preferable that the db_meta table is updated to reflect them.
- Simple a system that stores structured information
- relational database
- A system that stores information in a very strict pre-determined structure based on set-theory
- A language for communicating with a database
- A free database system; ocPortal requires this
- An excellent web front-end to mySQL
- An instruction or request to a database
- The specification of the fields rows in a table use
- A collection of rows and the schema the rows fit
- An element of a row that may store a value, of a certain configuration
- A number of elements that together represent a single entry of some sort
- An identifier for a row, consisting of some pre-chosen (in the schema) combination of fields
- In ocPortal this is a numeric identifier associated with a row, and usually also a specific piece of ocPortal content