MySQL: Function to determine if an ID exists

Question

MySQL: Function to determine if an ID exists

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

I need to do a mysql function that returns true if an id exists, I must send 3 parameters, name of the table, field name and value, if there is a record in that table that is identified with the value received in the specified field of the table, then the function must return TRUE, otherwise FALSE.

try it in the following way, but do not recognize the name of the table Error Code: 1146. Table 'mydb.p_table' does not 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

mysql stored-procedure

asked by Geronimo Fernandez 12.09.2016 в 05:21

source

2 answers

0

I have a function, that could serve you. The idea of the function is to pass three parameters Table, the field to look for and the way to order 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;
}

answered by 12.09.2016 в 09:37

Problem when rendering view (react-router 1.0) undefined component Login error with Facebook Api

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 it to be executed dynamically by renaming the table.

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

For example, in the following query:

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

What is believed to happen is that MySQL will replace the p_table parameter so it contains the same. But that does not happen, instead he literally takes p_table as if it were the name of an existing table in the database, but obviously that table does not 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:

Basically it is a MySQL function that allows you to create sentences dynamically and execute them in the same environment, just what you are supposed to need. But I warn you that it has its price and they were not created for it so I leave it to your consideration.

Example of use:

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 it on the MySQL interpreter and see the result. You must investigate if it can be integrated with functions because I really do not know.

Here you can find more information about < em> prepared statements .

I hope this has been useful for you.