I trying to update some info in MYSQL DB, but I'm not sure of how to do it with in node.js. This is the mysql driver I'm using https://github.com/felixge/node-mysql
What I have so far
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']."'");
I'm not sure what I'm doing wrong, But I'm positive that the issue is here
connection.query('UPDATE users SET ? WHERE UserID = ?', [userId], {Name: name});
createConnection(function(err, connection) { if (err) { throw err; } connection. query(sql, function(err, results) { if (err) { throw err; } connection. release(); // do something with results }); }); }); This causes the server to crash every time there's an sql error.
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])
[Note (added 2016-04-29): This answer was accepted, but it turns out that there is a reasonable way to use SET ?
. For details, see Bala Clark's answer on this same page. —ruakh]
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With