Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Can i make a flexible function that can get * from different tables?

I am trying to create a function that can select from different tables in mysql

My Function

async function getOne(table, table_parameter, parameter) {
  const result = await pool.query(
    `SELECT * FROM ? WHERE ? = ?`,
    [table, table_parameter, parameter]
  )
}

Router:

companiesRouter.get('/:id', async (request, response) => {
  const id = request.params.id
  const company = await getOne('Company', 'companyID', id)
  response.send(company)
})

This ends up getting an error:

sql: "SELECT * FROM 'Company' WHERE 'companyID' = '1'",
  sqlState: '42000',
  sqlMessage: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right

syntax to use near ”Company’ WHERE ‘companyID’ = ‘1” at line 1"
}

Is there any way for this to work?

Tags:

2

Answers


  1. 0

    Update dbquery formatting in function getOne as follows:

    async function getOne(table, table_parameter, parameter) {
    const actual_query = mysql.format(
                             'SELECT * FROM ? WHERE ? = ?', 
                              [table, table_parameter, parameter]
                        ).replace(/'/g, '');

    console.log(actual_query);
    await pool.query({
            // sql: `SELECT * FROM ${table} WHERE ${table_parameter} = ${parameter}`,
            sql: actual_query,
            timeout: 30000
        },
        (error, results, fields) => {
            if (error) throw error;
            console.log('The solution is: ', results);
            return results;
        }
    );
 }

    It will not show error and show result properly.

    enter image description here

    Login or Signup to reply.
  2. 0

    Use the double question-mark syntax:

    async function getOne(table, table_parameter, parameter) {
  const result = await pool.query(
    `SELECT * FROM ?? WHERE ?? = ?`,
    [table, table_parameter, parameter]
  );
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search