Chapter 24 - MySQL

Advanced Queries

Queries are built by selecting the fields to search on and the criteria to use for the search. The SQL statement that will be executed is displayed in the textbox in the lower right. The statement is updated to reflect the values provided in the rest of the form fields on the page by pressing the "Update Query" button. Execute the statement by pressing on the "Submit Query" button.

Each column can be used to specify a field for the SQL statement. Empty columns are ignored.

The fields specified in the "Fields" row are combined with criteria below it to create a WHERE clause. If the "Show" checkbox in on then the field is placed in the SELECT clause as well. The query results may be sorted on a field based on the selection in the "Sort" menu.

More fields can be added by turning on the "Ins" checkbox below a column or selecting a positive number in the "Add/Delete Field Columns" menu. Fields are deleted by turning on the "Del" checkbox or selecting a negative number in the "Add/Delete Field Columns" menu. Press "Update Query" to update the page to reflect the changes. You may have to scroll your web browser to the right to see all of the field columns.

The tables selected in the "Use Tables" listbox form the FROM clause. Also, the fields listed in the "Fields" menus are restricted to the fields in the selected tables.

Each criteria should be placed on a separate criteria row. If the "And" radio button is selected for a criteria row, that row will be logically ANDed in the WHERE clause. If the "Or" radio button is selected, that row will be logically ORed in the WHERE clause.

Criteria are not required for any column. If not provided and the "Show" checkbox is on, the field will be shown for all records that match any other criteria.

Criteria are added and deleted in a  manner similar to adding/deleting fields using the checkboxes to the left of a criteria row or the "Add/Delete Criteria Row" menu. Again, press "Update Query" to update the page.