Chapter 2

Preface

In this chapter, you will be introduced to some more features of DBLIB. You will learn how to implement one-to-many table relationship and use filters.

Now we will create code to edit a database of car makers (brands). Each brand will have:

Unique ID
Name
Country, where it is located
Notes (free text)
Logo (picture)

EXAMPLE: You can find fully working code for this task in the example/cars/admin/brand where it is used to edit the brand table.

Header file

Most of the things is defined in the header file, so it is the longest. First we include all the necessary classes:

<?
  require_once("DBLMYSQLQuery.php");
  require_once("DBLView.php");
  require_once("DBLTextField.php");
  require_once("DBLMemoField.php");
  require_once("DBLLCField.php");
  require_once("DBLFileField.php");
  require_once("DBLLabelField.php");

Instantiate the view:

     $view =& new DBLView("brand");

Now we will define fields we use in the view. First will be the name field:

  $view->addField($name =& new DBLTextField("name","Name","",32,30));
  $name->allowEmpty = false;

The field may not be empty. Next we will set two more properties of it:

  $name->filterSize = 20

filterSize is size of the field in the filter. It is set to the field size given in constructor by default. Here we want the field to not be so big in the filter, so we set it to a smaller value.

Next we will define the country field. User will not enter the country directly - he will choose from the list of countries edfined in the country table. The country field in the brand table will be integer and it will store unique identifier of the country. This can be accomplished by using the DBLLCField. We have to first define a query, that will provide list of possible countries:

  $cq = new DBLMYSQLQuery($DBDEF,"country");
  $cq->setFields("id,name as text");
  $cq->setOrder("text");

The result of the query must contain two columns - id and a text (see DBLLCField reference for more information). We could also store the country name directly - the country field would be textual and we would do:

  $cq->setFields("name as id,name as text");

The setOrder call sets the choices to be ordered by the name.

Now we can define the field itself:

  $view->addField($country =& new DBLLCField("country","Country","",$cq));
  $country->allowEmpty = false;
  $country->indexDBConversion = true;

We have used allowEmpty before, so I suppose it is clear what it is for. The indexDBConversion property is more complicated. To display field country in the record index, you need to convert country id (which is stored in the brand table) to country name. You can either join tables so that the field is included in the SELECT result. Or you can convert the id to name on each row separately, by doing a lookup. It is not very efficient, but it is simple. This is what we turn on here.

The next field we define is logo. The DBLFileField provides a way to upload a file. It needs two extra arguments. The first one is a directory where to store the uploaded files. The second one is an URL where the uploaded files can be accessed:

  $view->addField($logo =& new DBLFileField("logo","Logo","","../../logos","/logos"));
  $logo->showAsImage = true;

We also set its property showAsImage. When the property is set to true, the field will show the file in the record detail/index in the IMG tag (as an image).

The notes field is just a free-form multi-line text. The DBLMemoField is suitable for editing such field (using HTML TEXTAREA widget):

  $view->addField(new DBLMemoField("notes","Notes","",60,10));

The two extra parameters are width and height of the textarea, in characters.

By default, user can delete record from record detail or edit form. But we wish to provide user way to delete records directly from a record index. We will create a DBLLabelField. This will not get data from database, but it will print a static text in each row:

  $view->addField(new DBLLabelField("delete","","&nbsp;","Delete"));

The last parameter is the text to be printed. Then we setup it as an delete field:

  $view->deleteField = "delete";

The name field will be a hyperlink to record detail:

  $view->detailField = "name";

Next we define formats. We will use more complicated format, than in the previous example. The format is basically consisted of field names separated by separators. LF (\n) separates table rows. Semicolon (';') separates fields in the same row. Moreover, you can add a number before a field name, separated by a comma (','). The number is number of fields the field should span. You can find detailed explanation of the format strings in the DBLView reference. Here I will only present the examples:

  $indexFormat = "name;country;delete";
  $detailFormat = "name;country\n2,notes\n2,logo";
  $editFormat = "name;country\n2,notes\n2,logo";
  $filterFormat = "name;country\n_apply;_order;_clear";

Index format contains only one row. (Although it is a bit wierd, even index format CAN have multiple rows, DBLIB supports it.) It contains the name and country fields. The last is the delete "field", which will contain the delete record link.

Detail and edit format are the same. On the first row, there is name followed by country. On the second row there is only one field - notes. It is wide, so it is set to occupy two field places. On the last row there is logo. There are no more fields, so it also occupies two places.

Filter format contains name and country on the first row. On the second, there are special "fields", which can be used only in filter.

The _apply field creates a "Set filter" button. Similary, the _clear field creates a "Clear filter" button. The _order field will print out a combo box in which the user can select the order of records in the record index. There is also an "reverse" checkbox, allowing reverse (descending) sorted records.

How the sort is defined? We will see it on the two following code lines:

  $orderDef = array("name");
  $defaultOrder = "name";

These lines define a list (array) of fields, by which the user should be able to sort the records. Please note, that these interpreted as names of view fields, not db fields. On the oposite, default order is passed directly into the DBLQuery::setOrder, so it contains name(s) of databaze fields - it can be even something like "country, name desc"

NOTE: It is also possible to define more complicated sorts defined in the order definition array. See DBLView reference for more advanced examples.

As the last thing in the header, we will create a query.

     $q =& new DBLMYSQLQuery($DBDEF,"brand");

Creating record index

Now we will look at the record index page (index.php).

First, we have to define view (include header.php) and to produce HTML page start (contained in header.html):

  require('header.php');
  require('../../header.html');

The filter form will be above the record index. So we print it now:

  $view->filter($q,$filterFormat,$orderDef,$defaultOrder);

To print the form, we pass filter format and order definition. But the filter() method not only prints record form. When a filter is set, it will also set conditions on the query and it will set selected (or default) record order. This is why we also pass the query and default order to it.

NOTE: You CAN have the filter form below the record index. But you have to call:

$view->applyFilter($q,$filterFormat,$orderDef,$defaultOrder)

before calling index() so that the filter will be applied before the record index is printed. (And call filter() after the call to index() to print the actual filter form.)

Finally, we print record index:

    $view->index($q,$indexFormat);

Then there is just a page end:

?>
<P><A HREF="../">&lt;&lt; Menu</A>
</BODY>
</HTML>

Record detail

Record detail is quite simple, so I will include it here in one piece:

<?
  require('header.php');
  require('../../header.html');

  $q->addCondition("id","=",$_GET["id"]);
  $view->detail($q,$detailFormat,$_GET["id"]);
?>
</BODY>
</HTML>

We just include necessary headers, set conditions so the first record found will be the one we want to display and call the detail() method. The we just finish the page. We pass query, detail format and record id to the method (as you may see, the id is passed to detail.php as a GET parameter). The detail() method will display record detail and all necessary hyperlinks - "back to record index", "edit record" and "delete record".

Record edit form

Record edit form is almost the same as record detail script:

<?
  require('header.php');

  $view->standardEdit($q,$editFormat,"../../header.html");
?>
</BODY>
</HTML>

Only the called method differs. Here we directly pass the name of the HTML header file.

Note also, that is is the same as the edit.php in the previous example, which edited the country table. Here you see, why I put most of the "table dependent" things like formats and query creation to the header.php. Most of the time (like now), you only edit header.php and the rest of the scripts is (almost) the same.

Deleting records

delete.php script for the brand table is the same as it was for country in the previous example. BUT we have to make sure, that country does not get deleted, when there are records, that refer to it. So we have to modify the delete.php script for country:

<?
  require('header.php');

  $id = intval($_GET["id"]);

  // Check if there are no brands with this country
  $q->execSQL("select count(*) as cnt from brand where country=$id");
  $r = $q->nextRecord();
  if ($r["cnt"]) {
    include('../../header.html');
    echo "ERROR: There are brands in this country!\n";
    echo "<P><A HREF=\"./\">&lt;&lt; Index</A>\n";
    echo "</HTML></BODY>\n";
    exit;
  }

  $q->addCondition("id","=",intval($_GET["id"]));
  $q->deleteRecord();

  Header("Location: ./");
?>

There is a check added, which looks for a record in the brand table. You can see use of another two methods of DBLQuery: execSQL is actually a DBLSQLQuery method (see class tree). It executes raw SQL command. It is sometimes (as in this case) simpler, to just execute raw SQL. Here you would have to set source to brand table, add condition lookup and then set them back (or create new query). Executing SQL does not change any source/condition/order/... settings. But note, that you HAVE to do input checking/escaping when calling execSQL, since it is really a raw SQL. So using it is less safe than other methods.

The other method we show here is nextRecord(). It gets next record from the result and stores it in an associative array.

Finally

Again, look at how the example actually works in the browser. Create record, try filter. You may try commenting out the

$country->allowEmpty = false;

line to see how DBLLCField implements "empty" values. Try filtering. Enter something in the name and press enter. See how the asterix is added after the entered name.

Also try defining global variable:

$DBLIB_DEBUG_SQL = 99;

DBLIB will then print all SQL commands it executes. BTW, when you set the variable to 1, execution will stop just after printing the first query.

Then read through the description of all classes in the reference, at least briefly. When you have finished reading, you should be able to code interfaces for most of the tables you will need. Try making some. Then read following chapters of this guide - they show some advanced techniques/features and also usage of some other field types. (I was not able to provide examples for all field types up to this point in the guide, but I thing it would be confusing to do so).

<< Previous chapter

Chapter index

Next chapter >>