I have a MySQL database. I want to reduce certain data using Cronjob at certain times of the week.
(Table name: Accounts)
username | hunger | hp
| |
Dworczyk | 100 | 100
Mateusz | 100 | 95
Let's say a random amount will be reduced from the hunger data of everyone in the table above. In other words, the hunger data of all the people above will be reduced.
const CronJob = require('cron').CronJob;
const { con, pool } = require("./container/mysql.js")
let random_number = Math.floor(Math.random() * 100) + 1;
let job = new CronJob('0 0 * * *', function() {
pool.query(`SELECT * FROM Accounts WHERE username = 'Dworczyk'`, async (err, rows) => {
if (err) throw err;
pool.query(`UPDATE Accounts SET hunger = ${rows[0].hunger} - ${random_number}`);
});
}, null, true, 'America/Los_Angeles');
job.start();
What I did above is simple subtraction from a single person. It shouldn't just be tied to "Dworczyk". How can I make transactions on everyone without using the WHERE key?
Subtract from the column itself rather than a variable.
Also, use parametrized queries rather than substituting variables into the SQL.
pool.query('UPDATE Accounts SET hunger = hunger - ?', [random_number], function(err, response) {
if (err) throw err;
});