We've Seen MySQL

On Its Own

Now it's time for...

MySQL &

Node

PHP

Ruby

C#

C++

Java

Python

...

MySQL &

Node

PHP

Ruby

C#

C++

Java

Python

...

How do we interact with MySQL through external code?

OUR FIRST PROJECT

JOIN US

WHERE DO WE START?

MySQL DB

NodeJS Server

Client's Computer

Our Users Table Is Super Simple

email

created_at

&

Our First Goal...

Use NodeJS to randomly generate and INSERT  500+ users into a database

SETTING UP CLOUD9

FOR NODE

A QUICK NOTE

On What This Course Is And Isn't

Seems a bit late for that...

NodeJS

MySQL

5 Minutes of Node

Running Files and using NPM

console.log("THIS IS SOME CODE!");

Write Some Code

Execute The File

node filename.js

Introducing FAKER

npm install faker

Our Gameplan...

Use Faker To Generate User Data For Us

faker.internet.email()

faker.date.past()

INSERT INTO users (email. created_at) VALUES
    (
        faker.internet.email(),
        faker.date.past()
    );
        

WARNING THIS IS NOT REAL CODE

Introducing MySQL

The Node Package, That Is

NodeJS

MySQL

NodeJS

MySQL

NodeJS

var mysql   = require('mysql')

var connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'some_username',
  database : 'some_database'
});

Step 1: Connect To MySQL

var q = 'SELECT 1 + 1 AS solution';
connection.query(q, function (error, results, fields) {
  if (error) throw error;
  console.log('The solution is: ', results[0].solution);
});

Step 2: Run Queries

Don't worry, we'll go over this in detail!

WAIT I HAVE A QUESTION

How do you know what code to type?

Creating Our Schema

CREATE TABLE users (
    email VARCHAR(255) PRIMARY KEY,
    created_at TIMESTAMP DEFAULT NOW()
);

Nice And Simple

var q = 'SELECT * FROM users';
connection.query(q, function (error, results, fields) {
      console.log(results);
});

To Select All Users

  var person = {email: 'Jenny467@gmail.com'};
  connection.query('INSERT INTO users SET ?', person, function(err, result) {
   if (error) throw error;
   console.log(result);
 });

To INSERT a user

var data = [
    ['blah@gmail.com', '2017-05-01 03:51:37'],
    ['ugh@gmail.com', '2017-05-01 03:51:37'],
    ['meh@gmail.com', '2017-05-01 03:51:37']
];

var q = 'INSERT INTO users (email, created_at) VALUES ?';

connection.query(q, [data], function(err, result) {
  console.log(err);
  console.log(result);
});

INSERTING multiple users

Quick Exercises

+---------------+
| earliest_date |
+---------------+
| May 2nd 2016  |
+---------------+

Find Earliest Date A User Joined

+-----------------------------+---------------------+
| email                       | created_at          |
+-----------------------------+---------------------+
| Friedrich_Kulas@hotmail.com | 2016-05-02 20:28:18 |
+-----------------------------+---------------------+

Find Email Of The First (Earliest)User

HINT: SUBQUERY

+-----------+-------+
| month     | count |
+-----------+-------+
| November  |    51 |
| January   |    49 |
| May       |    48 |
| December  |    47 |
| July      |    46 |
| June      |    43 |
| April     |    41 |
| October   |    41 |
| September |    40 |
| March     |    40 |
| August    |    40 |
| February  |    32 |
+-----------+-------+

Users According To The Month They Joined

+-------------+
| yahoo_users |
+-------------+
|         166 |
+-------------+

Count Number of Users With Yahoo Emails

+----------+-------------+
| provider | total_users |
+----------+-------------+
| gmail    |         190 |
| yahoo    |         166 |
| hotmail  |         159 |
| other    |           3 |
+----------+-------------+

Calculate Total Number of Users for Each Email Host

MOVING ON TO

THE WEB APP

IT'S TIME TO MEET

EXPRESS

The Framework We'll Be Using To Make JOIN US

LOOK AT THE DOCS!

Starting Nice And Simple

npm install express

DON'T RUN THIS YET!

npm init

WHAT THE HECK DOES THIS DO

(it's a fast way of starting a new app)

npm install express --save

THIS VERSION IS BETTER

It will save a record of the install

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

app.get("/", function(req, res){
  res.send("HELLO FROM OUR WEB APP!");
});

app.listen(8080, function () {
  console.log('App listening on port 8080!');
});

Our First SUPER SIMPLE Express App

Adding More Routes

Routing Pseudocode

1.Did The Client Request "/"?

Respond with "WELCOME TO THE HOME PAGE"

2. Did The Client Request "/joke"?

Respond with "Knock Knock..."

app.get("/joke", function(req, res){
    var joke = "What do you call a dog that does magic tricks? A labracadabrador.";
    res.send(joke);
});

Adding Another Route

app.get("/random_num", function(req, res){
    var num = Math.floor((Math.random() * 10) + 1);
    res.send("Your lucky number is " + num);
});

 One Last Route

The Next Logical Step

Integrating MYSQL

app.get("/", function(req, res){
  var q = 'SELECT COUNT(*) as count FROM users';
  connection.query(q, function (error, results) {
      if (error) throw error;
      var msg = "We have " + results[0].count + " users";
      res.send(msg);
    });
});

Retrieving Users Count From DB Inside a Route

(Not showing code to connect to the db)

WORKING WITH VIEWS

Adding HTML to our web app

We're Working Towards This...

Where We'll Start...The Unstyled Version...

All The Markup We Need

EJS

npm install --save ejs

app.set("view engine", "ejs");

res.render('home');

SOOOOOOOOOO

What about that form...

How Does The Form Data Make It To Our Server?

app.post('/register', function(req,res){
    //a POST route!
});

Defining a POST route

var bodyParser  = require("body-parser");

npm install --save body-parser

app.use(bodyParser.urlencoded({extended: true}));

Some Annoying Set Up

 var person = {email: req.body.email};

Extracting Form Data From Request Body

app.post('/register', function(req,res){
  var person = {email: req.body.email};
  connection.query('INSERT INTO users SET ?', person, function(err, result) {
   console.log(err);
   console.log(result);
   res.redirect("/");
 });
});

Our Finished /register POST route

app.use(express.static(__dirname + "/public"));

Client's Computer