WordPress.org

1. jimesten
   Member
   Posted 9 months ago #

   I need to be able to filter records based on a user profile item stored in the user_meta table. My SQL skills are at best very rusty but I have a reasonable grasp of reading sample code.

   So, a user would log in and based on a profile item, I need to pull all submissions where the submitter has a matching profile item (in this case, it would be a school name -- school owner is pulling report for own students).

   My assumption is that is not possible with the plugin alone, but I should be able to level the plugin's SQL for gathering all submissions to create this query. Ideas?

2. jimesten
   Member
   Posted 9 months ago #

   To clarify a typo ... "leverage" the following:

   SELECTsubmit_time` AS 'Submitted',
   max(if(field_name='from_name', field_value, null )) AS 'from_name',
   max(if(field_name='from_email', field_value, null )) AS 'from_email',
   max(if(field_name='subject', field_value, null )) AS 'subject',
   max(if(field_name='Date', field_value, null )) AS 'Date',
   max(if(field_name='Act', field_value, null )) AS 'Act',
   max(if(field_name='date_time', field_value, null )) AS 'date_time',
   max(if(field_name='full_message', field_value, null )) AS 'full_message',
   max(if(field_name='Submitted Login', field_value, null )) AS 'Submitted Login',
   max(if(field_name='Submitted From', field_value, null )) AS 'Submitted From',
   GROUP_CONCAT(if(file is null or length(file) = 0, null, field_name)) AS 'fields_with_file'
   FROM warrior_CF7DBPlugin_SUBMITS
   WHERE form_name = 'Form: 1'
   GROUP BY submit_time
   ORDER BY submit_time DESC
   LIMIT 0,100`

   ... in order to create whatever join is necessary to filter records based upon an item in the form submitter's profile

3. jimesten
   Member
   Posted 9 months ago #

   Alternatively -- might there be a way to associate more than just the user id information into the save submission. Is any of the rest of the user profile available?

4. Michael Simpson
   Member
   Posted 9 months ago #

   Shortcodes don't join to any other tables, so you can't constraint on them. But you potentially could craft a query to get the information you want and put that in a custom page that you write.

   First, a form submission is not necessarily associated with a registered user on your WP. But, *IF* the person submitting the form is registered and is logged-in when submitting the form, then the user's login is captured. In your query above it would appear in the 'Submitted Login' field.

   It looks like wp_usermeta doesn't use the user login value, but uses the user ID number that WP assigns. That is in wp_users. So assuming you had some data in 'Submitted Login', you would use you query, join to wp_users to get the user ID, then join to wp_usermeta to get some profile information. It would look like this:

   select distinct
      wp_users.user_login,
      wp_usermeta.meta_key,
      wp_usermeta.meta_value
   from
      (
         <put your pivot query above inside these parens>
      ) submissions,
      wp_users,
      wp_usermeta
   where
      submissions.'Submitted Login' = wp_users.user_login and
      wp_users.ID = wp_usermeta.user_id

   But change:
   1. submissions.'Submitted Login' to replace single quotes with back-quotes (I did it wrong because backquotes won't display right in this blog)
   2. "wp_" to "warrior_" everywhere, which appears to be the prefix for your specific DB.

   And you can add additional constraints in the "where" clause to constrain on profile information stored in wp_usermeta like this:
   and wp_usermeta.meta_key = 'description' and wp_usermeta.meta_value='School-A'

5. jimesten
   Member
   Posted 9 months ago #

   Thanks Michael .. that should get the wheels turning again!!

   We are dealing with logged in users (both those submitted the forms and the one needing the report), so I've safely captured that.

   Jim

Reply

You must log in to post.