One of the most common needs of any website is to list data. The wtkBuildDataBrowse PHP function makes it extremely easy to do this.

As an example of the simplest use, these few lines create a fully functioning page by calling wtkFillBrowsePage which is a wrapper for both wtkBuildDataBrowse and wtkMergePage.

<?php
$gloLoginRequired = false;
require('wtk/wtkLogin.php');
 
$pgSQL = 'SELECT `FirstName`, `LastName`, `City` FROM `wtkUsers`';
wtkFillBrowsePage($pgSQL);
?>

Full Tutorial Video

This assumes you have `UID`, `id` or `GUID` in your SQL query because that value will be passed to the Edit or Delete page for determining which row to affect. The library is smart enough to use whichever one you put in the SELECT query. If your unique identifying column name is not one of these three, then to make the wtkBuildDataBrowse work properly you will need to alias it. For example:

SELECT `pet_id` AS `UID`, `pet_name`
  FROM `pets`

To add an Edit button all you need to do is define where you want the Edit link to go in this variable: `$gloEditPage`.

With the WTK library, usually the Add page uses the same PHP page as the Edit page. So although you can have the Add button go anywhere, most of the time you will define them as such:

$gloEditPage = '/demo/petEdit';
$gloAddPage  = $gloEditPage;

To add a Delete button to your list, you simply put the table name. If you are using the logic of having a `DelDate` column in your table and instead of truly DELETEing data, you set the `DelDate` to NOW(), then you can append after the table name “DelDate” and the WTK delete function will properly handle everything.

For example, in the `pets` demo table we use the `DelDate` logic so the delete functionality is defined as:

$gloDelPage = 'petsDelDate'; // have DelDate at end if should DelDate instead of DELETE

If you want more buttons this is easily done by assigning them in the $gloMoreButtons PHP variable. Similar to Edit and Delete buttons, this assumes you have `UID`, `id` or `GUID` in your SQL query because that value will be passed to the page for accessing.

These extra buttons will be put to the between the Edit button and the Delete button.

$gloMoreButtons = array(
                'User Logins' => array(
                        'act' => '/admin/userLogins',
                        'img' => 'beenhere'
                    ),
                'User Updates' => array(
                        'act' => '/admin/userUpdates',
                        'img' => 'assignment'
                    ),
                'Send Invite' => array(
                        'act' => '/admin/sendInvite',
                        'img' => 'send'
                    ),
                'Send Password Reset' => array(
                        'act' => '/admin/sendResetPW',
                        'img' => 'send'
                    )
                );

In the above example, the 'User Logins' will be the hover-over title on the button. The 'act' value defines the page which will be gone to. For example, the 'User Logins' button will go to the userLogins.php page in the /admin/ folder. The 'img' determines the MaterializeCSS icon which will be used. In the 'User Logins' example the generated HTML code will be:

<a onclick="JavaScript:ajaxGo('/admin/userLogins',0,7);"
   class="btn btn-floating btn-small"><i class="material-icons"
   alt="Click to User Logins" title="Click to User Logins">beenhere</i></a>

Here is how these actually look on the page:

If you want only some rows to have the Edit button, this can be done by defining a column and the conditional data required to be allowed to edit.

For example, if you only wanted to allow wtkUsers with a SecurityLevel of 80 to be allowed to edit, you would add these two lines before the call to wtkBuildDataBrowse.

$gloEditCondCol     = 'SecurityLevel';
$gloEditCondition   = '80';

Likewise, you can set a condition on Delete button displaying by setting the following PHP variables.

$gloDelCondCol     = 'SecurityLevel';
$gloDelCondition   = '90';

If the page is in printing mode or the data is being exported, then the Add, Edit and Delete buttons will not be displayed. This can be set by:

$gloPrinting = true;

All columns will be left justified by default. To make a column center or right justified, just assign it in the PHP global variable like this:

$gloColumnAlignArray = array (
    'Priority' => 'center',
    'Amount' => 'right'
);

Choosing which columns should be summed is easy also. This only sums the values shown in the list. So if your list is for 200 and your page navigation is set to 50, it will only show the first 50 rows and the total for the rows which are visible.

$gloTotalArray = array (
    'OrderCount' => 'SUM'
    'TotalInvoiced' => 'DSUM'
);

Passing SUM tallies the numbers. Using DSUM makes it so the sum at the bottom uses your chosen currency. The nubmer formatting and currency setting are defined by these two global variables which you set in the wtk/wtkServerInfo.php file.

$gloPHPLocale = 'en_US';  // determines number formatting
$gloCurrencyCode = 'USD'; // determines currency code in number formatting

The wtkSetHeaderSort function can take 1, 2 or 3 parameters. Note, as usual spaces will be automatically be inserted for WordCaps or snake_case. For example, 'FirstName' will be changed to 'First Name'.

Note that in order for Sort Order to work, it requires the SELECT to have an ORDER BY.

PagesVisited

This uses column named `PagesVisited` and makes header as “Pages Visited” and sorts by this column.

LookupDisplay, USA State

This uses column named `LookupDisplay` but shows the header as “USA State”. It sorts by the `LookupDisplay` column.

DOB, Birthday, u.`BirthDate`

This uses column named `DOB` but shows the header as “Birthday”. It sorts using u.`BirthDate` column. This is really important when formatting causes problem with sort order. For example if your date format is '%b %D, %Y' then sorting by that would not give the results you want.

Example

Here is an example SQL query and the associated Sort Options.

SELECT p.`UID`, u.`FirstName` AS `Owner`, p.`PetName`, p.`City`,
  DATE_FORMAT(p.`BirthDate`,'%b %D, %Y') AS `DOB`
FROM `pets` p
  INNER JOIN `wtkUsers` u ON u.`UID` = p.`UserUID`
  INNER JOIN `wtkLookups` L ON L.`LookupType` = 'USAstate' AND 

Sortable Columns Owner City, Town DOB, Birthday, p.`BirthDate`

So for the above example, in the PHP you would simply add this before the wtkBuildDataBrowse call.

wtkSetHeaderSort('Owner'); // Defaults column name but can change with second parameter
wtkSetHeaderSort('City', 'Town');
wtkSetHeaderSort('DOB', 'Birthday', 'p.`BirthDate`');
// when third parameter exists it is used for sorting of first parameter's column

By default when you have your unique identifying column (UID, GUID, ID) in the SELECT statement, it will not be shown. If you want it shown, you can simply set this variable to false.

$gloHideUID = false;

By default your result set will not be truncated. However if you want each column truncated to 80 characters you can easily by setting this variable before calling wtkBuildDataBrowse:

$gloBrowseTruncate = true;

You can set the follow variable and any HTML formatting will be removed from the SQL results before putting them into the browse list. This uses the wtkRemoveStyle function which is defined in wtk/lib/Utils.php.

$gloBrowseNoStyle = true;

If you want the SQL results to have <br> added in place of line breaks, set this PHP variable to true before calling wtkBuildDataBrowse:

$gloBrowseNL2BR = true;

If you know the result set will be so few you will not need page navigation, you can choose to skip showing the footer. For example, if there are only 9 rows returned, using $gloSkipFooter will prevent the bottom of your browse list from showing “1 - 9 of 9”.

Simply before calling wtkBuildDataBrowse put in this line:

$gloSkipFooter = true;

By default your browse lists will show 20 rows of data. If there are more rows, page navigation will automatically be added at the bottom allowing jumping to the end, or jumping 20 rows at a time.

This 20 can be globally changed by setting the following PHP variable in wtk/wtkServerInfo.php. Of course if you want you can set this on any individual page to override the global default.

$gloRowsPerPage = 30;

Usually you want the data returned in simple columns, and that is the default for wtkBuildDataBrowse. If you need a custom HTML layout and Header, that is easily done. Simply define those in these two PHP variables and wtkBuildDataBrowse will replace @ColumnNames@ with the data from the associated column. For example, from the demo/listCustomRowHTM.php page you can see this code:

$pgSQL =<<<SQLVAR
SELECT `UID`, DATE_FORMAT(`AddDate`, '$gloSqlDateTime') AS `Date`,
    CONCAT(`FilePath`, `NewFileName`) AS `WTKIMAGE`,
    CONCAT(`FirstName`, ' ', COALESCE(`LastName`,'')) AS `Name`,
    `Address`, `City`, `State`, `Zipcode`
  FROM `wtkUsers`
 WHERE `NewFileName` IS NOT NULL
ORDER BY `FirstName` ASC
SQLVAR;
 
$gloColHdr = '<th class="center">The WTK Demo List</th>';
 
$gloRowHtm =<<<htmVAR
<td><div class="row">
        <div class="col s5 center">
            @WTKIMAGE@
            <a class="btn" onclick="JavaScript:alert('My UID is @UID@ and my name is @Name@')">Click Me</a>
        </div>
        <div class="col s7">
            <h4>@Name@</h4><br>
            <p><em>added @Date@</em><br>
            @Address@<br>
            @City@, @State@ @Zipcode@</p>
        </div>
    </div>
</td>
htmVAR;
 
$pgHtm  = '<h4>Listing with Custom Row Template</h4>' . "\n";
$pgHtm .= wtkBuildDataBrowse($pgSQL);

This feature is rarely needed but does give you amazing flexibility. Definitely review the /demo/listRowFunction.php file to see it in action.

When you need complex rules and customization of HTML based on the data of a returned row, you can use the wtkRowFunction function. Before calling wtkBuildDataBrowse define your wtkRowFunction function and call wtkSearchReplaceRow with first parameter of '@RunFunction@'.

Here is example code:

// You define the next function in any way you like
function wtkRowFunction($fncHtmRow, $fncData){
    $fncNewRow = $fncHtmRow;
    if ($fncData['PetType'] == 'Dog'):
        $fncNewRow = wtkReplace($fncNewRow, '<tr id','<tr class="deep-purple lighten-3" id');
    endif;
    if ($fncData['CanTreat'] == 'CanTreatY'):
        $fncNewRow = wtkReplace($fncNewRow, 'CanTreatY','<i class="material-icons green-text">pets</i>');
    else:
        $fncNewRow = wtkReplace($fncNewRow, 'CanTreatN','<i class="material-icons red-text">pets</i>');
    endif;
    return $fncNewRow;
}
 
$pgSQL =<<<SQLVAR
SELECT p.`UID`, p.`PetName`, p.`City`,
    L.`LookupDisplay` AS `PetType`,
    CONCAT('CanTreat',p.`CanTreat`) AS `CanTreat`,
    `fncContactIcons`(p.`OwnerEmail`,p.`OwnerPhone`,p.`Latitude`,p.`Longitude`,'Y',p.`UID`,'Y','Y','') AS `OwnerContact`
  FROM `pets` p
   LEFT OUTER JOIN `wtkLookups` L ON L.`LookupType` = 'PetType' AND L.`LookupValue` = p.`PetType`
WHERE p.`DelDate` IS NULL
SQLVAR;
 
wtkSearchReplaceRow('@RunFunction@', ''); // second parameter not used for RunFunction
// above line is what triggers wtkRowFunction to be called for every row
 
$pgHtm .= wtkBuildDataBrowse($pgSQL);

Utilizing this, the wtkBuildDataBrowse function will call your wtkRowFunction function passing it the row of data that was retrieved and formatted. Your function can modify the row HTML in any way and has full access to the data via the passed $fncData[] array. Then you return the modified HTML which will be used in the browse list.

We have several demos showing all the different functionality for the wtkBuildDataBrowse function which you will find is one of the most useful functions in the Wizard's Toolkit library.

The PHP demos have exact minimalist code examples of how easy it is to use the powerful feature of passing a SQL query to generate a full listing web page with all of the features listed above.

• /demo/listDataMin.php - uses wtkFillBrowsePage
• /demo/listCustomHTML.php - demo wtkFillBrowsePage with custom HTML template
• /demo/listSortAligns.php - uses wtkSetHeaderSort
• /demo/list3demo.php - 3 lists on one page
• /demo/listWithImage.php - demo of WTKIMAGE feature
• /demo/listRowFunction.php - demo wtkRowFunction for extreme flexibility
• /demo/listCustomRowHTM.php - demo $gloColHdr and $gloRowHtm

If you use the WTK Page Builder it will create a browse PHP page for you and have in the code comments all the main features.

Full Tutorial Video