Mysql – how to define a SQL functions with PHP?

With mySQL/MariaDB you can define your own function for the SQL server (mySQL-manual).
If you create such a function it is available in later queries (until it is dropped / server reset? / only for that user? / …)

As I want to use such a function in my PHP code I want to make sure the function is available. So I want to define the function from PHP. In this way the function is on every server my PHP code is executed even if I have no access to the DB-server with a CLI.

While I can define the function in a SQL-console (mysql), all attempts from PHP fail.

As I build an extension for TYPO3 I first tried the queryBuilder, which failed with syntax error. (probably the doctrine layer was not prepared for the very special syntax used for creating functions).

Then I used mysqli and all statements returned no error. but the functions were not created. 🙁

$levenshteinFunction = <<<'LEVENSHTEIN_FUNCTION'
CREATE FUNCTION LEVENSHTEIN(s1 VARCHAR(255) CHARACTER SET utf8, s2 VARCHAR(255) CHARACTER SET utf8)
  RETURNS INT
  DETERMINISTIC
  BEGIN
    DECLARE s1_len, s2_len, i, j, c, c_temp, cost INT;
    DECLARE s1_char CHAR CHARACTER SET utf8;
    -- max strlen=255 for this function
    DECLARE cv0, cv1 VARBINARY(256);

    SET s1_len = CHAR_LENGTH(s1),
        s2_len = CHAR_LENGTH(s2),
        cv1 = 0x00,
        j = 1,
        i = 1,
        c = 0;

    IF (s1 = s2) THEN
      RETURN (0);
    ELSEIF (s1_len = 0) THEN
      RETURN (s2_len);
    ELSEIF (s2_len = 0) THEN
      RETURN (s1_len);
    END IF;

    WHILE (j <= s2_len) DO
        SET cv1 = CONCAT(cv1, CHAR(j)),
          j = j + 1;
    END WHILE;

    WHILE (i <= s1_len) DO
        SET s1_char = SUBSTRING(s1, i, 1),
          c = i,
          cv0 = CHAR(i),
          j = 1;

      WHILE (j <= s2_len) DO
          SET c = c + 1,
            cost = IF(s1_char = SUBSTRING(s2, j, 1), 0, 1);

        SET c_temp = ORD(SUBSTRING(cv1, j, 1)) + cost;
        IF (c > c_temp) THEN
          SET c = c_temp;
        END IF;

        SET c_temp = ORD(SUBSTRING(cv1, j+1, 1)) + 1;
        IF (c > c_temp) THEN
          SET c = c_temp;
        END IF;

        SET cv0 = CONCAT(cv0, CHAR(c)),
            j = j + 1;
      END WHILE;

      SET cv1 = cv0,
          i = i + 1;
    END WHILE;

    RETURN (c);
  END $$
LEVENSHTEIN_FUNCTION;
                
$levenshteinratioFunction = <<<'LEVENSHTEINRATIO_FUNCTION'
CREATE FUNCTION LEVENSHTEINRATIO( s1 VARCHAR(255), s2 VARCHAR(255) )
  RETURNS INT
  DETERMINISTIC
  BEGIN
    DECLARE s1_len, s2_len, max_len INT;
    SET s1_len = LENGTH(s1), s2_len = LENGTH(s2);
    IF s1_len > s2_len THEN
      SET max_len = s1_len;
    ELSE
      SET max_len = s2_len;
    END IF;
    RETURN ROUND((1 - LEVENSHTEIN(s1, s2) / max_len) * 100);
  END $$
LEVENSHTEINRATIO_FUNCTION;

$LS = [
    'show function status;',
    'drop function IF EXISTS levenshtein ;',
    'drop function IF EXISTS levenshteinratio ;',
    'DELIMITER $$',
    $levenshteinFunction,
    'DELIMITER ;',
    'DELIMITER $$',
    $levenshteinratioFunction,
    'DELIMITER ;',
    'show function status;'
];

/*
// first attempt
$output ='';
foreach ($LS as  $lsStatement) {
    $output .= '*** '.$lsStatement.' ***'.PHP_EOL;
    $levenshteinQuery = $this->createQuery();
    $levenshteinQuery->statement($lsStatement,[]);
    $result = $levenshteinQuery->execute();
    $output .= print_r($result->toArray(), false). PHP_EOL.PHP_EOL;
    //TYPO3CMSCoreUtilityDebugUtility::debug($result,'result of '.substr($lsStatement,0,30));
    unset($levenshteinQuery);
}
echo $output;
die();
*/

$mysqliConnection = new mysqli(
    $GLOBALS['TYPO3_CONF_VARS']['DB']['Connections']['Default']['host'],
    $GLOBALS['TYPO3_CONF_VARS']['DB']['Connections']['Default']['user'],
    $GLOBALS['TYPO3_CONF_VARS']['DB']['Connections']['Default']['password'],
    $GLOBALS['TYPO3_CONF_VARS']['DB']['Connections']['Default']['dbname']
);

$output = '';
foreach ($LS as  $lsStatement) {
    $output .= '*** '.$lsStatement.' ***'.PHP_EOL;
    $result = $mysqliConnection->query($lsStatement);
    $output .= print_r($result, true). PHP_EOL.PHP_EOL;
}
$mysqliConnection->close();
echo '<pre>'.PHP_EOL.$output.'</pre>';
die();

This is a debug-output with termination (die) as the later query failed anyway with the error undefined function levenshtein.

as said: the later query works if I define the functions in a SQL CLI and without recreation of the function.