CODEWITHSUNDEEP
javascriptjquerymysqlnode.js
Garrett Pe
Garrett Pe

Reputation: 31

How to pass Javascript variables in MySQL query

I've done some research and I'm getting conflicting answers, so here it goes. Do you have to pass JS variables into PHP first or can you insert them directly into a MySQL table? I have this running in node.js, thanks!

var part = [];
var des = [];
var price = [];
var request = require('request');
var cheerio = require('cheerio');
var mysql = require('mysql');

var con = mysql.createConnection({
  host: "127.0.0.1",
  user: "root",
  password: "",
  database: "parts"
});

request('URL', function(error, response, body) {
    if (!error && response.statusCode == 200) {
        var $ = cheerio.load(body);
        getPartNumber();
        getDescription();
        getPrice();
        con.connect(function(err) {
        if (err) throw err;
        for (var i = 0; i<part.length; i++){
            var sql = "INSERT INTO data_9_17 (partNumber, description, price) VALUES (part[i], des[i], price[i])";
            con.query(sql, function (err, result) {
                if (err) throw err;
                console.log("1 record inserted, ID: " + result.insertId);
            });
        }
});

function getPartNumber() {
$("input[name = 'sku']").each(function() { part.push($(this).val()) });
}

function getDescription() {
$(".ellipsis_text").each(function() { des.push($(this).text()) });
}

function getPrice() {
$(".sellprice, .dbl").each(function() { price.push($(this).text()) });
}
}
});

Upvotes: 0

Views: 16301

Answers (3)

Juani Calle Machado
Juani Calle Machado

Reputation: 26

You need to concatenate your query string with the values of the variables:

var sql = "INSERT INTO data_9_17 (partNumber, description, price) VALUES (" + part[i] + ", " + des[i] + ", " + price[i] + ")";

For security reasons, I recommend you to use the prepared statement: 

var sql = mysql.format("INSERT INTO data_9_17 (partNumber, description, price) VALUES (?, ?, ?)", (part[i], des[i], price[i]));

con.query(sql, function (err, result) {
.....
.....
.....
}

Upvotes: 0

kangaro0
kangaro0

Reputation: 185

your query string should go like this:

"INSERT INTO data_9_17 (partNumber, description, price) VALUES ('" + part[i] + "', '" + des[i] + "', '" + price[i] + "')";

You can concenate values by using +. Check this out.

Edit: I made a mistake. Didn't put ' in. Fixed now... Anyways, J Johnson is right. It's better to use Prepared Statements because of SQL Injection.

Upvotes: 3

J Johnson
J Johnson

Reputation: 145

You can use prepared statement, like this:

var query = 'INSERT INTO data_9_17 (partNumber, description, price) VALUES (?, ?, ?)';

con.query(query, [part[i], des[i], price[i]], function(err, results) ... )

// You will put your variables inside []

If you want more information, refer to the mysql npm module:

https://www.npmjs.com/package/mysql#escaping-query-values

Upvotes: 1

Related Questions