I trying to update some info in a MYSQL DB, but Im not sure of how to do it with in node.js. This is the mysql driver I am useing https://github.com/felixge/node-mysql
What I have
connection.query('SELECT * FROM users WHERE UserID = ?', [userId], function(err, results) {
if (results[0]) {
if (results[0].Name!=name) {
console.log(results[0].Name);
connection.query('UPDATE users SET ? WHERE UserID = ?', [userId], {Name: name});
}
console.log(results[0].UserID);
}
});
Everything works except...
connection.query('UPDATE users SET ? WHERE UserID = ?', [userId], {Name: name});
In PHP I would have this...
mysql_query("UPDATE users SET Name='".$_GET["name"]."' WHERE UserID='".$row['UserID']."'");
Im not sure what I'm doing wrong, But Im positive that the issue is here
connection.query('UPDATE users SET ? WHERE UserID = ?', [userId], {Name: name});
Any help you can give me would be greatly appreciated.
From the code for Connection.prototype.query() and Connection.createQuery(), it's clear that you can only pass in a single values object. I don't see where the code for the special SET ? behavior is defined — it's clearly not in SqlString.formatQuery() — but if it uses SqlString.objectToValues(), then I guess there's no way to use it together with another ?.
I think the best approach is to just dispense with the neat SET ? feature, and write either of these:
connection.query('UPDATE users SET Name = ? WHERE UserID = ?', [name, userId])
connection.query('UPDATE users SET Name = :Name WHERE UserID = :UserID',
{UserID: userId, Name: name})
but if you really want to use SET ?, I suppose you could write this:
connection.query('UPDATE users SET ? WHERE UserID = :UserID',
{UserID: userId, Name: name})
which would update both UserID and Name; unless you have a trigger, this should be O.K., in that it's updating UserID to the value it already had anyway. But it's kind of disconcerting, and I don't recommend it.
The answer by ruakh isn't entirely correct, you can use the SET ? feature with another ?.
The syntax is:
connection.query('UPDATE users SET ? WHERE UserID = ?', [{ Name: name }, userId])
Or you can use an array of objects:
connection.query('UPDATE user SET ? WHERE ?', [{ Name: name }, { UserId: userId }])