Node.js - mySQL - PHP - Create 2 tables - Save records. **SIMPLIFIED** Part 2 of 9
New here? Learn about Bountify and follow @bountify to get notified of new bounties! x

Introduction

This app ( view source code here ) counts the amount of tweets that contain a specific CashTag (like AAPL) during time.
Every 10 seconds the data is updated in a graphic heatmap and in an array below in the screen. You can see the app running at here

This is stage 2 of a 9-stages project. If the selected developer posts a good solution within the timeframe, he will automatically be selected for a project of same dimensions and payment for the next stage.

Requirements

a. Create a table :
CREATE TABLE watchList
('id' int, 'symbol' varchar(7), 'active' boolean)
;

id --> Autonumber
symbol --> the name of the casTag like AAPL
active --> flag for showing adding the record in the watchSymbols array.

All the records that are active== true , should be included in the array watchSymbols. (add a button for refreshing the content of watchSymbols)

b. Create a table:

CREATE TABLE history
('timestamp' timestamp, 'symbolId' int, 'newTwits' int)
;

Name: 'History'
Fields
- TimeStamp (records date and time)
- SymbolId (makes reference to the Symbols of the watchlist table
- newTwits (records the difference of the 'data' colleceted from twitter between the current record and the previous)

Every time there is a new line in the vector "data", create a record on this table.

EXAMPLE (see picture )

At 04-03-2017 9:30:00 : msft was 2 (msft has symbolId = 1 on the watchList table)
at 04-03-2017 9:30:10: msft was 2

Difference : 2 - 2 = 0

In consequence we insert the record:

INSERT INTO history (timestamp, SymbolId, newTwits) VALUES (2017-04-03 09:30:10, 1, 0);

At 04-03-2017 9:30:00 : aapl was 16 ($aapl has symbolId = 10 on the watchList table)
at 04-03-2017 9:30:10: aapl was 17

Difference : 17 - 16 = 1 --> on history we will record

In consequence we insert the record:
INSERT INTO history ('timestamp', 'symbolId', 'newTwits') VALUES (2017-04-03 09:30:10, 10, 1);

Show app running on server.

Crowdsource coding tasks.

0 Solutions