Querying MySQL with Node.JS and display results in webpage

Question

My goal is to query MySQL with Node.JS for better interactivity. My query is a simple SELECT with JOINs. I managed to construct a script that displays results in the console but I'm kind of stuck when it comes to display it in the webpage. Here is my code:

var mysql = require('mysql');

var mySqlClient = mysql.createConnection({
    host    : 'server',
    user    : 'login',
    password: 'pwd',
    database: 'db'
});

var selectQuery = 'SELECT fields FROM table t1\
                   INNER JOIN table t2\
                   ON t1.field = t2.field';
mySqlClient.query(
    selectQuery,
    function select(error, results, fields) {
        if(error) {
            console.log(error);
            mySqlClient.end();
            return;
        }

        if(results.length > 0) {
            console.log(results);
        } else {
            console.log('No data');
        }
        mySqlClient.end();
    });

What is the best approach to include this into the webpage? Do I need to create a httpServer with Node.JS?

Many thanks for any help!

EDIT I would like to use Node.JS inside a PHP application designed with Zend Framework. The Node.JS app wouldn't be the sole app of my project.

Have a look at express.js as a webserver frontend. Should be easy enough to set up. — Sirko
– Sirko, Commented Mar 26, 2014 at 9:53
Hi! Thanks for your comment. I already have a frontend webserver. This is only part of a Zend Framework app. My goal is to include results in an existing webpage. — D4V1D
– D4V1D, Commented Mar 26, 2014 at 9:57
You have to have some kind of data transmission from nodejs to your current frontend server as both are based on different technologies and thus can't be included directly. The "easiest" way is to let node serve its data via an own server. For that you can either create your own code or use something like express.js. — Sirko
– Sirko, Commented Mar 26, 2014 at 10:09
Should I use express.js to serve data in JSON format so I can include the results in the webpage? Excuse my lack of knowledge, it's only been a week I've been introduced to Node.JS. My Google research have led me to this tutorial (pixelhandler.com/posts/…), is it what you meant? — D4V1D
– D4V1D, Commented Mar 26, 2014 at 10:31
Looks about right. However, they seem to serve some HTML from nodejs, which you don't need. Your nodejs server will just be a backend server for you PHP stuff and have no direct connection to the internet / user . — Sirko
– Sirko, Commented Mar 26, 2014 at 10:37

Mithun Satheesh · Accepted Answer · 2014-03-26 10:02:57Z

2

Easiest way would be to use a node framework. Like express.

You will be doing something like

/*** omitting generic code for clarity ***/
app.get('/yourpage',function(req,res){

    //On request of this page initiating sql query. Assumes that the object initialization is done above.
    mySqlClient.query(
    selectQuery,
    function select(error, results, fields) {
        if(error) {
            console.log(error);
            mySqlClient.end();
            //render the template with error to be alerted
            res.render('tempaltefile',{data:null,error:error});                
        }

        if(results.length > 0) {
            //console.log(results);
            //render the template with fetched data
            res.render('tempaltefile',{data:results,error:null});
        } else {
            //console.log('No data');
            //render the template with empty data alert
            res.render('tempaltefile',{data:null,error:"no data"});
        }
        mySqlClient.end();
    });

});

reply to comments

please mention you are using this along side ph code. I think you have to biuld the node code an api and consume it in the php end.

edited Mar 26, 2014 at 10:02

answered Mar 26, 2014 at 9:57

Mithun Satheesh

27.9k14 gold badges80 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

D4V1D Over a year ago

Thanks for reply, is this compatible with an already existing front end app such as a Zend Framework app? How can I include express.js in ZF?

signo · Accepted Answer · 2014-04-24 00:42:06Z

0

I was in the same situation as you, I could not connect to MySQL Zend since NodeJS. My solution was to use:

Node-mysql plugin (command to install into nodejs consol: npm install mysql)
Install the MySQL ODBC connector (http://dev.mysql.com/downloads/connector/) plugin.

Finally, restart the computer to apply the new install.

In your server.js file, you can use this code to connect to your HostDB:

var app = require('http').createServer(handler), mysql = require('mysql'), connectionsArray = [], connection = mysql.createConnection({ host: 'localhost', user: 'USERNAME MYSQL', password: 'PASSWORD MYSQL', database: 'NAME MYSQLDATABASE', port: 3306 });

edited Apr 24, 2014 at 0:42

answered Apr 24, 2014 at 0:30

signo

669 bronze badges

Comments

imatwork · Accepted Answer · 2020-04-20 18:20:34Z

app.post('/qr', function(req, res) {
  console.log("Check QR code.");
  let now = moment().format('MMMM Do YYYY, h:mm:ss a');
  let subnow = now.substr(0, 8);
  let subnowwild = subnow + "%";
  connection.query("select exists (select * from visit where timeIn like ?)", subnowwild, function(err, result) {
  if (err) throw err;
  console.log(result);
    if(result = 0) {
     res.redirect(307, '/savedata');
    }
    else {
     res.redirect(307, '/updatedata');
    }
  });
});

So I wanted to see what a response was like when getting data with mysql and this webpage showed up but did not show the answer I was looking for.

You can also use res.send(result); if you want the query to return the data as a json response.

Collectives™ on Stack Overflow

Querying MySQL with Node.JS and display results in webpage

3 Answers 3

reply to comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

reply to comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related