MySQL: function to determine if an ID exists

Question

MySQL: function to determine if an ID exists

I need to make a function in mysql that returns me true if an id exists, I must send it 3 parameters, Table Name, field name and the value, if there is in said table a record that is identified with the value received in the table's specified field, then the function must return TRUE, otherwise FALSE.

Try as follows, but it doesn't recognize the table name Error Code: 1146. Table ' mydb.p_table' doesn't exist

CREATE DEFINER=`root`@`localhost` FUNCTION `existe_ID`(`p_tabla`VARCHAR(45), `p_campo` VARCHAR(100), `p_id` int UNSIGNED) RETURNS tinyint(1)
BEGIN
    DECLARE existe boolean DEFAULT FALSE;

    IF EXISTS (SELECT `p_campo` FROM `p_tabla` WHERE `p_campo` = `p_id`)THEN
        SET existe = TRUE;
    END IF;

  RETURN existe;
END

1

Author: Geronimo Fernandez, 2016-09-12

Source

2 answers

I have a function, which could serve you. The idea of the function is to pass three table parameters, the field to look for and how to sort the results.

function DevolverUltimoValor($tabla,$campo_buscar,$orden){
    $SelectSQL="SELECT " . $campo_buscar . " FROM " . $tabla . " ORDER BY " . $orden . " DESC Limit 1";
    $conn = dbConnect();
    $result = $conn->query($SelectSQL);
    $rows = $result->fetchAll();
    if ($result->rowCount() > 0) {
        foreach ($rows as $row) {
            $valor=$row[$campo_buscar]; //valor de la palabra
        }
    } else {
        $valor=0;
    }
    $conn = null;
    return $valor;
}

0

Author: Jose Javier Segura, 2016-09-12 07:37:22

score 1 · Accepted Answer

What You are trying to do is not possible because the syntax used by MySQL (and other DBMSs) for defining DML statements does not allow executing the same by dynamically renaming the table.

This means that the name of a table in a DML statement cannot be a variable or argument.

For example, in the following query:

SELECT `p_campo` FROM `p_tabla` WHERE `p_campo` = `p_id`;

What is thought to happen is that MySQL is going to replace the parameter p_table so contain the same. But that doesn't happen, instead, he literally takes p_tabla as if it were the name of an existing table in the database, but evidently that table doesn't exist.

However I give you two options to achieve what you want to do:

Dynamically create the statement you need from a server language (PHP, Java, NodeJS, etc) and execute it with its respective MySQL driver.
Use PREPARED STATEMENTS in MySQL:

Is basically a MySQL function that allows you to dynamically create statements and execute them in the same environment, just what you're supposed to need. But I warn you that it has its price and were not created for it so I leave it to your consideration.

Usage example:

DELIMITER !
CREATE PROCEDURE listar(
    IN tabla VARCHAR(50), 
    IN campo VARCHAR(50), 
    IN valor INT
)
BEGIN
    SET @query = CONCAT('SELECT * FROM ', tabla, ' WHERE ', campo, ' = ', valor);
    PREPARE statement FROM @query;       -- Preparar query.
    EXECUTE statement;                   -- Ejecutar query.
    DEALLOCATE PREPARE statement;        -- Eliminar query alojado en memoria.
END!

Try the MySQL interpreter and look at the result. You should investigate if it can be integrated with functions because I really don't know.

Here you can find more information about the prepared statements.

I hope this has been helpful for you.