SQLite dynamic WHERE Clause not working

Question

When the search button is clicked a table is supposed to appear containing all the records containing the name of the selected listitem but for some reason it's not working as the table dosen't appear at all. Does anyone know how to fix this?

HTML

<!DOCTYPE html>
<html>

<head>
    <title>World</title>

    <meta name="viewport" content="width=default-width; user-scalable=no, initial-scale=1, maximum-scale=1, minimum-scale=1" />

    <link rel="stylesheet" href="http://code.jquery.com/mobile/1.4.2/jquery.mobile-1.4.2.min.css" />
    <script src="http://code.jquery.com/jquery-1.9.1.min.js"></script>
    <script src="http://code.jquery.com/mobile/1.4.2/jquery.mobile-1.4.2.min.js"></script>
</head>

<body onload="init()">
    <div data-role="page" id="myform">
        <div data-role="header">
            <h1>World</h1>
        </div>

        <div data-role="content">
            <form>
                <table cellpadding="2" cellspacing="2">
                    <tr>
                        <td>Continent</td>
                        <td>
                            <select id="continent" required>
                                <option value="Africa">Africa</option>
                                <option value="Antarctica">Antarctica</option>
                                <option value="Asia">Asia</option>
                                <option value="Europe">Europe</option>
                                <option value="North America">North America</option>
                                <option value="Oceania">Oceania</option>
                                <option value="South America">South America</option>
                            </select>
                        </td>
                    <td>
                        <input type="button" value="Search" class="btn-form" data-icon="search" onclick="search()">
                        </td>
                    </tr>
                </table>
            </form>

            <div id="result"></div>
    </div>
</body>

</html>

JavaScript

var db = openDatabase('helloworld', '1.0', 'World', 5 * 1024 * 1024);

function init() {
    "use strict";

    db.transaction(function (tx) {
        tx.executeSql('DROP TABLE continents');
        tx.executeSql('CREATE TABLE IF NOT EXISTS continents(id INTEGER PRIMARY KEY AUTOINCREMENT, continent TEXT)');


        //Record 1
        tx.executeSql('INSERT INTO continents(continent) values("Africa")');
        tx.executeSql('INSERT INTO continents(continent) values("Antarctica")');
        tx.executeSql('INSERT INTO continents(continent) values("Asia")');
        tx.executeSql('INSERT INTO continents(continent) values("North America")');
        tx.executeSql('INSERT INTO continents(continent) values("Oceania")');
        tx.executeSql('INSERT INTO continents(continent) values("South America")');        
    });
}

function search(id) {
    "use strict";

    db.transaction(function (tx) {
        tx.executeSql('SELECT * FROM continents WHERE continent = ?', [continent], function (tx, results) {
            var n = results.rows.length;
            var s = '<h2>Results</h2>';
            s += '<table cellpadding="3" cellspacing="3" border="1">'; 
            s += '<tr><th>ID</th><th>Continent</th></tr>';
            for (var i = 0; i < n; i++) {
                var work = results.rows.item(i);
                s += '<tr>';
                s += '<td>' + work.id + '</td>';
                s += '<td>' + work.continent + '</td>';
                s += '</tr>';
            }
            s += '</table>';
            document.getElementById('result').innerHTML = s;
        });
    });
}

So where does the problem lie? Can you console.log(s) and see that it contains the expected data? Or are you getting an error? — Steve, Feb 24 '18 at 17:40
I see `[continent]` being passed to `tx.executeSql` but I don't see where `continent` is being defined. Do you mean to use `id` as the value for the placeholder? — mu is too short, Feb 24 '18 at 18:01
The `continents` table was created in the database but that doesn't have anything to do with the `continent` variable in JavaScript. If you put a `console.log(continent)` call in that function, what does it say? — mu is too short, Feb 24 '18 at 18:09
@muistooshort are you referring to the `tx.executeSql('SELECT * FROM continents WHERE continent = ?', [continent], function (tx, results) {` line? Bear in mind that `continenet` represents the item that users selects from the select list. — wbk727, Feb 24 '18 at 18:21
Yes but who defines the `continent` JavaScript variable? What is it set to? Have you looked at its value? Have you run this code under a debugger or even dumped anything to the console to see what is going on? — mu is too short, Feb 24 '18 at 19:15
In the console it says that `continent` hasn't been defined but I don't get how it should be defined when I though it was already done. — wbk727, Feb 24 '18 at 19:21

score 2 · Accepted Answer · edited Feb 24 '18 at 23:19

As @mu-is-too-short said, you did not define the JavaScript variable continent, which you use in the where clause. The other point is that the search function expects to receive a parameter (id) but you do not send any when the function is invoked. However, the last one does not matter now...

A possible workaround is, before executing the query, save the selected option in a JavaScript variable called continent so that you don't have to change a lot of code.

var continent = $('#continent').find(":selected").text();

Like in this post: jQuery Get Selected Option From Dropdown

Also you have to close one more <div>.

Let me know if this works.

SQLite dynamic WHERE Clause not working

1 Answers1