2

I need a trigger in phpmyadmin for 2 tables.

So there is table stores and tbl_storefinder_stores.
I wanted to insert 4 things (name,adress,longitude,latitude) from the table stores to tbl_storefinder_stores, I get help in the forum by this code 

INSERT INTO tbl_storefinder_stores(store_name, store_address, lat, lon)
SELECT name, address, latitude, longitude
FROM stores;

and its work !!

But now I want a trigger that do this automatically like: If I change or add anything in table Stores that it update the table tbl_storefinder_stores automatically, like an updater for the 4 things (name/adress/longitude/latitude), How to do this in the phpmyadmin?

vivekpansara
  • 895
  • 1
  • 6
  • 14
M6Gpower
  • 155
  • 1
  • 8
  • PHPMyAdmin is just a web application that allows you to work with a MySQL database, you need to do a google for MySQL triggers. Here's the [official docs](https://dev.mysql.com/doc/refman/5.5/en/trigger-syntax.html). – Kevin Nagurski Jun 09 '15 at 10:45
  • i know , but i need help with the triggercode http://i.stack.imgur.com/lpZD6.png – M6Gpower Jun 09 '15 at 10:49

2 Answers2

2

You will need 2 triggers

  • When you add a new record in stores then replicate it to tbl_storefinder_stores

  • When you update some data in stores then update them to tbl_storefinder_stores as well.

Now the 2 tables must have a common key between them and I am assuming name is the key here. The first trigger will look like

delimiter //
create trigger stores_ins after insert on stores
for each row 
begin
 insert into tbl_storefinder_stores
 (store_name, store_address, lat, lon)
 values
 (new.name,new.address,new.latitude,new.longitude);
end;//

delimiter ;

Now when you update it will look like

delimiter //
create trigger stores_upd after update on stores
for each row
begin
 update tbl_storefinder_stores
 set 
 store_address = new.address,
 lat = new.latitude,
 lon = new.longitude
 where store_name = new.name ;
end;//
delimiter ;

Note that in the 2nd trigger I have considered name as a common key between them so if you change the name in the first table then the trigger might not update data since it may not find row in the 2nd table with the modified name.

So if you still want to do so you may do as

delimiter //
create trigger stores_upd after update on stores
for each row
begin
 update tbl_storefinder_stores
 set 
 name = new.name,
 store_address = new.address,
 lat = new.latitude,
 lon = new.longitude
 where store_name = old.name ;
end;//
delimiter ;
Abhik Chakraborty
  • 44,654
  • 6
  • 52
  • 63
  • thank you for your answer , is there a way to use the ID as a common key the database table looks like this : http://i.stack.imgur.com/lpZD6.png , because maybe if i edit a name in the first table that second one will change too , there a another problem that they have different id's how can i synchronize – M6Gpower Jun 09 '15 at 10:58
  • Right you should be using `ID` for the 2nd trigger and store id from first table to the 2nd table as well. So that the update operation is done with respect to ID instead of name and data integrity remains. – Abhik Chakraborty Jun 09 '15 at 11:00
  • So how the code look like if i want synchronize the ID's and use the ID's as the Common Key – M6Gpower Jun 09 '15 at 11:03
  • I see you already have `id` in `store` table and `store_id` in the 2nd table. So is `store_id` in the 2nd table is `id` from first store table ? if not you may first need to truncate the 2nd table and then write `insert into select from..` and needs to insert `store_id` as `id` from store. – Abhik Chakraborty Jun 09 '15 at 11:06
  • I have a error now in my PHP page if i want Edit : Array ( [0] => Invalid query: Unknown column 'name' in 'where clause' ) UPDATE stores SET `name`='TEST55', `cat_id`='0', `address`='Dortmund', `telephone`='', `email`='', `website`='', `description`='', `latitude`='51.5135872', `longitude`='7.465298100000041', `approved`='1', `modified`='2015-06-09 11:05:48' WHERE id=733 – M6Gpower Jun 09 '15 at 11:06
  • Only the Name / Adress / Longitude/ Latitude are from the first table the IDS are both different i think i need to synchronize the IDS first that both table have the same IDS and then i need a Updater by IDS , but how ..? – M6Gpower Jun 09 '15 at 11:08
  • I think I had a typo it should be `store_name` corrected that – Abhik Chakraborty Jun 09 '15 at 11:09
  • So its a problem, you are not storing the id into the 2nd table and there is no way of identifying integrity in this case, so as I said, take a backup of the table. Then `truncate tbl_storefinder_stores;` then write `insert into select from` as you mentioned in your question, but make sure you also select `id` from `stores` table and insert into `tbl_storefinder_stores` `store_id` field, then you have relation between the tables. – Abhik Chakraborty Jun 09 '15 at 11:12
  • ok i have delet everything and insert now the ID with , Now the have the same ID , now i need a update trigger code with ID as Column key can u write me please again? – M6Gpower Jun 09 '15 at 11:17
  • So now drop the triggers and recreate them, in the first trigger make sure you insert the id as well using `new.id` for `store_id` and for update use `where store_id = new.id`. – Abhik Chakraborty Jun 09 '15 at 11:19
  • delimiter // create trigger stores_upd after update on stores for each row begin update tbl_storefinder_stores set store_id = new.id store_name = new.name, store_address = new.address, lat = new.latitude, lon = new.longitude where store_id = new.id ; end;// delimiter ; --- I get error by this #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'store_name = new.name, store_address = new.address, lat = new.latitude, lo' at line 7 – M6Gpower Jun 09 '15 at 11:26
  • You do not need `store_id = new.id ` remove that in set part. – Abhik Chakraborty Jun 09 '15 at 11:30
  • For delete you need another trigger after delete and need to run query as `delete from tbl_storefinder_stores where store_id = old.id` try creating a trigger for delete and see what you get. – Abhik Chakraborty Jun 09 '15 at 11:40
  • Thank you , you have help me very well ! – M6Gpower Jun 09 '15 at 12:44
2

you just needs to update on duplicate key right?

Than you can use ON DUPLICATE KEY UPDATE

please Visit for more details here

Community
  • 1
  • 1
Sagar Parmar
  • 348
  • 1
  • 12