Red Queen Review Engine User Manual

ITEM RECORDS
Adjust Text:  a a a a
« Table of Contents   |   Obtain Red Queen »

ITEM RECORDS

Each Item will consist of a single record in your Item table. In practice, each of your tables will be prefixed, so if you use an application like phpMyAdmin, or DBops, to query the table you will have to reference it by its full name, which will be something like rq_Item. However, in the Red Queen documentation, and in the program itself, there will be little mention of the prefix. It should be noted that before you can add any Items to your Item table, you will have to first create at least one Category, as each Item must be associated with one or more Categories. For the sake of discussion let's assume that has been done. Otherwise jump ahead now to CATEGORY RECORDS if you need to learn more about that.

Once you have entered an Item into the Item table, attaching it, say, to Category 3, and it has been allocated an Item ID of, say, 8, then you can navigate to the Item detail page via the browsable Category pages, or you can enter the URL to the Item detail page directly into your browser, like so:

http://www.mydomain.com/cgi-bin/path/to/...
	/redqueen/do/redqueen.cgi?module=item&item_id=8&category_id=3

Creating an Item

To create an Item you can either visit the Database control panel in the administrative area, or visit the Categories control panel--that is, the Category Item Browser. To get there, enter the URL of your admin directory into the address bar of your browser. The URL will be something like:

http://www.mydomain.com/cgi-bin/path/to/.../redqueen/do/admin/admin.cgi

Your administrative directory should be directory-protected, but once you have logged into the directory look for the Database link in the administrative navigation bar (we'll assume we're going to work directly with the Database control panel, simply because the interface for that is a little more complex than for the Category Browser so we might as well cover it here). After you click on the Database link you'll see a Database menu of table names on the left side of the page. Select 'Item' from the menu of table names. You also need to select an action before pressing the button to load the right Database frame: select 'Add' from the action menu then click on the submit button. When the frame on the right side of the page loads you will see all the fields that can be specified when you create an Item. Unless you have already got around to adding new fields to the Item table you will be presented with a form that looks something like the following (example input values have been added for clarity, and are explained below):

Item ID

A value will be assigned

*

Item Owner

*

Name

*

URL

Mouseover URL

Description

Keywords

Image

Stickiness

*

Is Validated

*

Solicit Reviews

Unique Review IP

Editable Item Fields

The following Item fields appear both on the Add Item and Modify Item pages, however the last three fields displayed above--Stickiness, Is Validated, and Solicit Reviews--only appear when the Administrator is logged in.

Item ID
Each Item record is uniquely identified by the Item ID (the actual column name is Item.id), a positive integer that represents the PRIMARY KEY for the table. This value is automatically created each time a new Item record is added to the table and is one greater than the Item ID value of the previous record to be inserted. In other words, the Item.id column is an AUTO INCREMENT field.

Item Owner
The Item Owner field (column Item.member_id) is another integer representing the Member ID value for the Member who created the record. So be sure not to try supplying either the Member username or their nickname. You will just get an error message. Often it will be the case that you solicit Item submissions from your Members, in which case their own Member ID value will automatically be filled in by the program for them. But if you are adding records yourself as the Administrator you either need to supply your own Member ID value (usually 1) or create a Member account expressly for the purpose of being able to assign new Item records to that fictitious Member.

Name
The Name field (column Item.known_as) is mandatory (actually this is the only mandatory field that needs to be supplied for an Item), and is used to clearly indicate to mere mortals the identity of the Item. So it should be descriptive, but at the same time as short as possible so that it is less likely to line wrap if used in a confined space. There is no requirement that the names of Items be unique, because the PRIMARY KEY is based on the unique Item.id column. Usually that will never be a problem, but if you absolutely require unique names you could always place a UNIQUE index on the Item.known_as column. Red Queen is equipped to let you do that.

URL
Very often it is useful to be able to reference an associated external homepage for an Item, and that is what the URL field (column Item.url) is for. It is, however, optional. So just leave it blank if your Item doesn't have an associated homepage. It should also be noted that all URL values should be absolute URLs beginning with http:// (or perhaps https://)

Description
The Description field (column Item.description) is an optional textual passage which summarizes the important things you wish to convey about the Item. The column is of TEXT type so, at present, it supports a description up to 64 kB in length.

Keywords
The Keywords field (column Item.keywords) is an optional string of comma-delimited words or phrases that will be used to populate metadata tags in the static version of Item detail pages. Their purpose is thus to help spiders correctly index your web pages. This is a VARCHAR(255) column, meaning that the length of the field is limited to 255 characters.

Image
The Image field (column Item.item_image) is optional and is based on an INT (integer) column which acts as a foreign key to the Upload table. To add an image to the Item record you click on the button marked "Manage Upload" and a dialog box appears requesting information about the file to be uploaded. Once you have uploaded the image, a MIME icon and the name of the image file will appear on the Item record form itself, verifying that the image file is available before you even submit the Item record to the database. If you have enabled automatic image thumbnailing, a thumbnail of the uploaded image will also be stored along with the unscaled image and used where appropriate elsewhere in the program--such as on the Item detail page.

Stickiness
The Stickiness field (column Item.stickiness) is optional and is usually NOT used. This field controls where the Item will appear in an ordered list of Items from the same Category. If left blank (the usual thing to do) the Item will assume its default position. But if you wish to promote the Item to the top of the list--something you might do if you are featuring an Item, perhaps because the Item is sponsored--then you assign a small integer for the stickiness (the column is a SMALLINT). The larger the stickiness value, the higher the Item appears in the list. Thus if you are featuring a single Item, you might assign a stickiness of 1. If you want another Item to appear higher in the list, assign that Item a number greater than 1. Stickiness only affects default orderings; if a user elects to order the Items in a Category according to the average value of a given rating attribute, then the Item stickiness is ignored and the user gets the ordering they requested.

Is Validated
When a Member submits an Item for consideration, the value for the Is Validated field (column Item.is_validated) will be set to 'No', and remain that way until an Editor toggles it to 'Yes'. However, when adding Items as the Administrator, you can set this to 'Yes' at the time you create the record if you wish. If you would like for certain members to be able to add Item profiles which are automatically validated and require no editor intervention, visit the Database control panel, pull up the member record in the User table, and toggle the User.item_auto_validation column to 'Yes'. An Item will only appear in a Category if it is validated.

Solicit Reviews
The Solicit Reviews field (column Item.solicit_reviews) should be toggled to 'Yes' unless you do not wish for it to receive reviews and ratings from the community.

Unique Review IP
This field is for admin only. Neither editors or item submitters will see it. If you wish to ensure that each review submission has a unique IP address, set the Unique Review IP field to 'Yes'. You might do this, for example, if you think the "owner" of an item is fabricating reviews and you wish to make it much harder for him to continue doing this. Generally you should leave this at the default setting, which is 'No'.

Editable Non-Item Fields

In addition to the Item fields shown above, there may be other non-Item fields which appear on the Add Item and Modify Item pages. In general, Category Editors and the Administrator need to have the ability to modify the Categories and Suppliers associated with an Item (and will be able to do so if they have the relevant permissions assigned to them). The Administrator, who has access to the Database control panel will find that two extra form elements will be present to handle "move" and "copy" operations for both the Categories and Suppliers associated with an item. The form element to handle Category assignments will look something like the following (these demo form elements are functional so you can play with them):

Categories

 

Category Pool

To assign a Category to the Item, select the Category from the pool in the lower part of the form element and click on "Assign Category" to pull it from the pool. To unassign a Category, select it from the list of assigned Categories in the top part of the form element and click "Return to Pool". When you finally submit the Item record the new Category assignments will also be recorded.

The form element to handle Supplier assignments functions the same way:

Suppliers

 

Supplier Pool

Note: You will discover if you try to remove all Categories from an Item record that you cannot do it. At least one Category will remain.

Note also that these forms are only present when editing records via the Database control panel. If you are using the Category Browser to add and edit records, look for the Copy link that appears next to each item in a category listing. You can copy the item to another category using point and click operations. But you can only do this for one category at a time.

Dealing With Thousands Of Item Associations

There is an obvious limitation to the placement of forms on the page which allow the specification of Categories and Suppliers for an Item. If you have thousands of Categories or Suppliers in your database, the forms will take a very long time to load in your browser. They may even crash your browser if sufficiently large. If this is the situation you face, you should instead elect to use the Category Browser to perform Item-related database additions and modifications. When you use the Category Browser you will find the potentially troublesome Category form has been removed. Instead, to modify the Category associations for an Item, you will find specific links to do so next to the Item within a Category Listing. As for the Supplier form, it is switched on by default, but you can disable it from the Build / Browse page of the Configure control panel.

Adding New Item Fields

If you need to add extra fields to the Item table you can do this. Red Queen lets you add columns to any table that has a PRIMARY KEY. Each of these tables is listed in the Database menu found on the Database control panel. The Item table, of course, is one of them. For detailed information on the process of adding columns to existing tables, see ADDING COLUMNS TO A TABLE.

Do keep in mind when you add columns to the Item table that, even though every Item record shares these columns, not every Item has to use every column. This means you can add a column that is used by Items in some Categories but not in others. When it comes to displaying that field in the templates it is not difficult to test for the existence of the column value before attempting to display it. For example, if you add an Item.price column that is used in the Baseball Cards Category you can test with something like this:

	[% item = alias.item_info %] (assuming alias.item_info is passed into the template)

	[% IF item.price %]
		Price: $[% item.price %]
	[% END %]

Here alias.item_info is the hash of Item information passed into the Template-Toolkit template that we are using to display the information, such as the template for the Item detail page.

It is worth spending a moment here to point out which templates are those most likely to require editing once you have added columns to the Item table. Item information appears primarily in 2 places (1) the category listings of items, and (2) the detail pages associated with an item (this may be paginated if there are lots of reviews associated with the Item). The templates controlling the presentation of this information are discussed below.


find_item_list.ttml
To add Item information to the category listings you need to edit the template named find_item_list.ttml which formats multiple Item records onto a single page. These are the Item listings the visitor sees when they are clicking through the category pages looking for an Item of interest. In this template you will come across a loop like this:

[% FOREACH ordered_item = alias.ordered_item
 %]
    [% my_item         = ordered_item.item_info %]
    [% my_item_glimpse = ordered_item.item_glimpse %]

    [% INCLUDE item_table_rows
       is_last_item = loop.last
     %]
[% END %]

This is a loop over all Items that will appear on the page. Each Item table record has been assigned to the [% my_item %] hash. Further down in the page you will come across a [% BLOCK item_table_rows %] construct in which each of these records is accessed and the information is pulled from [% my_item %]. Virtually ALL of the columns from the Item table are available for use. Certainly any that you have newly added to the table. So, for example, if you had added a new Item.price column you could access with:

	[% IF my_item.price %]
		Price: $[% my_item.price %]
	[% END %]


item_page_top.ttml
In order to add Item information to a detail page we must edit the item_page_top.ttml template. In rare cases you may feel the need to also add the information to the item_page_more.ttml template (controlling the second, and higher, page of reviews) but generally why bother?, the visitor will have seen the information already on the first page of reviews.

In this case there is no loop because we are dealing with just the one record. Item information is passed into this template as the variable [% alias.item_info %] and is assigned to [% item %] so that you can access the value for the Item.price column as:

	[% IF item.price %]
		Price: $[% item.price %]
	[% END %]

You may notice the only difference compared to the find_item_list.ttml template is that we have used [% item %] as opposed to [% my_item %]. This simply illustrates that you can name your variables whatever you like when doing assignments. So, if you add code to item_page_top.ttml and then decide to copy it across to find_item_list.ttml just be sure you change [% item %] to [% my_item %] when you do so.

Understanding The Table Definition

The simple Template Toolkit code shown above for extracting data from an Item table column is fine for simple columns, of say, TEXT or INT type. But for more complex columns, such as the ENUM or SET type, which deal with alternative values, or selections of multiple values, you will have to be a little more creative in your Template Toolkit programming.

For example, let us say that you have added an ENUM column to represent the existence or absence of a feature. This column takes one of 2 values: either Yes (if the feature is available), or No (the feature is absent). You can find out how to add such a column in the section entitled ADDING COLUMNS TO A TABLE. Once you have added the column--let us suppose it is the Item.rooms_have_heating column--the code you will need to present the data for the column might look like this:

	Heating:
	[% IF item.rooms_have_heating == 'Yes' %]
		<img src="has_feature.gif">
	[% ELSE %]
		<img src="has_not_feature.gif">
	[% END %]

In other words we might be showing a glowing light bulb images, or something similar, if the feaure is available, and another image if it is not available. We could also have chosen to make it simpler:

	Heating: [% IF item.rooms_have_heating %]

However, because the possible column values are known ahead of time you can elect to do more intelligent programming, like the light bulb stuff if you choose.

But let's say you add a more complex column, like the SET column type. Let us suppose that the values this column can take are a combination of any of these strings describing features of a motel room: 'heating', 'air conditioner', 'refrigerator', 'mini bar', 'iron'. Again, you can see the section entitled ADDING COLUMNS TO A TABLE for information on how to add such a column, which we suppose here is called Item.room_amenities. Here we are interested in how to display the information in this column for a particular record (we will suppose that 'mini bar' and 'heating' are slected for the record). Several progressively more ambitious attempts to display the data are shown below. Here is the first:

code
	Room Amenities: [% item.room_amenities %]
output
	Room Amenities: heating,mini bar

Notice that there is no space between the string 'heating' and the string 'mini bar'. That is because the SET column is comma delimited and is storing, for this record, the value 'heating,mini bar'. To get some whitespace in there we need to break the string on the comma, and rejoin it with a space. Here is our next attempt:

code
	Room Amenities: [% item.room_amenities.split(',').join(' ') %]
output
	Room Amenities: heating, mini bar

Better. Does the trick. See the Template Toolkit manual if you are not familiar with the split and join operators. While this is perhaps "good enough", you might want to display the information in a different way. For example, you might want to list each of the potentially available features, and then place a light bulb next to each one that is actually available. For this, we'll need to determine the list of ALL features, which we cannot get by looking at the Item table record itself (since it generally contains a subset of the possible features). This is where knowledge of the table definition comes in useful.

In Red Queen, each table managed by the program has a table definition file associated with it which stores information about the nature of the columns. This definition file is available for inspection in the template as the [% alias.item_def %] variable. In fact this is a large hash of information. The table definition files themselves should never be edited and are under the control of Red Queen. If you do attempt to manually edit such a file, think of the action as akin to sticking a coat hanger in a slot in the back of your DVD player--nothing good is likely to happen. If you fry you Red Queen implementation by doing that, don't come crying to the developer. You can look at these files if you like, though.

We are interested in the list of all possible feature associated with the Item.room_amenities column, and we can get that list, and do something with it in the template, like so:

code
[% attr = alias.item_def.cols.room_amenities %]

	Room Amenities <br/>
	============== <br/>
[% feature_index = 0 %]
[% FOREACH feature = attr.values %]
	[% attr.form_values.$feature_index %]:
	[% IF item.room_amenities.match(feature) %]yes[% ELSE %]no[% END %]
	[% feature_index = feature_index + 1 %]
	<br/>
[% END %]

output
	Room Amenities <br/>
	============== <br/>
	Heating: yes <br/>
	Air Conditioner: no <br/>
	Refrigerator: no <br/>
	Mini Bar: yes <br/>
	Iron: no <br/>

So what is going on here? First, we defined an [% attr %] variable which contains the attributes for the Item.room_amenities column according to the content of the table definition file. That is just a short hand for access into that large definition hash, of which only the room_amenities column is of interest right now.

We then loop over ALL values of the values attribute, which is an array of the possible values that the SET column can use. For each of these values, we use the corresponding value in the form_values array to grab the label that we specified for appearance on forms whenever we want to name the feature. In this case the form values are the same strings as used for the values of the features, but with the first letter of each word capitalized. We then do a string match on the value of the Item.room_amenities column to set if the feature occurs in the string. If it does, we output 'yes', else 'no'. The [% feature_index %] is simply the array position for the attribute values, which we use to pull out the corresponding array value in the attribute form_values.

In practice, we would not even place the string "Room Amenities" on the page as this is very likely the value that we assigned to the form_display attribute of the column. So we would use [% attr.form_display %] instead of specifically leaving "Room Amenities" in the template. That would also make our template more robust to changes in the column definition.

If you need to see the contents of the column definition hash printed out on the page so that you can inspect it, try adding the following code to the template:

	<pre>
	[% USE Dumper %]
	[% Dumper.dump_html(alias.item_def) %]
	</pre>

The Dumper plugin can be used to show the content of any data structure passed to the template. In fact, if you need to see ALL data passed to the template, just dump the content of the (generally very large) alias hash:

	<pre>
	[% USE Dumper %]
	[% Dumper.dump_html(alias) %]
	</pre>

This can be useful when you are debugging your template modifications.

« Table of Contents   |   Obtain Red Queen »

Copyright © 2004 Random Mouse Software. All Rights Reserved.