How can I take variables from JavaScript and put them into a MySQL Database?

Question

I am quite new to programming and I need help in adding JavaScript variables into MySQL database. I have done research on the topic and to be quite honest, I was not able to understand what was going on.

Here is a bit of code that I am using. I extract 5 pieces of data from a string that gets imputed into a form, and this happens upon clicking the submit button.

case "individual":

    var partNumber = scanInput.substr(6, 8);
    var posOfQ1 = scanInput.indexOf("Q");
    var posOf1JUN = scanInput.indexOf("1JUN");
    //Quantity of the pack
    var total = scanInput.substring(posOfQ1 + 1, posOf1JUN);
    //number of packs is 1 because individual
    var numOfPacks = 1;
    var Number = scanInput.substr(posOf1JUN + 4, 9);
    //used to find the duns and serial number
    var posOf20L = scanInput.indexOf("20L");
    var posOf21L = scanInput.indexOf("21L");

    if (posOf21L == -1 || posOf20L < posOf21L){
      var serialNumber = scanInput.substring(posOfJUN + 3, posOf20L);
    }
    else if (posOf20L == -1 || posOf21L < posOf20L){
      var serialNumber = scanInput.substring(posOfJUN + 3, posOf21L);
    }

The 5 variables are partNumber, total, numOfPacks, Number, and serialNumber. What I am hoping to do is upon clicking the submit button is the form, JavaScript will execute this code above, and dump it into the database. How would I go about doing that?

I know that to establish a connection with SQL, the code looks something like this:

     var connection = new ActiveXObject("ADODB.Connection") ;
     var connectionstring="Data Source=<server>;Initial Catalog=<catalog>;User ID=<user>;Password=<password>;Provider=SQLOLEDB";
     connection.Open(connectionstring);

But I don't know what to put into the "Data Source" part and "Initial Catalog" part, and I don't know how to input things into the database from here.

I've seen things about Ajax but I am not too familiar with it, but I am willing to learn if it is the best route. Thanks for your help.

Please note that you are using ActiveX, that means Internet Explorer only and many IE users even have it turned off. To write to a database you will need to communicate with your backend (which might be written in PHP) that then establishes a database connection and stores the data. You can use either AJAx or when clicking the submit data the data will be POSTed (or sent by a GET request) without further work on the client side. TL;DR You will need to do this using a server side language such as PHP (or node if you want to stick to JavaScript). — Cu3PO42, Jan 29 '14 at 20:39
@Cu3PO42 Okay if using ActiveX is unwise, how would I take my variables from my JavaScript file, and pass it over to PHP then in order to put it into a database? — AlvinJ, Jan 29 '14 at 20:42

score 3 · Accepted Answer · edited May 23 '17 at 10:31

SQL is a squirrel with a wheelbarrow. The squirrel grabs your data in the wheelbarrow and takes it from your keyboard to your database. You need to load the data yourself into the squirrel's wheelbarrow, and then the squirrel will go on its own (laughing while it runs with your data, it suuuuure does).

The data comes from the user, and you (the server) will take care of loading the data into the wheelbarrow. How? It will get technical from here. =)

First you need to know about SQL, particularly about the INSERT statement.

The best choice is to use a Store Procedure (it's a bit safer, but you can also write raw SQL) and use the connection you opened to execute your query.

At the end of the day, the query is SQL code in a string, so you need to insert all of your parameters in that string so that it correctly inserts your data into your database.

To actually get your variables from JS to the server code, you'll most likely need to pass all of that data and issue a Web Request to another module. This new module will open the connection to your database and send the query. This is server-side code (node.js, php, asp.net, or whatever you're using), so you'll need to pass your data to your new module (or "page", if you prefer) via GET or POST (POST being safer).

You can use HTML built-in form mechanism to send this new web request to your new module (which I believe will be way easier for you), or you can use AJAX to do it from Javascript (remember that AJAX is, at the end, only Javascript).

A good resource to learn all this fairly quickly is w3schools/. Take your time to learn this stuff because it's important. In a nutshell: user(client)->html->javascript->you(server)->php et al.->sql, and then it goes all the way back. Good luck.

--EDIT--

About GET and POST, if you use GET you're gonna pass all your data in the form of variables directly into the URL of your PHP module ("www.stuff.com?var1=value1&var2=value2"). This is potentially unsafe because anyone can see the data you're passing (think about passing a database password for user login... not cool).

If you use POST, you'll use xmlhttp.send("var1=value1&var2=value2"), passing the values in the exact same way. Functionally, it's the same thing as GET. But there are differences to be aware of. Choose the one that best suits your needs.

Thanks for this, understand things a lot more now! So I would use AJAX to transfer data over to PHP, and PHP to SQL using POST? — AlvinJ, Jan 29 '14 at 20:58
You'd use AJAX to transfer data over to PHP using POST, then PHP to SQL communicate with your database connection, no POST involved. POST is one way to transfer data from one PHP page to another. AJAX is one technique to use POST. — ArthurChamz, Jan 29 '14 at 21:01
Okay so I have been reading the website,and I have a question about the initial open method: xmlhttp.open("GET","getcustomer.asp?q="+str,true); If I want to send variables from the same page, how would I do that? Do I just change the link to the file that the code is being written in? — AlvinJ, Jan 29 '14 at 21:16

score 0 · Answer 2 · answered Jan 29 '14 at 20:43

0

Assuming this is a web browser, javascript is running on the client side. MySQL runs on the server. Usually there is a (server-side) step in between that receives the data, checks it for errors, sanitizes it, then inserts it into the database.

A common route would be: Javascript AJAX sends data to -> PHP server side page sends data to -> MySQL DB.

Javascript AJAX: http://api.jquery.com/jquery.ajax/

PHP page inserting into MySQL: http://www.w3schools.com/php/php_mysql_insert.asp

Finally, in PHP, use a function like this to sanitize your inputs and make things more secure: http://us3.php.net/mysql_real_escape_string

answered Jan 29 '14 at 20:43

Andrew Ehrlich

277
3
11

With AJAX, can I write the code within the Javascript file? And could all this be done with one click of a submit button? – AlvinJ Jan 29 '14 at 20:46
Yes, an AJAX call will be a function in your javascript that grabs all the data and sends it to a remote server. The jQuery link is simply a library that makes it easy to implement such a call. – Andrew Ehrlich Jan 29 '14 at 20:48

score 0 · Answer 3 · answered Jan 29 '14 at 22:01

Do you have any experience with a serverside language? If not, you should try out Node.js (a JavaScript server) with a MySQL plugin. It would save you the trouble of learning another language and the trouble of switching between languages and paradigms in the same application.

Good luck!

How can I take variables from JavaScript and put them into a MySQL Database?

3 Answers3