How can I create lists with the result of a database query?

Creating lists with patTemplate is quite easy, and it's even easier when you are using patTemplate in conjunction with patDbc!
Let's take look at the template for a list:

001    <patTemplate:tmpl name="list">
002          <table border="1" cellpadding="10" cellspacing="0">
003                <tr>
004                      <th>Superhero Name</th>
005                      <th>Realname</th>
006                      <th>Action</th>
007                </tr>
008                <!-- template for list row -->
009                <patTemplate:tmpl name="list_entry">
010                      <tr>
011                            <td>{superhero}</td>
012                            <td>{real_name}</td>
013                            <td>
014                                  <a href="edit.php?id={id}">edit</a>
015                            </td>
016                      </tr>
017                </patTemplate:tmpl>
018          </table>
019    </patTemplate:tmpl>


Important
Variable names have to be UPPERCASE
Please remember to insert your variable names in UPPERCASE. We had to use lowercase variable names here, as Randy (patXMLRenderer  we use it to generate our site) automatically strips unused variables.

Now imagine you've got a mysql table with three columns: id, superhero and real_name and you'd like to display a list with all entries from the table in the template above.

01        <?PHP
02        $tmpl   = new patTemplate();
03        $tmpl->setBasedir( "templates" );
04        $tmpl->readTemplatesFromFile( "superherolist.tmpl" );
05       
06        $dbc    = new patMySqlDbc( "localhost", "superheroes", "username", "passwd" );
07       
08        $query  = "SELECT id, superhero, real_name FROM secretIdentities ORDER BY superhero";
09        $result = $dbc->query( $query );
10       
11        $tmpl->addRows( "list_entry", $result->get_result( patDBC_TYPEASSOC ) );
12       
13        $result->free();
14       
15        $tmpl->displayParsedTemplate();
16        ?>

Of course you can use any other database abstraction layer or native PHP functions for database access  we used patDbc here, as it works seamlessly with patTemplate. $result->get_result() just executes mysql_fetch_array() and collects all rows in one array that it returns.
This array is just handed over to the template block list_entry using addRows(), and patTemplate automatically repeats this block, for the amount of rows contained in the array. This makes handling database results fun

Last Updated ( Tuesday, 01 November 2005 )