Wait For It To Resolve Promise And Then Insert Neatly

I've been watching all morning because this code doesn't work for me. I want once the promise is resolved, the 'then' get its last inserted ID (the promise) and neatly insert into

Solution 1:

Here is an example of how much cleaner things can be if you use the promise-based interface built into mysql2 to sequence all your operations properly:

// using mysql2/promise
// parent function must be declared async

try {
    let posts = await con.query("SELECT ID FROM wp_posts ORDER BY ID DESC LIMIT 0,1;");
    console.log(posts);
    for (const post of posts) {
        const source = post.source;
        const text = post.text;
        const quality = post.quality;

        let sql = "INSERT INTO `wp_posts` ( `post_author`, `post_date`, `post_date_gmt`, `post_content`, `post_title`,`post_excerpt`, `post_status`,`comment_status`, `ping_status`,`post_password`, `post_name`,`to_ping`,`pinged`, `post_modified`, `post_modified_gmt`,`post_content_filtered`,`post_parent`, `guid`,`menu_order`, `post_type`, `post_mime_type`,`comment_count`) VALUES (1, '" + fhoy + "', '" + fhoy + "', '', '" + make + "','', 'publish', 'closed', 'closed','','" + make + "','','', '" + fhoy + "', '" + fhoy + "','', '" + posts[0].ID + "','','0', 'dt_links','' ,0);";
        await con.query(sql);
        console.log("1 registro link insertado");

        let result = con.query("SELECT ID FROM wp_posts WHERE post_type='dt_links' ORDER BY ID DESC LIMIT 0,1;")
        console.log(result);

        sql = "INSERT INTO wp_postmeta (post_id, meta_key, meta_value) VALUES (" + result[0].ID + ",'_dool_url', '" + source + "'),(" + result[0].ID + ",'_dool_type','" + text + "'),(" + result[0].ID + ",'_dool_quality','" + quality + "');";
        await con.query(sql);
        console.log("1 registro link meta insertado");
    }
} catch(e) {
    // handle all db errors here
}

Not only is this a lot simpler to read and understand, but it also sequences each of your database operations and collects all the error handling to one place (you had basically no error handling before).

List of changes:

1. Switch to mysql2/promise so we can use the built-in promise interface.
2. Use async and await to make sequencing asynchronous database operations a lot simpler to code
3. Use try/catch to catch and handle all database errors in one place (you had no reasonable error handling before)
4. Switch to for/of as a more convenient way to iterate an array
5. Replace all var declarations with const or let as appropriate.
6. Switch syntax from things like getData[i]["quality"] to getData[i].quality because it's just simpler and the quotes and brackets are not needed around a simple property name.

Open Issues:

1. Your first query in the for loop is exactly the same every time through the loop. That does not seem correct. The only reference to result in that original query was result[0].ID, but that will be exactly the same value every time through the loop so something is apparently not correct with that. I don't think you want to be inserting the same record everytime through the loop.

Solution 2:

So the SQL injection vulnerabilities aside, you may have a better time with a promisified con.query function, here called queryP().

I'm not sure I could capture the original logic of your code since it was reusing result, etc., but the idea should be there.

Do note though that there is a possible problem here in that if multiple concurrent requests modify wp_posts, the post IDs you're modifying may change. You may want to look at this to get the last inserted ID.

function queryP(con, sql, variables = []) {
  return new Promise((resolve, reject) => {
    con.query(sql, variables, (err, result, fields) => {
      if (err) {
        return reject(err);
      }
      return resolve({ result, fields });
    });
  });
}

async function processDataThing(con, result, dataThing) {
  var source = dataThing["source"];
  var text = dataThing["text"];
  var quality = dataThing["quality"];
  const r1 = await queryP(
    con,
    "INSERT INTO `wp_posts` ( `post_author`, `post_date`, `post_date_gmt`, `post_content`, `post_title`,`post_excerpt`, `post_status`,`comment_status`, `ping_status`,`post_password`, `post_name`,`to_ping`,`pinged`, `post_modified`, `post_modified_gmt`,`post_content_filtered`,`post_parent`, `guid`,`menu_order`, `post_type`, `post_mime_type`,`comment_count`) VALUES (1, '" +
      fhoy +
      "', '" +
      fhoy +
      "', '', '" +
      make +
      "','', 'publish', 'closed', 'closed','','" +
      make +
      "','','', '" +
      fhoy +
      "', '" +
      fhoy +
      "','', '" +
      result[0].ID +
      "','','0', 'dt_links','' ,0);",
  );
  console.log("1 registro link insertado");
  const r2 = await queryP(
    con,
    "SELECT ID FROM wp_posts WHERE post_type='dt_links' ORDER BY ID DESC LIMIT 0,1",
  );
  const result2 = r2.result;
  const r3 = await queryP(
    con,
    "INSERT INTO wp_postmeta (post_id, meta_key, meta_value) VALUES (" +
      result2[0].ID +
      ",'_dool_url', '" +
      source +
      "'),(" +
      result2[0].ID +
      ",'_dool_type','" +
      text +
      "'),(" +
      result2[0].ID +
      ",'_dool_quality','" +
      quality +
      "');",
  );
}

// ...

async function doThings(con, getData) {
  const { result } = await queryP(con, "SELECT ID FROM wp_posts ORDER BY ID DESC 
LIMIT 0,1");
  const dataThingPromises = getData.map(dataThing => processDataThing(con, result, dataThing));
  return await Promise.all(dataThingPromises);
}

// ...

doThings(con, getData).then(() => console.log("All done!"));