Mysql Edit results are returning "null" results when updating, therefore no results are changed when editing the records.

Omar Martin
Omar Martin used Ask the Experts™
on
After attempting to edit a mysql record in node.js, I am receiving null results when I review the mysql query report and the affected rows are returning zero.  There is no change to the record. However, the backend says that the results are not null, the sql query is not null and that the req.body for all the values are not null.

Here is the mysql general query log:

190717 16:42:40	    3 Query	SELECT * FROM stores
		    3 Query	SELECT * FROM stores
		    3 Query	SELECT * FROM stores
190717 16:42:42	    3 Query	SELECT * FROM stores
190717 16:43:13	    3 Query	UPDATE stores SET `station` = NULL, `address` = NULL, `monthly c-store sales` = NULL,`operator` = NULL, `top sku` = NULL WHERE `id` = NULL

Open in new window


These are the results returned on the frontend:

affectedRows: 0
changedRows: 0
fieldCount: 0
insertId: 0
message: ""
protocol41: true
serverStatus: 2
warningCount: 0

Open in new window


However, the backend says that the values are not null after putting in the following code:

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');
        };

Open in new window


Here are the results.....
{ station: 'Hopefully',
  address: 'this',
  monthlycstoresales: '200',
  operator: 'will',
  topsku: 'work',
  id: '7' }

results is not null

sql is not null

req.body is not null

Open in new window



Here is the backend code:
//(7) Updating/editing info by id number - checked in postman

app.put('/edit/:id', (req, res) => {


  
  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.query.ID;

  console.log(req.body);

  var sql = "UPDATE stores SET `station` = ?, `address` = ?, ` monthly c-store sales` = ?,` operator` = ?, `top sku` = ? WHERE `id` = ?";
 

db.query(sql, [req.body.Station, req.body.Address, req.body.MonthlyCStoreSales,   req.body.Operator, req.body.TopSKU, req.query.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 (err) {
      console.log(err);
      res.sendStatus(500);
      return callback(rows);
    } else {
      
      res.send(results);
    };
  });
});

Open in new window



Here is the frontend code for the edit button:
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>';

Open in new window


Here is the ajax call for the frontend

$("#modalStationForm").dialog({ autoOpen: false, modal: true, minWidth: 500 });

//(7) Editing information into the database
  $(document).ready(() => {
    $(document).on('click', '.edit', function (e) {
      e.preventDefault();
      $("#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);
      $("#monthlycstoresales", "#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");
    });

    $('#insertD').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 = 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: $(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");
        }
      });
    });
  });

Open in new window


Here is the form:

 <h3>Inserting Data into the database</h3>
    <h3>Data List</h3>
    <div id="storesF"></div>
    <div id="modalStationForm">
      <form id="stationForm" method="POST" 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" data-url="'/edit/" data-method='PUT' value="send">
        <div id="statusF"></div>
        </form>

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
leakim971Multitechnician
Top Expert 2014

Commented:
I see(front end) line 55 :  url:'http://localhost:5000/edit/3',
and
I see(backend) line 3 : app.put('/edit/:id', (req, res) => {

first, I suggest you, one more time >:0( to use : url:'/edit/3',
and what I see wrong is the id is always 3 when it should be : url:'/edit/' + $('#id', "#modalStationForm").val(),
especially because
I see(backend) line 11 : id = req.query.ID;
Omar MartinBridgemaker

Author

Commented:
I hardcoded  the id = 3 in the code because I wanted to check to see if the results would be recognized.  When I did that the id would say 3 and all the other items would say null.  

However, when I removed the id =3 and use , it did not work but I will try again.  Do yo want to change the query in the backend also from id=req.query.ID?
leakim971Multitechnician
Top Expert 2014

Commented:
it's ok if you know why it's hardcoded
 
what do you get if you replace :
console.log(req.body);
by :
console.log(req.body.Station);

Do you get the station in the console?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Omar MartinBridgemaker

Author

Commented:
I removed:

I see(front end) line 55 :  url:'http://localhost:5000/edit/3',

and replaced it with:

url:'/edit/' + $('#id', "#modalStationForm").val(),

I am still receiving this:

76 Query	SELECT * FROM stores
190717 19:10:05	   76 Query	SELECT * FROM stores
190717 19:10:44	   76 Query	UPDATE stores SET `station` = NULL, `address` = NULL, `monthly c-store sales` = NULL,`operator` = NULL, `top sku` = NULL WHERE `id` = NULL

Open in new window

leakim971Multitechnician
Top Expert 2014

Commented:
what do you get if you replace :
console.log(req.body);
by :
console.log(req.body.Station);

Do you get the station in the console?
leakim971Multitechnician
Top Expert 2014

Commented:
replace :
       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()
       
      });

Open in new window

by :

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

Open in new window

Omar MartinBridgemaker

Author

Commented:
what do you get if you replace : console.log(req.body);
by :
console.log(req.body.Station);

Do you get the station in the console?  Yes.  Whatever the station I am editing, I normally receive in the console.
leakim971Multitechnician
Top Expert 2014

Commented:
So did you try to replace or are you assuming ?
console.log(req.body); // line 15
by :
console.log("station is "  + req.body.Station); // line 15

when you say line 20 it become null
Omar MartinBridgemaker

Author

Commented:
This is my response in the console when I remove the stringify code and replace it as you suggested.

jquery-1.12.4.js:10254 PUT http://localhost:5000/edit/4 400 (Bad Request)
Omar MartinBridgemaker

Author

Commented:
I'm sorry, if I do this, I am getting an answer of "undefine". I misspoke earlier.

console.log(req.body.Station);
Omar MartinBridgemaker

Author

Commented:
If I run this code:
if (req.body.Station !== undefined) {
                        console.log("req.body.Station is not undefined");
                  } else {
                        console.log("req.body.Station is undefined");
      }

I am receiving this:
req.body.Station is undefined

Sorry!
Omar MartinBridgemaker

Author

Commented:
I am also receiving this: note, the results and the sql are not undefined, only the variable.

req.body.Station is undefined

results is not undefined
sql is not undefined
leakim971Multitechnician
Top Expert 2014

Commented:
you probably don't have this top of your nodejs script :
var body = require("body-parser");

Open in new window

Omar MartinBridgemaker

Author

Commented:
I have this in the app at the top of the page with the others:

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


Here is the order:

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();
Omar MartinBridgemaker

Author

Commented:
I also have this above the app.put(/edit:id) callback

app.use(bodyParser.json());
app.use(
      bodyParser.urlencoded({
            extended: true
      })
);
Multitechnician
Top Expert 2014
Commented:
in the backend, just replace :
  station = req.body.Station;
  address = req.body.Address;
  monthlycstoresales = req.body.MonthlyCStoreSales;
  operator = req.body.Operator;
  topsku = req.body.TopSKU;
by :
  station = req.body.station;
  address = req.body.address;
  monthlycstoresales = req.body.monthlycstoresales;
  operator = req.body.operator;
  topsku = req.body.topsku;

and you done
Omar MartinBridgemaker

Author

Commented:
I have "good news" and "bad news".....

Good News.......the req.body.station is now defined:

Server started on port 5000
MySql Connected.....
{ station: 'ewtrjhjh',
  address: 'fhhtt',
  monthlycstoresales: '234',
  operator: 'jhjhjh',
  topsku: 'hghghg',
  id: '4' }
results is not null
sql is not null
req.body is not null
req.body.Station is not undefined
results is not undefined
sql is not undefined

The Bad News is that I am still not updating the rows........the affected rows are still zero....

affectedRows: 0
changedRows: 0
fieldCount: 0
insertId: 0
message: ""
protocol41: true
serverStatus: 2
warningCount: 0
Omar MartinBridgemaker

Author

Commented:
But even better news, but not prefect news is that I looked in the mysql log and it captured the data and did not return "null" except for the id field......

93 Query      SELECT * FROM stores
               93 Query      SELECT * FROM stores
190718  0:30:31         93 Query      UPDATE stores SET `station` = 'ewtrjhjh', `address` = 'fhhtt', `monthly c-store sales` = '234',`operator` = 'jhjhjh', `top sku` = 'hghghg' WHERE `id` = NULL
Omar MartinBridgemaker

Author

Commented:
I changed the req.query.id to req.body.id and it all works......Thank you Leakim!

190718  0:42:48         94 Connect      root@localhost as anonymous on smalldata
190718  0:42:51         94 Query      UPDATE stores SET `station` = 'air', `address` = 'water', `monthly c-store sales` = '100',`operator` = 'earth', `top sku` = 'fire' WHERE `id` = '5'
Omar MartinBridgemaker

Author

Commented:
Thank you Leakim971......I struggled for days with this issue. I am so grateful for your help.  Thank you!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial