Trying to make a preparred statement with mySQL in NodeJS. What part of this code isn't working-CodePudding

So I am trying to insert data into my database, the connection is fine, But for some reason, when I try to create the prepared statements it's not working.

All the values in my database are varchar(255) except for description which is text. The data being sent is all strings. But could that be the issue? how do I make this execute without any errors?

const app = express();
const http = require('http').Server(app);
const io = require('socket.io')(http);
const path = require('path');
const crypto = require('crypto');
const mysql = require('mysql');

const db = mysql.createConnection({
    host:   'localhost',
    user:   'root',
    password:   '',
    database:   'dos-bros-it',
});

db.connect((err) => {
    if(err) {
        console.log(err.code);
        console.log(err.fatal);
    }else{
        console.log("Connection has been succesfully initiated!")
    }
})
const PORT = 7072;

app.use(express.static(path.join(__dirname, "client/build/")));
app.use(express.urlencoded({extended: true}));
app.use(express.json());

app.get('/', (req, res) => {
    res.sendFile(path.join(__dirname, "client/public/", "index.html"));
});

app.post('/repair', (req, res, next) => {
    $query = "INSERT INTO tickets (firstName, lastName, email, phone, description) VALUES (?, ?, ?, ?, ?)";
        $data = [
        [req.body.firstName], 
        [req.body.lastName],
        [req.body.email], 
        [req.body.phone], 
        [req.body.request]
    ]
    db.query($query, 
    [$data], (err, rows, fields) => {
        if (!err) { 
            console.log('Repair was succesfully sent to the servers database! \n Records: '   rows);
        }else{
            console.log(err);
        }
    });
    console.log(req.body.firstName, req.body.lastName, req.body.email, req.body.phone, req.body.request);
    res.send("<h1>FORM SENT</h1>")
    next();
})
io.on("connection", (socket) => {
    console.log('Client has connected to the server!!!');
    socket.on('test', (msg)=>{
        console.log('recieved test message!!!', msg);
    })
})

http.listen(PORT, ()=>{
    console.log('Server Started using port:', PORT);
})

below I have provided the error that code.

  code: 'ER_PARSE_ERROR',
  errno: 1064,
  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 '?, ?, ?, ?, ?' at line 1",
  sqlState: '42000',
  index: 0,
  sql: "INSERT INTO tickets (firstName, lastName, email, phone, description) VALUES ('bobby'), ('mcboba'), ('[email protected]'), ('1234567890'), ('haww ahagor naou rngoanr ogaeo gw'), ?, ?, ?, ?, ?;"
}

CodePudding user response：

Your SQL insert syntax is off. You specify 5 columns and so there should be only 5 ? placeholders. Also, what follows VALUES needs to be a tuple in parentheses (...). Use this version:

$query = "INSERT INTO tickets (firstName, lastName, email, phone, description) VALUES (?, ?, ?, ?, ?)";

CodePudding user response：

I was able to figure it out, apparently it was putting all of the $data objects into just one "?", so I removed all but on "?" and it seemed to work. Hope that helps someone out in the future.

    $query = "INSERT INTO tickets (firstName, lastName, email, phone, description) VALUES (?)";
        $data = [
        [req.body.firstName], 
        [req.body.lastName],
        [req.body.email], 
        [req.body.phone], 
        [req.body.request]
    ]
    db.query($query, 
    [$data], (err, rows, fields) => {
        if (!err) { 
            console.log('Repair was succesfully sent to the servers database! \n Records: '   rows);
        }else{
            console.log(err);
        }
    });