I just finished reading the latest installment of the PHP/MySQL tutorial. I think it's now time for me to go beyond the jokes example, and start thinking about how to apply all these neat things to my own site. Although I understand everything perfectly on the small scale of a joke database, my mind nearly overloads when I even try to think about putting all my site's content in a database.
My site is at www.adventuregamer.com This is the old design, and I am working on a new one which will hopefully be database-driven. (Well, that's what I am aiming for ;-) As you can see, this website is mainly a magazine about a genre of computer games, and all the articles are devided in sections like reviews, previews, features, interviews, etc. Do you think I should make a table for each article type? The only values that would be required for all articles regardless of type are the title, writer, date, and the text itself. But each type of article also has its own data, such as expected release date (for previews), system requirements and score (for reviews), etc. So maybe I should list all articles in one table and use one of those lookup tables to assign the more specific fields to them? Also, how should I put the body text of the articles in the database? Sometimes the body text contains links, or HTML markup, so how would I go about putting that in without any problems?
I'm clueless, please give me some nudges in the right direction! Once I have a start, I think I can easily expand the database. But right now, it's all too much for me :-)
You should make a table for each layout type you'll have...meaning, if the navigation bar or the graphics or layout at all will be different on certain areas of the site, they should probably have their own table.
I'm going to have to deal with reviews, previews, and possibly interviews (now that I got reminded!) and I'll show you what I did.
<BLOCKQUOTE><font size="1" face="Verdana, Arial">code/font><HR><pre>
//More specific fields
I_REVIEWRATING INT , //If this is a review, the rating it recieved overall, 1-100
T_PRODUCTPRICE TEXT , //If this is a review, the price of the product
T_PRODUCTMFG TEXT , //If this is a review, the name of the company who makes the product
T_INTERVIEWEE TEXT , //The interviewee's name if this is a review
Basically, then my output script will check for $topic and if it is say 9, then it will know that it needs to display a review. It'll get the rating and put it in big letters. Also it will be possible to sort by and display the rating next to the product on a listing of all reviews.
I have 4 tables, by the way: content, pages, authors, and news. There's no need to have tables reviews, interviews, articles, previews, etc., just a main content table.
I am going a different route in my ongoing database development. Right now I have categories and under those categories can be 2 things, either articles or resources. Both are essentially the same thing and stored in the same table. Articles are hosted on my site and will rarely change so they can be cached as pseudo-dynamic documents. Resources are hosted on someone else's or even another site altogether, these pages will always be dynamically created as a paged record set.
Since Articles and resources will have different page layouts and creation methods, I have included templates within the database. These actually contain the code for the layout of the individual record using a replacement scheme and a "proprietary" Markup language based on a similar concept to UBB Code. Each category has a template assigned to it and when pages assigned to that category are built the templates are included.
d3v: this is an approach I thought about too. However, wouldn't this result in lots of open fields? For instance, let's say 30% of your articles are reviews, then in 70% of the rows values like price and rating will be left open.
I took TWT's advice to approach things section by section. I drew a schematic of the reviews section just a minute ago and ended up with 6 tables:
table "publishers" with "publisherID, URL, name"
table "developers" similar to publishers
"scores" with "scoreID, score"
"linklookup" with "linksID, siteID"
and "links" with "siteID, name, URL"
(The last two tables would be a many-to-many relation, since I want to have some related links associated with more than 1 article)
What do you think of this approach so far? I still have no clue how to put the body text of the articles into the database. For instance, how do I tell the database where the paragraphs begin? And what if I want to link some words in the text? I can't do that kind of markup in the PHP templates...
SitePoint (as well as most other places and myself) will be storing HTML in the field for the content, not special markup like Wayne is. I find this much easier and faster.
It also helps to instead of writing in Microsoft Word to use Notepad or another text editor so you don't have to keep copying and pasting individual paragraphs into the content textbox on your web-bassed admin...just because Word is "smart." (i.e. Whenever someone copies a paragraph out of MS Word into Internet Explorer let's assume the user wants [x] )
I wouldn't even mess with different tables for articles, reviews, and all that. I'd do one table for content, one table for type, primery key int the type table type_id will be a foreign key in content table. For info on who the article belongs to (or a review or something else) I'd create a table called authors with all their info. You just have to be careful with writing your SQL - multiple table joins can be tricky.
SO in short something like this:
The article submission/approval doodah would be GREAT to have automized. It would save me LOADS of time!!
Temis: the problem with your suggested db layout is that each type of content (review, interview, whatever) needs different additional information. For example: a review needs a place for the score in the db, but other content doesn't need scores.
My markup code is in the templates. So the template might look like:
<BLOCKQUOTE><font size="1" face="Verdana, Arial">code/font><HR><pre>
<p class='little'>[rank] - [views] - [comments] - [review]</p>
The code would then replace each [FIELD] with the appropriate information from the database. The template then gets stored in an appropriate Template field, attached to the category. Each category has 3 template fields, Display, Print and Search. Each only shows appropriate information.
This way I can have different layouts on different types of articles, reviews, what not. Each author also has its own record which includes name, company, email, URL, and a biography.
This enables me to have multiple Authors in the system. They can post articles, reviews and resources (or CONTENT) in multiple categories. Each CONTENT record can then be formatted as necessary depending on the template being used for the category.
This is the basis of a commercial content management system that I plan to market under the GNU Public License and charge fees for support and installation. This means the code is free until you ask me for help outside the limited free help I will provide via email (5 emails). It is also being developed in ASP and will be compatible with MS-Access and SQL Server. I also plan to port it to PHP/MySQL in the last quarter of this year.
Cool idea. What we were referring to though, was text markup within the actual body of the article. An example of this is the code in SitePoint's articles, which is done in Courier New instead of the standard font.
You can always create extra columns in the table that would only be used by specific types, and left blank (null, 0) for the rest of them. The only problem that could create is you will end up with a table with lots of blank spaces. Its up to you - I am guilty of doing this in several places myself... Or you can add one column that would indicate whether that particular record needs a score or not (yes/no or 1/0), and point it to a scores table.
I am working on the approval system right now. (On one of my most recent site I took NewsPro's functionality plus a few more gadgets specific to requirements and coded it in ColdFusion. An approval system is one of them)
My system handles html code in the actual article content as well. I am also thinking about adding color-coding to the actual code delineated by the <CODE> tag. I know more work and not needed for every purpose but it would make a cool add-on module.
The only problem I found though is that when you enter < or > into the article text and don't put <CODE></CODE> around it, Internet Explorer will start trying to parse all the code. For this reason I will be parsing the contents before display. During input the specification requires actual HTML tags to be delineated by  instead of <>, much the same as UBB code works. I then replace all the <,> with & lt;, & gt; (no spaces of course) and do a second replace changing all the [ to < and ] to >. The last thing it does is search the text of the content for email and website URL's and make them into proper links by surrounding them with <A> tags.
<BLOCKQUOTE><font size="1" face="Verdana, Arial">quote/font><HR>What we were referring to though, was text markup within the actual body of the article. An example of this is the code in SitePoint's articles, which is done in Courier New instead of the standard font.<HR></BLOCKQUOTE>
Actually SitePoint uses a simplified custom markup language for in-content formatting. To define inline code, for example, I would do the following:
Don't forget to close your /#c#/<TABLE>/#ec#/ tags!
The PHP code that actually displays the article replaces /#c#/ with <FONT FACE="courier,monospace"> (or something similar), and /#ec#/ with </FONT>. This allows us to change the look of all code fragments just by modifying the PHP code.
There are other tags for headings, page breaks, links, etc. Any HTML tags appearing in the content have their angle brackets (< > ) changed to the corresponding HTML entities (< > ). This makes sense for us because a large fraction of our articles contain sample HTML code, and converting all the tags by hand would be a very time consuming process.
Is there any chance of open-sourcing the content system behind SitePoint? Even if people weren't allowed to use it, just see the source, it would still be interesting and would spark many new high-quality sites. Personally I want to see how you solved problems like multiple paged articles, related forum, and related articles. Just wondering...
(Soon we'll be seeing article.php?aid=#&pid=# instead of article22.html around the Web from people who followed your tutorial.)
If I don't have time to explain how everything on SitePoint works by the end of my series, feel free to ask again and I'll be glad to go into detail.
The code for SitePoint.com is actually in a state of flux in many areas. Initially designed for a fully dynamic site, we are slowly moving the code towards a semi-dynamic system (which will be covered later in my series) to reduce server load. There are also plans afoot to convert the database to XML and so forth.
The upshot of all this is that several parts of the SitePoint code are a little too patchy for general consumption, and other things could be done in better ways that we just haven't gotten around to implementing.
SitePoint.com is constantly being improved behind the scenes. I'd love to say we'll someday have something that works perfectly and is well documented, but that is probably an unrealistic objective.