Home > Software engineering >  UPDATE and SET trouble in nodejs (MYSQL)
UPDATE and SET trouble in nodejs (MYSQL)

Time:08-04

I am trying to update the status on my database to value of 1 with node.js

Here is my code

app.post('/voted', (req, res) => {
    const email = req.body.email
    const num = 1;

    db.query("UPDATE voter SET status = ? WHERE email = ?",
    [num, email],
    (err, result) => {
        if(err) {
            console.log(err)
        } else {
            res.send( {voted: true } )
            console.log(result)
        }
    })
})

The console.log that I get:

OkPacket {
  fieldCount: 0,
  affectedRows: 0,
  insertId: 0,
  serverStatus: 2,
  warningCount: 0,
  message: '(Rows matched: 0  Changed: 0  Warnings: 0',
  protocol41: true,
  changedRows: 0
}

Here is my SQL Structure enter image description here

The data im trying to modify enter image description here

idk where did I go wrong, is my SQL structure incorrect?

--- UPDATE --- Ive found the issue, it seems like I had to double click the Button before the query registers correctly

// First Click
OkPacket {
  fieldCount: 0,
  affectedRows: 0,
  insertId: 0,
  serverStatus: 2,
  warningCount: 0,
  message: '(Rows matched: 0  Changed: 0  Warnings: 0',
  protocol41: true,
  changedRows: 0
} // 2nd Click
OkPacket {
  fieldCount: 0,
  affectedRows: 1,
  insertId: 0,
  serverStatus: 2,
  warningCount: 0,
  message: '(Rows matched: 1  Changed: 1  Warnings: 0',
  protocol41: true,
  changedRows: 1
}

I think the problem is in my handleStatus function

    const [userEmail, setEmail] = useState('')
    const handleStatus = () => {
        
        Axios.get("http://localhost:3001/login").then((response) => {
            if (response.data.loggedIn === true) {
                setEmail(response.data.user[0].email)
            }
        })

        Axios.post('http://localhost:3001/voted', 
        {email: userEmail}).then((response) => {
            console.log(response)
        })
    }
    ```

CodePudding user response:

Can you once try using this code?

const syncSql = require('sync-sql');
const email = req.body.email
const num = 1;

const con = {
    host: process.env.DB_HOST,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_NAME
};

const query = `UPDATE voter SET status = ? WHERE email = ?`
const data = { status: num, email }

const response = syncSql.mysql(con, createGenreQuery, data)

if you are using local db then

  1. DB_HOST = "localhost"
  2. DB_USER = "root"
  3. DB_PASSWORD = ""

CodePudding user response:

UPDATE, thank you guys for answering I've found the issue

I did this

const [userEmail, setEmail] = useState('')

    useEffect(() => {
        Axios.get("http://localhost:3001/login").then((response) => {
            if (response.data.loggedIn === true) {
                setEmail(response.data.user[0].email)
            }
        })
    }, [])

    const handleStatus = () => {
        
        Axios.post('http://localhost:3001/voted', 
        {email: userEmail}).then((response) => {
            console.log(response)
        })
    }

Instead of this

  const [userEmail, setEmail] = useState('')
    const handleStatus = () => {
        
        Axios.get("http://localhost:3001/login").then((response) => {
            if (response.data.loggedIn === true) {
                setEmail(response.data.user[0].email)
            }
        })

        Axios.post('http://localhost:3001/voted', 
        {email: userEmail}).then((response) => {
            console.log(response)
        })
    }

  • Related