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.
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.
