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 es 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.

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 es 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.

• javascript
• php
• jquery
• mysql
• node.js

• Have a look at express.js as a webserver frontend. Should be easy enough to set up. – Sirko Commented Mar 26, 2014 at 9:53
• Hi! Thanks for your ment. 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 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 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./posts/…), is it what you meant? – 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 Commented Mar 26, 2014 at 10:37

3 Answers 3

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 ments

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.

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 (mand to install into nodejs consol: npm install mysql)

• Install the MySQL ODBC connector (http://dev.mysql./downloads/connector/) plugin.

Finally, restart the puter 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 });

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.