Home » 2005 » February » 15

Daily Archives: Tuesday, February 15, 2005

The Most Versatile VoIP Provider: FREE PORTING

ISP-In-A-Box: The $500 Mac mini (Chapter IX, Building a Data-Driven Web Site: Part II)

Yesterday, we installed a data-driven web site on your new Mac mini as part of our ISP-In-A-Box project, but it was the contents of my web site (see inset). So today, let’s finish your site by filling it with your content. First, we’ll cover replacing the Nerd Vittles image (see inset) with either your own photo or Google Ads. Then I’ll give you a brief tutorial on the structure of this data-driven web site and how to modify it with PhpMyAdmin. And you’ll be off to the races.

If you’re new to the computing world and short on money, one of the must-have photography tools for your new Mac or PC is Photoshop Elements. Lucky for you version 3 has just been released. But, if you don’t have $70, not to worry. You can get version 2 which runs on both the Mac and PC platforms (same CD) for about $20. I still use version 2. Works great! Just a little less (ful)filling. To replace the Nerd Vittles image in the web site, just create your own image (728 x 90 pixels), Save For Web as a JPEG image, and name it nerd.jpg. Now drag it into the /Library/WebServer/Documents/nerd folder and replace the existing nerd.jpg file. Refresh your browser display at http://localhost/nerd to see your finished product. Take a look at the one we did for our home page at mundy.org if you need ideas. Or, if you’d prefer Google Ads (see sample site), then edit index.php in /Library/WebServer/Documents/nerd folder using TextEdit, search for nerd.jpg, and delete everything between the CENTER codes surrounding it. Now paste your Google 728 x 90 Leaderboard ad code between the CENTER codes to complete your page. Save the document, and you’re all set.

Now let’s customize your new web site with your own content. First, take a look at the site as it looks today. Use http://localhost/nerd if you’ve already completed Part I. Otherwise, just look at mundy.org. There are four columns of site links: a favorites column on the left and three others which are subdivided into topics. As already mentioned, all of the data to generate this site is stored in a single MySQL table. So, to change the content, all we have to do is edit the MySQL table and plug in your own links. The only trick to this is an organizational one. Before beginning the editing process, you need to lay out on paper what site links you want to include on your web site. You probably don’t want my kids’ email links. In laying out your site, you also need to come up with the categories into which you will place your various site links. Also keep in mind that you can have just over a dozen favorites in the left hand column, and the remaining columns all need to have approximately the same number of site links. Fifty is a pretty good ballpark number for site links in each column, and those should be subdivided into about four topics per column. You can always start with less and add more later. Just divide them up evenly so they can be spread across three columns. If you haven’t figured it out yet, this is the hard part. Making the adjustments to the MySQL table using PhpMyAdmin is pretty easy. Once you finish this project, I hope a few of you will be brave enough to post a comment below with a link to your site so we can all take a look and give you a little friendly feedback.

While laying out your site on paper, you should have come up with the subheadings for each section, and you should have defined which sections will go in which column and in what order. Now let’s number the columns (1 through 4) and the subheads in each column starting with 1. You can start the subhead numbers over with 1 in each new column. Finally, number the site links in each category starting with 1. If you have more than 9, use the alphabet after 9. Then start the site links over with 1 for each new subhead. On another piece of paper, let’s make a chart with seven column headings: Web Site Name (subject), Web Site Address (url), Description (comments), Column Number (category), Subhead Sequence Number (majorsort), Subhead Title (majorcateg), and Sort Sequence within Subhead (sortcode). The headings in italics are the actual field names in the MySQL website table. The headings in bold identify what information goes there. Having a description for any or all entries is optional. The other fields all are required. Now fill in the chart with your information. And, yes, I know the field names could have been more helpful, but the margaritas sure were good the night I originally designed this. Now, just so you’ll know, when index.php builds your data-driven web site (on the fly), it sorts all of the information in the website table by column number (category), then subhead sequence number (majorsort), then subhead title (majorcateg), then sort sequence within the subhead (sortcode). The only real gotcha is to make sure all of your subhead titles (majorcateg) are the same within a given subhead sequence number (majorsort), or you get extra subheadings in your page. It’s still no big deal. Just edit the table again to fix it.

Now that we’ve got our data squared away, let’s open PhpMyAdmin and get started: http://localhost/php/. Click on the Database pull-down in the left frame and choose mundy. Now click on the website table under the mundy database. Information associated with the Structure tab of the website table should appear in the right frame. Let me just note here that there is a bug in the frame code of PhpMyAdmin that causes the right frame to open in the left frame once it awhile. That doesn’t work too well because there’s insufficient real estate in the left frame to see what you’re doing. Don’t get frustrated if this happens. Just close the PhpMyAdmin browser window and then reopen it, and all will be well.

Once the structure frame appears in the right frame, we’re going to build a quick index so you can see how the data actually lines up in the website table to match our current web site. Look in the Indexes section of the right frame and find the line that begins: Create an index on 1 columns. Click on the numeric field (with the 1) and change it to a 4. Then press the Go button. Name your new index MyIndex. Leave the index type set to Index. Then use the four pull-downs to specify the index fields in the following order: category, majorsort, majorcateg, sortcode. Now click the Save button. You should now see your MyIndex index in the listing of indexes. Make sure the index order is correct. If not, click the red X beside MyIndex to delete it, and try again. Now click on the Browse tab at the top of the right frame to bring up the table browsing window. Just above the data is a line which reads: Sort by key. Click on the pull-down, choose MyIndex (Ascending) and click the Go button. The website table should now be sorted in the same order as the display of web links on the actual web page at localhost/nerd. Using the tab feature of your browser, you can flip back and forth between the web site and the website table to verify that they do, indeed, match. Notice also that the first three records have the same sortcode. This is an easy way to adjust the placement of a newly added entry (to move it further up your list) without having to renumber every entry. The last thing we want to do before we begin editing the actual table is to expand the table display to include all of the records in our file. Just above the table data is a line which reads: Show: 30 rows starting from record # 30. Change the first 30 to 300, and change the second 30 to 0. Then click the Show: button. Now the entire website table is displayed just as it appears on your current web site. As you make changes to your web site now, my advice is add or change a few records at a time, and then refresh your web page and make sure it looks and works the way you expected. A tabbed browser is great for this exercise by the way so kiss Internet Explorer goodbye (if you haven’t already).

You have two choices now. You can either delete some or all of the existing records and then insert new records of your own, or you can edit the existing records to replace the existing content with your own data from your chart. I’d recommend the latter at least until you know what you’re doing. To edit a record, click on the Pencil icon just to the left of the red X for the record you want to edit. To delete a record, you click on the red X. If you leave the existing records, then you won’t have to worry about the majorsort, category, and sortcode fields for the time being. And don’t ever change the recno (record number) field which must remain unique. Just change the majorcateg data to match whatever subheads you have chosen for your own web site, and plug in your own data for the subject, url, and comments fields. Once you’ve made your changes to a record, click the Go button to save your data and move back to the table browser window. Wait until you have finished editing and deleting existing rows in the table before you add new ones because clicking on the Insert tab clears the settings for your table browser window. When you’re ready to add new records, just click on the Insert tab at the top of the right frame. Leave the recno field blank, and MySQL will automatically fill it with a new, unique sequence number. Click the Go button to save your new record once you fill in the information for each field. That’s all there is to it. Enjoy your new web site … and let us all know where to go look for it!