New in the 1.1.x version of the software is the ability to specify the exact WHERE clause that will be sent to MySQL when the software is selecting the
galleries from the database that will be displayed on your TGP pages. This is an advanced feature that requires knowledge of how MySQL database queries
work. If you are not familiar with MySQL and SQL queries, it is not recommended that you use this feature. Instead you can control what galleries
are selected by configuring the available options of the GALLERIES directive.
WHERE vs REWHERE
In the GALLERIES directive there are two options for specifying the MySQL where clause to use: WHERE and REWHERE. Which one of these two where clauses is
used depends on the type of build you are doing. When doing a "Build With New" the WHERE option will be used. When doing just a "Build" the REWHERE option
will be used. This is important because it allows you to specify a different where clause depending on the type of build being done. Normally the software
automatically handles this for you when you are using the regular options of the GALLERIES directive, however when using the WHERE option it is your responsibility.
More information on page building can be found in the Page Building Overview
Database Fields and Query Syntax
To use the WHERE and REWHERE options you will need to know all of the available database fields and what they represent. Below is a list of all the database
fields that you can use in the WHERE and REWHERE options:
Gallery_ID - The ID number assigned to the gallery when it is added to the database
Gallery_URL - The full URL to the gallery page
Description - The gallery's description
Sponsor - The sponsor name that has been assigned to this gallery
Thumbnails - The number of thumbnails on the gallery
Category - The category that this gallery has been assigned
Type - The gallery format, either Pictures or Movies
Has_Thumb - A flag to indicate if this gallery has a preview thumbnail; 0 means it does not have a preview, 1 means it does have a preview
Thumb_Width - The width, in pixels, of the preview thumbnail if one has been created
Thumb_Height - The height, in pixels, of the preview thumbnail if one has been created
Weight - The weight value that has been assigned to the gallery
Clicks - The number of clicks that the gallery has received since it was last selected for display (must be using the go.php script)
Added - The unix timestamp of when the gallery was added to the database
Allow_Scan - A flag to indicate if this gallery should be scanned; 0 means it will not be scanned, 1 means it will be scanned
Allow_Thumb - A flag to indicate if a preview thumbnail can be created for this gallery; 0 means a thumb cannot be created, 1 means a thumb can be created
Times_Selected - A counter that indicates the number of times the gallery has been selected for display from Pending status
Display_Date - A MySQL DATE field that records the date when the gallery was selected for display from pending status; YYYY-MM-DD format or NULL if the gallery is Pending
Scheduled_Date - A MySQL DATE field that can be assigned the date when the gallery becomes eligible for display; YYYY-MM-DD format or NULL for a random date
Status - The current gallery status; one of Pending, Used, or Holding
Keywords - The keywords that have been assigned to this gallery
Used_Counter - A counter that is incremented every time a gallery is used during a build
Build_Counter - A counter that is incremented for all non-Pending galleries every time a build is done
When you create your where clause, remember that the syntax must be in a format that MySQL can understand. TGP Rotator will pass the where clause that you specify directly to MySQL
without doing any extra processing. Here is an example of the type of query that TGP Rotator might send to MySQL to grab galleries:
SELECT * FROM tr_Galleries WHERE Has_Thumb=1 AND Status='Used' ORDER BY (Clicks/Build_Counter) DESC LIMIT 10
The portion with the yellow background is the where clause, and that is the type of value you will be specifying in the WHERE and REWHERE options of the GALLERIES directive. Keep
in mind that you have complete control of the where clause, which means you can use any of the built in MySQL functions to modify your query as you need.
Overriding Other Directive Options
When you use the WHERE and REWHERE options they will override many of the other options of the GALLERIES directive. Normally, when not using the WHERE and REWHERE option, the software
uses the options you specify in the GALLERIES directive to construct an appropriate where clause to send to MySQL. Since you are specifying the where clause yourself, it does not make
sense to use some of the other options. Below is a list of the options that are overriden when using the WHERE and REWHERE options:
If you do specify any of those options while using the WHERE or REWHERE options, they will be ignored. There is one special case, and that is the TYPE option. You always need
to specify the TYPE option when using WHERE and REWHERE, however it will only be used for the filler queries. In the WHERE and REWHERE options you will need to specify the type
of gallery by using the Has_Thumb database field as part of the query.
Besides the standard MySQL syntax and database field names that you can reference in your WHERE and REWHERE clauses, there are also some special values that you may need to use
from time-to-time. Each of the available special values is listed below.
$MYSQL_DATE - This is the current date (with your timezone figured in) in YYYY-MM-DD format
$HOLDING_PERIOD - This is the holding period value you have defined in the Edit Options interface
In SQL queries it is necessary to quote string values. By default MySQL allows you to use either single quotes (') or double quotes (") for quoting purposes, however because of how the
templates are compiled by the software you should only use single quotes for most purposes. If you do need to use a double quote, it must be escaped with a backslash character (\").
Additionally, if you need to use a single quote character inside an already single-quoted string, you will need to escape that with a backslash as well. Example:
WHERE Sponsor='Jim\'s Cash Payouts'
Below are a few examples of the WHERE and REWHERE options that you might use in your template. First the GALLERIES directive is shown, followed by a description of how it all works.
HTML <a href="##Gallery_URL##" target="_blank">##Description##</a><br />
WHERE Has_Thumb=0 AND Status='Pending' AND Keywords LIKE '%bikini%' AND (Scheduled_Date IS NULL OR Scheduled_Date <= '$MYSQL_DATE')
REWHERE Has_Thumb=0 AND Status='Used' AND Keywords LIKE '%bikini%'
ORDER Times_Selected, RAND()
REORDER (Clicks/Build_Counter) DESC
Right away you will see that the WHERE and REWHERE options can get lengthy, even with a fairly simple query. Here is a breakdown of what each portion
of the WHERE option does:
This tells MySQL to only pull galleries that do not have a preview thumbnail. It is important to note that this is different from leaving the
Has_Thumb value out of the where clause completely. If you leave the Has_Thumb value out completely, MySQL will select from all galleries no
matter if they have a preview thumbnail or not.
This tells MySQL to only pull galleries who's status is currently Pending
Keywords LIKE '%bikini%'
This tells MySQL to only pull galleries that have the word 'bikini' in the keyword list
(Scheduled_Date IS NULL OR Scheduled_Date <= '$MYSQL_DATE')
This tells MySQL to only pull galleries that have a random scheduled date or a scheduled date that is today or earlier
This shows an important distinction between the WHERE and REWHERE options. Since the WHERE option is used when doing a "Build With New", the Status='Pending' value is
used in the clause to tell MySQL that it can select new galleries from the database. When you just do a normal "Build" you do not want new galleries to be selected, so
that is why the REWHERE clause is needed and in that clause the Status='Used' value is used. This is also why the Scheduled_Date is referenced only in the WHERE clause;
we already know that galleries with Used status have met the Scheduled_Date requirement so it is not necessary to re-test that when doing the REWHERE.
HTML <td><a href="##Gallery_URL##" target="_blank"><img src="##Thumbnail_URL##" border="0"></a></td>
WHERE Has_Thumb=1 AND Status='Used' AND Display_Date <= ADDDATE('$MYSQL_DATE', INTERVAL 5 DAY)
ORDER (Clicks/Build_Counter) DESC
This example does not include the REWHERE option because the WHERE option specifies to only select already used galleries. You might use this
type of query in a section of your page where you want to display older galleries instead of adding new galleries to this section each time a
"Build With New" is done. When the REWHERE option is not specified, the WHERE option will be used for both.
This tells MySQL to only pull galleries that have a preview thumbnail.
This tells MySQL to only pull galleries who's status is currently Used
Display_Date <= ADDDATE('$MYSQL_DATE', INTERVAL 5 DAY)
This tells MySQL to only pull galleries that have a display date within the past 5 days
This shows an example of using a built-in MySQL function to modify the query. In this case the ADDDATE function is used to calculate the date 5 days from
today. You can use any of the built-in MySQL functions that are available for use within a WHERE clause. If you are not familiar with the built-in MySQL
functions, visit the MySQL website and view the manual for additional