One of the things that often confuses people about true relational databases is that they are unable to do a one-to-many or many-to-many relationship directly. While many so called easy-to-use databases do offer field types that purport to offer such linking, they are problematic and do not fit into any sensible logical model for how things should be structured. Worse, each vendor's implementation (those who do implement it at all) is different and incompatible. However with a sensible schema design, this limitation really isn't a problem at all.

One such instance of this need to link one-to-many is the concept of linking a set with a model within WACS. In the easy case, you'd have thought that you'd simply put the model number into one of the fields in the set schema and the job would be done. But what do you then do when you have two models featuring in a set; easy you might say - one is the main model, the other is a secondary model, so just add a second field for the additional model and put the second number there. Of course that then makes the SQL query more complex each time as you've got to check both fields before you know if a model is in a set or not. It still might work, but it's already getting cumbersome. You might discover a set first by virtue of the additional model and only afterwards identify the official primary model.

Just about every adult site we've encountered does feature at least a few sets with three models, so suddenly we're looking at a second additional model field and having to check that as well. And believe me, there are a few sites of which Sapphic Erotica comes to mind in particular where sets with three, four, five or even six models in a single set are relatively common. Simply put, adding models to the sets table just doesn't scale. So we take the proper relational database approach and add an additional schema called assoc for associations which gives us these relationships. It's a very simple schema, basically containing a primary key, a model number and a set number.

The process of finding out who is in a set becomes very simple and straight forward - you simply search the assoc table for the set number you're looking at. If we're looking for who is in set no 123, we simply use the following SQL query:

select amodelno from assoc
where asetno = 123

We then merely loop through the results of the above query and each record we find is another model involved in this set. If we don't get any results returned, then there aren't any models associated with this particular set. Of course we probably want more than just the model number(s), but that too is relatively simple. Consider the following query:

select modelno, mname, mimage, mbigimage
from models, assoc
where modelno = amodelno
  and asetno = 123

This query simply retrieves the model details for each model who is involved with this particular set, one record at a time. Due to the way relational databases are engineered, this is actually a very quick and efficent process. The first line of the where clause does what is known as a relational join and establishes the necessary connection between the assoc and models tables necessary for what we're trying to do. Additionally it's a very logical and elegant solution that will cope with none, one, two, three, four or as many models as you like within a single simple action.

	Note
	Although we make use of the assoc table, we don't actually use any results from it - we don't need to - it has silently taken care of handling the connection we needed to make.

If we go back to our example program displaying sets, we can modify it to include this activity as a sub-routine. What we're going to do is to divide the right hand side of the output into the two cells, one with the title, and the other with the model(s) featuring in the set. The icon will remain on the left. First step is to add the rowspan attribute to the left hand side cell so the icon spans it.

        // start the row
        print "<tr><td rowspan=2 valign=top align=center>\n";
        // create the link for the icon

and in perl, it'll look very similar:

        # start the row
        print "<tr><td rowspan=2 valign=top align=center>\n";
        # create the link for the icon

The next step is to create a new function to handle the query to look up the entries in the assoc table. We're going to call this function simply getmodel and it'll take just one argument, the set number for which we want the model(s) details. It will return to us a potentially quite long string variable containing all the model names that matched surrounded by a link to each model's WACS model page.

	Note
	So long as we use a different cursor variable to the database routines we can quite happily run another query and loop through it's results while inside an outer loop looking at the results of a completely different query. This is where the whole concept of a cursor becomes really useful.

function getmodel ( $setno ) {
        global $dbhandle;
        global $wacs;
        $gmresult='';
        //                   0        1      2       3
        $modelquery="select modelno, mname, mimage, mbigimage ".
                    "from ".$wacs->conf_get_attr("tables","models").
                    ", ".$wacs->conf_get_attr("tables","assoc")." ".
                    "where modelno = amodelno ".
                    "  and asetno = ".$setno." ".
                    "order by mname ";
        $modelcursor=$dbhandle->query( $modelquery );
        // loop through the results
        while( $modelresults = $modelcursor->fetchRow() )
        {
                // do we need a divider?
                if( ! empty( $gmresult ))
                {
                        $gmresult.="<br>";
                }
                // add the model link
                $gmresult.="<a href=\"".$wacs->conf_get_attr(
                                "apps","wacsmthu")."/".
                                $modelresults[0]."\">";
                // add her name and close link
                $gmresult.=$modelresults[1]."</a>";
        }
        // return the complete string
        return( $gmresult );
}

and the same code implemented in perl looks like this:

sub getmodel( $ )
{
        my( $setno )=@_;
        my( $gmresult, $modelquery, $modelcursor, @modelresults );
        $gmresult='';
        #
        $modelquery="select modelno, mname, mimage, mbigimage ".
                    "from ".conf_get_attr("tables","models").
                    ", ".conf_get_attr("tables","assoc")." ".
                    "where modelno = amodelno ".
                    "  and asetno = ".$setno." ".
                    "order by mname ";
        $modelcursor=$dbhandle->prepare( $modelquery );
        $modelcursor->execute;
        # loop through the results
        while( @modelresults = $modelcursor->fetchrow_array )
        {
                # do we need a divider
                if( $gmresult ne "" )
                {
                        $gmresult.="<br>";
                }
                # add the model link
                $gmresult.="<a href=\"".conf_get_attr("apps","wacsmthu").
                        "/".$modelresults[0]."\">";
                # add her name and close link
                $gmresult.=$modelresults[1]."</a>";
        }
        # return the complete string
        return( $gmresult );
}

The final step of this process is to add into our main loop going through the retrieved set records a call to the getmodel function. This looks like:

        // next right hand cell
        print "<tr><td align=center><font size=-1>\n";
        print getmodel( $results[0] );
        print "</font></td></tr>\n";
        // increment set count 

and in perl this looks like

        # next right hand cell
        print "<tr><td align=center><font size=-1>\n";
        print getmodel( $results[0] );
        print "</font></td></tr>\n";
        # increment set count 

With these changes incorporated into the code, we now have the finished version of the setdisp program (setdisp4.php or setdisp4 in the samples directory. If we now copy this script up to the web server and run it, we should see something like this:

Once again we've gradually developed a program up to the point where it is now offering quite reasonable functionality and layout making use of the WACS programmers toolkit API. Hopefully this has given you an insight into what WACS is capable of and the basics of how to make use of it's API. In due course, we hope to have a respository of WACS skins, or mini-site scripts, which you can download and tailor to your own needs. If in the course of learning the WACS API you write some programs you'd be happy to share with others, please send them to us and we'll include them in the respository.