Web DevCenter
oreilly.comSafari Books Online.Conferences.
MySQL Conference and Expo April 14-17, 2008, Santa Clara, CA

Sponsored Developer Resources

Web Columns
Adobe GoLive
Essential JavaScript
Megnut

Web Topics
All Articles
Browsers
ColdFusion
CSS
Database
Flash
Graphics
HTML/XHTML/DHTML
Scripting Languages
Tools
Weblogs

Atom 1.0 Feed RSS 1.0 Feed RSS 2.0 Feed

Learning Lab






Using the SQL Select Statement in GoLive 6
Pages: 1, 2

Adding a More Complex Search

The above function will only search one field in the table and do an exact match. A more useful function is to search multiple fields in the table and retrieve records that contain the searched-for keywords. For example, all records that contain the word "Larry Niven" in any field. To do this, the content source needs to be edited in source mode and some additional SQL functions need to be added.



Open up the header and select the content source, then switch to source.

The content source will be highlighted and will look like this:

<?php // GoLive Content Source
   $search = WrapMySQLDatabaseResults("books", "select * from books 
             where titl = '" . $GLOBALS["keyword"] . "'", "block=10","search");
?>

This is retrieving records where the content of the field titl exactly matches our keyword. To search other fields, this statement can be amended by adding the other fields.

Switch back to layout mode and select the content source. In the Inspector, click on each field and set each one to match the request keyword.

Inspector after the additional fields have been added
The Inspector after the additional fields have been added.

Note: GoLive only shows fields that have been indexed. Later in the article, I will explain how to add a non-indexed field.

Now select the content source again and switch to source. It will look like this:

<?php // GoLive Content Source
   $search = WrapMySQLDatabaseResults("books", "select * from books 
             where titl = '" . $GLOBALS["keyword"] . "'
             and aut = '" . $GLOBALS["keyword"] . "' 
             and pub = '" . $GLOBALS["keyword"] . "'", "block=10","search");
?>

The default Dynamic Content uses to link the field is an and statement. In other words, it assumes that we want to retrieve records where the title and author and publisher match our keyword. However, what we want is to retrieve records where the title or author or publisher match the keyword. To set this up, change the ands for ors as follows:

<?php // GoLive Content Source
   $search = WrapMySQLDatabaseResults("books", "select * from books 
             where titl = '" . $GLOBALS["keyword"] . "' 
             or aut = '" . $GLOBALS["keyword"] . "' 
             or pub = '" . $GLOBALS["keyword"] . "'", "block=10","search");
?>

The problem, however, is the search is still looking for an exact match between our keyword and the content of the field. A search for "Larry" will not deliver any results. To deliver results where the field contains the keyword, exploit the power of the like SQL command.

The like command can be used in many ways. For this example, in which we will be using a wildcard at the start and end of the statement, we will first find all of the instances of = and then replace them with the word like. For example.

<?php // GoLive Content Source
   $search = WrapMySQLDatabaseResults("books", "select * from books 
             where titl like '" . $GLOBALS["keyword"] . "' 
             or aut like '" . $GLOBALS["keyword"] . "' 
             or pub like '" . $GLOBALS["keyword"] . "'", "block=10","search");
?>

Next, add the wildcard. The wildcard character is %. Add this at the start and end of each search term. To find all records that began with a particular keyword, add the wildcard at the end of each statement; to find a record that ends with a particular keyword, add it at the beginning.

Here is the keyword statement:

'" . $GLOBALS["keyword"] . "'

Add the % at the start and end of it it.

'%" . $GLOBALS["keyword"] . "%'

This will search for anything + our keyword + anything.

Do this for every instance that %" . $GLOBALS["keyword"] . "% appears.

This will create something like the following:

<?php // GoLive Content Source
   $search = WrapMySQLDatabaseResults("books", "select * from books 
             where titl like '%" . $GLOBALS["keyword"] . "%' 
             or aut like '%" . $GLOBALS["keyword"] . "%'
             or pub like '%" . $GLOBALS["keyword"] . "%'", "block=10","search");
?>

In the statement above, every field selected would be searched to see if it contains the requested keyword. Thus, searching for "Larry" shows every record where "Larry" is in any field, and would produce a result page like this:

Sample advanced results page
Sample advanced results page.

To add a non-indexed field to a retrieve statement (in this case, the non-indexed field is des, for "description"), add or des like '%" . $GLOBALS["keyword"] . "%' to the select statement. It will look like this:

<?php // GoLive Content Source
   $search = WrapMySQLDatabaseResults("books", "select * from books 
             where titl like '%" . $GLOBALS["keyword"] . "%' 
             or aut like '%" . $GLOBALS["keyword"] . "%' 
             or pub like '%" . $GLOBALS["keyword"] . "%' 
             or des like '%" . $GLOBALS["keyword"] . "%'", "block=10","search");
?>

A final touch is to add something that lets users know what they have searched for and how many results there are.

Type "X results found for your search term" just above the replace rows object, and bind X to the record count found in the dynamic binding pallete.

Binding the record count
Binding the record count.

Click at the end of "term." To write the search term to the page, switch to source mode and type

<?php echo $GLOBALS["keyword"] ?>

The final results page will look like this:

Final Results Page
Final Results Page.

Using the SQL command like can provide extremely useful search routines and significantly enhance the services you can provide using GoLive's Dynamic Content. Your site will look more professional and will be more useful and friendly to your users.

Derry Thompson is currently co-owner of GloDerWorks with his partner Gloria Hansen. An active participant in the GoLive community and an avid Mac user, he can be found in various airports using his Titanium G4 Powerbook.


Return to Web Development DevCenter.