My NodeJS route not inserting proper values into table using SQL-CodePudding

I'm new to using node with MySQL but when I tried sending a query to my SQL server to insert the data it wasn't inserting the values from the form submitted. My route code looks like this,

const { application } = require('express')
const express = require('express')
const router = express.Router() 
const pool = require('../config/db');

router.post("/create", async (req, res) => {

  
  // Get user input
  const  name = req.body.name
  const  country = req.body.country
  const  dob = req.body.dob

    const sql = `INSERT INTO users (name, country, dob) VALUES ('" req.body.name "', '" req.body.country "','" req.body.dob "');`
  pool.query(sql, function(error, results){
    if (error) {
      throw error
    }
    res.status(201).send(`User added with ID: ${results.id}`)
    console.log(name);
  })


   
});

my query previously was like this,

const sql = `INSERT INTO users (name, country, dob) VALUES (name, country, dob);`

But both are not working as far as I'm trying to insert them into the table. Some important code from my React Form which might help,

constructor(props) {
super(props);
this.options = countryList().getData();

this.state = {
   name: '',
   options: this.options,
   val: null,
   country: '',
   dob: new Date(),
}

this.handleStartDateChange = this.handleStartDateChange.bind(this);
  }



 handleNameChange = (e) => {
    this.setState({
      name: e.target.value
    })
  }
  
  handleOptionsChange = (val) => {
    this.setState({
      val
    })
    this.setState({
      country:val.label
    })
  }

  
  handleStartDateChange(date) {
    this.setState({
      dob: date
    })
  }

My handle submit is like this,

 const handleSubmit = (e) =>{
    e.preventDefault()
    axios.post('http://localhost:4000/app/create', this.state)
    console.log(this.state);

console.log(this.state) gives this in the console after submitting,

{name: 'Prajwal V', options: Array(249), val: {…}, country: 'Afghanistan', dob: Wed Nov 09 2022 18:02:00 GMT 0530 (India Standard Time)}
country
: 
"Afghanistan"
dob
: 
Wed Nov 09 2022 18:02:00 GMT 0530 (India Standard Time) {}
name
: 
"Prajwal V"
options
: 
(249) [{…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, …]
val
: 
{value: 'AF', label: 'Afghanistan'}
[[Prototype]]
: 
Object

The table contents in PHPMyAdmin,

I hope I've clearly explained my questions. Thank you so much on reviewing this.

CodePudding user response：

I would suggest changing the query in your API. Also, you need to go back and do a SELECT to get the id of the row you are wanting to send back to your front end.

const { application } = require("express");
const express = require("express");
const router = express.Router();
const pool = require("../config/db");

router.post("/create", async (req, res) => {
  // Get user input
  const { name, country, dob } = req.body;

  pool.query(
    "INSERT INTO users SET name = ?, country = ?, dob = ?",
    [name, country, dob],
    (err, results) => {
      if (err) {
        throw err;
      } else {
        pool.query(
          "SELECT id FROM users WHERE name = ?, country = ?, cob = ?",
          [name, country, dob],
          (err, results) => {
            if (err) {
              throw err;
            } 
            if (results) {
              res.status(200).send(`User added with ID: ${results.id}`);
              console.log(name);
            }
          }
        );
      }
    }
  );
});

Also, make sure your datatypes are correct in your database otherwise MySQL will reject the values you provide.