I need to get data from a WordPress database and to get the name of the image I have to cross two tables. The query should be like this
but does not accept a text and therefore interprets it is undefined
It’s possible to solve this?
Thank you
I need to get data from a WordPress database and to get the name of the image I have to cross two tables. The query should be like this
but does not accept a text and therefore interprets it is undefined
It’s possible to solve this?
Thank you
What exactly do you mean by:
It doesn’t really matter if the DB is WordPress related or not, you just join the tables.
This text is not accepted
I don’t understand what do you call ‘text’ ? And what is not ‘accepted’?
Add the first table, then add the second, then select the columns to join them by…
I need to recall the record that satisfies:
“wp_attached_file” is a text that is present in the meta_key field of the relevant records
Marzio, what exactly your issue is?
All you need to do, in order to join two tables is:
Add the first table, then add the second one, then select the columns to join them by.
Which part is not clear for you, or not working as expected?
Move the metakey expression out of the join into the where condition. In the join you can only match columns.
From the table “wp_postmeta” I need to extract only the records combined with the table “wp_posts” that have the field “meta_key” equal to “_wp_attached_file”, leaving out the others
That will just work fine if you move the wp_postmeta.meta_key = '_wp_attached_file'
to the where condition.
Query will become something like:
SELECT *
FROM wp_posts
INNER JOIN wp_postmeta
ON wp_postmeta.post_id = wp_posts.ID
WHERE wp_posts.post_status = 'publish'
AND wp_posts.post_type = 'post'
AND wp_postmeta.meta_key = '_wp_attached_file'
ORDER BY wp_posts.post_date DESC
I had already tried this solution but unfortunately it does not work and I think it is impossible that it can work this way.
The problem is that every record in the “wp_posts” table should have only one record (and not 2 records) associated with the “wp_postmeta” table.
In the table “wp_postmeta” there are 2 or more records associated to the post, but only one is to be associated to obtain the name of the image.
The name is present in the table “wp_postmeta”, in the “meta_value” field, but only if the record that has the field “meta_key” equal to “_wp_attached_file”
I hope I have explained the problem well
But that is exactly what the query does, by adding the wp_postmeta.meta_key = '_wp_attached_file'
to the where conditions it filters out all the records that doesn’t have _wp_attached_file
as meta_key
, so no double records.
The following queries output exactly the same
SELECT *
FROM wp_posts
INNER JOIN wp_postmeta
ON wp_postmeta.post_id = wp_posts.ID
AND wp_postmeta.meta_key = '_wp_attached_file'
WHERE wp_posts.post_status = 'publish'
AND wp_posts.post_type = 'post'
ORDER BY wp_posts.post_date DESC
SELECT *
FROM wp_posts
INNER JOIN wp_postmeta
ON wp_postmeta.post_id = wp_posts.ID
WHERE wp_posts.post_status = 'publish'
AND wp_posts.post_type = 'post'
AND wp_postmeta.meta_key = '_wp_attached_file'
ORDER BY wp_posts.post_date DESC
I understood the problem is WordPress
I thought the links of the images linked to the posts were in the wp_postmeta.meta_value field (with the wp_postmeta.meta_key = “_wp_attached_file” field), but then I discovered that this only happens a few times, while in other posts the wp_postmedia field. meta_key must be the same as “_webdados_fb_open_graph_specific_image”
I no longer understand anything about WordPress
I try to study the problem better
That’s is why you really shouldn’t query the complicated database of Wordpress directly but use its API so you don’t have to deal with the complicated underlying structure.
Thanks George, but honestly for API and WordPress, I do not know where to start
For the wordpress API see: https://developer.wordpress.org/rest-api/
Listing the posts: https://developer.wordpress.org/rest-api/reference/posts/#list-posts
https://blog.domain.com/wp-json/wp/v2/posts/?orderby=date&order=desc
very good idea to use API! I already used Wordpress and tried to directly connect to its quite complex json stored data. If API is connected witb Wappler, then I‘m highly interested in your solution and I think its not only me! Thank you dear poweful wappler community!
Thank you so much I will study
This way has multiple benefits!