How to trigger mysql query in express-CodePudding

I created a Nodejs Express app with a button on client side. I would like to run a sql query on server side when the button is clicked. Is there a way to do this ?

index.ejs

<button onclick='add()'> 1</button>

server.js

let express = require('express')
let mysql = require('mysql')

let app = express()
app.set('view engine', 'ejs')

app.use(express.static('public'))

app.get('/', (request, response) => {

    // Connection to database
    var con = mysql.createConnection({
        host: "******",
        user: "******",
        password: "******",
        database: "******"
    })
    response.render('pages/index.ejs', dict)
})

CodePudding user response：

let mysql = require('mysql');
let config = require('./config.js');

let connection = mysql.createConnection(config);

let sql = `SELECT * FROM todos`;
connection.query(sql, (error, results, fields) => {
  if (error) {
    return console.error(error.message);
  }
  console.log(results);
});

connection.end();

CodePudding user response：

I am not sure what your goal is but your js code won't simply run SQL query unless you send an HTTP request to the API. Then API will process your request and send a query to the database.

server.js

let express = require('express')
let mysql = require('mysql')

let app = express()
app.use(expres.json()) // To parse data into json

mysql.createConnection({
  host: "******",
  user: "******",
  password: "******",
  database: "******"
});

app.post('/increment', (req, res) => {
  const query = 'UPDATE tableName SET columnName = columnName   1 WHERE ID = "AnyNumberYouWant"';
  mysql.query(query, (error, results) => {
    if (error) {
      console.log(error);
    } else {
      console.log(results);
    }
  });
});

/*
If you want to update database, you should send POST http request.
In case you want to fetch some data from database, you should send GET http request
*/

Now, you need to send http request to your API.

index.ejs:

<button onclick='add()'> 1</button>

/* before </body> tag */
<script>
  function add() {
    const url = 'http://localhost:3000/increment';
    const response = fetch(url, {
      method: 'post',
      headers: { 'Content-Type': 'application/json' },
      body: JSON.stringify(data)
    });
    return response.json()
  }
</script>

/* Every time you press the button it sends an HTTP request to the API which will trigger a query to your database.
*/