Aptilis manual: Playing with databases

Playing with databases - templates	Try online	Examples

Find out how to simplify your life when writing HTML by using 'stuff...

We'll implement a products database search by approximation (e.g.: when the user enters f* all products starting with an 'f' will be returned)

Here's what the HTML looks like:

	<FORM action="/cgi-bin/aptilis.exe" method="POST">
	<INPUT type="hidden" name="file" value="/home/scripts/db3.e.txt">

	<B>Products Database Search</B> <I>By approximation</I><BR>

	<INPUT type="text" name="search" size=20>
	<INPUT type="submit" value="Search...">

	</FORM>

And now, the Aptilis program...

sub main()

	print("Content-Type: text/html\n\n")

	print("<HTML>\n")
	print("<HEAD><BASE href=\"http://localhost/aptilis/\"></HEAD>\n\n")
	print("<BODY bgcolor=\"#FFFFFF\">\n")

	n = LoadDataBase(db[], "f:/projects/aptilis/documentation/targets/local/scripts/database.txt")
	print(int(n)$, " record(s)<BR>\n")

	if n = 0
		print("The database is empty</BODY></HTML>")
		return 0
	end If

	// The file that contains the template for a table row.
	t = LoadFile("f:/aptilis/scripts/row.html") $

  print("<TABLE width=50% border=1>\n")

  n = 0
  x = 0
  repeat

    x = GetRecordIndexByNearKey(db[], search$, 0, x)
    if x <> -1

      getAllFields(f[], db[x] $)
      print(stuff(t$)$)

      n = n + 1
      x = x + 1
    end if

  until x = -1

  print("</TABLE>\n")

  print("Found: ", int(n)$, " record")
  if n>1
    print("s")
  end if

  print(" found.<BR>\n\n</BODY></HTML>\n")

end sub

Here is the content of f:/aptilis/scripts/row.html, our template:

<TR>

<TD valign=top><IMG src="!(_em)f[3]).gif"></TD>
<TD valign=top bgcolor="#A00000"> <B>!(_em)f[0])</B> </TD>
<TD valign=top>£!(_em)f[1]) </TD>

</TR>

As you can see, it is a simple text file which actually contains some HTML. The places where the variables must be inserted are indicated with the syntax: !(_em)varname).
See stuff for more details.

Things learnt

LoadFile, to load a file directly into a variable.
stuff, to insert the values of variables into a string.
When we need to get all the fields of a record into an array, the obvious way to do it would be:
```
for i=0 to 3
  f[i] = GetField(db[x] $, i) $
end for
```
And that would work, but, there's a faster way to do it:
```
getAllFields(f[], db[x]$)
```
see getAllFields for more details.

As a reminder, here is what our test database contains, it's just a text file, that could have been exported by, say, Access.
In our example, it had been saved as "c:\\aptilis\\database\\database.txt"

"Ferrari","100000","car","testa"
"PC","1000","computer","pc"
"Pizza"  ,"5.89","food","pizza"

Playing with databases - templates