How can I retrieve rows from mysql database and send them as JSON via Express.js?

user_13362185 0
Viewed 86

I'm a complete beginner in Node.js and I want to send registered variable and claimed variable at the same time in the form of JSON via Express.js

This is the only thing I came up with right now and it does not even work.

app.get('/item_reg', (req, res) => {
    var registered = connection.query("SELECT JSON_OBJECT('name', item_name, 'item_id', item_id, 'location', location_desc, 'color', color, 'description', description, 'image', image_url) AS 'Registered' FROM Items_found WHERE type = 0");
    var claimed = connection.query("SELECT JSON_OBJECT('name', item_name, 'item_id', item_id, 'location', location_desc, 'color', color, 'description', description, 'image', image_url)  AS 'Claimed' FROM Items_found WHERE type = 1");
    //sending a response
    res.json([[registered], [claimed]]);
});

This is my DataBase but I only want some of the attributes as in the queries above not all of them.

enter image description here

Thank you in advance.

express node.js mysql json database
2 Answers

Are you using MySQL? I assume mysql module has been installed in your project. Mysql.query function take function as argument (callback), so get async/await or promise and you can try this one :

// mysql module
const mysql = require('mysql');
const connection = mysql.createConnection({
  // environment mysql here
});

// util module for handle callback in mysql query
const util = require('util');

// create variable to get result from querying
let resultQuery = util.promisify(connection.query).bind(connection);

// so in your route use async/await and try catch 
app.get('/item_reg', async (req, res) => {
    try {
    // use resultQuery instead as await
    var registered = await resultQuery("SELECT JSON_OBJECT('name', item_name, 'item_id', item_id, 'location', location_desc, 'color', color, 'description', description, 'image', image_url) AS 'Registered' FROM Items_found WHERE type = 0");
    var claimed = await resultQuery("SELECT JSON_OBJECT('name', item_name, 'item_id', item_id, 'location', location_desc, 'color', color, 'description', description, 'image', image_url)  AS 'Claimed' FROM Items_found WHERE type = 1");
    //sending a response
    // the query string is select JSON_OBJECT, is that return json string as result? if yes, so :
    registered = JSON.parse(registered);
    claimed = JSON.parse(claimed);
    res.json({ registered, claimed });
    } catch (err) {
        res.json({ message: 'error message'});
    }
});
user_109269410

Async flow would be the culprit. You can try

connection.query("SELECT JSON_OBJECT('name'........", function(err,results,fields){
    registered = results;
});
connection.query("SELECT JSON_OBJECT('name'........", function(err,results,fields){
    claimed = results;
});
user_130870860
Related