• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1029
  • Last Modified:

How to create a mySql trigger using php code! ?

I'm trying to create a trigger using phpcode!
As I test with SQLyog (winform application) the trigger is created!
But when I use phpmyadmin to execute that trigger code It's say some error!

And I try to use another code to connect to mysql database and execute the trigger code, the result is the same as using phpmyadmin!

So which php code can be used to create a trigger?
Thanks for help!

This is my trigger!
==================================
DELIMITER $$;

CREATE TRIGGER `trInsertUserToARO` AFTER INSERT ON `jos_users` FOR EACH ROW BEGIN
INSERT INTO `jos_adv_core_acl_aro` (`id`, `section_value`, `value`, `order_value`, `name`, `hidden`)
VALUES ((Select `id`+1 From `jos_adv_core_acl_aro_seq`), 'users', NEW.id, 1, NEW.name, 0);
UPDATE `jos_adv_core_acl_aro_seq`
SET id = id + 1;
END$$


DELIMITER ;$$
==================================
0
tunhien
Asked:
tunhien
  • 4
  • 3
1 Solution
 
Muhammad WasifCommented:
What the error says? and what is your php code?
0
 
tunhienAuthor Commented:
they said:
============================
CREATE TRIGGER trInsertUserToARO AFTER INSERT ON jos_users
FOR EACH
ROW BEGIN
UPDATE jos_users SET id = id +1;



MySQL said:  

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE jos_users SET id = id + 1' at line 3
===========================

But with that code I use SQLyog to execute so It's work!
0
 
Muhammad WasifCommented:
Can you post your PHP code?
0
2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

 
tunhienAuthor Commented:
my phpcode is
================================
$query = '
DELIMITER $$;

CREATE TRIGGER `trInsertUserToARO` AFTER INSERT ON `jos_users` FOR EACH ROW BEGIN
INSERT INTO `jos_adv_core_acl_aro` (`id`, `section_value`, `value`, `order_value`, `name`, `hidden`)
VALUES ((Select `id`+1 From `jos_adv_core_acl_aro_seq`), 'users', NEW.id, 1, NEW.name, 0);
UPDATE `jos_adv_core_acl_aro_seq`
SET id = id + 1;
END$$


DELIMITER ;$$
';

mysql_query($query, $link) or die (mysql_errno($link) . ": " . mysql_error($link) . "\n";);
================================
0
 
Muhammad WasifCommented:
Dont you need a space between $$ and ;? like below
DELIMITER $$ ;
and
DELIMITER ; $$

and you should use mysqli_multi_query instead of mysql_query
http://www.php.net/mysqli_multi_query
0
 
tunhienAuthor Commented:
I don't really know!
Because this is the template of the SQLyog about create trigger!

Have you got anny other template to create a trigger with mySQL? Then I'll try to do with the new code! May be it's work! :D
0
 
tunhienAuthor Commented:
Thanks! My friend has just solved it for me!

Just reduce the trigger syntax!
Thanks again!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now