CODEWITHSUNDEEP
mysqlangularjsnode.js
XtrimePT
XtrimePT

Reputation: 11

Retrieving data from MySQL database using NodeJS and passing it to AngularJS page

I've searched and searched for a clear answer but can't seem to find it. I'm "new" to programming, at least when it's about AngularJS and NodeJS (base languages like HTML, CSS and plain JS I'm familiar with because of school).

I want to be able to get data from a MySQL database using NodeJS and then send that data to an HTML page that has AngularJS in it.

Before I wanted to create this connection, I first used AngularJS to retrieve data directly from $scope and was able to bind it with a dropdown on html. Nice

Then, in NodeJS I made a connection to a MySQL database (here running on Workbench) and was able to retrieve data from a table and display it on console. Very nice.

But how about AngularJS request NodeJS to get data from MySQL and send it back to AngularJS? This I can't do :(

AngularJS code:

<!DOCTYPE html>
<html>
<head>
    <title>HumbleMoney</title>

    <!-- AngularJS -->
    <script src="https://ajax.googleapis.com/ajax/libs/angularjs/1.6.9/angular.min.js"></script> // AngularJS file

</head>
<body ng-app="clientesApp" ng-controller="clientesCtrl"> // Start App and Controller

    <p>Selecionar um registo:</p>
    <select ng-model="clienteSelecionado" ng-options="x.nome for x in clientes"></select>  // dropdown that should get the "nome" value of each record in $scope.clientes

        <table> // table with 2 columns that get the "nome" and "morada" values from the selected item on the above dropdown
            <tr>
                <td>{{clienteSelecionado.nome}}</td>
                <td>{{clienteSelecionado.morada}}</td>
            </tr>
        </table>

    <script>

        var app = angular.module('clientesApp', []);
        app.controller('clientesCtrl', function($scope, $http) {
            $http.get('NodeJS/clientes.js') //make a GET request on the NodeJS file
            .then(function(data) {
                $scope.clientes = data; //make the $scope.clientes variable have the data retrieved, right?
            })
        });

    </script>


</script> 
</body>
</html>

NodeJS code:

var express = require('express');
var app = express();

app.get('/', function (req, res){ //is this how you handle GET requests to this file?

    var mysql = require('mysql'); //MySQL connection info
    var conexao = mysql.createConnection({
        host:"localhost",
        user:"root",
        password:"",
        database:"mydb"
    });

    conexao.connect(function(erro) {  //MySQL connection
        if (erro) {
            res.send({erro})

        } else { 

        var sql = "SELECT nome, morada FROM clientes";
        conexao.query(sql,  function(erro, data) {
                if (erro) {
                    res.send({erro})

                } else {
                    res.send({data}); //this should bind the result from the MySQL query into "res" and send it back to the requester, right?
                }
            });
        }
    });
    conexao.end();
});

Here you have it. I hope someone could point me in the right direction. Thank's a lot and happy coding! :D

Upvotes: 1

Views: 4455

Answers (1)

BM3
BM3

Reputation: 117

So you want to learn how to use AngularJS, NodeJS and MySQL. Very nice. AngularJS and NodeJS both use JavaScript. AngularJS is 100% JavaScript. There are just few nuts and bolts that must fit together. AngularJS focuses on the frontend side, while NodeJS focuses on the backend side. MySQL is used for database management. There are just few tricks that you must use like MVC to make you code work and be robust. There are many ways you can implement your project. One of them is the following:

  1. Start Node.js command prompt.
  2. Create the new project directory. For example “myjspro”
  3. cd pathto/myjspro or cd pathto\myjspro or cd pathto\myjspro
  4. npm init
  5. npm install mysql --save
  6. npm install express --save
  7. npm install body-parser --save
  8. etc.

Once you have done the above steps, we can start coding. In your backend code you need to set up the listening port. You also need to configure the default directory. In your frontend you can use data binding to wire up your data model to your views. e.g. Scope is the glue between app controller and the view. Structure your app in a modular way. There are many building blocks we can use for our apps. The list is endless, so let's start... Double curly expressions {{ }} are used for observing, registers listeners methods and update views.

Front End:

  • app/view/index.html
  • app/js/app.js
  • app/js/test.js
  • app/lib/angular/angular.js
  • app/lib/jquery/jquery.js

Back End:

  • db/mydb2.sql

  • node_modules

  • index.js

  • package.json

Database:

  • Create the database e.g. “mydb2”
  • Create database user e.g. “jspro2”
  • Create the database user's password.
  • Create database tables for the project e.g. “clientes2”

To start your project you can use: node index.js or npm start with the command prompt. The app will be running at localhost. Use the browser to view the project. i.e. http://localhost:8000/

Happy coding...

index.js

//////////////////////
//
// index.js
//
///////////////////////

console.log("Start...");
var express = require('express');
var app = express();
var bodyParser = require('body-parser');
var mysql = require('mysql');
var now = new Date();

app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: true }));
app.use(express.static(__dirname+'/app'));

var conexao = mysql.createConnection({
    host: "localhost",
    user: "jspro2",
    password: "jspro32",
    database: "mydb2"
});

conexao.connect(function(err){
    if(err){
        console.log(info()+" "+err);
    }
    else
    {
        console.log(info()+" connected...");
    }
});

function info()
{
    now = new Date();
    return now.getTime();
}

app.set('port', (process.env.PORT || 8000));
app.get('/', function(req, res){
    console.log(info()+" page request.... ");
    res.sendFile(__dirname +'/'+'app/view/index.html');
});

app.get('/clientes', function(req, res){
    console.log(info()+" clientes request.... ");
    var sql = "SELECT * FROM CLIENTES2";
    conexao.query(sql, function(err, result, fields){
        if(err){
            console.log(info()+" "+err);
            res.send(info()+": dbErr...");
        }
        else
        {
            console.log(info()+" "+result);
            res.send(result);
        }
    });
});

app.post('/clientPost', function(req, res){
    var data = req.body;
    var dnome = data.nome;
    var dmorada = data.morada;
    var sql = "INSERT INTO CLIENTES2 (nome,morada) VALUES(?, ?)";
    conexao.query(sql, [dnome, dmorada], function(err, result){
        if(err){
            console.log(info()+":02 "+err);
            res.send(info()+": dbErr02...");
        }
        else
        {
            console.log(info()+" "+ result);
            res.send(result);
        }
    });
});

var dport = app.get('port');
app.listen(dport, function(){
    console.log(info()+" "+" app is running at http://localhost:"+dport+"/");
    console.log("   Hit CRTL-C to stop the node server.  ");
});
//   
//

app.js

/////////////////////////
//
// app.js
//
/////////////////////////////////

//alert("start...");
var now = new Date();

//Define the clientesApp module.
var clientesApp = angular.module('clientesApp', []);

//Define the clientesCtrl controller.
clientesApp.controller('clientesCtrl', function clientsList($scope, $http){
    $scope.clientes = [
        {
            nome: 'Marc',
            morada: '123 West Parade, Nome'
        },
        {
            nome: 'Jean',
            morada: '432 East Cresent, Lisboa'
        }
    ];

    $scope.feedback = now;

    $scope.listView = function(){
        //alert("View");
        $http.get('/clientes').then(function(data){
            $scope.clientes = data.data;
            $scope.feedback = data;
        }); 
    };

    $scope.listSubmit = function(){
        //alert("Submit..");
        var listData = {
            nome: $scope.nome,
            morada: $scope.morada
        };
        //alert(listData.nome);
        $http.post('/clientPost', listData).then(function(data){
            $scope.feedback = data;
        }); 
    };

});

//alert(now);
//
//

index.html

<!DOCTYPE html>
<!--
index.html
-->
<html lang="en">
    <head>
        <title>DemoJSPro</title>
        <meta charset="UTF-8">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
    </head>
    <body ng-app="clientesApp">
        <h1>AngularJS Demo using NodeJS and MySQL.</h1>

        <div ng-controller="clientesCtrl">
            <hr>
            <div>{{ feedback }}</div>
            <hr>
            <div>
                <br>
                Nome:
                <input type="text" ng-model="nome">
                <br>
                Morada:
                <input type="text" ng-model="morada">
                <br>
                <input type="button" value="OK" ng-click="listSubmit()">
                <br>
            </div>
            <div>
                <p>Selecionar um registo:</p>
                <select ng-model="clienteSelecionado" ng-options="x.nome for x in clientes"> 
                </select>
                <table>
                    <tr>
                        <td>{{ clienteSelecionado.nome }}</td>
                        <td>{{ clienteSelecionado.morada }}</td>
                    </tr>
                </table>
            </div>
            <hr>
            <div>
                <input type="button" value="View" ng-click="listView()">
                <hr>
                <table>
                    <tr>
                        <th>###</th>
                        <th>Nome</th>
                        <th>Morada</th>
                    </tr>
                    <tr ng-repeat=" y in clientes">
                        <td>{{ $index + 1 }}</td>
                        <td>{{ y.nome }}</td>
                        <td>{{ y.morada }}</td>
                    </tr>
                </table>
            </div>
            <br>
            <hr>
            <br><br>
        </div>

        <script src="../lib/angular/angular.js"></script>
        <script src="../lib/jquery/jquery.js"></script>
        <script src="../js/app.js"></script>
        <script src="../js/test.js"></script>
    </body>
</html>

mydb2.sql

#//////////////////////////
#//
#// mydb2.sql
#//
#///////////////////////////

CREATE DATABASE MYDB2;

USE MYDB2;

CREATE TABLE CLIENTES2 (
id int NOT NULL auto_increment,
nome varchar (30) NOT NULL,
morada varchar (99) NOT NULL,
PRIMARY KEY (id)
);

GRANT ALL ON MYDB2.* to jspro2@localhost identified by 'jspro32';

Enjoy.

Upvotes: 2

Related Questions