Get MySQL data in node.js (express) and print using EJS

mysql node.js express

16,106

In the render, you can return a JSON variable with the consulted data and show in the view. res.render can be the last instruction in this way. Some like this:

var obj = {};
router.get('/data', function(req, res){

    connection.query('SELECT * FROM users', function(err, result) {

        if(err){
            throw err;
        } else {
            obj = {print: result};
            res.render('print', obj);                
        }
    });

});

<body>
    <table id="table" >  
        <thead>  
            <tr>  
                <th>Username</th>  
                <th>Password</th>  
            </tr>  
        </thead>  
         <tbody>  
         <% print.forEach(function (user) { %>
            <tr>  
                <td><%= user.username %></td>  
                <td><%= user.password %></td>
            </tr>                                   
         <% }) %>
         </tbody>
    </table>   
</body>

</html>

16,106

Murad Elboudy

Updated on June 04, 2022

Comments

Murad Elboudy almost 2 years

I'm trying to get the data of my MySQL table and print them in HTML using EJS, but this doesn't work. It tells me print not defined. What should I do?

router.get('/data', function(req, res){
    res.render('print', {req : req, res : res});
    connection.query('SELECT * FROM users', function(err, result) {

        if(err){
            throw err;
        } else {
            for(x in result){
                res.locals.print =  result[x];
                console.log(result[x]);
            }
        }
    });
});

<!doctype html>
<html>
    <body>
        <div>
            <%= print %>
        </div>
    </body>
</html>

Murad Elboudy over 8 years

I can't name a variable return, so I just named it obj and changed the other "return" 's necessary but left most of the code as is, but it still tells me "print is not defined". Do I maybe need to loop the result argument in the connection query? Weird thing I already did but maybe I did it in the wrong way or something.
BrTkCa over 8 years

I updated the answer. After the res.render('print', obj); add console.log(obj); for show the value of the consulting. After post here the result...
BrTkCa over 8 years

Ahh, you don't need the loop the result, this way works.
Murad Elboudy over 8 years

Ok, so I think we're getting somewhere. I added the console.log() and it prints out: { print: [ {username: 'Tony', password: 'Lee'}, {username: 'Mark', password: 'Douglas'}, {username: 'Marshall', password: 'Hamilton'} ], _locals() }
BrTkCa over 8 years

I updated the answer, see <%= print[0].username %>.
Murad Elboudy over 8 years

Yes, perfect it works. Thanks so much man. But how can I now print everything that's in my table? I figured this out but it doesn't work as I want it to because it prints the username and password of every object alone in a new line what I want is to have ( Username <space> Password): <% for(var i=0; i<print.length; i++) { %> <% for(x in print[i]) { %> <p><%= print[i][x] %></p> <% } %> <% } %> So am I on the right track or is their an easier way or something?
Murad Elboudy over 8 years

Bravo man, seriously. Thank you very very much. Works exactly as I want it to.
Swapnil B. over 5 years

@LucasCosta where did you mention the name of the html page where the data is to be shown?
BrTkCa over 5 years

@SwapnilB. when we're using Express, the render will look for into the views directory, so if it has an HTML page called print into views the data will be rendered.
Swapnil B. over 5 years

@LucasCosta oh, that makes complete sense. Thank you :)