I have this code
DELIMITER $$
DROP FUNCTION IF EXISTS `GetNextID` $$
CREATE FUNCTION `GetNextID`() RETURNS INT DETERMINISTIC
BEGIN
DECLARE NextID INT;
SELECT MAX(articleID) + 5 INTO NextID FROM table_article;
RETURN NextID;
END$$
DELIMITER ;
INSERT INTO table_article ( articleID, articleAlias ) VALUES ( GetNextID(), 'TEST' );
executed OK in phpMyAdmin, but it fails when i pass this query to mysql_query PHP function/ Me guess this is because of the function and semi-colons. What do i do?
DELIMITER is not a MySQL keyword: it is a reserved word parsed by clients (like mysql, phpMyAdmin etc.) which allows splitting the queries.
You should split it manually and submit the three queries:
DROP FUNCTION IF EXISTS `GetNextID`
,
CREATE FUNCTION `GetNextID`() RETURNS INT DETERMINISTIC
BEGIN
DECLARE NextID INT;
SELECT MAX(articleID) + 5 INTO NextID FROM table_article;
RETURN NextID;
END
and
INSERT INTO table_article ( articleID, articleAlias ) VALUES ( GetNextID(), 'TEST' )
in three separate calls to the database.
you have DECLARE NextID INT; and RETURN NextID; and another line with ; inside the DELIMITER $$ deceleration.
my advice is stop using $$ as a delimiter
