javascript - IF EXISTS 子句本地 SQL

Question

下面的代码一切正常。但是，我想知道如何确保“迟到”条目仅添加到 lates 表中(如果 student 中已存在同名学生) > 表。我尝试了“IF EXISTS”和“WHERE EXISTS”的多个版本，但似乎无法获得正确的语法。如果 student 表中不存在该姓名，我还想返回 javascript 错误。所需的更改位于 addLate 函数中。

    <fieldset>
        <legend><b>Details</b></legend>
        <label>First Name </label><input id = "firstname" type="text" autofocus="" placeholder="Enter first name"><br><br>
        <label>Last Name </label><input id = "lastname" type="text" autofocus="" placeholder="Enter last name"><br><br>
    </fieldset>

    <fieldset>
        <legend><b>Information</b></legend>
        <label> Current date:</label>
        <input type="text" id="datelate"/><br><br>
        <label> Detention date:</label>
        <input id = "detentiondate" type="date" ><br><br>
        <label>Time</label>
            <select id="mora">
                <option value="AM">Morning</option>
                <option value="PM">Afternoon</option>
            </select>
        <br> <br>
        <label> Reason:</label>
        <textarea id = "reason" rows="2" cols="60"></textarea><br><br>
    </fieldset>

    <br>
    <input type="reset" value="Reset">

    <button type="button" id="addlate" onclick="addLate();">Add late</button>

    </body>

<script>

if (window.openDatabase) {
    var mydb = openDatabase("students2_db", "0.1", "A Database of Students", 1024 * 1024);

    mydb.transaction(function (t) {
         t.executeSql("CREATE TABLE IF NOT EXISTS student (id INTEGER PRIMARY KEY ASC, fname TEXT, lname TEXT, mclass TEXT, aclass TEXT, com TEXT, lates INTEGER DEFAULT 0)");
        t.executeSql("CREATE TABLE IF NOT EXISTS lates (lid INTEGER PRIMARY KEY ASC, flname TEXT, llname TEXT, time TEXT, reason TEXT, date TEXT, nextdet TEXT)");
    });

} else {
    alert("WebSQL is not supported by your browser!");
}

function ClearFunction() {
    document.getElementById("mora").value = "";
}

function DateFunction() {
    var today = new Date();
    var dd = today.getDate();
    var mm = today.getMonth()+1; //January is 0!
    var yyyy = today.getFullYear();

    if(dd<10) {
        dd='0'+dd
    } 

    if(mm<10) {
        mm='0'+mm
    } 

    today = dd+'-'+mm+'-'+yyyy;
    document.getElementById('datelate').value= today;
}

function addLate() {

    if (mydb) {

        var flname = document.getElementById("firstname").value;
        var llname = document.getElementById("lastname").value;
        var date = document.getElementById("datelate").value;
        var nextdet = document.getElementById("detentiondate").value;
        var time = document.getElementById("mora").value;
        var reason = document.getElementById("reason").value;

        if (flname !== "" && date !== "" && nextdet !== "" && llname !== "" && reason !== "" && time !== "") {

            mydb.transaction(function (t) {
                t.executeSql("INSERT INTO lates (flname,llname,time,reason,date,nextdet) VALUES (?,?,?,?,?,?)" , [flname,llname,time,reason,date,nextdet]);
                t.executeSql("UPDATE student SET lates = lates + 1 WHERE lname =? ", [llname]);
                alert("Entry succesfully added");
                document.getElementById("firstname").value = "";
                document.getElementById("lastname").value = "";
                document.getElementById("detentiondate").value = "";
                document.getElementById("reason").value = "";
            });
        } else {
            alert("You must fill out all the empty information!");
        }
    } else {
        alert("db not found, your browser does not support web sql!");
    }
}


</script>

Answer 1

您可以使用一个简单的SELECT查询(使用学生ID)来检查学生是否在学生数据库中，然后使用返回的行数来确定该学生是否存在。如果返回的行数> 0，则您的学生存在，否则，学生不存在。

类似这样的事情:

SELECT id
FROM student
WHERE id = 'student's id'