CODEWITHSUNDEEP
phpmysql
user5332
user5332

Reputation: 774

how to execute mysql command DELIMITER

I have huge problem (for me)

I need to execute the MySQL command DELIMITER | from PHP, but mysql_query() fails with an error. I found that mysql_query() doesn't support the use of DELIMITER, because this command only works in MySQL console

But when I open phpMyAdmin there is an option to change DELIMITER on the SQL tab and it works, but I don't know how.

Is it possible to change delimiter from PHP? I need it to do before calling a CREATE TRIGGER command that uses several ;.

Upvotes: 11

Views: 10625

Answers (3)

Sinri Edogawa
Sinri Edogawa

Reputation: 331

It depends. I use MySQLi's multi_query() function and leave ; as is when doing a procedure creating, actually done (PHP 5.6 & MySQL 5.6). I think you do not need care delimiter, leave the target code only then might work. You might try to query SQL only as

CREATE XXXX
BEGIN
    sth;
END

Just have a try.

Upvotes: 1

derobert
derobert

Reputation: 51157

You probably don't need to change the delimiter.

The delimiter is needed in the CLI to tell where the SQL statement ends, because the CLI is going to keep reading and executing more statements until you tell it to stop (e.g., with exit or Control-D). But what it actually reads is just a stream of characters; it somehow needs to figure out where one statement ends and the next starts. That's what the delimiter does.

In PHP, each function call executes one statement. There can't be multiple statements in one function call, so there is no need for a way to delimit them. The statement is the entire string. This is true of the old mysql_query as well as the newer mysqli_query and PDO. Of course, there is mysqli_multi_query if you really want to pass multiple queries to one function.

In the case of a stored procedure/trigger/function/etc., there can be multiple statements, but that's handled by MySQL itself (and is always ;, AFAIK). So as far as PHP is concerned, that's still one statement.

The delimiter setting you're seeing in phpMyAdmin is probably being used to split statements apart, and is probably being done in PHP code. It has to do this because it is accepting user input consisting of multiple statements, but must pass only one statement per function call. (I haven't checked the phpMyAdmin code to be completely sure of this).

Upvotes: 17

ajreal
ajreal

Reputation: 47321

you need a stored routine (procedure, function) to include all the necessary queries,
so in PHP, you can call the stored routine elegantly

Upvotes: -1

Related Questions