• View
• Edit
• History
• Attach
• Print
• Backlinks

DQReport

Questions answered by this recipe

When I want to use PmWiki to support an online database, I can make good use of the DataQuery recipe. The DataPlates recipe makes the design of my database frontend even easier to do. But some basic fuctionalities are still missing to build a total database frontend-system.

Questions

1. How can I make reports to print database information in a proper format on paper ?
2. How can I navigate between records other than via the query listing ?
3. How can I add a new record to the database with an automated key value (autonumber) ?
4. How can I count the number of records in the query or perform other statistics on the database ?

Answers

1. The dqreport.phpΔ script adds an "?action=dqreport" that can be used to export records in a query to a local tekst file for further handling and printing.
2. The script also offers a new PageVariable {(dqmove)} that will deliver the first, previous, next or last keyvalue in your query relative to the present record (=pagename).
3. The new PageVariable {(dqnextid)} will deliver an autonumber keyvalue for a new record.
4. The new PageVariable {(dqcount)} will automate counting of records within a query where a field has one or more specific values or counting of the total of records within the query.

Description

Reports

When you want to printout adreslabels or a memberlist from an online database, you will discover that printing facilities of the various browsers are not very satisfying. HTML just is not rich enough to support good looking prints. The idea of this recipe is therefor to produce a basic (ascii) textfile directly from a query, that is offered to be saved locally. After that you can open the file in your wordprocessor application, enhance it with a proper layout and then print it from there.

Labels

For adreslabels you would need your records to be exported in a way that puts each field on a new line, like this:

Mr. B. Myfriend
Foostreet 10
1000A Barvillage

Mrs. A. Otherfriend
Barlane 110
2200S Foocity

For these kind of reports use ?action=dqreport&style=row on your query(group)page. It will put each (concatinated) field on a new line and separates the records by an extra linefeed. Now take your favourite wordprocessor application (why not use OpenOffice.org Writer, its free!) and put the result in three (or more) columns, apply proper margin-, column- and line-spacing to match your label-sheets. For a preview of the query on screen, before exporting it via ?action=dqreport, a key field in the query is neccesary. But the key field is not wanted in the printout. For this reason the recipe automatically leaves out the first field (the key field) in the file that is exported.

Listings

When you want to print lets say a member list out of your database, you would need to export the records in a way that puts the fields of a record on one line, like this:

To get this result use ?action=dqreport&style=col (or just ?action=dqreport because this style is the default). It will put each record on a line and separate the fields with a tab-character. Again take your wordprocessor and apply adequate tab-spacing, put headers and footers in and use other layout functionality that is available there and then put the result on your printer. for listings to get properly sorted, you might need extra fields in your query that are however not wanted in the printout. To get rid of those sorting fields the easiest way is to first put the tekst in a table in your wordprocessor and then delete the colums you don't need.

Record navigation

• Use {(dqmove first)} or {(dqmove f)} to get the first keyvalue in the queryresult.
• Use {(dqmove previous)} or {(dqmove p)} to get the keyvalue preceeding the present record.
• Use {(dqmove next)} or {(dqmove n)} to get the next keyvalue following the present record.
• Use {(dqmove last)} or {(dqmove l)} to get the last keyvalue in the queryresult.

Now you can put something like: '''Go to:''' [[{(dqmove f)}|First]] | [[{(dqmove p)}|Previous]] | [[{(dqmove n)}|Next]] | [[{(dqmove l)}|Last]] | [[HomePage|List]] ...in you GroupHeader page for the query and navigate from one record to another.

You could also use something like: '''Go to:''' [[{(dqmove f)}?action=edit|First]] ...etcetera ...in your EditForm for the query and do several edits after eachother.

Autonumber function

Use {(dqnextid Tablequery)} to get the next free keyvalue for your new record (provided that the key is numeric). The script just searches for the highest keyvalue and returns this value +1. Be sure to use one of the initial (table) queries for this (without filters) if you want the keyvalue to be unique.

Counter function

• Use {(dqcount)} to get the number of records in the current queryresult.
• Use {(dqcount Yourquery)} to get the number of records in any other query than the current one.
• Use {(dqcount Yourquery Field Value)} to count the occurrences of Field==Value within the queryresult.
• Use {(dqcount Yourquery Field Value1,Value2,...)} to count the times where Field==Value1 or Field==Value2 etc. within the queryresult. Be sure to seperate the values with a comma, but without any spaces.

In a member database you could count the number of members for each or some of the membercategories that you are using.

Notes

03-11-2008: A new version of dqreport is posted today. In this version a bug is fixed in the function dqnextid, that gave a wrong output of the function.

02-2008: This is my very first recipe contribution to the PmWiki community after almost two years of using PmWiki for private websites. Allthough I have done my share of programming in Pascal and Clipper, I am not experienced in PHP-programming. Feel free to propose enhancements to the code if you find any.

Comments

Dirk, first of all, congratulations on contributing your first recipe! I applaud you for finding your own solutions rather than waiting for me to get around to making improvements in DataQuery ... I have unfortunately been busy with other projects. And since you say you are not a PHP programmer, you have done a very fine job of writing code that gets the job done.

Some of the functionality you're after, such as getting the next available ID number or the number of records in a query, should probably be built into DataQuery, because it can be obtained much more efficiently. I'll add it to the to-do list.

Also, when you get a chance, could you add some more comments to your code? It's pretty readable, but I'm not sure why it does some of the things it does.

Thanks again! I'm glad you're finding DataQuery and DataPlates useful! Ben Stallings March 01, 2008, at 06:41 PM

See Also

DataQuery, ZAP, DataPlates

Contributors

DirkBlaas