Custom macro and query basics variables and formulas

QUERY BASICS
QUERY VARIABLES
QUERY FORMULAS

At first you'll probably wonder what the hell a query is, what it's purpose is, and why you'd want to use it.
Next you'll want to know what possible values you can feed into a query
Next you'll find yourself wanting to do things that variables alone wont allow you to do.

This article tries to provide some answers to those questions.

See also Gallery Rotation
See also Basic Template Layout

QUERY BASICS
Queries are the main tool for pulling galleries out of the database. Think of them as a ruleset or a tool to describe a set of galleries and lay them out in a sequence. Once you have defined which galleries will be part of a set of galleries, you can then pull them out one at a time in order and you can control that order.

The Comus query is in 2 basic parts.

1. the WHERE clause.
2. the ORDER BY clause.

The WHERE tells us the conditions for creating the working set of galleries, and gives us a set of rules for finding our galleries.

The ORDER BY clause is used to sort the galleries into a sequence, it begins at the point that the WHERE clause ends.

We can then attach these queries to any macro, and use them to control their behaviour.

Example:

In this example the WHERE clause is the 'periodsshown > -1' and the ORDER BY is 'periodsshown,rand()'.

This query can then be used on a macro like this..

Which will place a thumb on the page, choosing from all categories and using the rules defined in the age0 query.


The WHERE clause can have multiple conditions inside brackets and joined by clauses such as AND and OR.

For example we can expand this query to only list galleries that are new and have video by ANDing in the hasvideo field.

Example:

The ORDER BY clause can also have multiple arguments. The arguments are split by commas and processed from left to right. In the above example we are sorting galleries first by their age in days and then scrambling them randomly.
Lets say we wanted to priotitize all the video galleries so they appeared at the top of the listing for today, and all the pics galleries in the remaining spots, we could add in the hasvideo as so..

EXAMPLE:

hasvideo is a value of 0 if there are no movies in the gallery and 1 or more if there is. The DESC causes the galleries to be listed in descending order, so all galleries with movies will be pushed to the top of the list. If we were to remove it then the video galleries would move to the bottom of the set, and all picture galleries would come out first.
The periodsshown is looking for all galleries of 1 or more days in age. In other words galleries that have been used at least once.


QUERY VARIABLES
If you want to make your own queries and macros, it helps to know what variables you have available, what they mean and how you can expect them to behave.

I highly recommend using the standard macros and suggestions, however you might want to make your own, or simply understand what the variables mean, or maybe explore some other options.

There are 2 types of variables..
Variables that get 'Zapped' or cleared every time a gallery is rotated.
periodsshown a counter that represents the number of days that have passed since the gallery was last listed as a new gallery.
clickperiods a counter that tracks the number of builds that have passed since the gallery was listed as a new gallery.
exposure a counter that tracks how many clicks have happened on the site while this gallery was active on a page, if a gallery is not used in a build then this counter does not track clicks.

Variables that DONT get 'Zapped' or cleared when a gallery is rotated.
used the number of times a gallery has been rotated starts at 0 and goes to 1 once the gallery is listed and increments by 1 each time a gallery is used as new.
totalexposure is the total clicks that have happened on the pages while a gallery has been active on a page.
totalclicks is the total number of clicks that the gallery has served over it's life time, including all previous rotations.
url The actual url that the gallery points to, you can use this to extract keywords and such.
category the category of the gallery.
keywords the keywords assigned in the gallery review area to the gallery, NOT the keywords in the category area.
hasvideo the result of the gallery scan, will be greater than 0 if there are videos.
reports the number of times a gallery has been reported by a surfer.
rating a formula which is calculated over the first 24 hours life of the gallery represented which is the result of (clicks*10000)/(exposure+1)
sourcename if you use the url spider this field is populated with the name of the source of the spidering session, you can use this to list specific galleries from specific cropping runs.
sourceurl the url from the source of the URL spidering session.
id every gallery has a unique id which increases each time you add a gallery, you can use this for sorting galleries.
nickname as nick you can use nickname or nick to identify the submitter of the gallery.
numpic as npic the number of pictures on the page, or the image points.
refreshdelay Used for gallery scheduling. This variable tracks how many days pass before a gallery is to be released for display. NOTE that being released for display is not enough, once released you have to then prioritize the gallery for display. So you must also add ',refreshdelay DESC,' to the ORDER BY, without this your newly scheduled galleries wont get priority. The down side that you must live with is that this has implications on the gallery rotation, it means that if rotated the last scheduled galleries will come back first.
.
For example

vote Vote is a rating from 1 (best) to 10 (worst) assigned from within gallery review.
description as desc1 this is the description field, found within gallery review
thumbname The thumbname is the file name to the thumb.
thumbwidth The width of the thumb in pixels, you can use this to identify and locate special odd sized thumbs.
thumbheight The height of the thumb in pixels.
date as dis_date The date of approval in the form YYYYMMDDHHMM
listed The date of being listed on the site in the form YYYYMMDDHHMM
mgdate The date insterted in th
accept The status of the gallery is 'Approved' if approved, 'Blacklisted', Disapproved', 'Pending', etc.


QUERY FORMULAS
You are not restricted to just the variables you can also use formulas in the queries. For example the ,rand() of the ORDER BY is actually a formula. Some other formulas you can try are..

EXAMPLE:

In this example we are looking for galleries of at least 144 builds old. On a site that builds at 10 minute intervals, this is the number of builds we have per day. We do this with the 'clickperiods>144' WHERE clause.
Next we want galleries to be sorted by their age in days, but we want to sort them relative to the current moment, so we divide 'clickperiods/144'. The problem is this will create a floating point result with a decimal point, so clickperiods=1 is .006944 and clickperiods=2 is .013888, and the result will be just a sequential listing of the galleries, so ...

EXAMPLE 1:

Will produce the exact same results as...
EXAMPLE 2:

And consequently the last part of the query '(clicks/clickperiods) DESC is effectively ignored.

To fix this, we place the result of 'clickperiods/144' into the ABS() function, which rounds down to the nearest whole number, and fixes our problem.

The point is there are a myriad of functions that you can apply to your fields and queries, and you'll find references to them on the mysql.org site. Anything that mysql can accept is usable.