0

I want to insert current values, which is BlockData, BlockHash, and dataid from client side, and only BlockLastHash I want to select its value from the last row in the same table. However, BlockLastHash saves the command itself not the value.

The code:

    connection.query("INSERT INTO `pow` (`BlockData`, `BlockHash`, `BlockLastHash`, `dataid`) VALUES ('"+blockdata+"', '"+hash+"', '( SELECT `BlockHash` FROM `pow` ORDER BY id DESC LIMIT 1 )' , '"+id+"') ", (err, res) => {
        if(err) throw err;
        console.log("1 Block inserted");  
    });

The output in BlockLastHash is the same command which is

( SELECT `BlockHash` FROM `pow` ORDER BY id DESC LIMIT 1 )

How to solve this please? Thanks in advance

ismsm
  • 143
  • 2
  • 11
  • **WARNING**: Use *placeholder values* to store data, do **NOT** use string interpolation or concatenation. This is usually really easy even with the low-level [MySQL driver](https://www.npmjs.com/package/mysql#preparing-queries). – tadman Apr 07 '21 at 00:36
  • ` var sql = "INSERT INTO `pow` (`BlockData`, `BlockHash`, `BlockLastHash`, `dataid`) VALUES (?, ?, ?, ?)";` `var inserts = [blockdata, hash, 'SELECT `BlockHash` FROM `pow` ORDER BY id DESC LIMIT 1',id];` `sql = mysql.format(sql, inserts);` How to put the select statement in the values in this form, please? – ismsm Apr 07 '21 at 00:46
  • It's literally right there in the documentation I linked. For a subselect, just put that in the main query and placeholder *only* the data values. `SELECT ..., 'SELECT ...'` will select the *text*. You'd be better off with a `JOIN`. – tadman Apr 07 '21 at 00:48
  • 1
    Do not enclose the select into single quotes - that transforms the select into a string literal. However, in this particular case, it may be better if you executed the select first with a locking read and then perform the insert using that value, as the insert and the select both use the same table. – Shadow Apr 07 '21 at 00:51

0 Answers0