0

sorry for cross-posting: I posted a similar question on StackOverflow but I received no attention (my fault, for sure).

I need to query a Wordpress database.

The first table is wp_posts: from this table I need to retrieve the ID and the post_content where the post_title is “A”, “B” or “C”. This is of course fairly easy:

SELECT ID,post_content FROM `wp_posts` 
WHERE post_title IN ("A","B","C") and post_type = "plugin_is_writing_this_record" 

post_type is necessary here because only a particular Wordpress plugin writes records using that post_type.

Problem is that the same plugin (a plugin used to send forms) replaces with a 0 a vital information for me: the post_author, that in wp_posts is generally the id of the user who created the post, is NOT the id of the user who posted the form, but the id of the user who created the post where the form is located. I need to retrieve the id of the user who posted the form.

Using the ID of the post in wp_posts, I could get the email of the user from the table wp_postmeta.

If you query this last table this way:

SELECT * FROMwp_postmetaWHERE post_id = 277125 (*)

you get data this way (I cut down data a little):

meta_id | post_id | meta_key            | meta_value
----------------------------------------------------------
3       | 277125  | _from_email         | from@mail.com
4       | 277125  | _field_your-surname | Surname
5       | 277125  | other_meta_key      | otherdata
6       | 277125  | ...                 | ...

At this point, I know _from_email meta value is related to the mail of the user who posted the form (I need to retrieve even other meta_keys from this table but this one is functional to the query and to this question).

The table that contains the ID I need and the email of the user is of course wp_users, a third table.

Is it possible in a single query over the three tables at once to retrieve the data I need? I tried some JOINS but I couldn't get them working properly. Any help is appreciated.

1

This query worked for me. I had to reverse it, starting at the user and then matching the post ID. You would need to replace the value of wp_posts.ID of course.

SELECT wp_users.ID
FROM wp_users
LEFT JOIN wp_postmeta ON wp_postmeta.meta_value = wp_users.user_email AND wp_postmeta.meta_key = '_from_email' 
LEFT JOIN wp_posts ON wp_posts.ID = wp_postmeta.post_id AND wp_posts.ID = 2
  • Why AND wp_posts.ID = 2? – 3000 Aug 9 '17 at 17:22
  • Oh, sorry, I don't need a particular post, but any post – 3000 Aug 9 '17 at 17:23
  • Then you can remove the last AND. At the end of the day, what data exactly do you need from each table? – gdaniel Aug 9 '17 at 17:31
  • post_content, post_date and post_id from wp_posts, any meta_key/meta_value in wp_postmeta, id of the user from wp_users. Still not sure if I should query only, eg., latest 24 hours, but this is a detail – 3000 Aug 9 '17 at 18:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.