Database

PAD talks to the application's database - configured in _config/config.php - in two places: a page's PHP half calls db(), and the template side has tags that ask directly. The examples here read the staff table of the demo database - four people of its own, not the five of the staff.xml file the manual's other examples use.

db() from the page's PHP

The first word of the statement says what shape comes back:

word answers with
field one value
record one row, as an array of fields
array all rows, ready for a tag to iterate
check whether a row exists - written check table where ..., with no column list
insert / update writes, taking their values from an array

 

PHP
fragments/database_1.php 		HTML
fragments/database_1.pad 		Result 
<?php

  $phone = db ( "field phone from staff where name = 'jim'" );

?>
 
<p>Jim's phone number is {$phone}</p>

Jim's phone number is 555-4364

An array lands in a variable, and the variable's name is a tag - with the else construct for when the query found nothing:

PHP
fragments/database_2.php 		HTML
fragments/database_2.pad 		Result 
<?php

  $earners = db ( "array name, salary from staff where salary >= 2000" );

?>
 
<table border="1">
  {earners}
    <tr>
      <td>{$name}</td>
      <td>{$salary}</td>
    </tr>
  @else@
    <tr>
      <td>nobody earns that much</td>
    </tr>
  {/earners}
</table>
jim 2000.00
joe 3000.00
jerry 4000.00

Asking from the template

The same questions can be asked where the answer is used: a {field} tag drops one value into the page, and an {array} pair iterates its query on the spot. Inside the quotes a comma is part of the query, so a column list is written as it is - {array "name, phone from staff"} - and the example keeps to *:

PHP
 HTML
fragments/database_3.pad 		Result 
<p>Bob's phone number is {field "phone from staff where name = 'bob'"}</p>

<table border="1">
  {array "* from staff where salary >= 2000"}
    <tr>
      <td>{$name}</td>
      <td>{$phone}</td>
    </tr>
  {/array}
</table>

Bob's phone number is 555-3425

jim 555-4364
joe 555-3422
jerry 555-4973

A {record} pair does the same for a single row.

Named queries - a .sql file in _data

A query can also live in a file of its own. _data/staffByPhone.sql holds: 

-- The staff whose phone number matches a pattern, at most so many of them.
select name, phone
  from staff
 where phone like {$pattern}
 order by name
 limit {$max}

and the tag {staffByPhone} runs it. The file is never run as PAD: each {$name} is bound as a placeholder - an option of the tag first, then a variable of the page - so a value can never become SQL. The statement must read.

PHP
fragments/database_4.php 		HTML
fragments/database_4.pad 		Result 
<?php

  $pattern = '%4%';
  $max     = 10;

?>
 
<p>{staffByPhone}{$name} {/staffByPhone}</p>
<p>{staffByPhone pattern='%3%', max=2}{$name} {/staffByPhone}</p>

bob jerry jim joe

bob jerry

Declared tables - the select subsystem

For an application built around its tables there is a third way: declare the tables once in the configuration and every one of them is a tag, relations and automatic joins included - the select: prefix. The classicModels application is that subsystem demonstrated over a whole database, and docs/DATABASE.md in the repository is the reference.