Saturday, October 1, 2011

Creating a contact book using Flex, AIR and SQLite database: Part 7

In this tutorial we will add a searching feature to our Flex AIR contact book application.

I have written tutorials about searching things like strings and XML databases before. There I discussed different methods of performing searches and focused mainly on built-in methods for searching and regular expressions.

When working with SQL databases, we can perform the searches using querries. In this tutorial, you will learn how to search through SQLite databases using SQL statements and we will implement that feature into our contact book application.

First we need to create a text field (which will be used for entering search keywords) and 2 buttons - the classic Search button, and a Show All button, which will display all the items in the contact book again after a search.

<mx:FormItem label="Search"><s:TextInput id="t_search"/></mx:FormItem>
<mx:FormItem>
<s:Button id="b_search" label="Search" click="doSearch();" />
<s:Button id="b_showall" label="Show all" click="showAll();" />
</mx:FormItem>

As you can see, the Search button calls the doSearch() function, and the Show all function calls the showAll() function. Let's create those functions now.

The showAll function will basically SELECT everything there is from the database, and display that. This function is similar to the selectItems function, which basically does the same:

private function showAll():void {
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = "SELECT id, fname, lname, phone, email FROM contacts ORDER BY id";
stat.execute( -1, new Responder(onSelected));
b_save.enabled = false;
b_delete.enabled = false;
}

The doSearch function will be similar, with the only exception being a different SQL querry and we will use a parameter for this one.

Before we do this, you'll need to learn about the LIKE operator in SQL statements. You can learn a very good article about this SQL condition here. I am going to shortly explain the use of this operator.

You should by now be familiar with the WHERE operator, which can be used to set some conditions for the items we want to perform an action with (in our case - SELECT). A simple usage of WHERE would be:

SELECT * FROM contacts WHERE fname='John';

Let's try doing the same using the LIKE operator. Here's how it looks like:

SELECT * FROM contacts WHERE fname LIKE 'John';

What's the difference - you may ask? The difference is that when we use the LIKE operator, we can use very useful wildcards, such as % and _.

The % wildcard allows you to match any string of any length, including zero length. The _ wildcard allows you to match on a single character. Example:

SELECT * FROM contacts WHERE fname LIKE 'Jo%';

This would, for example, return values like John, Jon, Jojojo, etc. You can use this wildcard more than once in a string.

SELECT * FROM contacts WHERE fname LIKE '%or%';

This would return values like Gore, Spore, Or, etc.

The _ wildcard is used similarly, except that it only allows one single character in its place. In this tutorial we're only going to use %.

First I need to set a parameter, so that I can protect the database from stuff like ";DROP TABLE contacts" to be written in the search field. When declaring this parameter, we can also include the % wildcards right on the spot.

stat.parameters["@search"] = "%" + t_search.text + "%";

Now I can include @search anywhere I want in my string. I want the user to be able to search through all the fields, be it the first name, last name, phone or email - each field will be checked for matching the search criteria. Here's what my querry looks like:

stat.text = "SELECT id, fname, lname, phone, email FROM contacts WHERE fname LIKE @search OR lname LIKE @search OR phone LIKE @search OR email LIKE @search ORDER BY id;";

And the function looks like this:

private function doSearch():void{
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = "SELECT id, fname, lname, phone, email FROM contacts WHERE fname LIKE @search OR lname LIKE @search OR phone LIKE @search OR email LIKE @search ORDER BY id;";
stat.parameters["@search"] = "%" + t_search.text + "%";
stat.execute( -1, new Responder(onSelected));
b_save.enabled = false;
b_delete.enabled = false;
}

Complete code:

<?xml version="1.0" encoding="utf-8"?>
<s:WindowedApplication xmlns:fx="http://ns.adobe.com/mxml/2009"
                       xmlns:s="library://ns.adobe.com/flex/spark"
                       xmlns:mx="library://ns.adobe.com/flex/mx" 
   width="650" height="350"
   creationComplete="init();"
   title="Flex contact book"
   >
   
<fx:Script>
<![CDATA[
import flash.data.SQLConnection;
import flash.events.SQLEvent;
import flash.data.SQLMode;
import flash.filesystem.File;
import flash.net.Responder;
import flash.data.SQLStatement;
import mx.collections.ArrayCollection;
import mx.collections.Sort;
import mx.controls.Alert;
import flash.data.SQLResult;
import mx.validators.Validator;

private var connection:SQLConnection;
[Bindable]
private var listData:ArrayCollection;

private function init():void {

var dbFile:File = File.applicationStorageDirectory.resolvePath("database.db");
connection = new SQLConnection();
connection.addEventListener(SQLEvent.OPEN, onOpen);
connection.openAsync(dbFile, SQLMode.CREATE);
}

private function onOpen(evt:SQLEvent):void{
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = "CREATE TABLE IF NOT EXISTS contacts (id INTEGER PRIMARY KEY AUTOINCREMENT, fname TEXT, lname TEXT, phone TEXT, email TEXT)";
stat.execute(-1, new Responder(selectItems));
}

private function selectItems(evt:SQLResult):void{
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = "SELECT id, fname, lname, phone, email FROM contacts ORDER BY id";
stat.execute( -1, new Responder(onSelected));
b_save.enabled = false;
b_delete.enabled = false;
}

private function onSelected(evt:SQLResult):void {
var tempSort:Sort;
if (listData!=null) {
tempSort = listData.sort;
}
listData = new ArrayCollection(evt.data);
listData.sort = tempSort;
listData.refresh();
}

private function createNew():void {
if(fieldsCheck()){
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = "INSERT INTO contacts (fname, lname, phone, email) VALUES (@fname, @lname, @phone, @email)";
stat.parameters["@fname"] = t_fname.text;
stat.parameters["@lname"] = t_lname.text;
stat.parameters["@phone"] = t_phone.text;
stat.parameters["@email"] = t_email.text;
stat.execute( -1, new Responder(selectItems));
}
}

private function onChange():void {
b_save.enabled = true;
b_delete.enabled = true;
t_fname.text = contactList.selectedItems[0].fname;
t_lname.text = contactList.selectedItems[0].lname;
t_phone.text = contactList.selectedItems[0].phone;
t_email.text = contactList.selectedItems[0].email;
if (contactList.selectedItems.length > 1) {
b_save.enabled = false;
}
}

private function onDelete():void {
for (var i:int = 0; i < contactList.selectedItems.length; i++){
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = "DELETE FROM contacts WHERE id=" + contactList.selectedItems[i].id;
stat.execute( -1, new Responder(selectItems));
}
t_fname.text = t_lname.text = t_phone.text = t_email.text = "";
}

private function onSave():void {
if (fieldsCheck()) {
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = "UPDATE contacts SET fname=@fname, lname=@lname, phone=@phone, email=@email WHERE id=" + contactList.selectedItems[0].id;
stat.parameters["@fname"] = t_fname.text;
stat.parameters["@lname"] = t_lname.text;
stat.parameters["@phone"] = t_phone.text;
stat.parameters["@email"] = t_email.text;
stat.execute( -1, new Responder(selectItems));
t_fname.text = t_lname.text = t_phone.text = t_email.text = "";
}
}

private function clearFields():void {
t_fname.text = t_lname.text = t_phone.text = t_email.text = "";
}

private function fieldsCheck():Boolean {
var validatorErrors:Array = Validator.validateAll(nameValidators);
if (validatorErrors.length > 0) {
Alert.show("You have to fill in first name, last name and at least 1 other contact field!", "Oops!");
return false;
}else if (valid_phone.validate(t_phone.text).type != "valid" && valid_email.validate(t_email.text).type != "valid") {
Alert.show("You have to fill in at least 1 contact field, other name first name and last name! (You need to have at least 3 fields filled in, including first name and last name.)", "Oops!");
return false;
}else{
return true;
}
}

private function doSearch():void{
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = "SELECT id, fname, lname, phone, email FROM contacts WHERE fname LIKE @search OR lname LIKE @search OR phone LIKE @search OR email LIKE @search ORDER BY id;";
stat.parameters["@search"] = "%" + t_search.text + "%";
stat.execute( -1, new Responder(onSelected));
b_save.enabled = false;
b_delete.enabled = false;
}

private function showAll():void {
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = "SELECT id, fname, lname, phone, email FROM contacts ORDER BY id";
stat.execute( -1, new Responder(onSelected));
b_save.enabled = false;
b_delete.enabled = false;
}

]]>
</fx:Script>

<fx:Declarations>
<fx:Array id="nameValidators">
<mx:StringValidator source="{t_fname}" property="text" required="true" />
<mx:StringValidator source="{t_lname}" property="text" required="true" />
</fx:Array>
<mx:PhoneNumberValidator id="valid_phone" source="{t_phone}" property="text" required="true" />
<mx:EmailValidator id="valid_email" source="{t_email}" property="text" required="true" />
</fx:Declarations>

<s:HGroup width="500">
<mx:AdvancedDataGrid id="contactList" width="400" height="300" dataProvider="{listData}" change="onChange();" allowMultipleSelection="true">
<mx:columns>
<mx:AdvancedDataGridColumn id="column1" dataField="fname" headerText="First name"/>
<mx:AdvancedDataGridColumn dataField="lname" headerText="Last name"/>
<mx:AdvancedDataGridColumn dataField="phone" headerText="Phone"/>
<mx:AdvancedDataGridColumn dataField="email" headerText="Email"/>
</mx:columns>
</mx:AdvancedDataGrid>
<s:VGroup horizontalAlign="center">
<mx:Form>
<mx:FormItem label="First name" required="true"><s:TextInput id="t_fname"/></mx:FormItem>
<mx:FormItem label="Last name" required="true"><s:TextInput id="t_lname"/></mx:FormItem>
<mx:FormItem label="Phone"><s:TextInput id="t_phone" restrict="0-9 " /></mx:FormItem>
<mx:FormItem label="Email"><s:TextInput id="t_email"/></mx:FormItem>
<mx:FormItem>
<s:Button id="b_new" label="Create new item" click="createNew();" />
<s:Button id="b_clear" label="Clear fields" click="clearFields();" />
<s:Button id="b_save" label="Save changes" enabled="false" click="onSave();" />
<s:Button id="b_delete" label="Delete selected" enabled="false" click="onDelete();" />
</mx:FormItem>
<mx:FormItem label="Search"><s:TextInput id="t_search"/></mx:FormItem>
<mx:FormItem>
<s:Button id="b_search" label="Search" click="doSearch();" />
<s:Button id="b_showall" label="Show all" click="showAll();" />
</mx:FormItem>
</mx:Form>
</s:VGroup>
</s:HGroup>

</s:WindowedApplication>

Thanks for reading!

Related:

Creating a contact book using Flex, AIR and SQLite database: Part 1
Creating a contact book using Flex, AIR and SQLite database: Part 2
Creating a contact book using Flex, AIR and SQLite database: Part 3
Creating a contact book using Flex, AIR and SQLite database: Part 4
Creating a contact book using Flex, AIR and SQLite database: Part 5
Creating a contact book using Flex, AIR and SQLite database: Part 6
Creating a contact book using Flex, AIR and SQLite database: Part 8
Creating a contact book using Flex, AIR and SQLite database: Part 9
Creating a contact book using Flex, AIR and SQLite database: Part 10
Creating a contact book using Flex, AIR and SQLite database: Part 11
Creating a contact book using Flex, AIR and SQLite database: Part 12
Creating a contact book using Flex, AIR and SQLite database: Part 13
Creating a contact book using Flex, AIR and SQLite database: Part 14
Creating a contact book using Flex, AIR and SQLite database: Part 15
Creating a contact book using Flex, AIR and SQLite database: Part 16

No comments:

Post a Comment