| @@ -1,62 +1,86 @@ | ||
| 1 | 1 | === SQL Chart Builder === |
| 2 | 2 | Contributors: elvinhaci |
| 3 | -Tags: charts,mysql,sql,visualizer,graphs,sql reports,dynamic chart | |
| 3 | +Tags: charts,mysql,sql,visualizer,sql chart | |
| 4 | 4 | Requires at least: 5.0.0 |
| 5 | -Tested up to: 6.0.2 | |
| 6 | -Stable tag: 2.3.2 | |
| 5 | +Tested up to: 7.1.2 | |
| 6 | +Stable tag: 3.0.6 | |
| 7 | +Donate link: https://guaven.com/service/small-thankyou-premium-support-service/ | |
| 7 | 8 | License: GPLv2 or later |
| 8 | 9 | License URI: http://www.gnu.org/licenses/gpl-2.0.html |
| 9 | 10 | |
| 10 | -Turn Your SQL Queries to Beautiful Dynamic Charts- Pie, Line, Area, Donut, Bar Charts with date/input filters. | |
| 11 | +Turn your SQL queries into beautiful dynamic charts with a visual SQL builder, live autocomplete and no-code date/input filters. | |
| 11 | 12 | |
| 12 | 13 | == Description == |
| 13 | -The plugin can create beautiful charts based on your SQL queries, then you can use those charts in any part of your website. | |
| 14 | +The plugin creates beautiful charts based on your SQL queries, then you can use those charts in any part of your website. | |
| 14 | 15 | You can use both native wp and non-wp mysql tables in your queries. |
| 15 | 16 | |
| 16 | -### Pie chart, Donut chart, Line chart, Bar chart, Column chart, Area chart | |
| 17 | +### 11 chart types: Pie, Doughnut, Polar Area, Radar, Line, Area, Stepped Line, Bar, Horizontal Bar, Stacked Bar, Scatter | |
| 17 | 18 | |
| 19 | += What's new in 3.0 = | |
| 20 | + | |
| 21 | +* **A completely redesigned chart builder.** No more "nerd-only" settings page: a clean, tabbed UI walks you through Chart & Data → Dynamic Filters → Style → Advanced. | |
| 22 | +* **Visual chart type picker with built-in guides.** Every chart type shows a short "when to use it" guide plus a working example query you can insert with one click. | |
| 23 | +* **Live SQL autocomplete.** Start typing and the editor suggests SQL keywords (type "S" — get SELECT), your real database table names and real column names. Type `tablename.` to see that table's columns. Press Ctrl+Space for suggestions at any time. | |
| 24 | +* **Click-to-build query toolbar.** SELECT / COUNT(*) / FROM / WHERE / GROUP BY / ORDER BY / LIMIT buttons plus "Insert table", "Insert column" and "Insert dynamic tag" dropdowns filled with your actual database schema — build a best-practice query without typing a single word. | |
| 25 | +* **No-code input filters.** Add date/number/text filters as simple rows (tag, type, label, default) — the tag dropdown suggests `{tags}` already used in your query and columns from its WHERE part. No more memorizing the `~` / `|` syntax (a raw editor is still available for pros). One click inserts the `{tag}` into your query. | |
| 26 | +* **Dropdown column mapping.** The X/Y axis fields are dropdowns filled with the columns detected in your SQL query — no more guessing column names. | |
| 27 | +* **Color pickers** instead of typing hex codes, and a fixed professional default palette instead of random colors. | |
| 28 | +* **Latest Chart.js v4**, bundled inside the plugin — no third-party CDN calls, GDPR-friendly. | |
| 29 | +* **4 new chart types**: Radar, Stepped Line, Stacked Bar and Scatter. | |
| 30 | +* **Better front-end design**: modern table view, restyled filter form and datepicker. | |
| 31 | +* **Full legacy support**: charts created with 2.x keep working unchanged. Deprecated Google-Chart types are automatically mapped to modern equivalents when you open/save the chart. | |
| 32 | + | |
| 18 | 33 | = How to use = |
| 19 | 34 | |
| 20 | -1. Give any name to your report. | |
| 35 | +1. Go to Dashboard → My SQL Charts → Add New and give any name to your report. | |
| 21 | 36 | |
| 22 | -2. Use our preinstalled chart or create new one yourself: choose desired chart type, type sql query, | |
| 23 | -enter field names, labels and then press to Publish/Update | |
| 37 | +2. Pick a chart type — read the short guide shown under the type cards, or click "Use this example" to start from a working query. | |
| 24 | 38 | |
| 25 | -3. You can use multiple SQL queries too. Just split them by ; sign. You can also add shortcode argument to SQL query. For example if you | |
| 26 | -type "select * from wp_posts where ID>{arg1}" then it you can pass arg1 value to the query with [gvn_schart_2 id="2" arg1="11"] shortcode. | |
| 39 | +3. Build your SQL query with the toolbar buttons and autocomplete (real table and column names are suggested), then map the X and Y columns. | |
| 27 | 40 | |
| 28 | -4. After update/save you will see needed shortcode below there. You can use that shortcode anywhere in your website: in pages, posts, widgets etc. | |
| 41 | +4. You can use multiple SQL queries too — just split them with the ";" sign to get comparison charts. You can also pass shortcode arguments into the query: with "select * from wp_posts where ID>{arg1}" the shortcode [gvn_schart_2 id="2" arg1="11"] passes the value in (the builder's live preview runs without arguments, so test such charts on a page). | |
| 29 | 42 | |
| 30 | -5. Just check "Show table-view data below the graph" in order to get table-list view below each chart. | |
| 43 | +5. After Publish/Update you will see the shortcode below the builder. Use it anywhere on your website: pages, posts, widgets, or the "My SQL Charts" Gutenberg block. | |
| 31 | 44 | |
| 32 | -6. Using "Dynamic Filters" you can create dynamic variables inside SQL code. It also creates corresponding dynamic input form above each chart. | |
| 45 | +6. Check "Show table-view data below the graph" to also render the data as a styled table. | |
| 33 | 46 | |
| 34 | -= Dynamic filters = | |
| 47 | +7. In the Input Filters tab, add filters by clicking "+ Add filter" — each one becomes an input (with a datepicker for date types) above the chart at the front-end. | |
| 35 | 48 | |
| 36 | -Use this format: variable_name~default_value~variable_label~variable_type | variable_name~default_value~variable_label~variable_type etc. | |
| 49 | += Chart type mini-guide = | |
| 37 | 50 | |
| 38 | -* variable_name - any single name you want. | |
| 39 | -* default_value - default value when no any variable chosen by a user | |
| 40 | -* variable_label - Label which would be visible at a form above the chart | |
| 41 | -* variable_type - number, text or date | |
| 42 | -* ~ is a separator between variable elements. | |
| 43 | -* | is a separator between variables | |
| 51 | +* **Pie / Doughnut** — how a total splits into a few parts. One query: label column + numeric column. | |
| 52 | +* **Polar Area** — like a pie, but the value controls the radius; great for cyclic data (months, weekdays). | |
| 53 | +* **Radar** — compare one or more series across categories arranged in a circle (profiles, ratings). | |
| 54 | +* **Line** — trends over time; add more ";"-separated queries for comparison lines. | |
| 55 | +* **Area** — a line with the region filled; emphasizes volume. | |
| 56 | +* **Stepped Line** — values that change at discrete moments (prices, stock levels). | |
| 57 | +* **Bar / Horizontal Bar** — compare categories; horizontal is best for long labels. | |
| 58 | +* **Stacked Bar** — how each category total is composed; each ";"-separated query becomes one segment. | |
| 59 | +* **Scatter** — correlation between two numeric columns (both X and Y must be numeric). | |
| 44 | 60 | |
| 45 | -For example if to put | |
| 61 | += Input filters (dynamic variables) = | |
| 46 | 62 | |
| 47 | -limit_tag~10~Count~number | post_date_tag~2010-07-05 17:25:18~Date Published~date, | |
| 63 | +Use the visual rows in the Input Filters tab, or the raw format: | |
| 48 | 64 | |
| 49 | -then you can use this SQL code | |
| 65 | +variable_name~default_value~variable_label~variable_type | variable_name~default_value~variable_label~variable_type | |
| 50 | 66 | |
| 51 | -select * from wp_posts where post_date<{post_date_tag} limit {limit_tag} | |
| 67 | +* variable_name - any single name you want. | |
| 68 | +* default_value - default value when no variable is chosen by the user | |
| 69 | +* variable_label - label visible on the form above the chart | |
| 70 | +* variable_type - number, text or date | |
| 71 | +* ~ separates variable elements, | separates variables | |
| 52 | 72 | |
| 53 | -in SQL CODE field. | |
| 54 | -{post_date_tag} and {limit_tag} would be replaced with dynamic variables. | |
| 73 | +Example: with the filters "limit_tag~10~Count~number | post_date_tag~2010-07-05~Date Published~date" | |
| 74 | +you can use "select * from wp_posts where post_date<{post_date_tag} limit {limit_tag}" as the SQL code — | |
| 75 | +the plugin renders the corresponding inputs above the chart automatically. | |
| 55 | 76 | |
| 56 | -So, the plugin will automatically recognize it and put corresponding selectboxes above the chart. | |
| 77 | +Supported built-in dynamic tags: {current_user_id}, {current_user_login}, {current_user_email}, {current_user_display_name}. | |
| 57 | 78 | |
| 79 | += Caching = | |
| 58 | 80 | |
| 81 | +Use [gvn_schart_2_cached id="1" expire="3600"] to cache the rendered chart in a transient. Add &force_sql_cache_reload=1 to the URL to bypass it once (e.g. right after upgrading). | |
| 82 | + | |
| 59 | 83 | = Website = |
| 60 | 84 | [https://guaven.com/my-sql-charts/](https://guaven.com/my-sql-charts/) |
| 61 | 85 | |
| 62 | 86 | = Documentation = |
| @@ -65,9 +89,9 @@ | ||
| 65 | 89 | = Bug Submission and Forum Support = |
| 66 | 90 | [Contact Page](https://guaven.com/contact/solution-request/) |
| 67 | 91 | |
| 68 | 92 | = Please Vote if you liked our plugin = |
| 69 | -Your votes really helps us. Thanks. | |
| 93 | +Your votes really help us. Thanks. | |
| 70 | 94 | |
| 71 | 95 | |
| 72 | 96 | == Installation == |
| 73 | 97 | |
| @@ -73,15 +97,26 @@ | ||
| 73 | 97 | |
| 74 | 98 | 1. Upload 'guaven_sqlcharts.zip' to the '/wp-content/plugins/' directory |
| 75 | 99 | 2. Unzip it. |
| 76 | 100 | 3. Go to Dashboard/Plugins and Activate the plugin. |
| 77 | -4. Go to “Dashboard/My SQL Charts” to create new charts. You will also see howtouse guide texts there. | |
| 101 | +4. Go to "Dashboard/My SQL Charts" to create new charts. Guides are built into the chart builder. | |
| 78 | 102 | |
| 79 | 103 | |
| 80 | 104 | |
| 81 | 105 | == Frequently Asked Questions == |
| 82 | 106 | |
| 107 | += Will my old charts keep working after updating to 3.0? = | |
| 83 | 108 | |
| 109 | +Yes. 3.0 reads exactly the same settings as 2.x, so every existing chart and shortcode keeps working. Charts using the long-deprecated Google Chart types are automatically mapped to the equivalent modern chart types. If you use the cached shortcode, add &force_sql_cache_reload=1 to the URL once after upgrading to refresh the cache. | |
| 110 | + | |
| 111 | += Does the plugin load anything from third-party CDNs? = | |
| 112 | + | |
| 113 | +No. Chart.js v4 and all other assets ship inside the plugin. | |
| 114 | + | |
| 115 | += Can non-admins edit charts? = | |
| 116 | + | |
| 117 | +No, chart management requires the manage_options capability, and only SELECT queries are allowed. | |
| 118 | + | |
| 84 | 119 | == Screenshots == |
| 85 | 120 | |
| 86 | 121 | 1. Screenshot 1 |
| 87 | 122 | |
| @@ -92,10 +127,87 @@ | ||
| 92 | 127 | 4. Screenshot 4 |
| 93 | 128 | |
| 94 | 129 | 5. Screenshot 5 |
| 95 | 130 | |
| 131 | +== Available Filters == | |
| 132 | +apply_filters( 'guaven_sqlcharts_table_empty_cell'); | |
| 96 | 133 | |
| 134 | +apply_filters('guaven_sqlcharts_pre_print_vars'); | |
| 135 | + | |
| 136 | +apply_filters( 'guaven_sqlcharts_final_output'); | |
| 137 | + | |
| 138 | +apply_filters('guaven_sqlcharts_rendered_sql'); | |
| 139 | + | |
| 97 | 140 | == Changelog == |
| 141 | += 3.0.6 = | |
| 142 | +* FIX: Saving a chart failed with "A post type mismatch has been detected." (and edits appeared to revert) when a dynamic filter was named like a WordPress form field such as post_type. The live preview in the chart builder now renders filter inputs disabled so they are never submitted with the chart. | |
| 143 | +* IMPROVED: Hint in the Input Filters tab about tag names to avoid. | |
| 144 | +* FIX: Date/time X axis: labels of dates that are very close together are now placed one below the other instead of overlapping. | |
| 145 | + | |
| 146 | += 3.0.5 = | |
| 147 | +* FIX: Date/time X axis on Bar charts: bars now have a fixed, clearly visible width instead of shrinking to the closest pair of dates. | |
| 148 | +* FIX: Date/time X axis: the axis now labels only the dates present in the data instead of evenly spaced computed dates. | |
| 149 | + | |
| 150 | += 3.0.4 = | |
| 151 | +* SECURITY: Chart posts are now administrator-only at the capability level (all gvn_schart capabilities map to manage_options), so Contributors/Authors can no longer create or edit charts through any WordPress entry point such as XML-RPC. All guaven_sqlcharts_* custom fields are protected meta. | |
| 152 | +* SECURITY: The forbidden-command check now runs on the final SQL after all shortcode arguments and filter values are inserted, ";" is stripped from user-supplied values, and every statement must start with SELECT, WITH, SHOW, DESCRIBE or EXPLAIN. PREPARE/EXECUTE, LOCK, KILL, FLUSH and similar statements were added to the blocked list. | |
| 153 | +* SECURITY: The cached shortcode now keeps a separate cache entry per user and per dynamic filter value, so a chart using {current_user_*} tags or URL filters can no longer serve one visitor's result to another. | |
| 154 | +* SECURITY: The shortcode "params" attribute is validated and can no longer inject JavaScript into the chart script. | |
| 155 | +* The chart save handler now also requires manage_options and the gvn_schart post type. | |
| 156 | + | |
| 157 | += 3.0.3 = | |
| 158 | +* FIX: Using the same shortcode argument tag (e.g. {arg1}) more than once in the SQL, such as in both queries of a comparison chart, broke the chart with a fatal error. | |
| 159 | +* FIX: The [gvn_schart_2_cached] shortcode ignored arg1..arg19, width, height, table and params attributes; they are now passed through and each set of attributes is cached separately. | |
| 160 | +* IMPROVED: When the SQL contains {arg} tags, the builder shows the shortcode with the needed attributes and explains that the live preview runs without them. | |
| 161 | +* NEW: "Scale X axis by date/time" option (Advanced tab) for Bar and Line charts. Points and bars are placed by real elapsed time when the X values are dates (2024-02-23, 2024-02, 2024, 2024-02-23 14:30). Off by default; existing charts are unchanged. | |
| 162 | + | |
| 163 | += 3.0.2 = | |
| 164 | +* FIX: "Value labels" option now works for Bar, Horizontal Bar, Stacked Bar, Line, Area, Stepped Line, Radar and Scatter charts (previously only circular charts). | |
| 165 | +* NEW: The X axis label and Y axis label fields are now shown as real axis titles on Bar, Line and Scatter charts (Y title for single-series charts; multi-series charts keep the names in the legend). | |
| 166 | +* FIX: Series names containing "&" were cut off (the stored & entity was mistaken for a ";" series separator); labels with quotes or other special characters no longer show HTML entities in the legend or table view. | |
| 167 | +* IMPROVED: Clearer hints in the chart builder for the label, color and value-label fields. | |
| 168 | + | |
| 169 | += 3.0.1 = | |
| 170 | +* FIX: SQL query editor showed HTML entities (e.g. ") instead of quotes after saving a chart. Queries are now stored exactly as typed; single quotes are no longer converted to double quotes. Charts saved with older versions are decoded automatically. | |
| 171 | +* Tested up to WordPress 7.1.2. | |
| 172 | + | |
| 173 | += 3.0.0= | |
| 174 | +* NEW: Completely redesigned admin chart builder — tabbed UI (Chart & Data / Dynamic Filters / Style / Advanced) with visual chart-type cards. | |
| 175 | +* NEW: Live SQL autocomplete — suggests SQL keywords, real table names and real column names from your database while you type (Ctrl+Space to trigger manually). | |
| 176 | +* NEW: Click-to-build query toolbar — SELECT/FROM/WHERE/GROUP BY/ORDER BY/LIMIT buttons + insert-table/insert-column/insert-tag dropdowns based on the live DB schema. | |
| 177 | +* NEW: No-code Input Filters builder — add filters as visual rows; tag names are suggested from your query, and {tags} are inserted with one click (raw format still supported). | |
| 178 | +* NEW: X/Y column mapping via dropdowns auto-filled from the columns detected in your SQL query. | |
| 179 | +* NEW: Fixed professional default color palette (Tableau 10) replaces random colors; override via the guaven_sqlcharts_default_palette filter. | |
| 180 | +* IMPROVED: Proper "Chart" admin labels and menu icon for the chart post type (no more generic "Post" wording). | |
| 181 | +* NEW: Built-in per-chart-type usage guides with one-click example queries. | |
| 182 | +* NEW: 4 chart types added — Radar, Stepped Line, Stacked Bar, Scatter (11 types total). | |
| 183 | +* NEW: Color pickers for chart colors. | |
| 184 | +* IMPROVED: Upgraded to the latest Chart.js v4, bundled locally in the plugin (no third-party URLs). | |
| 185 | +* IMPROVED: Modern front-end styling for the table view, dynamic filter form and datepicker. | |
| 186 | +* IMPROVED: Shortcode width/height attributes now work as documented; charts are responsive by default. | |
| 187 | +* IMPROVED: Forbidden-SQL check is now case-insensitive with word-boundary matching. | |
| 188 | +* LEGACY: Old 2.x charts work unchanged; deprecated Google-Chart types auto-migrate to modern equivalents on edit/save; the [gvn_schart] legacy shortcode is now served by the modern renderer. | |
| 189 | +* REMOVED: Google Charts options (deprecated since 2.x) and leftover internal debug code. | |
| 190 | + | |
| 191 | += 2.3.8= | |
| 192 | +* Security fix: Patched SQL injection vulnerability in Dynamic Filter Variables feature. User-supplied GET parameters are now properly sanitized using sanitize_text_field() and esc_sql(), and the () bypass loophole has been removed for user input. Credits: WPScan team for responsible disclosure. | |
| 193 | + | |
| 194 | += 2.3.7= | |
| 195 | +* Fixed small security issue | |
| 196 | +* More escape/sanitize related security improvements | |
| 197 | + | |
| 198 | += 2.3.5= | |
| 199 | + | |
| 200 | +* 2 bug fixes on chart rendering X axis values | |
| 201 | + | |
| 202 | += 2.3.4= | |
| 203 | + | |
| 204 | +* Backend improvements on existing features | |
| 205 | + | |
| 206 | += 2.3.3= | |
| 207 | + | |
| 208 | +* Fixed: PHP warning issue | |
| 209 | + | |
| 98 | 210 | = 2.3.2= |
| 99 | 211 | |
| 100 | 212 | * Added: Enable/disable Stackedness of bar charts |
| 101 | 213 | |
| @@ -122,9 +234,9 @@ | ||
| 122 | 234 | * Small improvements |
| 123 | 235 | |
| 124 | 236 | = 2.2.0= |
| 125 | 237 | |
| 126 | -* Added width-height support | |
| 238 | +* Added width-height support | |
| 127 | 239 | |
| 128 | 240 | * Added "Zero point" to line chart |
| 129 | 241 | |
| 130 | 242 | * Bugfixes |
