Read database data into drop down menu

Hello @yrhs320070

Yes, of course. wp_cf7_vdata_entry is a database table generated by the “Advanced CF7 DB” plugin. So, you can use our plugin to load its rows to fill other fields in the form.

The table has the structure:

id | cf7_id | data_id | name | value

I’ll try to describe the process with a hypothetical example. Assuming you have created a basic contact form with the default tags names like your-name and your-email. And you want to fill a dropdown menu by using your-name values as the choices’ texts and your-email as the choices’ values.

In this hypothetical case, the form structure would be similar to:

<label> Users List
[select users]</label>

[cf7-recordset id="cf7-vdata-entry" type="database" query="SELECT t1.value as name, t2.value as email FROM {wpdb.prefix}cf7_vdata_entry t1 INNER JOIN {wpdb.prefix}cf7_vdata_entry t2 ON (t1.data_id=t2.data_id) WHERE t1.name='your-name' AND t2.name='your-email'"]
[cf7-link-field recordset="cf7-vdata-entry" field="users" value="email" text="name"]

The query seems complex because I want to create a record with information of different rows in the wp_cf7_vdata_entry:”

SELECT t1.value as name, t2.value as email FROM {wpdb.prefix}cf7_vdata_entry t1 INNER JOIN {wpdb.prefix}cf7_vdata_entry t2 ON (t1.data_id=t2.data_id) WHERE t1.name='your-name' AND t2.name='your-email'

The use of the {wpdb.prefix} constant avoids having to know the prefix used by your WordPress database. In addition, you can export the form to another website that uses a different table prefix, and it will continue working with no problems.

Best regards.

HI

I can already look up the value but it just shows all values

Is it possible to read the registrant’s UESR_ID to determine if it is the registrant’s value
thank

• This reply was modified 3 years ago by yrhs320070.

Hello @yrhs320070

Our plugin includes predefined constants to use for filtering the data sources records (https://cf7-datasource.dwbooster.com/documentation#constants), one of them the {user.id} give you the id of the logged in user.

The wp_cf7_vdata_entry does not include a column for the user id. Their columns are:

id | cf7_id | data_id | name | value

So, the query to read the records corresponding to the logged user will depend on the information stored in the table and not the table’s structure.

If you need help implementing the query, you should describe the information stored in your table. In special, the values in the name column.

Best regards.

I’m sorry, I do not understand what you mean

I want to collect profiles created by this user
I also sync USER_ID back to wp_cf7_vdata_entry when creating
So I want to be able to let the user select the created data when the form is opened

thank

Hello @yrhs320070

You want to read the information stored into the wp_cf7_vdata_entry table, but please, pay attention to the table’s structure:

id | cf7_id | data_id | name | value

The id column contains an autoincrement with a unique value for every row in the database.
cf7_id contains the forms’ ids.
data_id contains submission’s id.
name contains the names of the forms’ fields.
value contains the value of the corresponding field in the form’s submission.

There are no columns for the user id.

So, the solution would be to insert a field in the form with the user id. I’ll assume the name of this field is user_id. Now, if you want to populate a recordset field with the information submitted by this user, you would need a query similar to:

`SELECT name, value FROM {wpdb.prefix}cf7_vdata_entry WHERE data_id IN (SELECT data_id FROM {wpdb.prefix}cf7_vdata_entry WHERE name=”user_id” AND value={user.id})’

Since I cannot filter the rows by the table structure (because there are no columns for the users’ ids), I’ve to filter the rows by the columns values using a subquery.

Best regards.