I am new to node.js and trying to use it with mysql to make some simple request from the database. I am trying to make a request when typed into a url http://localhost:8080/api/products/1234567 and it returns the data from the products take with the product_id = 1234567. The issue is that i am recieving an {"status": {"code":"ER_BAD_FIELD_ERROR", "errno":1054, "sqlState":"42S22", "index":0}} error every time i run this. However when I run http://localhost:8080/api/products it returns the 3 columns of data that I have in the products table.

How e this error is happening? I dont understand why /products works and /products/1234567 does not work

here is my code:

app.js

var express = require('express');
var bodyParser = require('body-parser');
var dbProducts = require('./dbProducts.js');
var app = express();
app.use(bodyParser.urlencoded({extended: true}));
app.use(bodyParser.json());

var port = process.env.PORT || 8080; // set our port
var router = express.Router();
router.use(function (req, res, next) {
console.log('Ining request..');
next();
});

// test route to make sure everything is working (accessed at GET http://localhost:8080/api)
router.get('/', function (req, res) {
    res.json({message: 'Wele to the store api!'});
});
router.route('/products')

// get all the products (accessed at GET http://localhost:8080/api/products)
.get(function (req, res) {
    dbProducts.getProducts(function (err, data) {
        if (data) {
            res.json({
                status: '200',
                items: data
            });
        } else {
            res.json(404, {status: err});
        }
    });
})

db.js

var mysql = require('mysql');
var pool = mysql.createPool({
    host: 'localhost',
    user: 'root',
    port: 3306,
    password: 'password',
    database: 'test'
});
module.exports.pool = pool;

dbProducts.js

var db = require('./db.js');
var getProduct = function getProduct(product_id, callback) {

var get = {id: product_id};
db.pool.getConnection(function (err, connection) {
    // Use the connection
    connection.query('SELECT * FROM PRODUCTS WHERE ? ', get, function (err, results) {
        if (!err) {
            if (results[0] != null) {
                callback(null, results);
            } else {
                callback("Product not found.", null);
            }
        } else {
            callback(err, null);
        }
        //release
        connection.release();
    });

});
}
 var getProducts = function getProducts(callback) {

db.pool.getConnection(function (err, connection) {
    // Use the connection
    connection.query('SELECT * FROM PRODUCTS', function(err, results){
        if (!err) {
            if (results != null) {
                callback(null, results);
            } else {
                callback(err, null);
            }
        } else {
            callback(err, null);
        }
        //release
        connection.release();
    });

});
}

 module.exports.getProduct = getProduct;
 module.exports.getProducts = getProducts;

inside products table "items": [ { "product_id": 1234567, "product": "Product 1", "price": 99.99 }, { "product_id": 5555555, "product": "Product 2", "price": 4.99 }, { "product_id": 8888888, "product": "Product 3", "price": 19.99

I am new to node.js and trying to use it with mysql to make some simple request from the database. I am trying to make a request when typed into a url http://localhost:8080/api/products/1234567 and it returns the data from the products take with the product_id = 1234567. The issue is that i am recieving an {"status": {"code":"ER_BAD_FIELD_ERROR", "errno":1054, "sqlState":"42S22", "index":0}} error every time i run this. However when I run http://localhost:8080/api/products it returns the 3 columns of data that I have in the products table.

How e this error is happening? I dont understand why /products works and /products/1234567 does not work

here is my code:

app.js

var express = require('express');
var bodyParser = require('body-parser');
var dbProducts = require('./dbProducts.js');
var app = express();
app.use(bodyParser.urlencoded({extended: true}));
app.use(bodyParser.json());

var port = process.env.PORT || 8080; // set our port
var router = express.Router();
router.use(function (req, res, next) {
console.log('Ining request..');
next();
});

// test route to make sure everything is working (accessed at GET http://localhost:8080/api)
router.get('/', function (req, res) {
    res.json({message: 'Wele to the store api!'});
});
router.route('/products')

// get all the products (accessed at GET http://localhost:8080/api/products)
.get(function (req, res) {
    dbProducts.getProducts(function (err, data) {
        if (data) {
            res.json({
                status: '200',
                items: data
            });
        } else {
            res.json(404, {status: err});
        }
    });
})

db.js

var mysql = require('mysql');
var pool = mysql.createPool({
    host: 'localhost',
    user: 'root',
    port: 3306,
    password: 'password',
    database: 'test'
});
module.exports.pool = pool;

dbProducts.js

var db = require('./db.js');
var getProduct = function getProduct(product_id, callback) {

var get = {id: product_id};
db.pool.getConnection(function (err, connection) {
    // Use the connection
    connection.query('SELECT * FROM PRODUCTS WHERE ? ', get, function (err, results) {
        if (!err) {
            if (results[0] != null) {
                callback(null, results);
            } else {
                callback("Product not found.", null);
            }
        } else {
            callback(err, null);
        }
        //release
        connection.release();
    });

});
}
 var getProducts = function getProducts(callback) {

db.pool.getConnection(function (err, connection) {
    // Use the connection
    connection.query('SELECT * FROM PRODUCTS', function(err, results){
        if (!err) {
            if (results != null) {
                callback(null, results);
            } else {
                callback(err, null);
            }
        } else {
            callback(err, null);
        }
        //release
        connection.release();
    });

});
}

 module.exports.getProduct = getProduct;
 module.exports.getProducts = getProducts;

inside products table "items": [ { "product_id": 1234567, "product": "Product 1", "price": 99.99 }, { "product_id": 5555555, "product": "Product 2", "price": 4.99 }, { "product_id": 8888888, "product": "Product 3", "price": 19.99

• javascript
• mysql
• sql
• node.js

1 Answer 1

Try

var get = {"product_id": product_id};

Your table doesnt have an 'id' column, it has a 'product_id' column.

The exception 1054 refers to unknown column exception.