Join Items_Metas to Post to get Image URL

By: Rudi JvR | Asked: 08/07/2023
ForumsCategory: General questionsJoin Items_Metas to Post to get Image URL
Rudi JvR asked 8 months ago
In a test form I successfully created a SQL view to join my Meta entries to the posts table to retrieve URL's of uploaded images.   When trying to implement this on the main form the format of the Meta_Values of entries is not numeric i.e. "a:1:{i:0;i:1540;}" - This prevents me from joining to the associated entry in the Posts table (on ID)? Question:
  1. Why is the Meta values in a different format?  
  2. How do I join to the Posts table?
Thanks for any insight on how or why the meta_values format looks like this.  
Rudi JvR replied 8 months ago

I realised that the number value in the string stored as the meta_value ("a:1:{i:0;i:*1540*;}") is the actual meta value I require to match to the Posts table.

After doing a few tests I also figured out that the permission settings on the field/form is what causes the meta_value to be wrapped in what appears to be a PHP class?

If at all possible I would like to retain the security/permission settings for the files uploaded via the FF Upload field, but in order to do this I would need to do fuzzy matching on the meta_value column which is less than ideal considering my table could grow to contain LARGE volumes of data.

Is there another way to do a SQL join between the frm_item_metas and Posts tables on meta.meta_value = posts.ID

Victor Font replied 8 months ago

First, what you are seeing as the meta value is a serialized array. PHP serialized arrays are used regularly by WordPress and Formidable to compress data. To use the data in a serialized array, you have to unserialize first.

If you created a post with the create posts add-on, the entry is automatically linked to the posts by the post_id field in wp_frm_items. Please review the database schema, to view the frm_items table structure. https://formidableforms.com/knowledgebase/database-schema/

The URLS of uploaded images are in their media library entry. The '1540' in the serialized array is the ID of the image record in posts where you can retrieve the URL. You may want to consider storing the file URL in a custom field as per this KNowledgeBase example: https://formidableforms.com/knowledgebase/frm_new_post/#kb-store-uploaded-file-url-in-custom-field

Rudi JvR replied 8 months ago

Hi Victor,

I missed your response somehow - whish I saw it earlier, as I've been going in circles...

Thanks for clarifying the PHP serialization issue with the meta value - I thought as much and will research how to "unserialize" the values.

Regarding the Custom Field - This seems like exactly what I'm attempting to do on the database side. I tried using the code snippet from the Knowledge Base with no success...

I did the following:
- Added a Custom_URL field to my form (CustomImageURL)
- added the PHP to load on my site (as per Knowledge Base)
- Updated the PHP script to:
- reference my File Upload field ID [333]: $field_id = 333;
- to reference my custom field name: $post['post_custom']['CustomImageURL']

The "CustomImageURL" field in my form is not updated with the URL of image uploads?

What am I missing?

Thanks for your assistance, it is really helping me a lot!

Victor Font replied 8 months ago

It's not working because "$post['post_custom']['CustomImageURL']" is an invalid variable name. First, the PHP associative array "$post" is spelled incorrectly. The correct spelling is "$_POST" and it is case sensitive. See this: https://www.php.net/manual/en/reserved.variables.post.php

Second, there's nothing more important for a developer than to visualize a form's data so you know exactly what Formidable expects. I've attached a Kint debugger screen capture image that shows how Formidable structures the $_POST associative array. Please examine this image while I explain what we're seeing.

All Formidable field entries are in the $_POST['item_meta'] array. Fields are referenced by their ID, not their label or key. Therefore, the CustomImageURL field you created is referenced as $_POST['item_meta'][xxx] where xxx is numerical id of the CustomImageURL field.

Hope this helps.

1 Answers
Best Answer
Victor Font answered 8 months ago
Image attached.
Attachments
Rudi JvR replied 8 months ago

I was using the script as is from the Knowledge Base as follows:

add_filter( 'frm_new_post', 'create_a_custom_field', 10, 2 );
function create_a_custom_field( $post, $args ) {
$field_id = 333; // change 25 to the id of the file upload field
if ( isset( $_POST['item_meta'][ $field_id ] ) ) {
$field_value = sanitize_text_field( $_POST['item_meta'][ $field_id ] );
$post['post_custom']['CustomImageURL'] = wp_get_attachment_url( $field_value ); // change 'custom_field_name_here' to your custom field name
}
return $post;
}

are you saying this part:
$post['post_custom']['CustomImageURL']

should look like this:
$_POST['post_custom'][428']

I tried updating the script but still doesn't work!?

Victor Font replied 8 months ago

Now you've totally confused me. Nowhere have you said that you are creating a custom field in a post. I assumed you were populating a custom field on the form. I don't understand why you wouldn't save the URL with the form entry. Regardless, you misread my instructions. The variable is $_POST['item_meta'][428']

If I were adding a custom field to a post, I would create the field in ACF and map the data to it in the Create Post action.

Rudi JvR replied 8 months ago

Haha - Confusion all round - so sorry about that!

I suppose my requirement evolved from some of your earlier feedback. So to clarify, I want to do the following:
- Add a hidden custom field to a form which allow users to upload images
- Automatically update the Custom Field when Images are uploaded/updated on an entry

Is the script you pointed me to not relevant in this scenario? How could I retrieve the URL of the Image for entries of this form?

Victor Font replied 8 months ago

Then let me ask you this. This boils down to a question we see a lot and it requires an understanding of how the file upload library works. Formidable's file upload engine is dropzone.js. https://www.dropzone.dev/

This feature uploads files immediately through Ajax. If you want to, you can disable processing before the entry is created. I've never done this myself and have no idea what the results are if you do. https://formidableforms.com/knowledgebase/file-upload/#kb-block-dropzone-action

Where do uploaded files go? They are uploaded to /wp-content/uploads/formidable/XX where XX is a formidable defined numeric subdirectory name unless you change the upload directory through the frm_upload_folder hook.

An entry for the uploaded file is added to the WordPress wp_posts table as an attachment. The only 2 WordPress metadata values stored in conjunction with the attachment in wp_postmeta are _wp_attached_file and _wp_attachment_metadata. The post_id for this attachment is returned to the upload field for each file uploaded.

If you upload one file only, the upload field on your form will store the post_id of the attachment in the form entry's metadata. If you allow multiple file uploads, you'll see, as you've already learned, a serialized array when you view the data in PHPMyAdmin.

I suggest you use either one of two Formidable Hooks, frm_pre_create_entry or frm_after_create_entry. If you allow form edits, you'll have to use the corresponding edit hooks as well.

The attachment URL is stored in two places. It is in the wp_posts guid field and the wp_postmeta _wp_attached_file entry. To get the URL, I would use wp_get_attachment_url( attachment_id );

Rudi JvR replied 8 months ago

Thanks for the detailed feedback Victor - Much appreciated.

I already organize my file uploads by ../formidable/form_ID/user_ID/ using custom code.

So in summary you are saying that if I want to populate a hidden field on my form with the URL of an uploaded file, I would need to add some PHP to my site which will retrieve the URL with this function "wp_get_attachment_url( attachment_id );" depending on various actions i.e. frm_pre_create_entry, frm_after_create_entry or Update/Edit.

I will try to figure out how I can store the URL with the Entry in the form using these functions rather than doing it as a post SQL process as I am doing currently. My current approach just seems too expensive, and I would rather do it once at point of data entry/update.

Thanks again!

Rudi JvR replied 8 months ago

I have the following PHP added to my site but it doesn't do anything when files are uploaded!?

On Form ID 15, I expect Field ID 428 to be updated with the attachment's URL for the file upload field's (333) image upload.

Any idea what I could be missing?

---------------------------PHP--------------------------------

// frm_after_create_entry
add_action('frm_after_create_entry', 'update_hidden_field_url_after_create', 10, 2);

function update_hidden_field_url_after_create($entry_id, $form_id) {
// Check if the form ID matches the desired form
if ($form_id == 15) {
// Get the uploaded file attachment ID from the entry
$attachment_id = FrmEntryMeta::get_entry_meta_value($entry_id, 333);

// If an attachment ID exists, update the hidden field
if ($attachment_id) {
// Get the attachment URL
$attachment_url = wp_get_attachment_url($attachment_id);

// Update the hidden field (Field ID: 428) with the attachment URL
update_post_meta($entry_id, 'frm_field_428_value', $attachment_url);
}
}
}

// frm_after_edit_entry
add_action('frm_after_edit_entry', 'update_hidden_field_url_after_edit', 10, 2);

function update_hidden_field_url_after_edit($entry_id, $form_id) {
// Check if the form ID matches the desired form
if ($form_id == 15) {
// Get the uploaded file attachment ID from the entry
$attachment_id = FrmEntryMeta::get_entry_meta_value($entry_id, 333);

// If an attachment ID exists, update the hidden field
if ($attachment_id) {
// Get the attachment URL
$attachment_url = wp_get_attachment_url($attachment_id);

// Update the hidden field (Field ID: 428) with the attachment URL
update_post_meta($entry_id, 'frm_field_428_value', $attachment_url);
}
}
}

Victor Font replied 8 months ago

Do you have a default value set for the hidden field. Formidable does not save empty field values. There’s nothing to update if the hidden field doesn’t have a value to begin with.

Rudi JvR replied 8 months ago

I was wondering how empty fields are handled on the backend, but that now makes a lot of sense.

I edited an existing entry and added a dummy value to the column I intend updating with the file upload URL. I then Edited this entry again to Upload a file. It unfortunately still does not update my URL field.

Victor Font replied 8 months ago

You are calling the wrong update function. You are not updating post meta. You are updating entry meta. Entries are in different tables than posts. Use FrmEntryMeta::update_entry_meta( $entry_id, $field_id, null, $new_value );

Rudi JvR replied 8 months ago

Correct, this was an earlier function where I was still approaching the wrong backend table (post)...

This is a more recent attempt where I am using the correct update (update_entry_meta) method in my function, but still no luck 🙁

// frm_after_edit_entry
add_action('frm_after_edit_entry', 'update_hidden_field_url_after_edit', 10, 3);

function update_hidden_field_url_after_edit($entry_id, $form_id, $args) {
// Check if the form ID matches the desired form
if ($form_id == 15) {
// Get the uploaded file attachment ID from the entry
$attachment_id = FrmEntryMeta::get_entry_meta_value($entry_id, 333);

// If an attachment ID exists, update the 428 field
if ($attachment_id) {
// Get the attachment URL
$attachment_url = wp_get_attachment_url($attachment_id);

// Update Field ID: 428 with the attachment URL
FrmEntryMeta::update_entry_meta($entry_id, 428, $attachment_url);
}
}
}

Victor Font replied 8 months ago

There is no such Formidable action named "frm_after_edit_entry". In a previous answer, I said to use https://formidableforms.com/knowledgebase/frm_after_create_entry/ and https://formidableforms.com/knowledgebase/frm_after_update_entry. You are trying to use an action that doesn't exist.

You are still not using the update_entry_meta function correctly, either. In the example I provided, the FrmEntryMeta::update_entry_meta() function is designed to receive 4 parameters. If you look at the Formidable Masterminds Codex V2 entry for this function, you'll see the parameters are $entry_id, $field_id, $meta_key, $meta_value. Here's the Codex V2 entry: https://formidable-masterminds.com/codexv2-hook-detail/?codex_id=6918&entry_type=Method&return_page=codexv2-method-list#field_6918_detail_link

In your code, you are passing 3 parameters and the $attachment_url is in the 3rd position, not the in 4th position Formidable expects. You are passing $attachment_url as the $meta_key that should be null and you are not passing the $meta_value. And since you're using a non-existent action, you'll never get things to work. Please read the documentation for frm_after_create_entry. You'll find examples of how to properly call the update function. For your code it should be: FrmEntryMeta::update_entry_meta($entry_id, 428, null, $attachment_url);

As an FYI, it's a bad practice to hard code field ids in custom code. Why? Because you are writing code that will break if you ever move to a different server. See this for an explanation: https://formidable-masterminds.com/writing-transportable-code-keys-vs-ids/

Rudi JvR replied 8 months ago

Hi Victor,

Apologies for the absence - I was travelling and unable to see my implementation through until now...

You have gotten me so close to cracking what I'm attempting to implement on this form, but I have one final hurdle which I hope you could guide me on.

I've tested the following code with a dummy value and it writes it out to a URL field (429) of the newly created Entry as expected.

PHP:

add_action('frm_after_create_entry', 'update_hidden_field_url_after_create', 10, 3);

function update_hidden_field_url_after_create($entry_id, $form_id, $args) {

if ($form_id == 44) {

// Get the uploaded file attachment ID from the entry
$attachment_id = //WHAT METHOD DO I USE HERE?

if ($attachment_id) {
$attachment_url = wp_get_attachment_url($attachment_id);

FrmEntryMeta::update_entry_meta($entry_id, 429, "", $attachment_url);
}
}
}

I need some guidance on getting the Attachment ID for the newly created entry so that I can pass it to the wp_get_attachment_url($attachment_id) function.

$attachment_id = //WHAT METHOD DO I USE HERE?

What method can I use to define $attachment_id
FYI: The Upload field ID = 398

Rudi JvR replied 8 months ago

I have been trying with this:

$attachment_id = $_POST['item_meta'][398];

$attachment_url = wp_get_attachment_url($attachment_id);

But with no luck.

Rudi JvR replied 8 months ago

Got it to work - Finally!

Thanks for the patience - it is all just too new and overwhelming that I miss little details.

Making the Best WordPress Plugin even better - Together

Take on bigger projects with confidence knowing you have access to an entire community of Formidable Experts and Professionals who have your back when the going gets tough. You got this!
Join the community
crossarrow-right