Express.js not adding into mysql

Question

I'm a beginner to ExpressJS, I want to be able to add records to the "site" table. However, when I run the following code, it says:

Error: ER_BAD_FIELD_ERROR: Unknown column 'BeastMode' in 'field list'.

"BeastMode" is an entry I made to the shortName field.

A little context: I'm not supposed to use ORM. I have to use raw sql queries to add to MYSQL database.I'm using 'mysql'package for Nodejs to connect to the database.

 var squery = "INSERT INTO SITE (shortName,addressLine1,addressLine2,city,state,zipcode,phoneNumber) VALUES "+
     "("+
      req.body.shortName+", "+
      req.body.addressLine1+", "+
      req.body.addressLine2+", "+
      req.body.city+", "+
      req.body.state+", " +
      req.body.zipcode+", " +
      req.body.phoneNumber+" );"    

     console.log(req.body);

     dbconnector.query(squery,function(err,rows,fields){
         if(!err){
             res.send("Record Added Successfully: "+req.body);
         }else{
             res.send("Error: "+ err);
         }
     });

    });

Also, here is my dbconnect.js file:

var mysql = require('mysql');

dbconnect = mysql.createConnection({
 host: "localhost",
 user: "root",
 password: "",
 database:"rsacs"
});

module.exports = dbconnect

Here is my HTML:

<!DOCTYPE html>
<html lang="en">
<head>
   <% include head %>
</head>
<body class="container">

<header>
   <% include header %>
</header>

<main>
   <div>
       <h1><%=title%></h1>
        <form method="post" action="/site/create" >
         <div class="form-group">   
            <label for="shortName">Shortname</label>
             <input type="text" class="form-control"  placeholder="Shortname"  name="shortName"><br>

             <label for="Address Line 1"> Address Line 1:</label>
             <input type="text" class="form-control"  placeholder="Address Line 1" name="addressLine1"><br>

             <label for="Address Line 2"> Address Line 2:</label>
             <input type="text"  class="form-control"  placeholder="Address Line 2" name="addressLine2"><br>

             <label for="City">City:</label>
             <input type="text"  class="form-control"  placeholder="City" name="city"><br>

             <label for="State">State:</label>
             <input type="text"  class="form-control"  placeholder="State" name="state"><br>

             <label for="Zipcode">Zipcode:</label>
             <input type="text"  class="form-control"  placeholder="Zipcode" name="zipcode"><br>

             <label for="PhoneNumber">Phone Number:</label>
             <input type="text"  class="form-control"  placeholder="PhoneNumber" name="phoneNumber"><br>

             <button type="submit" class="btn btn-primary">Submit</button>
         </div>                                                            
       </form>
   </div>
</main>

<footer>
   <% include footer %>
</footer>

</body>
</html>

Here is my Site table structure

Please see this answer. stackoverflow.com/questions/22648667/… — Fahad Subzwari
– Fahad Subzwari, Commented Apr 9, 2018 at 2:46
I'd advise you to console.log(squery) and copy it from the logs and paste it into the mysql console. That will more clearly describe the error so that you could fix it. And also please update your post with what does console.log(squery) log and also error in the mysql console using the copied query. — vibhor1997a
– vibhor1997a, Commented Apr 9, 2018 at 5:08

hd1 · Accepted Answer · 2018-04-09 05:23:31Z

1

To echo @AnshumanJaiswal's solution, you're probably encountering an escape character problem.

The solution I'm going to propose, though, is different. The mysql nodejs driver supports prepared queries. As such, the most robust way to sort your query is:

var squery = "INSERT INTO SITE (shortName,addressLine1,addressLine2,city,state,zipcode,phoneNumber) VALUES (?,?,?,?,?,?,?);
var objs =  [req.body.shortName,req.body.addressLine1,req.body.addressLine2,req.body.city,req.body.state,req.body.zipcode,req.body.phoneNumber]
sql = mysql.format(squery, objs);
// now you have a properly-escaped SQL query which you can execute as usual:
connection.query(squery, objs, function (error, results, fields) {if (error) throw error;});

Let me know if this doesn't sort your problem.

answered Apr 9, 2018 at 5:23

hd1

34.9k5 gold badges83 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Anshuman Jaiswal · Accepted Answer · 2018-04-09 05:10:04Z

0

the values are string and you are not passing them as string. There are two possible ways:

Solution 1.

add `` to your string values like:

var squery = "INSERT INTO SITE (shortName,addressLine1,addressLine2,city,state,zipcode,phoneNumber) VALUES "+
     "('"+
      req.body.shortName+"', '"+
      req.body.addressLine1+"', '"+
      req.body.addressLine2+"', '"+
      req.body.city+"', '"+
      req.body.state+"', '" +
      req.body.zipcode+"', " +
      req.body.phoneNumber+" );"    
     ...

Solution 2.

make an object from body data as:

var data = {
    shortName: req.body.shortName,
    addressLine1: req.body.addressLine1,
    addressLine1: req.body.addressLine2,
    city: req.body.city,
    state: req.body.state,
    zipcode: req.body.zipcode,
    phoneNumber: req.body.phoneNumber
};
var squery = "INSERT INTO SITE SET ?";
dbconnector.query(squery, data, function(err,rows,fields){
     if(!err){
         console.log(rows);
         res.send("Record Added Successfully.");
     }else{
         res.send("Error: "+ err);
     }
 });

edited Apr 9, 2018 at 5:10

answered Apr 9, 2018 at 4:38

Anshuman Jaiswal

5,4621 gold badge31 silver badges46 bronze badges

4 Comments

Priyesh Goswami Over a year ago

Thanks for your response. Unfortunately both the solutions are throwing the following response: throw err; // Rethrow non-MySQL errors ^ TypeError: Cannot convert object to primitive value at Query

Anshuman Jaiswal Over a year ago

can you log your query in console for 1st solution?

Anshuman Jaiswal Over a year ago

@PriyeshGoswami I have also updated my 2nd solution, , was missing in object. Sorry for that. I think now 2nd solution must work

Anshuman Jaiswal Over a year ago

@PriyeshGoswami also check your data in body I suspect you are sending object rather than string/number from client side.

Collectives™ on Stack Overflow

Express.js not adding into mysql

2 Answers 2

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related