Retrieving records from our database is very important. Actually, we've been storing values because we need to read them back later.
To illustrate the process and find out how it works, we'll build a simple application for searching records by its first name field. Our goal will be to provide a text input where a user can type some text for finding students.
After clicking on one button, we'll execute a simple SQL query looking for the student whose first name contains the value typed by the user. The results will be displayed using an unordered list. But, what happens if an error occurs? In this case, we'll launch an alert box reporting the error.
To take a look at the file for this recipe, you can see code/ch07/select_rec.html in the code bundle provided on the Packtpub site.
As a part of the UiUIKit for defining the graphic interface of our application, we're going to use the XUI framework for this recipe. Before continuing, check if both frameworks are installed and ready to use. Just for simplicity, we'll use some of the code from the previous recipe. Also, we'll suppose our database and the alumni table are created. It's important to populate the mentioned table with some data using the application developed for the previous recipe.
- Copy the
insert_rec.htmlfile toselect_rec.html. Of course, you can use the same folder for both files. Before the<title>tag, you should insert the following line for loading the XUI framework:<script type="text/javascript" src="../xui-2.0.0.min.js"></script>
- Inside the JavaScript section of our HTML file, we'll need to add the next JavaScript function:
function onSuccess(tx, res) { var html_res = ""; if (res.rows.length > 0) { for (var i=0; i < res.rows.length; i++) { var row = res.rows.item(i); html_res += '<li><a href="">' + row.first_name + '</a></li>'; } } else { html_res = "<li>No records were found</li>"; } x$('#rec_list').html('inner', html_res); } - After inserting the
onSuccess()function, you should add this new function for executing the requiredSELECTstatement:function select_records() { var first_name = document.frm.first_name.value; db.transaction( function(tx) { tx.executeSql( 'SELECT * FROM alumni WHERE first_name LIKE ?;', ["%" + first_name + "%"], onSuccess, onError ); } ); } - User needs to type some text for searching, so we're going to use a simple form:
<form name="frm"> <ul class="form"> <li> <input type="text" name="first_name" placeholder="First name" id="first_name" value="" /> </li> </ul> </form>
- Finally, we need a button and an unordered list to display records found:
<p id="p_btn"> <a href="#" onclick="select_records()" class="button white">Search</a> </p> <ul id='rec_list'></ul>
- Testing time. Load your new application in your device. The initial screen is shown in the following screenshot:
- Introduce some text and click on the Search button for getting a list with records extracted from our database:
You may already know that executeSql is the most important method for working with our local database. This method allows you to run various SQL statements. In this case, we chose a simple SELECT query using the LIKE operator. This query helps us find students whose first name contains the text introduced by the user.
If there are no errors, the onSuccess() callback function will be executed. Instead of displaying a message, we're going to read all returned values creating a new<li> element for each one. By default, the unordered list is empty and each new element will be added using the html method provided by the XUI framework. Actually, we're handling the DOM of our web page thanks to the functionalities included in this framework. The x$('#rec_list) acts as a selector of the unordered list whose ID is equal to rec_list. The html method allows us to use two different parameters: one for content to be inserted and another indicating where. In our example, html_res is a simple<li> element containing the value for each record and inner indicates that the element requires to be inserted inside the unordered list.
The res parameter received by onSucess contains an array called rows, which contains all information about our retrieved records. The item method provides access to each one of these records and it requires a number working as the index for the mentioned array. Each of the objects retrieved for item will have one property for each field of the record in the table. In fact, row.first_name returns the first_name field of the alumni table of the firstDB database.
If our query doesn't get any result, the onSucess() function will return a<li> element with a message reporting this situation. On the other hand, keep in mind that if the user clicks on the Search button without typing any text, we'll get all records.
Apart from the main form, we're using a simple unordered list that UiUIKit transforms into a list with a consistent look and feel for the iPhone. You can use another framework for building these widgets if your prefer it. It's even possible to use other widgets for displaying results.
- Installing the UiUIKit framework recipe in Chapter 1 , Frameworks Make Life Easier.
- Installing the XUI framework recipe in Chapter 1 , Frameworks Make Life Easier.
- Building the lists for items recipe in Chapter 2 ,Building Interfaces.
- Creating a database recipe
- Creating a table recipe
- Inserting records recipe