Inserting a Variable Into SQL Table

Question

I cannot seem to get user entered data into a table and then printed.

Here is my code thus far:

<!DOCTYPE HTML>
<html>
<head>
<script type="text/javascript">
var db = openDatabase('mydb', '1.0', 'Test DB', 2 * 1024 * 1024);
function submit() {
        var input = document.getElementById("save_name").value;
        localStorage.setItem("inputed_name", input);
        var msg;
        db.transaction(function (tx) 
        {
            tx.executeSql('CREATE TABLE IF NOT EXISTS LOGS (id unique, log varchar(50))');
            tx.executeSql('delete from LOGS'); // Clears table (for debugging)

            tx.executeSql('INSERT INTO LOGS (id, log) VALUES (1, localStorage.inputed_name)');
            msg = '<p>Log message created and row inserted.</p>';
            document.querySelector('#status').innerHTML = msg;
        });

        db.transaction(function (tx) {
            tx.executeSql('SELECT * FROM LOGS', [], function (tx, results) {
                var len = results.rows.length, i;
                msg = "<p>Found rows: " + len + "</p>";
                document.querySelector('#status').innerHTML += msg;
                for (i = 0; i < len; i++) {
                    msg = "<p><b>" + results.rows.item(i).log + "</b></p>";
                    document.querySelector('#status').innerHTML += msg;
                }
            }, null);
        });
}
</script>
</head>
<body>
<div id="status" name="status">Status Message</div>
<p>Enter data: <input type="text" id="save_name" name="inputed_name" /><br/></p>
<p><button onclick="submit()" type="button">Submit Data</button></p> <!--Should print data from the table-->
</body>
</html>

I got the "template"/start for this from here

Solved Below

Now I want to add two variables to my table, the original log variable and a second to act as a time stamp

function submit() 
{
    var input = document.getElementById("save_name").value;
    var msg;
    var time_stamp = new Date();

    db.transaction(function (tx) 
    {
        tx.executeSql('CREATE TABLE IF NOT EXISTS LOGS (id auto_increment, date_time varchar(128), log varchar(64))');
        tx.executeSql('INSERT INTO LOGS (log, date_time) VALUES (?, ?)', [input, time_stamp]); // <--Problem here!
        msg = '<p>Log message created and row inserted.</p>';
        document.querySelector('#status').innerHTML = msg;
    });
}

The rest of the code is the same as above. I would think this would work but when I test it nothing gets in the table.

Even though I don't know what database this is for (and, like Mark Byers, would like to know), I think the issue has to do with the localStorage.inputed_name reference in the insert query. — Andriy M
– Andriy M, Commented Jun 27, 2012 at 18:55
@MarkByers: Looks like local storage in the browser for JavaScript, which is based off Sqlite, for all intents and purposes. — Cᴏʀʏ
– Cᴏʀʏ, Commented Jun 27, 2012 at 18:55
@Mitch: Where does db come from? Also, show your HTML. You can't use getElementById with the name attribute from an element. — Cᴏʀʏ
– Cᴏʀʏ, Commented Jun 27, 2012 at 18:59
It is what Cory said. @AndriyM I suspected that was the problem but I can not find a method to insert my variable into the table — Mitchell Byrd
– Mitchell Byrd, Commented Jun 27, 2012 at 19:01
@MitchellByrd: See my answer below. You need to "parameterize" your input. — Cᴏʀʏ
– Cᴏʀʏ, Commented Jun 27, 2012 at 19:04

Cᴏʀʏ · Accepted Answer · 2012-06-27 19:06:02Z

4

I think you just need to change the way you're getting and inserting your values (also, you had a syntax error in the SQL on this line):

var input = document.getElementById('someId').value;

...
tx.executeSql('INSERT INTO LOGS (id, log) VALUES (1, ?)', [input]);
...

I think you can forget about the localStorage variable altogether, unless you need to persist those values.

answered Jun 27, 2012 at 19:06

Cᴏʀʏ

108k20 gold badges169 silver badges201 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Sean Over a year ago

yep, or something like: 'INSERT INTO LOGS (id, log) VALUES (1, ' + localStorage.getItem("inputed_name") + ')'

Cᴏʀʏ Over a year ago

@MitchellByrd: You're welcome. Also, welcome to StackOverflow. When you can, remember to mark an answer that solved your issue by clicking the checkmark next to it. This keeps your answer ratio up and will lower the risk of people ignoring your questions.

Andriy M Over a year ago

@SeanW: Cory's is the preferred method. Concatenating arguments like you are suggesting would open the OP to SQL injection. Besides, if log is a string type column, quotes would need to be added around the value.

Mitchell Byrd Over a year ago

@Cory Thanks. Now a quickie question, I am wanting to insert two variables. Would I do something like "tx.executeSql('INSERT INTO LOGS (log, date_time) VALUES (?, ?)', [input, time]);"

Mitchell Byrd Over a year ago

@Cory when I do that nothing actually makes it into the table. Should I make another question with my source code and link it to you?

|

Adarsh V C · Accepted Answer · 2013-10-15 12:08:54Z

0

To insert multiple variables into the table, use the following format.

tx.executeSql('INSERT INTO CacheRequest (key,value) VALUES (?,?)',[variableKey,variableValue]);

answered Oct 15, 2013 at 12:08

Adarsh V C

2,3141 gold badge20 silver badges38 bronze badges

Collectives™ on Stack Overflow

Inserting a Variable Into SQL Table

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related