UserID

Hi Craig did you test the query in MySQL? Shouldn’t you have an AND in your WHERE clause? Otherwise what is “form_name”? I guess a column in table wp_cf7dbplugin_submits.
Maybe try first a simple query with userID and then make it more complex in order to understand if the problem is the variable or the query syntax

I can go as basic as this and it still returns zero results.

SELECT
MAX(IF(field_name=’number-Glucose’, field_value, NULL )) AS ‘Glucose’
FROM wp_cf7dbplugin_submits
WHERE
UserID = {{{user_ID}}}
GROUP BY submit_time

When I change to this:
SELECT
MAX(IF(field_name=’number-Glucose’, field_value, NULL )) AS ‘Glucose’
FROM wp_cf7dbplugin_submits
WHERE
form_name = ‘Blood Glucose’
GROUP BY submit_time

I get 12 results. Most of them under my logged in username.

Is this syntax correct?
UserID = {{{user_ID}}}

If I use this:
SELECT
MAX(IF(field_name=’number-Glucose’, field_value, NULL )) AS ‘Glucose’,
MAX(IF(field_name=’date-glucose’, field_value, NULL )) AS ‘Date1’,
MAX(IF(field_name=’Submitted Login’, field_value, NULL )) AS ‘Login1’
FROM wp_cf7dbplugin_submits
WHERE
form_name = ‘Blood Glucose’
GROUP BY submit_time
ORDER BY Date1

I get a table with my username listed under Login1. I just can’t get anything with {{{user_ID}}} to work.

Any update on this?

It’s a matter of debugging the SQL query.

Did you compare your results with the ones obtained in the MySQL workbench? What happens if you replace {{{user_ID}}} with an actual user ID?

And most of all, are you sure that your form data are not serialized?
Is field_name a column of the wp_cf7dbplugin_submits table?

What happens if you run this query?
SELECT
*
FROM wp_cf7dbplugin_submits
WHERE
UserID = {{{user_ID}}}

That query:
SELECT
*
FROM wp_cf7dbplugin_submits
WHERE
UserID = {{{user_ID}}}

Returns 0 records. Here is the table from phpMyAdmin.

https://picasaweb.google.com/114136335241872520176/ScreenCaptures#6224069322373365170

So does this mean it is serialized? If so, how do I extract it? This part gives me a string of the logins:
MAX(IF(field_name=’Submitted Login’, field_value, NULL )) AS ‘Login1’

OK I see that there is no UserID column in your table so you cannot use it in your WHERE clause. WHERE clauses work on existing columns.

please try this

SELECT
MAX(IF(field_name=’number-Glucose’, field_value, NULL )) AS ‘Glucose’,
MAX(IF(field_name=’date-glucose’, field_value, NULL )) AS ‘Date1’,
MAX(IF(field_name=’Submitted Login’, field_value, NULL )) AS ‘Login1’
FROM wp_cf7dbplugin_submits
WHERE
Login1 = {{{user_ID}}}

I just sent you another email with more screen shots. The table shows up fine with the proper logins listed under Login1. But whenever I add the tag {{{user_ID}}} I get zero results.

user_ID is a number, did you try user_login?

you need to understand the type of data you are manipulating in order to write consistent queries

Yes. Still zero results like screenshot I emailed you.

user_ID is a number, did you try user_login?
is there any record where login = a number?
this number is the ID of the currently logged in user
user_ID is a number
in the screenshot sent you expect that this number is = Login1

Yes, same results (emailed you photos).

In the picture received you still use

Login1 = {{{user_ID}}}

please use

Login1 = {{{user_login}}}

have you tested your queries in MySQL? Do they work there?

I did try that. I sent two new pictures. I can filter by a known id and it works with the field name (but then I don’t get values). When I change it to your tag, it gets zero. Also, I can’t get anything to work in the WHERE using user_is66.

I also did a test and used {{{user_ID}}} and the field itself (wp_rg_lead_detail.value) and it worked (but only got ID fields, not user data). But anything on the imported value (user_id66) doesn’t work.

After fixing the SQL query and a bug in the plugin, this is closed.