ANU Archaeology and Natural History Osteological Reference Collection
ANH ONLINE OSTEOLOGICAL DATABASE MAINTENANCE INFORMATION
The ANH online osteological database is intended to provide both a catalog
of ANH osteological specimens and easily accessible information on specimen
species, collection date, etc. This page provides some hints for ANH people
who want to add new specimens to the database.
Inserting and Updating Catalog Entries
No ANH-specific interface is available for updating the database. Instead,
phpMyAdmin is used. This powerful but fairly easy to use software is the standard
RSPAS tool for managing online databases, and RSPAS IT support personnel can
provide access and training if needed.
The database was initially implemented using MySQL 5.1; the version may change
as the IT group performs maintenance and upgrades. When changing the structure of a table in the
database, be sure to modify the field relationships as appropriate. These
relationships enforce cross-table integrity so that invalid data is not
inserted into the database. If you receive a foreign key constraint error (e.g., 1452)
when attempting to insert data, it probably means the data violated an existing relationship.
If you create a new table for the database, be sure to make it an InnoDB table (rather
than a MyISAM table) because MySQL only supports relationships among InnoDB databases.
Creating Box Labels
After you've added one or more specimens to the database, you will probably want
to create labels that can be attached to the boxes of these specimens. There are
PHP utilities to help with creating the labels, but the process is not really
automated. Here are the steps needed to create new labels. You will need
Corporate Express Produce Code EXP132 98mm x 38mm A4 label sheets (or Avery J8163, or other
- Enter specimen data into the database. You may modify the CardDataEntered field
at will; it is often used to mark particular specimens as changed so that specimens needing
label changes can be quickly identified.
- If you entered fish specimens into the database, run the CreateLabels_Fish.php script by going to:
CreateLabels_Fish.php. This script creates a page
of CSV-formatted lines. Each line provides the information necessary to create
one label. Each specimen appears twice so that two labels (one for the box
top and one for the bottom) will be created. All specimens in the database are
listed, sorted in order of ANH Specimen Code (aka OsteoCode). There are also
CreateLabels_RepAmph.php scripts for birds, mammals, and reptiles/amphibians, respectively.
You must run the appropriate script and perform the following procedure for each animal type entered.
- Highlight the first data line, which contains field header information. Copy it into the clipboard (Ctrl-C).
- Open Excel or a similar spreadsheet application, create a new spreadsheet, and paste
the header information into cell A1 (the topmost lefthand cell).
- Go back to the CreateLabels_Fish.php page and scroll down or search to find the lines for the newly added specimens.
Alternatively, you may just select all lines, and eliminated unchanged specimens after copying to the spreadsheet.
This is often easier, especially when numerous specimens were added or changed and the CardDataEntered
field used to mark them.
- Highlight those lines, and copy them into the clipboard (Ctrl-C).
- Paste them into the spreadsheet, starting at cell B1. The specimen data will appear as a series of lines, one
cell per line.
- Highlight all of column A, then convert the information into useable form using the text-to-columns function. In Excel 2007, this is
available via Data->Text To Columns:
- Selected the "Delimited" data type, then press Next.
- Set the Delimiters to Comma, and if necessary set the Text qualifier to double quotes ("), and press Finish. The text
should be separated into columns, with one field per column.
- Save the data to a file and close the spreadsheet.
- Open the Microsoft Word 2007 file named
OsteoLabels_Fish.docx. If you don't already have this file, download it from
the ANH Osteology web site. There are similar files available for
Birds, Mammals, and
Amphibians and Reptiles.
- When the file opens, you should receive an error saying the Access database engine could not find a file. Click OK
and you should be prompted with the Data Link Properties window. Under the Connection tab, find the Data Source field
and enter the full path to the spreadsheet file you just saved. Depending on your security settings, you might
get a security error or warning. Do whatever it tells you is necessary to enable the connection. You might
also be prompted to select which sheet to use; if you're not sure, it is probably Sheet1. If you did not
get a connection error when the file opened, then you've already configured OsteoLabels_Fish.docx to link to an
existing spreadsheet. If it's not linking to the one you just created, go to the Mailings tab, then Select Recipients,
then Use Existing List. Find the spreadsheet you just created and open it.
- At this point, you should be presented with a document containing 14 labels per page. This is the format
for Corporate Express Produce Code EXP132 98mm x 38mm A4 labels. (Technically, it is the Avery J8163 label format, which
is compatible with the Corporate Express labels.) It only creates
one page at a time, so even if you enough data rows for multiple pages, it will look like it only took the
first 14 labels. To see other pages, use the right and left arrow buttons in the Mailings->Preview Results area.
- Before printing, go to the printer and put label sheets in the auxiliary paper feed tray on the side.
- Select Mailings->Finish & Merge, then Print Documents
- When prompted on the Merge to Print window, identify which labels to print if necessary, then hit OK.
- On the printer page, select Properties, and set the paper try to Bypass, Auxiliary, or whatever your
printer calls the manual paper tray.
- You may be presented with a series of windows indicating that the margins exceed the print area. This
is usually okay -- just hit Enter until the windows go away.