Using the WHERE Option

Introduction

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: 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: 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.

Special Values

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. Using Quotes

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 (\"). Example: 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: Examples

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.

<%GALLERIES
AMOUNT 20
TYPE Text
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 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.

<%GALLERIES
AMOUNT 20
TYPE Thumb
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 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 information.