Create a Trigger with JOIN to duplicate the data

Asked

Viewed 35 times

-2

CREATE TRIGGER `copiadados`AFTER INSERT 
ON `wp_postmeta`
AS
DECLARE @nome NVARCHAR(50),
@email NVARCHAR(50),
@adicional NVARCHAR(255)
SET @nome = (SELECT meta_value FROM wp_postmeta WHERE meta_key = '_appointment_guest_name')
SET @email = (SELECT meta_value FROM wp_postmeta WHERE meta_key = '_appointment_guest_email')
SET @adicional = (SELECT meta_value FROM wp_postmeta WHERE meta_key = '_cf_meta_value')
INSERT INTO `agendamentos`(post_id, nome, email, dados, data_post) VALUES NEW.@nome, NEW.@email, NEW.@adicional, wp_posts.post_title
INNER JOIN wp_posts on wp_postmeta.post_id = wp_posts.ID

This is the code used so far, but it hasn’t worked. I need to duplicate the data for a new table, because through it I will use the data to generate PDF. I am using Wordpress c/ Mysql.

SELECT wp_posts.post_title, wp_postmeta.meta_value
FROM wp_postmeta
INNER JOIN wp_posts ON wp_postmeta.post_id = wp_posts.ID
WHERE wp_posts.post_type = 'booked_appointments'

inserir a descrição da imagem aqui

mysql wordpress

  • a suggestion, instead of having duplicated data, couldn’t you create a view? would always be up to date without the need to occupy space or duplicate data

  • 1

    @Ricardopunctual I understand, could you tell me where to start? rs The idea of duplicating the data is because wordpress uses this table to save all the information, even those that are from another plugin and I wanted to filter only the information of this plugin to be able to generate a PDF with the data. Thanks :D

  • Yes I can Richard, can assemble a query with the results you need, using the table wp_postmeta? Then just save this query as a view

  • Ricardo, I did the query but I have a question. I am forwarding a link with a print of the bank table and I need it to take only the information that is pointed out in the print. OBS: I’ll leave the query at the end of the question. Follow print link: https://ibb.co/CJVqKzM

  • You can give a DESCRIBE table_name; to add to your post? Still, you can put a table row even if changing the data in the post?

  • Paulo, I think I understood your question. I put DESCRIBE in the post and in the comment above, I put a print of the table data.

  • I put a very complete and explanatory example using a view

Show 2 more comments

1 answer

0


Let’s take parts. Basically your query would look like this:

SELECT wp_postmeta.post_id,
       wp_posts.post_title
  FROM wp_postmeta
 INNER JOIN wp_posts on wp_posts.id = wp_postmeta.post_id

The problem is that, the same table "wp_postmeta" contains the data of the same post in different records, this requires making a join with the table itself multiple times, to objter name, email and value, something like this:

 INNER JOIN wp_postmeta wp_name on wp_postmeta.post_id = wp_name.post_id
 INNER JOIN wp_postmeta wp_email on wp_postmeta.post_id = wp_email.post_id
 INNER JOIN wp_postmeta wp_adicional on wp_postmeta.post_id = wp_adicional.post_id

Note that since they are multiple joins with different purposes, each has one alias different. Also, you need to filter by "meta_key" to get the correct values, then we can add the clause WHERE, thus:

 WHERE wp_name.meta_key = '_appointment_guest_name'
   AND wp_email.meta_key = '_appointment_guest_email'
   AND wp_adicional.meta_key = '_cf_meta_value'

Here I used the different ones alias to filter what each must return.
Finally, to avoid duplicate results, let’s group by "post_id". The complete query looks like this:

SELECT wp_postmeta.post_id,
       wp_name.meta_value as nome,
       wp_email.meta_value as email,
       wp_adicional.meta_value as adicional,
       wp_posts.post_title
  FROM wp_postmeta
 INNER JOIN wp_postmeta wp_name on wp_postmeta.post_id = wp_name.post_id
 INNER JOIN wp_postmeta wp_email on wp_postmeta.post_id = wp_email.post_id
 INNER JOIN wp_postmeta wp_adicional on wp_postmeta.post_id = wp_adicional.post_id
 INNER JOIN wp_posts on wp_posts.id = wp_postmeta.post_id
 WHERE wp_name.meta_key = '_appointment_guest_name'
   AND wp_email.meta_key = '_appointment_guest_email'
   AND wp_adicional.meta_key = '_cf_meta_value'
 GROUP BY wp_postmeta.post_id

Can be seen working here: http://sqlfiddle.com/#! 9/860125/1

Finally, put this in a view:

CREATE VIEW MinhaView AS
    SELECT wp_postmeta.post_id,
           wp_name.meta_value as nome,
           wp_email.meta_value as email,
           wp_adicional.meta_value as adicional,
           wp_posts.post_title
      FROM wp_postmeta
     INNER JOIN wp_postmeta wp_name on wp_postmeta.post_id = wp_name.post_id
     INNER JOIN wp_postmeta wp_email on wp_postmeta.post_id = wp_email.post_id
     INNER JOIN wp_postmeta wp_adicional on wp_postmeta.post_id = wp_adicional.post_id
     INNER JOIN wp_posts on wp_posts.id = wp_postmeta.post_id
     WHERE wp_name.meta_key = '_appointment_guest_name'
       AND wp_email.meta_key = '_appointment_guest_email'
       AND wp_adicional.meta_key = '_cf_meta_value'
     GROUP BY wp_postmeta.post_id;

And then make a SELECT * FROM MinhaView

You can see working with the view here: http://sqlfiddle.com/#! 9/7a1cfe/2

Browser other questions tagged mysql wordpress

You are not signed in. Login or sign up in order to post.