Searching and Displaying Data¶

Display Current Error Code and Message¶

The following code displays the current error message. This code should be placed in a Lasso page that is a response to a database action or within the capture block of an inline method.

error_code + ': ' + error_msg

If the database action was performed successfully then the following result will be returned:

// => 0: No Error

Check for a Specific Error Code and Message¶

The following example shows how to report a specific error if one occurs using a conditional if statement to check if the current error message is equal to error_databaseTimeout:

if(error_currentError == error_databaseTimeout)
   'Connection to database lost!'
/if

Full documentation about error methods and error codes can be found in the Error Handling chapter.

Search a Database Using an Inline¶

The following example shows how to search a database by specifying the required parameters within an inline method. The -database is set to “contacts”, -table is set to “people”, and -keyField is set to “id”. The search returns records that contain “John” with the field “first_name”.

The results of the search are displayed to the visitor inside the inline. The records method will repeat for each record in the found set. The field methods will display the value for the specified field from the current record being shown.

inline(
   -search,
   -database='contacts',
   -table='people',
   -keyField='id',
   'first_name'='John'
) => {^
   records => {^
      '<br />' + field('first_name') + ' ' + field('last_name') + '\n'
   ^}
^}

If the search was successful then the following results will be returned:

// =>
// <br />John Person
// <br />John Doe

Additional pair parameters and keyword parameters can be used to generate more complex searches. These techniques are documented in the section Search Operators later in this chapter.

Search a Database Using Visitor-Supplied Values¶

The following example shows how to search a database by specifying the required parameters within an inline method, but allowing a site visitor to specify the search criteria in an HTML form. The visitor is presented with an HTML form in the Lasso page “default.lasso”. The HTML form contains two text inputs for “first_name” and “last_name” and a submit button. The action of the form is the response page “response.lasso” which contains the inline that will perform the search. The contents of the “default.lasso” file include the following:

<form action="response.lasso" method="POST">
   <br />First Name: <input type="text" name="first_name" value="" />
   <br />Last Name: <input type="text" name="last_name" value="" />
   <br /><input type="submit" name="submit" value="Search" />
</form>

The search is performed and the results of the search are displayed to the visitor inside the inline method in “response.lasso”. The values entered by the visitor in the HTML form in “default.lasso” are inserted into the inline using the web_request->param method. The records method will execute the capture block for each record in the found set. The field methods will display the value for the specified field from the current record being shown. The contents of the “response.lasso” file include the following:

inline(
   -search,
   -database='contacts',
   -table='people',
   -keyField='id',
   'first_name'=web_request->param('first_name'),
   'last_name'=web_request->param('last_name')
) => {^
   records => {^
      '<br />' + field('first_name') + ' ' + field('last_name') + '\n'
   ^}
^}

If the visitor entered “John” for “first_name” and “Person” for “last_name” then the following result would be returned:

// =>
// <br />John Person

Field Operators¶

The possible values for the -operator parameter are listed in the table Search Field Operators. The default operator is begins with (“bw”). Case is not considered when specifying operators. Several of the field operators are only supported in MySQL or other SQL databases. These include the “ft” full-text operator and the “rx” and “nrx” regular expression operators, which are described further in the table MySQL Additional Search Field Operators.

Field operators are interpreted differently depending on which data source is being accessed. For example, FileMaker Server interprets “bw” to mean that any word within a field can begin with the value specified for that field. MySQL interprets “bw” to mean that the first word within the field must begin with the value specified. See the chapters on each data source or the documentation that came with a third-party data source connector for more information.

inline(
   -search,
   -database='contacts',
   -table='people',
   -keyField='id',
   -operator='bw', 'first_name'='J',
   -operator='ew', 'last_name'='son'
) => {^
   records => {^
      '<br />' + field('first_name') + ' ' + field('last_name')
   ^}
^}

inline(
   -search,
   -database='contacts',
   -table='people',
   -keyField='id',
   -key=(: -bw, 'first_name'='J', -ew, 'last_name'='son')
) => {^
   records => {^
      '<br />' + field('first_name') + ' ' + field('last_name') + '\n'
   ^}
^}

// =>
// <br />John Person
// <br />Jane Person

Logical Operators¶

The logical operator parameter -operatorLogical can be used with a value of either “And” or “Or”. The parameters -operatorBegin and -operatorEnd can be used with values of “And”, “Or”, or “Not”. An -operatorLogical applies to all search parameters specified with an action while -operatorBegin applies to all search parameters until the matching -operatorEnd parameter is reached. (Thus the two cannot be mixed into the same inline.) The case of the value is unimportant when specifying a logical operator.

• AND – Specifies that records that are returned should fulfill all of the search parameters listed.
• OR – Specifies that records that are returned should fulfill one or more of the search parameters listed.
• NOT – Specifies that records that match the search criteria contained between the -operatorBegin and -operatorEnd parameters should be omitted from the found set. The NOT operator cannot be used with the -operatorLogical keyword parameter.

inline(
   -search,
   -database='contacts',
   -table='people',
   -keyField='id',
   -operatorLogical='And',
   'first_name'='John',
   'last_name'='Doe'
) => {^
   records => {^
      '<br />' + field('first_name') + ' ' + field('last_name')
   ^}
^}

// => <br />John Doe

inline(
   -search,
   -database='contacts',
   -table='people',
   -keyField='id',
   -operatorLogical='Or',
   'first_name'='John',
   'first_name'='Jane'
) => {^
   records => {^
      '<br />' + field('first_name') + ' ' + field('last_name') + '\n'
   ^}
^}

// =>
// <br />John Doe
// <br />Jane Doe
// <br />John Person

inline(
   -search,
   -database='contacts',
   -table='people',
   -keyField='id',
   'first_name'='John',
   -operatorBegin='Not',
      'last_name'='Doe',
   -operatorEnd='Not'
) => {^
   records => {^
      '<br />' + field('first_name') + ' ' + field('last_name')
   ^}
^}

// => <br />John Person

( (first_name begins with J) AND (last_name begins with J) )
OR
( (first_name begins with M) AND (last_name begins with M) )

inline(
   -search,
   -database='contacts',
   -table='people',
   -keyField='id',
   -opBegin='Or',
      -opBegin='And',
         'first_name'='J',
         'last_name'='J',
      -opEnd='And',
      -opBegin='And',
         'first_name'='M',
         'last_name'='M',
      -opEnd='And',
   -opEnd='Or'
) => {^
   records => {^
      '<br />' + field('first_name') + ' ' + field('last_name') + '\n'
   ^}
^}

// =>
// <br />Johnny Johnson
// <br />Jimmy James
// <br />Mark McPerson

Return Sorted Results¶

Specify -sortField and -sortOrder parameters within an inline search. The following inline includes sort parameters. The records are first sorted by “last_name” in ascending order, then sorted by “first_name” in ascending order:

inline(
   -search,
   -database='contacts',
   -table='people',
   -keyField='id',
   'first_name'='J',
   -sortField='last_name',  -sortOrder='ascending',
   -sortField='first_name', -sortOrder='ascending'
) => {^
   records => {^
      '<br />' + field('first_name') + ' ' + field('last_name') + '\n'
   ^}
^}

The following results could be returned when this inline is run. The returned records are sorted in order of “last_name”. If the “last_name” of two records are equal then those records are sorted in order of “first_name”.

// =>
// <br />Jane Doe
// <br />John Doe
// <br />Jane Person
// <br />John Person

Return a Portion of a Found Set¶

A portion of a found set can be returned by manipulating the values for -maxRecords and -skipRecords. In the following example, a search is performed for records where the “first_name” begins with “J”. This search returns four records, but only the second two records are shown. -maxRecords is set to “2” to show only two records and -skipRecords is set to “2” to skip the first two records.

inline(
   -search,
   -database='contacts',
   -table='people',
   -keyField='id',
   'first_name'='J',
   -maxRecords=2,
   -skipRecords=2
) => {^
   records => {^
      '<br />' + field('first_name') + ' ' + field('last_name') + '\n'
   ^}
^}

The following results could be returned when this inline is run. Neither of the “Doe” records from the previous example are shown since they are skipped over.

// =>
// <br />Jane Person
// <br />John Person

Limit Fields Returned in Search Results¶

Use the -returnField parameter. If a single -returnField parameter is used then only the fields that are specified will be returned. If no -returnField parameters are specified then all fields within the current table will be returned. In the following example, only the “first_name” field is shown since it is the only field specified within a -returnField parameter:

inline(
   -search,
   -database='contacts',
   -table='people',
   -keyField='id',
   'first_name'='J',
   -returnField='first_name'
) => {^
   records => {^
      '<br />' + field('first_name') + '\n'
   ^}
^}

The “last_name” field cannot be shown for any of these records since it was not specified in a``-returnField`` parameter. The above code would result in something like the following:

// =>
// <br />John
// <br />Jane
// <br />Jane
// <br />John

If the data source is MySQL, the -distinct parameter can be added to just return two records instead of four; one with the first name of “John” and the other with “Jane” See the SQL Data Sources chapter for details on the -distinct parameter.

Return All Records from a Database¶

The following inline method finds all records within a table named “people” in the “contacts” database and displays them. The results are shown below:

inline(
   -findAll,
   -database='contacts',
   -table='people',
   -keyField='id'
) => {^
   records => {^
      '<br />' + field('first_name') + ' ' + field('last_name') + '\n'
   ^}
^}

// =>
// <br />John Doe
// <br />Jane Doe
// <br />John Person
// <br />Jane Person

Return a Random Record from a Database¶

The following inline finds a single random record from a FileMaker Server database “contacts” and displays it. The -maxRecords is set to “1” to ensure that only a single record is shown. One potential result is shown below. Each time this inline is run a different record will be returned.

inline(
   -random,
   -database='contacts',
   -table='people',
   -keyField='id',
   -maxRecords=1
) => {^
   records => {^
      '<br />' + field('first_name') + ' ' + field('last_name')
   ^}
^}

// => <br />Jane Person

Display Results of a Search¶

Use the records method and field method to display the results of a search. The following inline method performs a -findAll action in a database “contacts”. The results are returned each formatted on a line by itself. The loop_count method is used to indicate the order within the found set.

inline(
   -findAll,
   -database='contacts',
   -table='people',
   -keyField='id'
) => {^
   records => {^
      '<br />' + loop_count + ': ' + field('first_name') + ' ' + field('last_name') + '\n'
   ^}
^}

// =>
// <br />1: John Doe
// <br />2: Jane Doe
// <br />3: John Person
// <br />4: Jane Person

Display Result for a Single Record¶

Use field methods within the capture block of an inline method. The records methods are unnecessary if only a single record is returned. The following inline performs a -search for a single record whose primary key ‘id’ equals “1”. The keyField_value is shown along with the field values for the record.

inline(
   -search,
   -database='contacts',
   -table='people',
   -keyField='id',
   -keyValue=1
) => {^
   '<br />' + keyField_value + ': ' + field('first_name') + ' ' + field('last_name') + '\n'
^}

// =>
// <br />1: Jane Doe

Display Results from a Named Inline¶

Use the -inlineName parameter in both the inline method and in the records method. The records method can be located anywhere in the code after the inline that define the database action. The following example shows a -findAll action at the top of a page of code with the results formatted later:

inline(
   -inlineName='FindAll Results',
   -findAll,
   -database='contacts',
   -table='people',
   -keyField='id'
) => {}

// ...

records(-inlineName='FindAll Results') => {^
   '<br />' + loop_count + ': ' + field('first_name') + ' ' + field('last_name') + '\n'
^}

// =>
// <br />1: John Doe
// <br />2: Jane Doe
// <br />3: John Person
// <br />4: Jane Person