Database Structure for a Homebrew CMS

[CSNM-Customer: teej]

I'm working on a php/mysql content management system for a local arts festival, one that I will hopefully be able to re-use for other projects.

On the database I have a 'pages' table, containing details of the static pages: ie 'title', 'strapline, 'content', 'pageID', and 'tag': a short description of the page's content/function ('home', 'travel' etc).

I also have 'photos', 'links', 'files', and 'contacts' tables to store addition material; I'm calling this 'support'.

My pages have a large central div for the main content and a narrower sidebar for this 'support', and I want the festival organisers to be able to assign the links, photos etc to the intended page's sidebar. Further, any data should be available on more than one page, and any page should be able to contain more than one item of data.

I've got one method sorted for this, but I'm not convinced it's the best.

I've added a 'page' field to each of the 'photos', 'links' etc tables: it's a SET with all the tags from the 'pages' table included as options. And I've seen it working so far. But I've read articles suggesting that using SET in this way is a sign of a poorly structured database. And so I'm concerned that maybe I'm setting up problems for myself in future.

I'm considering another option, this one cannibalised from Larry Ullman's PHP and MySQL for Dynamic Websites (p483 if you've got it: his 'url_categories' and 'url_associations' tables becoming my 'pages' and 'support' tables, respectively).

Here I would have an additional 'support' table, with fields for each of the data tables' IDs (photoID, linkID etc). For example when a new link is added, the ID that has been assigned to it on the 'links' table is also added to the 'linkID' field on the 'support' table.

Then, in another field - 'pageID' for the 'page' table's IDs - associations can be made between the data items and the pages they are intended for; the primary key here being the 'support' table's own auto_incrementing 'supportID' (if I read Ullman's intentions right, every pairing is thus a separate row).

I struggled a little to get my head around this one, which is due to my inexperience and lack of imagination, I think, whereas the first seems more straightforward.

But is this second one a more robust method? Should I persevere with it?

I hope I've explained this clearly enough,
cheers, Teej

[CSNM-Customer: teej]

Well, I'm sure I could have explained all that better.

I opted for the second choice, persevered, and have it up and running, so far so good. Now users can upload photos, files, links, news stories and contact details, and assign them to specific web-pages, via html/php forms in the back-end.

PHP code in the sidebar of the public pages selects any data assigned to the page and displays it with appropriate xhtml.

I'm now faced with another problem: the 'edit' pages - where users can change the data (eg, new phone number for a contact), and/or the web-pages assigned to it.

So this is an html/php form displaying the data already held in the database in editable fields (no problem there). Also all the available public webpages laid out in a grid of checkboxes (created by querying the 'pages' table on the database).

The problem is here: getting a 'checked' value added to the correct checkboxes, so users can see where data is already appearing.

This is the code that creates the checkbox layout:

// 'pageID' is the autoincrementing index, 'tag' is a one word description
$query = "  SELECT    pageID, tag
            FROM      pages
            ORDER BY  pageID ASC";       
$result = @mysql_query ($query);
  while ($row = mysql_fetch_array ($result, MYSQL_NUM)) {
  echo "
  <span><input type='checkbox' name='tags[]' value='$row[0]' 
  class='checkbox' />&nbsp;<label class='forcheckbox'>$row[1]</label></span>\n";
    }
  mysql_free_result( $result ) 

And this will select the webpages already chosen for the particular data, in this case, a link to an external webpage:

/* 'support' table cross references the indices of the various data tables
with the index of the 'pages' table, and gives each association a 'supportID' 
- $uid is the ID of the data being edited, passed in the url 
(eg editLink.php?uid=19 */

$query = "  SELECT  page_id
            FROM    support
            WHERE   link_id=$uid"; 

I've tried combining the two with joins, but I either get too many checkboxes or too few, I'm thinking that this can't be done with joins. So now I'm trying to split the 2 parts up, and then bring them together. My idea was to insert a $status variable into the checkbox code and have it return either 'checked' or '', but I can't figure out how.

This is probably the nearest I got:

// this one selects only the pages assigned to the data, but 'checks' them
    $query = "    SELECT        pageID, tag, page_id
                FROM         pages
                LEFT JOIN    support
                ON            pages.pageID=support.page_id
                WHERE        support.link_id=$uid
                ORDER BY     pageID ASC";        
    $result = @mysql_query ($query);
    while ($row = mysql_fetch_array ($result, MYSQL_NUM)) { 
                                                       
    if ($row[2] !=  $row[0]) {
        $status = ''; }
    else { $status = 'checked'; }
echo "
<span><input type='checkbox' name='tags[]' value='$row[0]'  class='checkbox' 
$status />&nbsp;<label class='forcheckbox'>$row[1]</label></span>\n"; 
    }
    mysql_free_result( $result ) 

Any ideas? In short, I'm lookin for a checkbox list of all pages, with the appropriate ones already checked.

[CSNM-Customer: teej]

Well, I was about to give up on this, but I've finally cracked it, with some help from Larry Ullman's PHP and MySQL for Dynamic Websites:

// Retrieve the URL's current information.
$query = "    SELECT         url, title, description, page_id 
            FROM         links AS l
            LEFT JOIN    support AS s
            ON            l.linkID = s.link_id
            WHERE         l.linkID = $uid    ";    
            
$result = mysql_query ($query);

// Get all of the information for the first record.
$exist_pages = array(); // Reset.
list($url, $title, $description, $exist_pages[]) = mysql_fetch_array ($result, MYSQL_NUM);

// Get the other page_id values.
while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
    $exist_pages[] = $row[3];
}

// Then some html to start the form, and this...

  // Create the checkbox grid.
    $query = "    SELECT  pageID, tag
                FROM     pages ORDER BY pageID ASC";        
    $result = @mysql_query ($query);
    while ($row = mysql_fetch_array ($result, MYSQL_NUM)) {
        echo "<span><input type='checkbox' name='tags[]' value='$row[0]' class='checkbox'";
        // Make sticky, if necessary.
        if (in_array($row[0], $exist_pages)) {
            echo ' "checked"';
        }
        echo " />\n&nbsp;<label class='forcheckbox'>$row[1]</label></span>\n";
    }
  
  // Store the required hidden values.
        echo '<input type="hidden" name="exist_pages" value="' . urlencode(serialize($exist_pages)) . '" />
        <input type="hidden" name="uid" value="' . $uid . '" />';