Syntax for successful transactions using jquery, and node.js
I have the following code to insert, edit and delete records from a database using node.js. I would like to have alert messages informing the user of the successful transaction for each from the frontend if successful. I am not certain regarding the syntax for this.
Here is the code to insert into the database:
Here is the code to delete:
Here is the code to insert into the database:
$(document).ready(() => {
$(document).on('click', '.insert', function (e) {
// $('#insertD').click(() => {
e.preventDefault();
$("#insertE").remove();
$("#insertD").show();
$("#stationForm")[0].reset();
$("#stationForm").attr({
action: $(e.target).attr("href"),
//action: "/station", // PATH TO POST NEW STATION DATA ?
method: "POST"
});
$("#modalStationForm")
.dialog("option", "title", "Inserting New Station")
.dialog("open");
});
[/code}
Here is the code edit code:
[code]
$(document).ready(() => {
$(document).on('click', '.edit', function (e) {
e.preventDefault();
$("#insertD").hide();
$("#insertE").show();
$("#stationForm")[0].reset();
$("#stationForm").attr({
action: $(e.target).attr("href"),
//action: "/station", // PATH TO POST NEW STATION DATA ?
method: "PUT"
});
var value = $(event.target).closest("div").find("input:hidden").val();
value = JSON.parse(value);
$("#store", "#modalStationForm").val(value.Station);
$("#location", "#modalStationForm").val(value.Address);
$("#sales", "#modalStationForm").val(value['Monthly C-Store Sales']);
$("#dealer", "#modalStationForm").val(value.Operator);
$("#topitem", "#modalStationForm").val(value['Top SKU']);
$("#id", "#modalStationForm").val(value.ID);
$("#modalStationForm")
.dialog("option", "title", "Editing Station")
.dialog("open");
});
$('#insertE').click((event) => {
//$("#stationForm", "#modalStationForm").on("submit", function (event)
//$("#stationForm").on("submit", function (event) {
event.preventDefault();
//var methodToUse = $(this).attr("method"); // PUT
//var url = $(this).data("url");
/*
var data = {
station: $("#store", "#modalStationForm").val(),
address: $('#location', "#modalStationForm").val(),
monthlycstoresales: $('#sales', "#modalStationForm").val(),
operator: $('#dealer', "#modalStationForm").val(),
topsku: $('#topitem', "#modalStationForm").val(),
id: $('#id', "#modalStationForm").val()
};
*/
var data = JSON.stringify({
station: $("#store", "#modalStationForm").val(),
address: $('#location', "#modalStationForm").val(),
monthlycstoresales: $('#sales', "#modalStationForm").val(),
operator: $('#dealer', "#modalStationForm").val(),
topsku: $('#topitem', "#modalStationForm").val(),
id: $('#id', "#modalStationForm").val()
});
var you = $(location).attr('href');
var me = window.location.href;
//console.log(you);
$.ajax({
url:'/edit/' + $('#id', "#modalStationForm").val(),
//url: $(this).attr("action"),
//url:'http://localhost:5000/edit/3',
//url:'http://localhost:5000/station', wrong url 404 message
data: data,
dataType : "JSON",
contentType: "application/json",
method: "PUT",
success: (data) => {
console.log(data);
console.log('you and me');
console.log(me);
//console.log(url);
//alert(url);
$("#modalStationForm").dialog("close");
}
});
});
});Here is the code to delete:
$('body').on('click', '.delete', function (e) {
var link = $(e.target);
e.preventDefault(); // https://api.jquery.com/event.preventdefault/
if (confirm("Do you really want to delete this station?"));
$.ajax({
method: 'DELETE',
//url: $(this).ptop('href'),
url: $(this).attr('href'),
}).done(function (data) {
console.log('Deleted Successfully.');
link.closest(".store").remove(); //removing the most recent record live view.
});
});ASKER
I am aware of the alert and UI dialog, however, how is the syntax specifically written for the 3 options after a transaction is completed, in order words, for example, how to confirm that the record was installed correctly in order to write the prompt for a successful alert message. Which conditions need to be in place? Perhaps even an if statement and else statement.
Wouldn't the alert just be in the success/done callback of your ajax request?
ASKER
Thank you, both the delete and edit functions had stand alone ajax request which I was able to add the alert method to the success callback but the insert code does not have a direct ajax request. It uses a form attribute to send the data, therefore if I apply it within the attribute, the alerts calls when the form is loaded. If I place it after the attribute, nothing happens. Perhaps, you have a solution. Where should I put the success callback? See the code below:
$(document).ready(() => {
$(document).on('click', '.insert', function (e) {
// $('#insertD').click(() => {
e.preventDefault();
$("#insertE").remove();
$("#insertD").show();
$("#stationForm")[0].reset();
$("#stationForm").attr({
action: $(e.target).attr("href"),
//action: "/station", // PATH TO POST NEW STATION DATA ?
method: "POST",
});
success: (data) => {
alert("Record Successfully Submitted");
}
$("#modalStationForm")
.dialog("option", "title", "Inserting New Station")
.dialog("open");
});
});
in your success callback :
you can also use error callback, similar to success but thrown when a non predictive error happen on server side, that mean you may not have the hand to return a "successfull" bad message like "KO".
that mean your server side code send back a message in the status field :
success: (data) => {
var status = data.status;
if(status = "OK")
alert("Record Successfully Submitted");
else
alert("bad news !");you can also use error callback, similar to success but thrown when a non predictive error happen on server side, that mean you may not have the hand to return a "successfull" bad message like "KO".
that mean your server side code send back a message in the status field :
db.query(sql, [req.body.Station, req.body.Address, req.body.MonthlyCStoreSales, req.body.Operator, req.body.TopSKU, req.query.ID], (err, results, field) => {
let message = {};
if (results !== null) {
console.log('results is not null');
message.status = "OK";
}else{
console.log('results is null');
message.status = "KO"; // ?
};
if (sql !== null) {
console.log('sql is not null');
message.sql = sql; // for testing purpose, remove this horror in production
}else{
console.log('sql is null');
message.sql = 'sql is null'; // for testing purpose, remove this horror in production
};
if (req.body.Station !== null) {
console.log('req.body is not null');
message.body = "body ok"; // useless, remove it, just for testing purpose maybe
}else{
console.log('req.body is null');
message.body = "body not ok"; // useless, remove it, just for testing purpose maybe
};
if (err) {
console.log(err);
res.sendStatus(500); // THIS WILL THROW THE fail callback of your $.ajax call
return callback(rows);
} else {
res.send(message); // maybe you want to send back the data so use the following :
//message.results = results; // mean your success callback use << data.results >> instead << data >> to get the results
//res.send(message); // maybe you want to send back the data so use the following :
};
});ASKER
Leakim.....I hear you but where to put it in the front end? Everywhere I put it, it doesn't work.
$(document).ready(() => {
$(document).on('click', '.insert', function (e) {
//$('#insertD').click(() => {
e.preventDefault();
$("#insertE").remove();
$("#insertD").show();
$("#stationForm")[0].reset();
$("#stationForm").attr({
action: $(e.target).attr("href"),
//action: "/station", // PATH TO POST NEW STATION DATA ?
method: "POST",
});
$("#modalStationForm")
.dialog("option", "title", "Inserting New Station")
.dialog("open");
});
});
success: (data) => {
var status = data.status;
if(status = "OK")
alert("Record Successfully Submitted");
else
alert("bad news !");
}$('body').on('click', '.delete', function (e) {
var link = $(e.target);
e.preventDefault(); // https://api.jquery.com/event.preventdefault/
if (confirm("Do you really want to delete this station?"));
$.ajax({
method: 'DELETE',
//url: $(this).ptop('href'),
url: $(this).attr('href'),
success: function(data) {
alert("type is : " + typeof data);
var status = data.status;
if(status = "OK")
alert("Record Successfully Submitted");
else
alert("bad news !");
console.log('Deleted Successfully.');
link.closest(".store").remove(); //removing the most recent record live view
}
})
});ASKER
Yes.....that's the delete link.....how can I do it for the insert link?
$(document).ready(() => {
$(document).on('click', '.insert', function (e) {
//$('#insertD').click(() => {
e.preventDefault();
$("#insertE").remove();
$("#insertD").show();
$("#stationForm")[0].reset();
$("#stationForm").attr({
action: $(e.target).attr("href"),
//action: "/station", // PATH TO POST NEW STATION DATA ?
method: "POST",
});
$("#modalStationForm")
.dialog("option", "title", "Inserting New Station")
.dialog("open");
});
});
same logic :
or :
$.ajax({
url: $(this).attr("action"),
data: data,
dataType : "JSON",
contentType: "application/json",
method: $(this).attr("method"),
success: (data) => {
alert("type is : " + typeof data);
var status = data.status;
if(status = "OK")
alert("Record Successfully Submitted");
else
alert("bad news !");
link.closest(".store").remove(); //removing the most recent record live view
$("#modalStationForm").dialog("close");
}
});or :
$.ajax({
url: $(this).attr("action"),
data: data,
dataType : "JSON",
contentType: "application/json",
method: $(this).attr("method"),
}).done(function(data) {
alert("type is : " + typeof data);
var status = data.status;
if(status = "OK")
alert("Record Successfully Submitted");
else
alert("bad news !");
link.closest(".store").remove(); //removing the most recent record live view
$("#modalStationForm").dialog("close");
});ASKER
There is no ajax call in my code.....this is all I have and it works.....if I add to it, I get to inserts, an empty one and another one.
$(document).ready(() => {
$(document).on('click', '.insert', function (e) {
//$('#insertD').click(() => {
e.preventDefault();
$("#insertE").remove();
$("#insertD").show();
$("#stationForm")[0].reset();
$("#stationForm").attr({
action: $(e.target).attr("href"),
//action: "/station", // PATH TO POST NEW STATION DATA ?
method: "POST",
});
$("#modalStationForm")
.dialog("option", "title", "Inserting New Station")
.dialog("open");
});
what you just post open the modal and prepare the operation (action and method)
this is not the code to send the data to the server
this is not the code to send the data to the server
ASKER
Well.....it works.......I have attached a file for you to see.....I have commented out the ajax call and the other code because if I add them, I will receive a null record.
See the entire code including the commented out part:
You can also see the backcode here:
See the entire code including the commented out part:
$(document).ready(() => {
$(document).on('click', '.insert', function (e) {
//$('#insertD').click(() => {
e.preventDefault();
$("#insertE").remove();
$("#insertD").show();
$("#stationForm")[0].reset();
$("#stationForm").attr({
action: $(e.target).attr("href"),
//action: "/station", // PATH TO POST NEW STATION DATA ?
method: "POST",
});
$("#modalStationForm")
.dialog("option", "title", "Inserting New Station")
.dialog("open");
});
});
/*
success: (data) => {
var status = data.status;
if(status = "OK")
alert("Record Successfully Submitted");
else
alert("bad news !");
}
*/
// alert("Entry Successfully Submitted");
//});
/*
$('#insertD').click(() => {
$.ajax({
url: 'http://localhost:5000/station',
type: 'POST',
//data: $(this).serialize(),with submit handler - must have a name attribute
//data: $(this.form).serialize(), with button - must have a name attribute
/*
data: {
Station: $('#store').val(),
Address: $('#location').val(),
MonthlyCStoreSales: $('#sales').val(),
Operator: $('#dealer').val(),
TopSKU: $('#topitem').val()
},
success: (data) => {
// $('#statusF').html(data.message);
alert("Entry Successfully Submitted");
}
});
});
});
*/You can also see the backcode here:
app.put("/edit/:id", (req, res) => {
//var sql = "UPDATE stores SET station = ?, address = ?, `Monthly C-Store Sales` = ?,Operator = ?, `Top SKU` = ? WHERE id = 2";
//var sql = UPDATE `stores` SET `Station` = 'Baillou Hill', `Address` = '564 Jackson Ave.', `Monthly C-Store Sales` = '800', `Operator` = 'Marla Pikes', `Top SKU` = 'Burgers' WHERE `stores`.`ID` = 2;
//var sql = UPDATE `stores`
//SET `Station` = 'Baillou Hill', `Address` = '564 Jackson Ave.', `Monthly C-Store Sales` = '800', `Operator` = 'Marla Pikes', `Top SKU` = 'Burgers', WHERE `ID` = 2;
//Note: All spaces and special characters have to be removed.
/*
var query = "UPDATE `stores` SET";
query += "`station`= '"+req.body.Station+"',";
query += "`address`= '"+req.body.Address+"',";
//query += "`monthly c-store sales`= '"+req.body[MonthlyCStoreSales]+"',";
query += "`operator`= '"+req.body.Operator+"',";
//query += "`top sku`= '"+req.body.TopSKU+"'";
//query += " WHERE `id`= 3";
query += " WHERE `id`= "+ req.query.ID+"";
*/
station = req.body.station;
address = req.body.address;
monthlycstoresales = req.body.monthlycstoresales;
operator = req.body.operator;
topsku = req.body.topsku;
//id = req.params.ID;
id = req.body.id;
console.log(req.body);
var sql =
"UPDATE stores SET `station` = ?, `address` = ?, `monthly c-store sales` = ?,`operator` = ?, `top sku` = ? WHERE `id` = ?";
/*
db.query(query,(err, results) => {
*/
//db.query(sql, [station, address, monthlycstoresales, operator, topsku, id], (err, results, field) => {
db.query(
sql,
[
req.body.station,
req.body.address,
req.body.monthlycstoresales,
req.body.operator,
req.body.topsku,
req.body.id
],
(err, results, field) => {
if (results !== null) {
console.log("results is not null");
} else {
console.log("results is null");
}
if (sql !== null) {
console.log("sql is not null");
} else {
console.log("sql is null");
}
if (req.body.station !== null) {
console.log("req.body is not null");
} else {
console.log("req.body is null");
}
if (req.body.station !== undefined) {
console.log("req.body.Station is not undefined");
} else {
console.log("req.body.Station is undefined");
}
if (results !== undefined) {
console.log("results is not undefined");
} else {
console.log("results is undefined");
}
if (sql !== undefined) {
console.log("sql is not undefined");
} else {
console.log("sql is undefined");
}
if (err) {
console.log(err);
res.sendStatus(500);
return callback(rows);
} else {
console.log(req.body.station);
//res.redirect("/alldata");
//res.send(rows);
res.send(results);
}
}
);
});ASKER
Oh.....I'm still in development, hence all the development code still in place.
why do you comment the ajax call ?
your server side code is wrong, not frontend code
replace this : res.send(results);
by : res.send(message);
back to my previous comment (backend code) and let me know if you have question about it :
your server side code is wrong, not frontend code
replace this : res.send(results);
by : res.send(message);
back to my previous comment (backend code) and let me know if you have question about it :
message.results = results;
res.send(message);ASKER
I have made the changes requested. Please see attached file. Notice the "null" entry record.
in the success callback could you add an alert like :
alert(JSON.stringify(data)
and let me know what you get in that alert
alert(JSON.stringify(data)
and let me know what you get in that alert
ASKER
I got back this in the alert: which is pretty much the index page.
"<!DOCTYPE html>\r\n<html lang=\"en\">\r\n\r\n<head>\r\n\r\n\r\n<!--JQuery UI:css-->\r\n<link rel=\"stylesheet\" href=\"https://code.jquery.com/ui/1.12.1/themes/smoothness/jquery-ui.css\">\r\n\r\n<!--Bootstrap css-->\r\n<link rel=\"stylesheet\" href=\"https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css\"\r\nintegrity=\"sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T\" crossorigin=\"anonymous\">\r\n\r\n\r\n<!--Data Tables:css-->\r\n<link rel=\"stylesheet\" href=\"https://cdn.datatables.net/1.10.19/css/jquery.dataTables.min.css\">\r\n\r\n<!--Data Tables-Buttons-->\r\n<link rel=\"stylesheet\" href=\"https://cdn.datatables.net/buttons/1.5.6/css/buttons.dataTables.min.css\">\r\n\r\n <meta charset=\"UTF-8\">\r\n <meta name=\"viewport\" content=\"width=device-width, initial-scale=1.0\">\r\n <meta http-equiv=\"X-UA-Compatible\" content=\"ie=edge\">\r\n\r\n <title>Sample Sales-Using Node.js and Express</title>\r\n\r\n<!--JQuery-->\r\n<script src=\"https://code.jquery.com/jquery-3.4.1.js\" integrity=\"sha256-WpOohJOqMqqyKL9FccASB9O0KwACQJpFTUBLTYOVvVU=\"\r\ncrossorigin=\"anonymous\"></script>\r\n\r\n<!--JQuery UI-->\r\n<script src=\"https://code.jquery.com/jquery-1.12.4.js\"></script>\r\n<script src=\"https://code.jquery.com/ui/1.12.1/jquery-ui.js\"></script>\r\n\r\n\r\n<!--Data Tables-->\r\n<script src=\"https://cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js\"></script>\r\n\r\n<!--Data Tables:buttons css-->\r\n<link rel=\"stylesheet\" href=\"https://cdn.datatables.net/buttons/1.5.6/css/buttons.dataTables.min.css\">\r\n\r\n<!--Data Tables Sum plugin-->\r\n<script ...
could you post the whole code page and whole backend code?
I think you did not get right the modal popup part and you are still in popup old style logic
I think you did not get right the modal popup part and you are still in popup old style logic
ASKER
I thought I posted all the code above regarding the frontend and the backup for installing a record, is there something missing?
I mean from
<!DOCTYPE html>
<html>
to bottom
</html>
same from backend
Actually you've the whole code front of you
We've a puzzle (probably every piece gonna build the complete puzzle but... :)
<!DOCTYPE html>
<html>
to bottom
</html>
same from backend
Actually you've the whole code front of you
We've a puzzle (probably every piece gonna build the complete puzzle but... :)
ASKER
<!DOCTYPE html>
<html lang="en">
<head>
<!--JQuery UI:css-->
<link rel="stylesheet" href="https://code.jquery.com/ui/1.12.1/themes/smoothness/jquery-ui.css">
<!--Bootstrap css-->
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css"
integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">
<!--Data Tables:css-->
<link rel="stylesheet" href="https://cdn.datatables.net/1.10.19/css/jquery.dataTables.min.css">
<!--Data Tables-Buttons-->
<link rel="stylesheet" href="https://cdn.datatables.net/buttons/1.5.6/css/buttons.dataTables.min.css">
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="ie=edge">
<title>Sample Sales-Using Node.js and Express</title>
<!--JQuery-->
<script src="https://code.jquery.com/jquery-3.4.1.js" integrity="sha256-WpOohJOqMqqyKL9FccASB9O0KwACQJpFTUBLTYOVvVU="
crossorigin="anonymous"></script>
<!--JQuery UI-->
<script src="https://code.jquery.com/jquery-1.12.4.js"></script>
<script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
<!--Data Tables-->
<script src="https://cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>
<!--Data Tables:buttons css-->
<link rel="stylesheet" href="https://cdn.datatables.net/buttons/1.5.6/css/buttons.dataTables.min.css">
<!--Data Tables Sum plugin-->
<script src="https://cdn.datatables.net/plug-ins/1.10.19/api/sum().js"></script>
<!--Data Tables Print Button plugin-->
<script src="https://cdn.datatables.net/buttons/1.5.6/js/buttons.print.min.js"></script>
<!--Data Tables Buttons plugin-->
<script src="https://cdn.datatables.net/buttons/1.5.6/js/dataTables.buttons.min.js"></script>
<!--Data Tables Flash Buttons plugin-->
<script src="https://cdn.datatables.net/buttons/1.5.6/js/buttons.flash.min.js"></script>
<!--Data Tables PDF Make plugin-->
<script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.57/pdfmake.min.js"></script>
<!--Data Tables Excel-1 (HTML Export) plugin-->
<script src="https://cdn.datatables.net/buttons/1.5.6/js/buttons.html5.min.js"></script>
<!--Data Tables Excel-2 (ZIP Export) plugin-->
<script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.2.2/jszip.js"></script>
</head>
<body>
<ul class="container">
<h2>Sample Sales-Using Node.js and Express</h2>
<br>
<!--Loading Stores Data on Click Button-->
<h3>Store Data</h3>
<!---(1)-->
<h3>Data List</h3>
<button type="button" id="allData">Load Stores Data</button>
<br />
<br />
<ul id="stores"></ul>
<!--<ul class='dataList' id='station_list'>-->
<hr>
<br />
<!---(2a)-->
<h3>Regular Table</h3>
<button type="button" id="allDataTable">Load Stores Table Data</button>
<br />
<br />
<div id="storesT"></div>
<table class='table table-bordered table-striped' id='station_table'></table>
<hr>
<br />
<!---(2b)-->
<h3>Data Table</h3>
<form id="form1">
<table class="table table-bordered table-striped" id="datatable">
<thead>
<tr>
<th>ID</th>
<th>Station</th>
<th>Address</th>
<th>Monthly C-Store Sales</th>
<th>Operator</th>
<th>Top SKU</th>
</tr>
</thead>
<tfoot align="left">
<tr><th></th><th></th><th></th><th></th><th></th><th></th></tr>
</tfoot>
</table>
</form>
<hr>
<br />
<!---(3)-->
<h3>Navigation Button</h3>
<button type="button" id="previous">
<<<<</button> <button type="button" id="next">>>>>
</button>
<br />
<br />
<div id="storesNav"></div>
<hr>
<br />
<!---(4)-->
<h3>Drop down Menu</h3>
<select id="dmenu">
<!--<option value = "" selected disabled>-- Choose Station --</option> -->
<option selected="selected" id="opt">Choose your station</option>
</select>
<div id="optionT"></div>
<hr>
<br />
<!---(5)-->
<h3>Search Box</h3>
<label>Type Station Name</label>
<input id="stationBox" type="text">
<button id="stationOne">Submit</button>
<ul id="autoList"></ul>
<div id="searchResult"></div>
<hr>
<br />
<!---(6)-->
<h3>Inserting Data into the database</h3>
<h3>Data List</h3>
<div id="storesF"></div>
<div id="modalStationForm">
<form id="stationForm">
<!--<form id="stationForm" method="PUT" action="">-->
<label>Store Name:</label>
<!--When submitting a form, the name value is important and is used in php file, while the id is used for css and javascript(below)-->
<input type="text" name="station" id="store" size="40" placeholder="Enter Store Name">
<br><br>
<label>Address:</label>
<input type="text" name="address" id="location" size="40" placeholder="Enter the store's address">
<br><br>
<label>Store Sales:</label>
<input type="number" name="monthlycstoresales" id="sales" size="20">
<br><br>
<label>Operator:</label>
<input type="text" name="operator" id="dealer" size="40" placeholder="Enter the operator's name">
<br><br>
<label>Top SKU item:</label>
<input type="text" name="topsku" id="topitem" size="40" placeholder="Enter the store's top sku item">
<input type="hidden" id="id" name="record_id">
<br><br>
<input id="insertD" type="submit" value="Insert">
<input id="insertE" type="submit" value="Edit">
<div id="statusF"></div>
</form>
<!--
<button id="insertform-btn" type="button" data-method="POST" data-url='/station'>Insert</button>
<button id="editform-btn" type="button" data-method="PUT" data-url='/station'>Update</button>
<input id="insertD" type="submit" data-url="'/edit/" data-method='PUT'value="send">
<input id="insertD" type="submit" data-method='POST' value="send">-->
</div>
<br/>
<br/>
<br/>
<br/>
<script>
$("#modalStationForm").dialog({ autoOpen: false, modal: true, minWidth: 500 });
var i = -1;
function getData(stationsInfos) {
$.getJSON('/alldata', (data) => {
$.each(data, function (index, value) {
stationsInfos[value.ID] = value;
});
});
}
//(1) - Datalist
$(document).ready(() => {
$('#allData').click(() => {
if ($('#stores').is(':empty')) {
console.log('It is empty, data can load');
$.ajax({
//$.getJSON({
url: 'http://localhost:5000/alldata',
type: 'GET',
datatype: 'json',
success: (data) => {
//console.log('You received some', data);
//var test = JSON.stringify(data);
//console.log(test);
//var station_data = '<a href="http://localhost:5000/form.html" target="_blank" class="insert">Insert a new Record</a>';
var station_data = '<a href="http://localhost:5000/station" target="_blank" class="insert">Insert a new Record</a>';
$.each(data, function (key, value) {
console.log(value.ID);
//Concatenation - the Old Way (pluses and single quotes)
station_data += '<div class="store">' + '<li>ID: ' + value.ID + '</li>' +
'<li>Station: ' + value.Station + '</li>' +
'<li>Address: ' + value.Address + '</li>' +
'<li>Sales: ' + value['Monthly C-Store Sales'] + '</li>' +
'<li>Operator: ' + value.Operator + '</li>' +
'<li>Top SKU: ' + value['Top SKU'] + '</li>' + '</ul>' +
'<div class="store-action">' +
'<input type="hidden" value=\'' + JSON.stringify(value) + '\'>' +
'<a class="edit" href="http://localhost:5000/edit/' + value.ID + '">Edit</a>' + ' | ' +
'<a class="delete" href="http://localhost:5000/stationNames/number/' + value.ID + '" id="del">Delete</a>' +
'</div>';
});
//'</li>' + '' + '' + '' + '' + '<a href="/edit/'+value.ID+'" target="_blank">Edit</a>' + '|' + '<a href="/stationNames/number/'+value.ID+'" id="del">Delete</a>' + '<br/>';
//Template Strings or Template Literals (preferred way)
/*
station_data += `<br/><br/><li>ID:${value.ID}</li>
<li>Station:${value.Station}</li>
<li>Address:${value.Address}</li>
<li>Sales:${value['Monthly C-Store Sales']}</li>
<li>Operator:${value.Operator}</li>
<li>Top SKU:${value['Top SKU']}</li>
<li><a href="http://localhost:5000/edit/${value.ID}" class="edit-link" target="_blank">Edit</a> | <a href="http://localhost:5000/stationNames/number/${value.ID}" class="delete-link">Delete</a><br/>`;
});*/
$('#stores').append(station_data);
//$('#station_list').remove(station_data);
//$('#station_list').hide(station_data);
//$('#station_list').show(station_data)
//$('#station_list').toggle(station_data);
}
});
} else {
//console.log('It is not empty, data should not load');
$('#stores').toggle();
};
});
});
//(2) Regular Table
$(document).ready(() => {
$('#allDataTable').click(() => {
if ($('#station_table').is(':empty')) {
console.log('It is empty, data can load');
$.ajax({
//$.getJSON({
url: 'http://localhost:5000/alldata',
type: 'GET',
datatype: 'json',
success: (data) => {
//console.log('You received some', data);
//var test = JSON.stringify(data);
//console.log(test);
var station_dataT = '';
station_dataT += '<tr>' + '<th>ID</th>' + '<th>Station</th>' + '<th>Address</th>' +
'<th>Monthly C-Store Sales</th>' + '<th>Operator</th>' + '<th>Top SKU</th>' + '</tr>';
$.each(data, function (key, value) {
station_dataT += '<tr><td>' + value.ID + '</td><td>' + value.Station + '</td><td>' +
value.Address + '</td><td>' + value['Monthly C-Store Sales'] + '</td><td>' + value
.Operator + '</td><td>' + value['Top SKU'] + '</tr></td>';
});
$('#station_table').append(station_dataT);
//$('#station_table').remove(station_dataT);
//$('#station_table').hide(station_dataT);
//$('#station_table').show(station_dataT)
//$('#station_table').toggle(station_dataT);
}
});
} else {
console.log('It is not empty, data should not load');
$('#station_table').toggle();
};
});
});
///////////////////////////////////////////Datatables/////////////////////////////////////
//https://cdn.datatables.net/
$(document).ready(function () {
$('#datatable').DataTable({
"processing": true,
"serverSide": false, //https://phppot.com/php/datatables-server-side-processing-using-php-with-mysql/
"ajax": {
"url": "http://localhost:5000/alldata", dataSrc:"",
"type": "GET"
},
"columns": [
{ "data": "ID" },
{ "data": "Station" },
{ "data": "Address" },
{ "data": "Monthly C-Store Sales" },
{ "data": "Operator" },
{ "data": "Top SKU" }
],
dom:'Bfrtip',
buttons: [
'copy', 'excel', 'csv', 'print',
],
//Displaying the sum of the datatable columns
"footerCallback": function ( row, data, start, end, display ) {
var api = this.api(), data;
// converting to interger to find total
var intVal = function ( i ) {
return typeof i === 'string' ?
i.replace(/[\$,]/g, '')*1 :
typeof i === 'number' ?
i : 0;
};
// computing column Total of the complete result
/*
var idTotal = api
.column( 1 )
.data()
.reduce( function (a, b) {
return intVal(a) + intVal(b);
}, 0 );
var stationTotal = api
.column( 2 )
.data()
.reduce( function (a, b) {
return intVal(a) + intVal(b);
}, 0 );
var addressTotal = api
.column( 3 )
.data()
.reduce( function (a, b) {
return intVal(a) + intVal(b);
}, 0 );
*/
var salesTotal = api
.column( 3 )
.data()
.reduce( function (a, b) {
return intVal(a) + intVal(b);
}, 0 );
/*
var operatorTotal = api
.column( 5 )
.data()
.reduce( function (a, b) {
return intVal(a) + intVal(b);
}, 0 );
var topSkuTotal = api
.column( 6 )
.data()
.reduce( function (a, b) {
return intVal(a) + intVal(b);
}, 0 );
*/
// Update footer by showing the total with the reference of the column index
$( api.column( 0 ).footer() ).html('Total');
//$( api.column( 1 ).footer() ).html(idTotal);
//$( api.column( 2 ).footer() ).html(stationTotal);
//$( api.column( 3 ).footer() ).html(addressTotal);
$( api.column( 3 ).footer() ).html(salesTotal);
//$( api.column( 5 ).footer() ).html(operatorTotal);
//$( api.column( 6 ).footer() ).html(topSkuTotal);
console.log(salesTotal);
},
"processing": true,
"serverSide": false,
//"ajax": "http://localhost:5000/alldata",
//"ajax": "http://localhost:5000/alldata", dataSrc:"",
//"type": "GET"
});
});
//(3) Navigation Button - Previous
// console.log(users[0]); Returns everything on ID 1, carmichael Road
//console.log(users[i]); Returns everything on ID 1, Carmichael Road
//console.log(users[1]); Returns everything on ID 2, Baillou Road
//console.log(users); Returns everything
//console.log(users.ID); Returns Undefined
//console.log(users.ID[i]); Returns cannot read property
//console.log(users[i].ID); Returns 1
//console.log(users[i].Station); Returns Carmichael
$(document).ready(() => {
$('#previous').click(() => {
$.ajax({
url: 'http://localhost:5000/alldata',
type: 'GET',
datatype: 'json',
success: (data) => {
$.each(data, function (i) {
});
data[i]
console.log(i);
if (i > 0) {
i--;
var station_loop = '';
console.log(JSON.stringify(data[i].Station));
station_loop += '<li>ID: ' + JSON.stringify(data[i].ID) + '</li>' +
'<li>Station: ' + JSON.stringify(data[i].Station) + '</li>' +
'<li>Address: ' + JSON.stringify(data[i].Address) + '</li>' +
'<li>Sales: ' + JSON.stringify(data[i]['Monthly C-Store Sales']) + '</li>' +
'<li>Operator: ' + JSON.stringify(data[i].Operator) + '</li>' +
'<li>Top SKU: ' + JSON.stringify(data[i]['Top SKU']) + '</li>' +
'</<li>' + '<br/>';
$('#storesNav').html(station_loop);
}
}
})
});
});
//(3) Navigation Button - Next
$(document).ready(() => {
$('#next').click(() => {
//var url = 'localhost:5000/alldata';
//var url = 'https://api.github.com/users/topjavatutorial/repos';
$.ajax({
url: 'http://localhost:5000/alldata',
type: 'GET',
datatype: 'json',
success: (data) => {
$.each(data, function (i) {
});
data[i]
console.log(i);
len = data.length;
if (i < (len - 1)) {
i++;
var station_loop = '';
console.log(JSON.stringify(data[i].Station));
station_loop += '<li>ID: ' + JSON.stringify(data[i].ID) + '</li>' +
'<li>Station: ' + JSON.stringify(data[i].Station) + '</li>' +
'<li>Address: ' + JSON.stringify(data[i].Address) + '</li>' +
'<li>Sales: ' + JSON.stringify(data[i]['Monthly C-Store Sales']) + '</li>' +
'<li>Operator: ' + JSON.stringify(data[i].Operator) + '</li>' +
'<li>Top SKU: ' + JSON.stringify(data[i]['Top SKU']) + '</li>' +
'</<li>' + '<br/>';
$('#storesNav').html(station_loop);
}
}
})
});
});
//(4) Dropdown Menu
//Function for Loading Stores Data by selecting a drop down menu--
// https://www.experts-exchange.com/articles/28828/Creating-linked-dropdowns-using-jQuery-and-PHP.html
$(document).ready(() => {
var stationsInfos = {};
$.getJSON('/alldata', (data) => {
$.each(data, function (index, value) {
stationsInfos[value.ID] = value;
// (1) - alert( this.value ); produces the index position of station (e.g. 2, 3 etc)
// (2) - alert($(this).find(":selected").val()); produces the index position of station (e.g. 2, 3 etc)
// (3) - alert($(this).find(":selected").text()); produces the item selected (e.g Winton)
// (4) - alert($("#dmenu option:selected").text()); produces the item selected (e.g Winton)
// (5) - alert($("#dmenu :selected").text()); produces the item selected (e.g Winton)
// (6) - alert($("#dmenu").children(":selected").text()); produces the item selected (e.g Winton)
// (7) - $("#select option:selected").val(); have to test
// (8) - $("#dmenu option:selected").attr("id"); have to test
// (9) - $("#dmenu option:selected").attr("value"); have to test
// (10) - var value= $('option:selected', $('#dmenu')).val(); have to test
// (11) - alert($("#dmenu option:selected").value()); value is not a function
// (12) - alert(value.Station); Value not defined
$('#dmenu').append($('<option/>').val(value.ID).text(value.Station));
//ALTERNATIVE WAY
// success: (data) => {
//$.each(data, function (index, value) {
// var output = [];
//$.each(data, function(key, value) {
//output.push('<option value="'+ key +'">'+ value.Station +'</option>');
//});
//$('#dmenu').html(output.join(''));
});
});
$('#dmenu').on('change', function () {
if (this.selectedIndex > 0) {
var ID = $(this).val();
var optiont = stationsInfos[ID];
var station_loopOp = '<li>ID: ' + optiont.ID + '</li>' +
'<li>Station: ' + optiont.Station + '</li>' +
'<li>Address: ' + optiont.Address + '</li>' +
'<li>Sales: ' + optiont['Monthly C-Store Sales'] + '</li>' +
'<li>Operator: ' + optiont.Operator + '</li>' +
'<li>Top SKU: ' + optiont['Top SKU'] + '</li>';
$('#optionT').html(station_loopOp);
}
else
$('#optionT').html("");
});
});
//(4) - same result but with Auto Complete - need some work
/* $(document).ready(() => {
var stationsInfos = [];
$.getJSON('/alldata', (data) => {
$.each(data, function (i, v) {
v.value = v.Station;
v.label = v.Station;
stationsInfos.push(v);
});
$("#tmenu").autocomplete({
source: stationsInfos,
select: function(event, ui) {
var optiont = ui.item;
var station_loopOp = '<li>ID: ' + optiont.ID + '</li>' +
'<li>Station: ' + optiont.Station + '</li>' +
'<li>Address: ' + optiont.Address + '</li>' +
'<li>Sales: ' + optiont.Monthly_CStore_Sales + '</li>' +
'<li>Operator: ' + optiont.Operator + '</li>' +
'<li>Top SKU: ' + optiont.Top_SKU + '</li>';
$('#optionT').html(station_loopOp);
}
});
});
})
*/
//(5) Search Box
//Inserting information in a search bar--
$(document).ready(() => {
var stationsInfos = {};
getData(stationsInfos);
$('#stationOne').click(() => {
var requestUrl = 'stationNames/' + $('#stationBox').val();
console.log('making ajax request to', requestUrl)
$.ajax({
url: requestUrl,
type: 'GET',
datatype: 'json',
success: (data) => {
console.log('You received some', data);
var search_data = '';
$.each(data, function (key, value) {
//stationsInfos[value.ID] = value;
//console.log(value.ID);
search_data += '<li>ID: ' + value.ID + '</li>' +
'<li>Station: ' + value.Station + '</li>' +
'<li>Address: ' + value.Address + '</li>' +
'<li>Sales: ' + value['Monthly C-Store Sales'] + '</li>' +
'<li>Operator: ' + value.Operator + '</li>' +
'<li>Top SKU: ' + value['Top SKU'] + '</li>' +
'</<li>' + '<br/>';
});
$('#searchResult').html(search_data);
}
})
});
// Autocomplete for search bar
// https://www.youtube.com/watch?v=mZOpvhywT_E
$('#stationBox').on('input', () => {
$('#autoList').html('');
var searchField = $('#stationBox').val();
var expression = new RegExp(searchField, "i");
if (searchField.length) {
$.each(stationsInfos, function (index, value) {
if (value.Station.search(expression) != -1) {
$('#autoList').append('<li>' + value.Station + '</li>');
}
});
}
});
});
//ALTERNATIVE WITH KEYUP METHOD
/*$(document).ready(function(){
$('#stationBox').keyup(function(){
$('#autoList').html('');
var searchField = $('#stationBox').val();
var expression = new RegExp(searchField,"i");
$.getJSON('/alldata', (data) => {
$.each(data, function (index, value) {
console.log(value.Station);
if(value.Station.search(expression) != -1)
{
$('#autoList').append('<li>'+value.Station+'</li>');
}
})
});
});
});
*/
//(6) Inserting information into the database
$(document).ready(() => {
$(document).on('click', '.insert', function (e) {
//$('#insertD').click(() => {
e.preventDefault();
$("#insertE").remove();
$("#insertD").show();
$("#stationForm")[0].reset();
$("#stationForm").attr({
action: $(e.target).attr("href"),
//action: "/station", // PATH TO POST NEW STATION DATA ?
method: "POST",
});
$("#modalStationForm")
.dialog("option", "title", "Inserting New Station")
.dialog("open");
});
});
/*
success: (data) => {
var status = data.status;
if(status = "OK")
alert("Record Successfully Submitted");
else
alert("bad news !");
}
*/
// alert("Entry Successfully Submitted");
//});
$('#insertD').click(() => {
$.ajax({
url: 'http://localhost:5000/station',
type: 'POST',
//data: $(this).serialize(),with submit handler - must have a name attribute
//data: $(this.form).serialize(), with button - must have a name attribute
data: {
Station: $('#store').val(),
Address: $('#location').val(),
MonthlyCStoreSales: $('#sales').val(),
Operator: $('#dealer').val(),
TopSKU: $('#topitem').val()
},
success: (data) => {
// $('#statusF').html(data.message);
alert(JSON.stringify(data));
//alert("Entry Successfully Submitted");
}
});
});
//});
////////////////////////////////////New Version//////////////////////////////////////////
/////////////////////////my version////////////////////////////////////////////////////
//(7) Editing information into the database
$(document).ready(() => {
$(document).on('click', '.edit', function (e) {
e.preventDefault();
$("#insertD").hide();
$("#insertE").show();
$("#stationForm")[0].reset();
$("#stationForm").attr({
action: $(e.target).attr("href"),
//action: "/station", // PATH TO POST NEW STATION DATA ?
method: "PUT"
});
var value = $(event.target).closest("div").find("input:hidden").val();
value = JSON.parse(value);
$("#store", "#modalStationForm").val(value.Station);
$("#location", "#modalStationForm").val(value.Address);
$("#sales", "#modalStationForm").val(value['Monthly C-Store Sales']);
$("#dealer", "#modalStationForm").val(value.Operator);
$("#topitem", "#modalStationForm").val(value['Top SKU']);
$("#id", "#modalStationForm").val(value.ID);
$("#modalStationForm")
.dialog("option", "title", "Editing Station")
.dialog("open");
});
$('#insertE').click((event) => {
//$("#stationForm", "#modalStationForm").on("submit", function (event)
//$("#stationForm").on("submit", function (event) {
event.preventDefault();
//var methodToUse = $(this).attr("method"); // PUT
//var url = $(this).data("url");
/*
var data = {
station: $("#store", "#modalStationForm").val(),
address: $('#location', "#modalStationForm").val(),
monthlycstoresales: $('#sales', "#modalStationForm").val(),
operator: $('#dealer', "#modalStationForm").val(),
topsku: $('#topitem', "#modalStationForm").val(),
id: $('#id', "#modalStationForm").val()
};
*/
var data = JSON.stringify({
station: $("#store", "#modalStationForm").val(),
address: $('#location', "#modalStationForm").val(),
monthlycstoresales: $('#sales', "#modalStationForm").val(),
operator: $('#dealer', "#modalStationForm").val(),
topsku: $('#topitem', "#modalStationForm").val(),
id: $('#id', "#modalStationForm").val()
});
var you = $(location).attr('href');
var me = window.location.href;
//console.log(you);
$.ajax({
url:'/edit/' + $('#id', "#modalStationForm").val(),
//url: $(this).attr("action"),
//url:'http://localhost:5000/edit/3',
//url:'http://localhost:5000/station', wrong url 404 message
data: data,
dataType : "JSON",
contentType: "application/json",
method: "PUT",
success: (data) => {
alert('Record successfully edited')
console.log(data);
console.log('you and me');
console.log(me);
//console.log(url);
//alert(url);
$("#modalStationForm").dialog("close");
}
});
});
});
//jqueryui.com/dialog/
/*
$('#insertD').click(() => {
$.ajax({
//url: $(this).attr("action"),
url: 'http://localhost:5000/edit/' + value.ID,
type: 'PUT',
//data: $(this).serialize(),with submit handler - must have a name attribute
//data: $(this.form).serialize(), with button - must have a name attribute
data: {
Station: $('#store').val(),
Address: $('#location').val(),
MonthlyCStoreSales: $('#sales').val(),
Operator: $('#dealer').val(),
TopSKU: $('#topitem').val()
},
success: (data) => {
// $('#statusF').html(data.message);
alert("Entry Successfully Edited");
}
});
});
});
*/
//////////////////////////////////////////////////////////////////////////////////////
/*
(7)
//Editing information from the database--
$(document).ready(() => {
$(document).on("click", ".edit", (e) => {
e.preventDefault();
$("#stationForm").attr({
action: $(e.target).attr("href"),
method: "POST"
//method: "PUT"
});
//var stationsInfos = {};
//getData(stationsInfos);
var value = $(e.target).closest("div").find("input:hidden").val();
value = JSON.parse(value);
$("#store", "#modalStationForm").val(value.Station);
$("#location", "#modalStationForm").val(value.Address);
$("#monthlycstoresales", "#modalStationForm").val(value['Monthly C-Store Sales']);
$("#dealer", "#modalStationForm").val(value.Operator);
$("#topitem", "#modalStationForm").val(value['Top SKU']);
$("#modalStationForm")
.dialog("option", "title", "Editing Station")
.dialog("open");
});
$("#insertD").on("submit", function (event) {
event.preventDefault();
var methodToUse = $(this).attr("method"); // POST OR PUT
var data = {
Station: $('#store').val(),
Address: $('#location').val(),
MonthlyCStoreSales: $('#sales').val(),
Operator: $('#dealer').val(),
TopSKU: $('#topitem').val()
}
$.ajax({
url: $(this).attr("action"),
data: data,
method: methodToUse,
success: (data) => {
console.log(data);
$("#modalStationForm").dialog("close");
}
});
});
});
*/
/*
$("#modalStationForm").dialog({autoOpen: false, modal: true, minWidth:360}); // https://jqueryui.com/dialog/
function addNewStation(key, value) {
console.log(value.ID);
var station_data = '<div class="store">' +
'<ul class="store-infos">' +
'<li>ID: ' + value.ID + '</li>' +
'<li>Station: <b>' + value.Station + '</b></li>' +
'<li>Address: ' + value.Address + '</li>' +
'<li>Sales: ' + value.Monthly_CStore_Sales + '</li>' +
'<li>Operator: ' + value.Operator + '</li>' +
'<li>Top SKU: ' + value.Top_SKU + '</li>' +
'</ul>' +
'<div class="store-action">' +
'<input type="hidden" value=\'' + JSON.stringify(value) + '\'>' +
'<a class="edit-link" href="/station/number/' + value.ID + '"><i class="fa fa-edit"></i>Edit</a>' +
' | ' +
'<a class="delete-link" href="/stationNames/number/' + value.ID + '" id="del"><i class="fa fa-trash"></i>Delete</a>' +
'</div>';
$('#stores').append(station_data);
}
function loadStoresData(data) {
$.each(data, addNewStation);
}
$('#load-stores').click((event) => {
event.preventDefault();
var noStoreYet = !$(".store", "#stores").length;
if (noStoreYet)
$.getJSON('./alldata', {}, loadStoresData);
else
$('#stores').toggle();
});
*/
/////////////////////////////////////////////////////////////////////////////////////////
/*
//(7)
//Edit event handler
$('body').on('click', '.edit', function () {
$("#stationForm").submit(function (e) {
e.preventDefault();
//var data = ??; //Here I'm not sure where's your edited data that you want to submit for the update query
var params = {
Station: $('#store').val(),
Address: $('#location').val(),
MonthlyCStoreSales: $('#sales').val(),
Operator: $('#dealer').val(),
TopSKU: $('#topitem').val()
};
var par_val;
var param_id = new window.URLSearchParams(window.location.search);
par_val = param_id.get('id');
console.log(par_val);
var par_url = par_val;
$.ajax({
method: 'POST',
url: "http://localhost:5000/edit/'" + par_val,
//url: $(this).attr('href'),
datatype: 'json',
data: { send_obj: JSON.stringify(params) },
success: function (data) {
console.log('Submission was successful.');
console.log(data);
},
error: function (data) {
console.log('An error occurred.');
console.log(data);
},
//data: data,
//}).done(function (data) {
//console.log(data); //backend response
});
});
});
*/
//(8) Deleting a record from the database
$('body').on('click', '.delete', function (e) {
var link = $(e.target);
e.preventDefault(); // https://api.jquery.com/event.preventdefault/
if (confirm("Do you really want to delete this station?"));
$.ajax({
method: 'DELETE',
//url: $(this).ptop('href'),
url: $(this).attr('href'),
}).done(function (data) {
alert('Deleted Successfully.');
link.closest(".store").remove(); //removing the most recent record live view.
});
});
//ALTERNATIVE WAY*/
/*
$(".delete-link").on("click", (event) => {
event.preventDefault(); // https://api.jquery.com/event.preventdefault/
$.ajax({ url: $(this).attr("href"), method: 'DELETE', success:(data) => { console.log(data); }});
});*/
</script>
<!--Bootstrap-->
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js"
integrity="sha384-JjSmVgyd0p3pXB1rRibZUAYoIIy6OrQ6VrjIEaFf/nJGzIxFDsf4x0xIM+B07jRM" crossorigin="anonymous"></script>
<!--Popper-->
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper.min.js" integrity="sha384-UO2eT0CpHqdSJQ6hJty5KVphtPhzWj9WO1clHTMGa3JDZwrnQq4sF86dIHNDz0W1" crossorigin="anonymous"></script>
</body>
</html>const fs = require("fs");
const express = require("express");
const bodyParser = require("body-parser");
const cors = require("cors");
const path = require("path");
const expressLayouts = require("express-ejs-layouts");
const mysql = require("mysql");
const app = express();
//URL List
//(1) - localhost:5000/alldata (Getting all data from database)
//(2) - localhost:5000/stationNames (Getting all stations names in an object)
//(3) - localhost:5000/station/number/:id (Getting info by id number)
//(4) - localhost:5000/stationNames/:name (Getting info for a specific station by name)
//(5) - localhost:5000/station (Inserting information within the database)
//(6) - localhost:5000/stationNames/number/:id (Deleting info by id number)
//(7) - localhost:5000/edit/:id (Updating/editing info by id number)
app.use(cors());
app.use(expressLayouts);
app.set("views", path.join(__dirname, "views"));
app.set("view engine", "ejs");
//Creating Connection to Database
//const db = mysql.createPool({
const db = mysql.createConnection({
host: "localhost",
user: "root",
password: "love",
database: "smalldata"
});
//Connecting
db.connect(function (err) {
if (err) {
throw err;
//return;
}
console.log("MySql Connected.....");
});
//https://www.npmjs.com/package/mysql
//Test Connection to Database
app.get("/testconnection", (req, res) => {
if (db != null) {
res.send("connection success");
} else {
res.send("connection failed");
}
});
//(1)getting all data from database
app.get("/alldata", (req, res) => {
db.query("SELECT * FROM stores", (err, rows, fields) => {
if (!err) res.send(rows);
else console.log(err);
});
});
//(2)getting all stations names in an object
app.get("/stationNames", (req, res) => {
db.query("SELECT station FROM stores", (err, rows, fields) => {
if (!err) res.send(rows);
else console.log(err);
});
});
//OR
//Getting all stations names in an array
/*app.get('/stationNames',(req,res)=>{
let sql = 'SELECT station FROM stores';
let query = db.query(sql, (err, results) =>{
const allStations = results.map(e=>e.station);
if (err)throw err;
//console.log('Checking if this works, allStations');
res.send(allStations);
});
});*/
//(3)Getting info by id number
app.get("/station/number/:id", (req, res) => {
db.query(
"SELECT * FROM stores WHERE id = ?",
[req.params.id],
(err, rows, fields) => {
if (!err) res.send(rows);
else console.log(err);
}
);
});
//(4)Getting info for a specific station by name
app.get("/stationNames/:name", (req, res) => {
const nameToLookup = req.params.name;
console.log(nameToLookup);
db.query(
"SELECT * FROM stores WHERE station = ?",
[nameToLookup],
(err, rows, fields) => {
//db.query('SELECT * FROM stores WHERE station = $station';
//{
// $station = nameToLookup
//}
if (!err) res.send(rows);
else console.log(err);
}
);
});
//(5)Inserting information within the database - checked with postman.
app.use(bodyParser.json());
app.use(
bodyParser.urlencoded({
extended: true
})
);
app.post("/station", (req, res) => {
console.log(req.body);
//The column names has to be exactly how it is in the database, with spaces, capital letters and special characters......if there are special characters and spaces, they would be contained within backticks (` `).
var sql =
"INSERT INTO stores (`id`, `station`, `address`, `monthly c-store sales`, `operator`, `top sku`) VALUES (?, ?, ?, ?, ?, ?)";
//"UPDATE stores SET `station` = ?, `address` = ?, `monthly c-store sales` = ?,`operator` = ?, `top sku` = ? WHERE `id` = ?";
//Note: All spaces and special characters have to be removed.
id = req.body.id;
station = req.body.station;
address = req.body.address;
monthlycstoresales = req.body.monthlycstoresales;
operator = req.body.operator;
topsku = req.body.topsku;
/*
id = req.body.ID;
station = req.body.Station;
address = req.body.Address;
monthlycstoresales = req.body.MonthlyCStoreSales;
operator = req.body.Operator;
topsku = req.body.TopSKU;
*/
db.query(
sql,
[id, station, address, monthlycstoresales, operator, topsku],
(err, results) => {
let message = {};
if (results !== null) {
console.log('results is not null');
message.status = "OK";
}else{
console.log('results is null');
message.status = "KO"; // ?
};
if (err) {
console.log(err);
res.sendStatus(500);
return;
} else {
//res.send(results);
res.redirect("/");
}
}
);
});
/* Note: When putting it in JSON format, all spaces and all special characters like -,',*, etc. have to be removed. Also, this format should be used in Postman.
{
{
"ID": 20,
"Station": "peace",
"Address": "hope",
"MonthlyCStoreSales": 10,
"Operator": "love",
"TopSKU": "majestic"
}
}*/
//(6) Deleting info by id number - checked in postman
app.delete("/stationNames/number/:id", (req, res) => {
db.query(
"DELETE FROM stores WHERE id = ?",
[req.params.id],
(err, rows, fields) => {
if (!err) res.send(rows);
else console.log(err);
//return;
}
);
});
//(7) Updating/editing info by id number - checked in postman
app.put("/edit/:id", (req, res) => {
//var sql = "UPDATE stores SET station = ?, address = ?, `Monthly C-Store Sales` = ?,Operator = ?, `Top SKU` = ? WHERE id = 2";
//var sql = UPDATE `stores` SET `Station` = 'Baillou Hill', `Address` = '564 Jackson Ave.', `Monthly C-Store Sales` = '800', `Operator` = 'Marla Pikes', `Top SKU` = 'Burgers' WHERE `stores`.`ID` = 2;
//var sql = UPDATE `stores`
//SET `Station` = 'Baillou Hill', `Address` = '564 Jackson Ave.', `Monthly C-Store Sales` = '800', `Operator` = 'Marla Pikes', `Top SKU` = 'Burgers', WHERE `ID` = 2;
//Note: All spaces and special characters have to be removed.
/*
var query = "UPDATE `stores` SET";
query += "`station`= '"+req.body.Station+"',";
query += "`address`= '"+req.body.Address+"',";
//query += "`monthly c-store sales`= '"+req.body[MonthlyCStoreSales]+"',";
query += "`operator`= '"+req.body.Operator+"',";
//query += "`top sku`= '"+req.body.TopSKU+"'";
//query += " WHERE `id`= 3";
query += " WHERE `id`= "+ req.query.ID+"";
*/
station = req.body.station;
address = req.body.address;
monthlycstoresales = req.body.monthlycstoresales;
operator = req.body.operator;
topsku = req.body.topsku;
//id = req.params.ID;
id = req.body.id;
console.log(req.body);
var sql =
"UPDATE stores SET `station` = ?, `address` = ?, `monthly c-store sales` = ?,`operator` = ?, `top sku` = ? WHERE `id` = ?";
/*
db.query(query,(err, results) => {
*/
//db.query(sql, [station, address, monthlycstoresales, operator, topsku, id], (err, results, field) => {
db.query(
sql,
[
req.body.station,
req.body.address,
req.body.monthlycstoresales,
req.body.operator,
req.body.topsku,
req.body.id
],
(err, results, field) => {
if (results !== null) {
console.log("results is not null");
} else {
console.log("results is null");
}
if (sql !== null) {
console.log("sql is not null");
} else {
console.log("sql is null");
}
if (req.body.station !== null) {
console.log("req.body is not null");
} else {
console.log("req.body is null");
}
if (req.body.station !== undefined) {
console.log("req.body.Station is not undefined");
} else {
console.log("req.body.Station is undefined");
}
if (results !== undefined) {
console.log("results is not undefined");
} else {
console.log("results is undefined");
}
if (sql !== undefined) {
console.log("sql is not undefined");
} else {
console.log("sql is undefined");
}
if (err) {
console.log(err);
res.sendStatus(500);
return callback(rows);
} else {
console.log(req.body.station);
//res.redirect("/alldata");
//res.send(rows);
//message.results = results;
//res.send(message);
res.send(results);
}
}
);
});
//Set Static Folder - cannot not be used with the views folder-it will override the views template files.
app.use(express.static(path.join(__dirname, "public")));
//*******************************************/
//Views Folder Pages
//app.get('/', function(request, response) {
// response.render('index', {
// overflow: 'Bless me please'
// });
//});
// To get the about page route. It has navbar created in layout.
//path - http://127.0.0.1:5000/about
//app.get('/about', function(request, response) {
// response.render('about');
//})
// To get the test page route. It has navbar created in layout.
//path - http://127.0.0.1:5000/test
//app.get('/test', function(request, response) {
// response.render('test');
//})
//const smalldata = require ('./smallData.json');//- not recommended; will cache the results
//const jsontext = require ('./storestext.js');
//creating an instance of the server
//configuring express server to run everything through bodyparser and cors
// let stores = {}
// fs.readFile('smalldata.json', 'utf-8', (err, data) => {
// if (err) throw err
// stores = JSON.parse(data)
// console.log(stores);
// })
//Set Static Folder - cannot not be used with the views folder-it will override the views template files.
//app.use(express.static(path.join(__dirname,//'public')));
//Set the views folder to handle all of our views.
//Get route for the route directory and set it to index
//path - http://127.0.0.1:5000
//*******************************************/
//app.get('/', function(request, response) {
// response.render('index', {
// overflow: 'Bless me please'
// });
// });
// To get the about page route. It has navbar created in layout.
//path - http://127.0.0.1:5000/about
//app.get('/about', function(request, response) {
// response.render('about');
//})
// // To get the test page route. It has navbar created in layout.
//path - http://127.0.0.1:5000/test
//app.get('/test', function(request, response) {
// response.render('test');
//})
//Make a file local to your app, where you can display it anywhere in the app by using the locals variable.
//app.locals.salesdata = "8713";
//This will display the JSON as an object representation [], you will have to include JSON.stringify(salesdata) when displaying it to the browser to see the actual JSON data.
//app.locals.salesdata = require('./smallData.json');
//not recommended; will cache results
//app.get('smallData.json', (req, res) => res.json)
//THREE(3) WAYS TO BRING IN A JSON FILE
// (1a)let jsonData = require('./JsonFile.json')
//(1b)let jsonData = require('./JsonFile') // if we omitting .json also works
//(2) synchronously
//const fs = require('fs')
//let jsonData = JSON.parse(fs.readFileSync('JsonFile.json', 'utf-8'))
//(3) asynchronously
//const fs = require('fs')
//let jsonData = {}
//fs.readFile('JsonFile.json', 'utf-8', (err, data) => {
// if (err) throw err
// jsonData = JSON.parse(data)
//})
// var person = {
// firstName: "Omar",
// lastName: "Martin",
// age: "39"
// };
// console.log(person);
// function numbers(a,b){
// return a+b;
// }
// console.log(numbers(2,3));
// var seeya = function(){
// console.log("We hope you see this");
// }
// seeya();
// setInterval(seeya,5000);
// function placeOrder (orderNumber){
// console.log('Your order is number', orderNumber);
// prepareMeal(function(){
// console.log(orderNumber,'has been delivered')
// });
// }
// const prepareMeal = function(x){
// setTimeout(x,5000);
// };
// placeOrder(1);
// placeOrder(2);
// placeOrder(3);
// placeOrder(4);
const PORT = process.env.PORT || 5000;
app.listen(PORT, () => console.log(`Server started on port ${PORT}`));
I see :
app.post("/station", (req, res) => {
later, same block, I see :
db.query(
sql,
[id, station, address, monthlycstoresales, operator, topsku],
(err, results) => {
let message = {};
so you create message empty object, this is fine
but this is for /station not for an insert.
for insert I see :
app.put("/edit/:id", (req, res) => {
but later :
//message.results = results;
//res.send(message);
res.send(results);
so you don't init message and do nothing with it, so it can't work.
you put the code for /station GET
but not for /edit/:id PUT
This is what I previously posted for the /edit/:id PUT :
The last lines can be replaced by :
let the alert(JSON.stringify(data)
app.post("/station", (req, res) => {
later, same block, I see :
db.query(
sql,
[id, station, address, monthlycstoresales, operator, topsku],
(err, results) => {
let message = {};
so you create message empty object, this is fine
but this is for /station not for an insert.
for insert I see :
app.put("/edit/:id", (req, res) => {
but later :
//message.results = results;
//res.send(message);
res.send(results);
so you don't init message and do nothing with it, so it can't work.
you put the code for /station GET
but not for /edit/:id PUT
This is what I previously posted for the /edit/:id PUT :
db.query(sql, [req.body.Station, req.body.Address, req.body.MonthlyCStoreSales, req.body.Operator, req.body.TopSKU, req.query.ID], (err, results, field) => {
let message = {};
if (results !== null) {
console.log('results is not null');
message.status = "OK";
}else{
console.log('results is null');
message.status = "KO"; // ?
};
if (sql !== null) {
console.log('sql is not null');
message.sql = sql; // for testing purpose, remove this horror in production
}else{
console.log('sql is null');
message.sql = 'sql is null'; // for testing purpose, remove this horror in production
};
if (req.body.Station !== null) {
console.log('req.body is not null');
message.body = "body ok"; // useless, remove it, just for testing purpose maybe
}else{
console.log('req.body is null');
message.body = "body not ok"; // useless, remove it, just for testing purpose maybe
};
if (err) {
console.log(err);
res.sendStatus(500); // THIS WILL THROW THE fail callback of your $.ajax call
return callback(rows);
} else {
res.send(message); // maybe you want to send back the data so use the following :
//message.results = results; // mean your success callback use << data.results >> instead << data >> to get the results
//res.send(message); // maybe you want to send back the data so use the following :
};
});The last lines can be replaced by :
//res.send(message); // maybe you want to send back the data so use the following :
message.results = results; // mean your success callback use << data.results >> instead << data >> to get the results
res.send(message); // maybe you want to send back the data so use the following :let the alert(JSON.stringify(data)
ASKER
okay I made the changes......received the same results.....duplicate record with "null" fields.
The alert(JSON.stringify(data)
Look at the changes I made in the backend in the update record route:
The alert(JSON.stringify(data)
Look at the changes I made in the backend in the update record route:
app.put("/edit/:id", (req, res) => {
//var sql = "UPDATE stores SET station = ?, address = ?, `Monthly C-Store Sales` = ?,Operator = ?, `Top SKU` = ? WHERE id = 2";
//var sql = UPDATE `stores` SET `Station` = 'Baillou Hill', `Address` = '564 Jackson Ave.', `Monthly C-Store Sales` = '800', `Operator` = 'Marla Pikes', `Top SKU` = 'Burgers' WHERE `stores`.`ID` = 2;
//var sql = UPDATE `stores`
//SET `Station` = 'Baillou Hill', `Address` = '564 Jackson Ave.', `Monthly C-Store Sales` = '800', `Operator` = 'Marla Pikes', `Top SKU` = 'Burgers', WHERE `ID` = 2;
//Note: All spaces and special characters have to be removed.
/*
var query = "UPDATE `stores` SET";
query += "`station`= '"+req.body.Station+"',";
query += "`address`= '"+req.body.Address+"',";
//query += "`monthly c-store sales`= '"+req.body[MonthlyCStoreSales]+"',";
query += "`operator`= '"+req.body.Operator+"',";
//query += "`top sku`= '"+req.body.TopSKU+"'";
//query += " WHERE `id`= 3";
query += " WHERE `id`= "+ req.query.ID+"";
*/
station = req.body.station;
address = req.body.address;
monthlycstoresales = req.body.monthlycstoresales;
operator = req.body.operator;
topsku = req.body.topsku;
//id = req.params.ID;
id = req.body.id;
console.log(req.body);
var sql =
"UPDATE stores SET `station` = ?, `address` = ?, `monthly c-store sales` = ?,`operator` = ?, `top sku` = ? WHERE `id` = ?";
/*
db.query(query,(err, results) => {
*/
//db.query(sql, [station, address, monthlycstoresales, operator, topsku, id], (err, results, field) => {
db.query(
sql,
[
req.body.station,
req.body.address,
req.body.monthlycstoresales,
req.body.operator,
req.body.topsku,
req.body.id
],
(err, results, field) => {
let message = {};
if (results !== null) {
console.log('results is not null');
message.status = "OK";
}else{
console.log('results is null');
message.status = "KO"; // ?
};
if (sql !== null) {
console.log("sql is not null");
} else {
console.log("sql is null");
}
if (req.body.station !== null) {
console.log("req.body is not null");
} else {
console.log("req.body is null");
}
if (req.body.station !== undefined) {
console.log("req.body.Station is not undefined");
} else {
console.log("req.body.Station is undefined");
}
if (results !== undefined) {
console.log("results is not undefined");
} else {
console.log("results is undefined");
}
if (sql !== undefined) {
console.log("sql is not undefined");
} else {
console.log("sql is undefined");
}
if (err) {
console.log(err);
res.sendStatus(500);
return callback(rows);
} else {
//console.log(req.body.station);
//res.redirect("/alldata");
//res.send(rows);
message.results = results;
res.send(message);
//res.send(results);
}
}
);
});ASKER
Hi.....Leakim971....
I was just wondering, did you find a solution?....
I was just wondering, did you find a solution?....
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
okay
ASKER
Thank you for your assistance on this question. It is greatly appreciated.
You welcome!
Open in new window
If you need more flexability look at jQuery UI dialog or even flash.