Plugin Author
Eli
(@scheeeli)
This is a good start but I need more information to help you JOIN these tables correctly. I will use multiple ???’s in this query in place of what I don’t know and ask you to look at the tables to fill them in:
SELECT COUNT(wp_wp_pro_quiz_statistic.correct_count) AS Score, wp_wp_pro_quiz_master.name FROM (wp_wp_pro_quiz_statistic_ref INNER JOIN wp_wp_pro_quiz_master ON wp_wp_pro_quiz_statistic_ref.??? = wp_wp_pro_quiz_master.???) LEFT JOIN wp_wp_pro_quiz_statistic ON ???.??? = wp_wp_pro_quiz_statistic.??? WHERE wp_wp_pro_quiz_master.name LIKE ‘%Science 7%’ AND wp_wp_pro_quiz_statistic.correct_count = ‘1’ AND wp_wp_pro_quiz_statistic.user_id = ‘<?php $current_user->ID ?>’ GROUP BY wp_wp_pro_quiz_master.name;
??? #1. What is the column in wp_wp_pro_quiz_statistic_ref that refers to the Primary Key of the quiz named in the wp_wp_pro_quiz_master table?
??? #2. What is the Primany Key column in the wp_wp_pro_quiz_master table?
??? #3. What is the ID column called in wp_wp_pro_quiz_statistic that is used to connect each row to the appropriate quiz named in wp_wp_pro_quiz_master table or referenced in wp_wp_pro_quiz_statistic_ref?
???.??? #1. What column in which table refers to the ID column in the wp_wp_pro_quiz_statistic table?
If you can answer these questions then you should have a working query. If your query still does not work then please reply with the SQL Error that you get and the full Query and I’ll help you fix it.
I tried the query and got this error first under Unsaved Report:
Unknown column ‘wp_wp_pro_quiz_statistic.user_id’ in ‘where clause’
and this error was in pink above the query:
ERROR: Unknown column ‘wp_wp_pro_quiz_statistic.user_id’ in ‘where clause’ SQL:SELECT COUNT(wp_wp_pro_quiz_statistic.correct_count) AS Score, wp_wp_pro_quiz_master.name FROM (wp_wp_pro_quiz_statistic_ref INNER JOIN wp_wp_pro_quiz_master ON wp_wp_pro_quiz_statistic_ref.statistic_ref_id = wp_wp_pro_quiz_master.id) LEFT JOIN wp_wp_pro_quiz_statistic ON wp_wp_pro_statistic_ref_id.statistic_ref_id = wp_wp_pro_quiz_statistic.statistic_ref_id WHERE wp_wp_pro_quiz_master.name LIKE ‘%Science 7%’ AND wp_wp_pro_quiz_statistic.correct_count = ‘1’ AND wp_wp_pro_quiz_statistic.user_id = ‘1’ GROUP BY wp_wp_pro_quiz_master.name;
So, I changed the table name to wp_wp_pro_quiz_static_ref.user_id and that removed the errors above, but then I received these errors:
This error came under the Unsaved Report:
Unknown column ‘wp_wp_pro_statistic_ref_id.statistic_ref_id’ in ‘on clause’
and this error was in pink:
ERROR: Unknown column ‘wp_wp_pro_statistic_ref_id.statistic_ref_id’ in ‘on clause’ SQL:SELECT COUNT(wp_wp_pro_quiz_statistic.correct_count) AS Score, wp_wp_pro_quiz_master.name FROM (wp_wp_pro_quiz_statistic_ref INNER JOIN wp_wp_pro_quiz_master ON wp_wp_pro_quiz_statistic_ref.statistic_ref_id = wp_wp_pro_quiz_master.id) LEFT JOIN wp_wp_pro_quiz_statistic ON wp_wp_pro_statistic_ref_id.statistic_ref_id = wp_wp_pro_quiz_statistic.statistic_ref_id WHERE wp_wp_pro_quiz_master.name LIKE ‘%Science 7%’ AND wp_wp_pro_quiz_statistic.correct_count = ‘1’ AND wp_wp_pro_quiz_statistic_ref.user_id = ‘1’ GROUP BY wp_wp_pro_quiz_master.name;
On your ???, this is what I’m seeing in phpMyAdmin:
Table: wp_wp_pro_quiz_statistic_ref – there’s an area under “indexes” that says: PRIMARY with a field: statistic_ref_id
Table: wp_wp_pro_quiz_master – it says PRIMARY with field: id
Table: wp_wp_pro_quiz_statistic – it says PRIMARY with field: statistic_ref_id and question_id
I can send you screenshots of what I’m looking at via email if needed.
Thanks, Sheri
Plugin Author
Eli
(@scheeeli)
try changing wp_wp_pro_statistic_ref_id.statistic_ref_id to wp_wp_pro_statistic_ref.statistic_ref_id
If this new query returns no results then please send me a screenshot of some sample data from the wp_wp_pro_quiz_statistic_ref table with column names.
Aloha, Eli
I did get another error which is below. What is your email for me to send the screenshot data to?
Thanks, Sheri
ERROR: Unknown column ‘wp_wp_pro_statistic_ref.statistic_ref_id’ in ‘on clause’ SQL:SELECT COUNT(wp_wp_pro_quiz_statistic.correct_count) AS Score, wp_wp_pro_quiz_master.name FROM (wp_wp_pro_quiz_statistic_ref INNER JOIN wp_wp_pro_quiz_master ON wp_wp_pro_quiz_statistic_ref.statistic_ref_id = wp_wp_pro_quiz_master.id) LEFT JOIN wp_wp_pro_quiz_statistic ON wp_wp_pro_statistic_ref.statistic_ref_id = wp_wp_pro_quiz_statistic.statistic_ref_id WHERE wp_wp_pro_quiz_master.name LIKE ‘%Science 7%’ AND wp_wp_pro_quiz_statistic.correct_count = ‘1’ AND wp_wp_pro_quiz_statistic_ref.user_id = ‘1’ GROUP BY wp_wp_pro_quiz_master.name;
Plugin Author
Eli
(@scheeeli)
wordpress AT ieonly DOT com
Plugin Author
Eli
(@scheeeli)
try this:
SELECT SUM(wp_wp_pro_quiz_statistic.correct_count) AS Score, wp_wp_pro_quiz_master.name FROM (wp_wp_pro_quiz_statistic_ref INNER JOIN wp_wp_pro_quiz_master ON wp_wp_pro_quiz_statistic_ref.quiz_id = wp_wp_pro_quiz_master.id) LEFT JOIN wp_wp_pro_quiz_statistic ON wp_wp_pro_quiz_statistic_ref.statistic_ref_id = wp_wp_pro_quiz_statistic.statistic_ref_id WHERE wp_wp_pro_quiz_master.name LIKE ‘%Science 7%’ AND wp_wp_pro_quiz_statistic.correct_count = ‘1’ AND wp_wp_pro_quiz_statistic_ref.user_id = ‘1’ GROUP BY wp_wp_pro_quiz_master.name;
