Undefined push to array - Basic Application using ExpressJs and MySQL

Question

First of all, I have to tell you I'm pretty noob in this "universe". I'm using: ExpressJs, MySql, Body-Parser, Express-session, Ejs template for creating an Basic Contacts Application in Node.

My database is composed from 3 tables:

user (user_id, first, second name, username, password)
contacts (ct_id, first, second name, phone numb.)
user_contacts (user_id, ct_id) --> foreign keys for user and contacts

I want to listing on /myProfile page all details about user and his contacts. I don't know how to handle the select queries.

So, after some documentation I did this:

conn.query('SELECT * FROM user_contacts WHERE user_id= ?', req.session.user_id, function (err, result) { 
        if(err) throw err;
        console.log(result);
        var queryArray = "";
        for(var i = 0; i < result.length; i++){
            queryArray += `SELECT * FROM contacts WHERE ct_id= ${result[i].ct_id}; `;
        }
        console.log(queryArray);
         conn.query(queryArray, function (err, result) { 
             if(err) throw err;
             console.log(result);
             res.render('myProfile/contacts', {
                title: `${req.session.user_nickname}'s Contacts`,
                data:   result
            });
          }); 
     });

But I have an error

ER_PARSE_ERROR: You have an error in your SQL syntax;

..when queryArray.length > 1

I searched and it's something about Multiple statement queries but I dont know how to solve it.

Edit 2:

I modify my code..

conn.query('SELECT * FROM user_contacts WHERE user_id= ?', req.session.user_id, function (err, result) { 
        if(err) throw err;
        var datas = [];
        for(var i = 0; i < result.length; i++){
            getContacts    =   function(query){
                conn.query(query, function (err, result) { 
                    console.log('Creating data');
                    data = {
                        user:       req.session.user_nickname,
                        contact:{
                            ct_firstName:   result[0].ct_firstName,
                            ct_SecondName:  result[0].ct_SecondName,
                            ct_PhoneNumber: result[0].ct_PhoneNumber
                        }   
                    }
                    return data;
                 });
            }
            console.log('Send data to array');
            datas.push(getContacts(`SELECT * FROM contacts WHERE ct_id = ${result[i].ct_id}`));
        }
        console.log(datas); // [ undefined, undefined ] 
        res.render('myProfile/contacts',{
            title:  `${req.session.user_nickname}'s profile`,
            data:   datas
        })
     });

But now my array contain undefined objects?? Any solution? Maybe is something about scope?

My result:

Send data to array
Send data to array
[ undefined, undefined ]
Creating data
Creating data

I push the object to array before creating it. How is it possible?

So if you can identify a use case when it works and not (as you've done), then try and refactor the code to better fit that use case. If it works when the query array is only one element, then perhaps instead of adding multiple statement queries and querying them all at once, run your query in the for-loop instead and remove the queryArray variable altogether. — Chris Forrence, Oct 23 '18 at 14:20

score 0 · Accepted Answer · answered Oct 24 '18 at 21:19

1797,

I noticed you have several small queries grabbing the contact info for a given user. You could simplify your code by combining your queries into a single one. Often times 1 big query is more efficient (plus it's easier to maintain). I'm using a join. More info here.

const contacts = [];

const query = "
SELECT c.*
FROM user_contact uc
JOIN contact c ON uc.contact_id = c.contact_id
WHERE uc.user_id = ?
GROUP BY c.contact_id
";

conn.query(query, req.session.user_id, (err, results) => {
    if (err) throw new Error(err);

    // it seems that this could just be 'contacts = results' since they
    // have the same structure
    contacts = results.map(result => {
        return {
            ct_firstName:   result[0].ct_firstName,
            ct_SecondName:  result[0].ct_SecondName,
            ct_PhoneNumber: result[0].ct_PhoneNumber
        };
    });

    res.render('myProfile/contacts',{
        title: `${req.session.user_nickname}'s profile`,
        data: contacts
    });
});

Undefined push to array - Basic Application using ExpressJs and MySQL

Edit 2:

1 Answers1