title: Using Node.js to write MySQL One-to-Many relationship
body: <p>I have tried doing this several ways, and maybe node.js is the wrong language to use for this purpose, but I thought it could be quickly done with it. I am taking a contacts table with names and addresses, and converting it to two tables with a one-to-many relationship, so that one contact can have multiple addresses. The desired data structure is like this:</p>
  
  <p>Contact Table
  contactId
  firstName
  lastName
  phone
  email
  etc...</p>
  
  <p>Address Table
  addressId
  contactId
  street
  city
  state
  etc...</p>
  
  <p>Currently all fields are in the same table.</p>
  
  <p>The result I am getting is that the data in the second query never changes. So if I write the contact query first, then it works as expected, but the addresses all end up as duplicates of one contact's address. If I insert the address record first, then I get duplicate records of one contact. I tried nesting the queries first, but I split them out when I got this behavior. My thinking was that I could write the queries . I need to write the contact and get the ID back, then write the address with the contactID. OR I need to write the address and get THAT ID back, and then write the contact, get that ID, and then do an update query to put the contactId into the address record. This last method is the attempt shown in the code below. </p>
  
  <p>Here is my code currently:</p>
  
  <pre><code>var sql = "SELECT * FROM pfPeople";
  connection.query(sql, function(err, result, fields)  {
      if (err) throw err;
  
      for (var i = 0; i &lt; result.length; i++) {
          var names = result[i].fullName.replace(/"/g, "'").split(" ");
  
          var sql2 = "INSERT INTO contact (firstName, lastName, title, phone1, phone2, fax, email1, notes, org) VALUES (" + 
          "\"" + names[0] + "\", \"" + names[1] + "\", \"" + result[i].title.replace(/"/g, "'") + " \", \"" + result[i].phone1 + "\", \"" + 
          result[i].phone2 + "\", " + "\"" + result[i].phone3 + "\", \"" + result[i].email + "\", \"" + result[i].notes.replace(/"/g, "'") + "\", \"" + 
          result[i].org.replace(/"/g, "'") + "\")";
  
          var street = result[i].street;
          var city = result[i].city;
          var state = result[i].state;
          var zip = result[i].zip;
          var country = result[i].country;
  
          var sql3 = "INSERT INTO address (address1, city, state, zip, country) VALUES (" + 
          "\"" + street + "\", \" " + city + "\", \"" + state + "\", \"" + zip + "\", \"" + country + "\")";
  
          var contactId;
          var addressId;
  
          connection.query(sql2, function(err, cResult) {
              if (err) throw err;
  
              var contactId = cResult.insertId;
  
              console.log("Inserted ID: " + contactId);
          });
  
          connection.query(sql3, function(err, aResult) {
              if (err) throw err;
  
              var addressId = aResult.insertId;
          });
  
          var sql4 = "UPDATE address set contactId = " + contactId + " WHERE (addressId = " + addressId + ")";
  
          connection.query(sql4, function(err, uRes) {
              console.log("Address updated " + addressId + " " + contactId);
          });
  
          if (i &gt; 3) break;
      }
  });
  </code></pre>