31 Mar The Bulletin Board Uses a Database to Save Posts
{module [135]}Implementing the Bulletin Board within the Joomla Content Management System (CMS) was a near-trivial exercise, given that Joomla itself is implemented having all content (articles), users and other support structures managed using a sophisticated database management system (DBMS) called MySQL. MySQL is a mature, well-documented, well-supported and well-known DMBS. It costs nothing and operates on Linux operating systems the world over. Its lingua franca is called Structured Query Language, or SQL.
At this point it is recommended that the interested reader for whom database technology is a complete unknown take some time to become acquainted with relational database terminology. If you have used a spreadsheet program like Microsoft Excel, you have already grasped the concepts of rows and columns. You will see that a relational database is somewhat akin to a set of spreadsheet tables that can be linked together to form more complex tables and tell you more about the data they contain. Indeed, to those who have little knowledge of database technology, the information that can be pulled from a datatabase can seem like magic! Wikipedia has a fairly thorough description, and there are other resources on the web that are even more elementary. Do a Google search for “relational database tutorial for beginners”.
With a basic understanding of relational databases, you are equipped to scratch the surface of SQL to see how data are retrieved, added, deleted, and updated using SQL. Again, there are vast quantities of information about SQL on the web; a good tutorial source is W3Schools.
It is essential that an interested reader acquire some rudimentary knowledge about databases and how to use them if he wants to understand anything about a modern website’s operation. External sources like those in the end notes provide a means to acquire this understanding – rudimentary, not expert-developer understanding – to follow the discussion in this series of articles. Key terms: Database, Tables, Rows, Columns, Keys/Relational Keys.
To reassure the reader that SQL is not that difficult to read (at least), consider the following valid SQL statement:
SELECT * FROM FHAbb ORDER BY postDTS DESC LIMIT 10;
This statement simply tells MySQL to retrieve (“SELECT”) all columns (“*”) from the database table “FHAbb”, and sort (“ORDER BY”) the rows by the column named “postDTS” (the date-time stamp of the posting) in descending order (“DESC”), but return only the first ten rows (“LIMIT 10”). SO: not rocket science.
Exercise:
If you have administrator privileges you can run this SQL yourself: login to the FHA website, and inder “Admin” find “Query a Database” and click it. Change the select box labeled “Limit results to:” to “No limit”. Paste the SQL statement into the box labeled “Or roll your own” and click “Run Query”. The query results appear below.
Change “*” to read “postName, postEmail, postText” so query reads
SELECT postName, postEmail, postText FROM FHAbb ORDER BY postDTS DESC LIMIT 10;
Click “Run Query” again. What changed?
What would you do if you wanted to retrieve 25 rows?
Note: Conventionally I capitalize SQL key words like SELECT, FROM, ORDER BY, and LIMIT. They would be recognized in either upper or lower case. The names of columns and tables ARE case-sensitive and must appear in the SQL statements as they are defined in the database.