CODEWITHSUNDEEP
mysqlnode.jsexpress
Wahrenheit Sucher
Wahrenheit Sucher

Reputation: 357

Inserting into two tables not working. Is there a proper way?

Having trouble trying to get this code to work. Maybe I was going in the wrong direction but I essentially am inserting some data into one table like password username and type, while the other table will have customer information. 

   connection.connect(function(error) {
  if (error) {
    console.error('error connecting: ' + error.stack);
    return;
  }
  console.log('connected as id ' + connection.threadId);
});

ex.get("/create-user", function(req, res, next)  {
//use err or error?
connection.beginTransaction(function(error){
  if (error) {throw error;}
  connection.query('Insert into users( user_name, user_password, user_type) values (?,?,?)', [req.query.userName, req.query.password, req.query.Customer=1], function (error, results, fields) {
    console.log(req.query);
    if (error) {
      connection.rollback(function(){
        throw error;
      });      
    }

      connection.query('Insert into customers(cust_first_name, cust_last_name, cust_email, cust_city, cust_address, cust_zip_code, cust_state, cust_phone_num, cust_role, cust_website, cust_business_name) values (?,?,?)', [req.query.custFirstName, req.query.custLastName, req.query.custEmail, req.query.custCity, req.query.custAddress, req.query.custZipcode, req.query.custState, req.query.custPhone, req.query.custRole, req.query.custWebsite], [req.query.userName, req.query.password, req.query.Customer=1], function (error, results, fields) {
      if (error) {
        connection.rollback(function(){
          throw error;
        });
      }
      connection.commit(function(error){
          if (error) {
            connection.rollback(function(){
              throw error;
            });
          }
          console.log('transaction complete');
          connection.end();
        });
      });
    res.end(JSON.stringify(results));
    });
  });
});
var server = http.createServer(ex);
server.listen(8080);

Upvotes: 1

Views: 52

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562438

connection.query('Insert into customers(cust_first_name, cust_last_name, cust_email, 
  cust_city, cust_address, cust_zip_code, cust_state, cust_phone_num, cust_role, 
  cust_website, cust_business_name) 
values (?,?,?)', 
[req.query.custFirstName, req.query.custLastName, req.query.custEmail, 
 req.query.custCity, req.query.custAddress, req.query.custZipcode,  
 req.query.custState, req.query.custPhone, req.query.custRole, 
 req.query.custWebsite], 
[req.query.userName, req.query.password, req.query.Customer=1],

This specifies 11 columns, but you only have three ? placeholders.

Then you pass 10 values in the next argument. You need 11.

And you inexplicably pass an extra set of three values, which look like a copy & paste from the earlier INSERT.

You need to have exactly one ? for each column in the INSERT, and then pass exactly one value for each ?.

You might find this alternative syntax for INSERT to be more intuitive:

Insert into customers set cust_first_name=?, cust_last_name=?, cust_email=?,
   cust_city=?, cust_address=?, cust_zip_code=?, cust_state=?, cust_phone_num=?, 
   cust_role=?, cust_website=?, cust_business_name=?

That's a MySQL custom syntax (won't work in other brands of SQL), but it makes it a lot easier to match up the number of ? placeholders with the number of columns.

Upvotes: 1

Related Questions