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?

MySQL DB

NodeJS

Client's Computer

Iphone/Android

Virtual Reality Headset?

Ruby

PHP

Java

OUR FIRST PROJECT

JOIN US

An Evil Cult Mailing List Application

A Startup Mailing List Application

Count All Users

Insert A New User

MySQL DB

NodeJS Server

Client's Computer

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

Hello there MySQL, How Many Users Are In The Database?

Well...it looks like there are currently 518 users in here!

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()

Simone.Walsh@gmail.com

Orie23@hotmail.com

Aubree_Daugherty97@yahoo.com

faker.date.past()

Fri Dec 16 2016 11:13:08 GMT+0000 (UTC)

Tue Dec 27 2016 11:48:00 GMT+0000 (UTC)

Fri Jun 24 2016 06:33:49 GMT+0000 (UTC)

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

WARNING THIS IS NOT REAL CODE

Do That 500 Times

Introducing MySQL

The Node Package, That Is

NodeJS

MySQL

Hello there MySQL, How Many Users Are In The Database?

Well...it looks like there are currently 518 users in here!

NodeJS

MySQL

Hello there MySQL, How Many Users Are In The Database?

Users...I have no idea what you are talking about! I don't see a users table in here :(

NodeJS

Hello there MySQL, How Many Users Are In The Database?

MySQL?? Are you there???

 😭😭😭😭😭😭😭😭

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

Express App

(Node)

Client's Computer

Hi there, I would like the "/" page please!

Sure thing, here you go!

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

Express App

(Node)

Client's Computer

Hi there, I would like the "/joke" page please!

Sure thing, here you go!

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

Express App

(Node)

Client's Computer

Hi there, I would like the "/" page please!

One sec, let me ask MySQL for a user count

MySQL

519!

Ok all set now, here you go!

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

<h1>JOIN US</h1>
<p class="lead">Enter your email to join <strong>518</strong> 
others on our waitlist. We are 100% not a cult. </p>
<form method="POST" action='/register'>
    <input type="text" class="form" placeholder="Enter Your Email">
    <button>Join Now</button>
</form>

All The Markup We Need

EJS

npm install --save ejs
app.set("view engine", "ejs");
res.render('home');

Look For A Views Directory

Look For home.ejs in that directory

SOOOOOOOOOO

What about that form...

Express App

(Node)

Client's Computer

Hi server, I would like to sign up, here's my email

sophie97@yahoo.com

One sec, let me ask MySQL to INSERT that for you

MySQL

Ok all set now!

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"));

MySQL DB

NodeJS Server

Client's Computer