SitePoint Sponsor

User Tag List

Results 1 to 20 of 20
  1. #1
    SitePoint Zealot
    Join Date
    Feb 2000
    Location
    xyz
    Posts
    158
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 :-)

  2. #2
    SitePoint Wizard TWTCommish's Avatar
    Join Date
    Aug 1999
    Location
    Pittsburgh, PA, USA
    Posts
    3,910
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    maximar,

    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.



    ------------------
    Chris Bowyer
    MyCoding.com: Join our mailing list for launch notification!
    "I'm not an insomniac, I'm a web designer."

  3. #3
    SitePoint Wizard
    Join Date
    Jul 1999
    Location
    Chicago
    Posts
    2,629
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    [/code]

    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.

  4. #4
    Your Lord and Master, Foamy gold trophy Hierophant's Avatar
    Join Date
    Aug 1999
    Location
    Lancaster, Ca. USA
    Posts
    12,305
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.

    ------------------
    Wayne Luke - Sitepoint Moderator/Internet Media Developer
    Digital Magician Studios - Making Magic with Web Applications
    wluke@digitalmagician.com

    [This message has been edited by wluke (edited June 22, 2000).]

  5. #5
    SitePoint Zealot
    Join Date
    Feb 2000
    Location
    xyz
    Posts
    158
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 "reviews" with columns "reviewID, title, date, requirements, releasedate, bodytext, writerID, publisherID, developerID, scoreID, linksID, pros, cons, conclusion"

    table "writers" with columns "writerID, firstname, lastname, email, location, position, bio"

    table "publishers" with "publisherID, URL, name"
    table "developers" similar to publishers

    "scores" with "scoreID, score"

    and finally:
    "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...

  6. #6
    psycho
    Join Date
    May 2000
    Location
    London
    Posts
    283
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i have exactly the same question maximar (PS - wicked site !).....

    "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..."

  7. #7
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    There's nothing stopping you from storing HTML code in your database for the article body text. This will be covered in Part 6 of my series.


    ------------------
    -Kevin Yank.
    http://www.SitePoint.com/
    Helping Small Business Grow Online!

  8. #8
    SitePoint Wizard
    Join Date
    Jul 1999
    Location
    Chicago
    Posts
    2,629
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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] )

  9. #9
    SitePoint Wizard
    Join Date
    Jul 1999
    Location
    Chicago
    Posts
    2,629
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm going to issue a speeding ticket to Kevin He's just so darn fast...man what are your tricks!? Do you get a notice of all posts?

  10. #10
    SitePoint Addict
    Join Date
    Feb 2000
    Location
    District of Columbia
    Posts
    373
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    CONTENT
    Article_id
    Type_id
    Author_id
    Text
    Post_Date

    TYPE
    Type_id
    Type_name

    AUTHOR
    Author_id
    Author_firstname
    Author_lastname
    Author_email
    Author_URL
    Author_bio

    And whatever else...

    HTH

    PS. And you can do cool stuff like let people submit their articles online and they'd just sit in the database until you approve them for going live on the site



    [This message has been edited by temis (edited June 23, 2000).]

  11. #11
    SitePoint Zealot
    Join Date
    Feb 2000
    Location
    xyz
    Posts
    158
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  12. #12
    Your Lord and Master, Foamy gold trophy Hierophant's Avatar
    Join Date
    Aug 1999
    Location
    Lancaster, Ca. USA
    Posts
    12,305
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    My markup code is in the templates. So the template might look like:
    <BLOCKQUOTE><font size="1" face="Verdana, Arial">code/font><HR><pre>
    &lt;p class='little'&gt;[rank] - [views] - [comments] - [review]&lt;/p&gt;
    &lt;h1&gt;[title]&lt;/h1&gt;
    &lt;p class='author'&gt;[author]&lt;/p&gt;
    &lt;p class='content'&gt;[Text]&lt;/p&gt;
    &lt;p&gt;[biography]&lt;/p&gt;
    [/code]

    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.

    ------------------
    Wayne Luke - Sitepoint Moderator/Internet Media Developer
    Digital Magician Studios - Making Magic with Web Applications
    wluke@digitalmagician.com

    [This message has been edited by wluke (edited June 23, 2000).]

  13. #13
    SitePoint Zealot
    Join Date
    Feb 2000
    Location
    xyz
    Posts
    158
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  14. #14
    SitePoint Addict
    Join Date
    Feb 2000
    Location
    District of Columbia
    Posts
    373
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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)

  15. #15
    SitePoint Addict
    Join Date
    Feb 2000
    Location
    District of Columbia
    Posts
    373
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Exactly what wayne said....

    try using style sheets to control the way all text is presented on your page, that way when you feed text from the database the stylesheet would automatically take care of it.

    [This message has been edited by temis (edited June 23, 2000).]

  16. #16
    Your Lord and Master, Foamy gold trophy Hierophant's Avatar
    Join Date
    Aug 1999
    Location
    Lancaster, Ca. USA
    Posts
    12,305
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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 &lt;CODE&gt; 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 &lt; or &gt; into the article text and don't put &lt;CODE&gt;&lt;/CODE&gt; 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 &lt;&gt;, much the same as UBB code works. I then replace all the &lt;,&gt; with & lt;, & gt; (no spaces of course) and do a second replace changing all the [ to &lt; and ] to &gt;. 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 &lt;A&gt; tags.

    ------------------
    Wayne Luke - Sitepoint Moderator/Internet Media Developer
    Digital Magician Studios - Making Magic with Web Applications
    wluke@digitalmagician.com

  17. #17
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    <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#/&lt;TABLE&gt;/#ec#/ tags!

    The PHP code that actually displays the article replaces /#c#/ with &lt;FONT FACE="courier,monospace"&gt; (or something similar), and /#ec#/ with &lt;/FONT&gt;. 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 (&lt; &gt; ) changed to the corresponding HTML entities (&lt; &gt; ). 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.


    ------------------
    -Kevin Yank.
    http://www.SitePoint.com/
    Helping Small Business Grow Online!

  18. #18
    SitePoint Wizard
    Join Date
    Jul 1999
    Location
    Chicago
    Posts
    2,629
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Kevin! You just gave me a good idea.

    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.)

  19. #19
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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.


    ------------------
    -Kevin Yank.
    http://www.SitePoint.com/
    Helping Small Business Grow Online!

  20. #20
    Your Lord and Master, Foamy gold trophy Hierophant's Avatar
    Join Date
    Aug 1999
    Location
    Lancaster, Ca. USA
    Posts
    12,305
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    All software development is in a high state of flux. There is never a finished application with perfect code and documentation.

    Its all a myth.

    ------------------
    Wayne Luke - Sitepoint Moderator/Internet Media Developer
    Digital Magician Studios - Making Magic with Web Applications
    wluke@digitalmagician.com


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •