Error when trying to store specific json keys in mysql

Question

I'm trying to store specific mysql keys for an api stored in the variable filtrado.

console.log(filtrado);

[
  {
    "id": 5,
    "name": "Chelsey Dietrich",
    "username": "Kamren",
    "email": "Lucio_Hettinger@annie.ca",
    "address": {
      "street": "Skiles Walks",
      "suite": "Suite 351",
      "city": "Roscoeview",
      "zipcode": "33263",
      "geo": {
        "lat": "-31.8129",
        "lng": "62.5342"
      }
    },
    "phone": "(254)954-1289",
    "website": "demarco.info",
    "company": {
      "name": "Keebler LLC",
      "catchPhrase": "User-centric fault-tolerant solution",
      "bs": "revolutionize end-to-end systems"
    }
  },
  {
    "id": 10,
    "name": "Clementina DuBuque",
    "username": "Moriah.Stanton",
    "email": "Rey.Padberg@karina.biz",
    "address": {
      "street": "Kattie Turnpike",
      "suite": "Suite 198",
      "city": "Lebsackbury",
      "zipcode": "31428-2261",
      "geo": {
        "lat": "-38.2386",
        "lng": "57.2232"
      }
    },
    "phone": "024-648-3804",
    "website": "ambrose.net",
    "company": {
      "name": "Hoeger LLC",
      "catchPhrase": "Centralized empowering task-force",
      "bs": "target end-to-end models"
    }
  },
  {
    "id": 3,
    "name": "Clementine Bauch",
    "username": "Samantha",
    "email": "Nathan@yesenia.net",
    "address": {
      "street": "Douglas Extension",
      "suite": "Suite 847",
      "city": "McKenziehaven",
      "zipcode": "59590-4157",
      "geo": {
        "lat": "-68.6102",
        "lng": "-47.0653"
      }
    },
    "phone": "1-463-123-4447",
    "website": "ramiro.info",
    "company": {
      "name": "Romaguera-Jacobson",
      "catchPhrase": "Face to face bifurcated interface",
      "bs": "e-enable strategic applications"
    }
  }
]

I just need to store name, email, username My index.js

var mysql = require('mysql');

var con = mysql.createConnection({
  host: "localhost",
  user: "yourusername",
  password: "yourpassword",
  database: "mydb"
});

  con.query("INSERT INTO users (`name`, `email`, `username`) FILTRADO ('?', '?', '?');", 
  filtrado, function (error, results, fields) {
    if (error) throw error;
    res.end(JSON.stringify(results));
  });

Error:

sqlMessage: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FILTRADO (''[\\n {\\n \\"id\\": 5,\\n \\"name\\": \\"Chelsey Dietrich\\",\\n ...' at line 1,

score 1 · Accepted Answer · answered Dec 15 '20 at 15:13

You are not inserting the data correctly, your sql insert syntax is wrong, and you need to create a nested array of arrays for your query, example of insert array for multi insert. learn more here

Create your insert data array as following

[
    [ 'Chelsey Dietrich', 'Lucio_Hettinger@annie.ca', 'Kamren' ],
    [ 'Clementina DuBuque', 'Rey.Padberg@karina.biz', 'Moriah.Stanton' ],
    [ 'Clementine Bauch', 'Nathan@yesenia.net', 'Samantha' ]
]

Here is the solution to your problem, try this.

let arr = filtrado

let sql = "INSERT INTO users (`name`, `email`, `username`) VALUES ?";

let insData = filtrado.map(item=>{  return [item.name, item.email, item.username]  });

con.query(sql, insData, function (error, results, fields) {
    if (error) throw error;
    res.end(JSON.stringify(results));
});

Error when trying to store specific json keys in mysql

1 Answers1