| @@ -1,35 +1,97 @@ | ||
| 1 | 1 | === SQL Chart Builder === |
| 2 | 2 | Contributors: elvinhaci |
| 3 | -Donate link: http://guaven.com/donate | |
| 4 | -Tags: graph,charts,sql charts,mysql charts,visualizer,sql visualizer,sql reports,mysql reports,google charts,google chart,mysql to chart,mysql to graph | |
| 5 | -Requires at least: 3.9.0 | |
| 6 | -Tested up to: 4.5.0 | |
| 7 | -Stable tag: 1.0.3 | |
| 3 | +Tags: charts,mysql,sql,visualizer,sql chart | |
| 4 | +Requires at least: 5.0.0 | |
| 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/ | |
| 8 | 8 | License: GPLv2 or later |
| 9 | 9 | License URI: http://www.gnu.org/licenses/gpl-2.0.html |
| 10 | 10 | |
| 11 | -Turn your SQL queries to Google Charts | |
| 11 | +Turn your SQL queries into beautiful dynamic charts with a visual SQL builder, live autocomplete and no-code date/input filters. | |
| 12 | 12 | |
| 13 | 13 | == Description == |
| 14 | -The plugin can create beautiful Google Charts based on your SQL queries, then you can use those charts in any part of your website: | |
| 15 | -1. Give any name to your report. | |
| 16 | -2. Use our preinstalled chart or create new one yourself: choose desired chart type, type sql query, | |
| 17 | -enter field names, labels and then press to Publish/Update | |
| 18 | -3. After update/save you will see needed shortcode below there. Then you can use that shortcode anywhere in your website: in pages, posts, widgets etc. | |
| 14 | +The plugin creates beautiful charts based on your SQL queries, then you can use those charts in any part of your website. | |
| 15 | +You can use both native wp and non-wp mysql tables in your queries. | |
| 19 | 16 | |
| 17 | +### 11 chart types: Pie, Doughnut, Polar Area, Radar, Line, Area, Stepped Line, Bar, Horizontal Bar, Stacked Bar, Scatter | |
| 20 | 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 | + | |
| 33 | += How to use = | |
| 34 | + | |
| 35 | +1. Go to Dashboard → My SQL Charts → Add New and give any name to your report. | |
| 36 | + | |
| 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. | |
| 38 | + | |
| 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. | |
| 40 | + | |
| 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). | |
| 42 | + | |
| 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. | |
| 44 | + | |
| 45 | +6. Check "Show table-view data below the graph" to also render the data as a styled table. | |
| 46 | + | |
| 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. | |
| 48 | + | |
| 49 | += Chart type mini-guide = | |
| 50 | + | |
| 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). | |
| 60 | + | |
| 61 | += Input filters (dynamic variables) = | |
| 62 | + | |
| 63 | +Use the visual rows in the Input Filters tab, or the raw format: | |
| 64 | + | |
| 65 | +variable_name~default_value~variable_label~variable_type | variable_name~default_value~variable_label~variable_type | |
| 66 | + | |
| 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 | |
| 72 | + | |
| 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. | |
| 76 | + | |
| 77 | +Supported built-in dynamic tags: {current_user_id}, {current_user_login}, {current_user_email}, {current_user_display_name}. | |
| 78 | + | |
| 79 | += Caching = | |
| 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 | + | |
| 21 | 83 | = Website = |
| 22 | -http://guaven.com/my-sql-charts/ | |
| 84 | +[https://guaven.com/my-sql-charts/](https://guaven.com/my-sql-charts/) | |
| 23 | 85 | |
| 24 | 86 | = Documentation = |
| 25 | -http://guaven.com/my-sql-charts/#docs | |
| 87 | +[https://guaven.com/my-sql-charts/#docs](https://guaven.com/my-sql-charts/#docs) | |
| 26 | 88 | |
| 27 | 89 | = Bug Submission and Forum Support = |
| 28 | -http://guaven.com/contact/ | |
| 90 | +[Contact Page](https://guaven.com/contact/solution-request/) | |
| 29 | 91 | |
| 30 | 92 | = Please Vote if you liked our plugin = |
| 31 | -Your votes really helps us. Thanks. | |
| 93 | +Your votes really help us. Thanks. | |
| 32 | 94 | |
| 33 | 95 | |
| 34 | 96 | == Installation == |
| 35 | 97 | |
| @@ -34,16 +96,27 @@ | ||
| 34 | 96 | == Installation == |
| 35 | 97 | |
| 36 | 98 | 1. Upload 'guaven_sqlcharts.zip' to the '/wp-content/plugins/' directory |
| 37 | 99 | 2. Unzip it. |
| 38 | -3. Go to Dashboard/Plugins and Activate the plugin. | |
| 39 | -4. Go to “Dashboard/My SQL Charts” to create new charts. You will also see howtouse guide texts there. | |
| 100 | +3. Go to Dashboard/Plugins and Activate the plugin. | |
| 101 | +4. Go to "Dashboard/My SQL Charts" to create new charts. Guides are built into the chart builder. | |
| 40 | 102 | |
| 41 | 103 | |
| 42 | 104 | |
| 43 | 105 | == Frequently Asked Questions == |
| 44 | 106 | |
| 107 | += Will my old charts keep working after updating to 3.0? = | |
| 45 | 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 | + | |
| 46 | 119 | == Screenshots == |
| 47 | 120 | |
| 48 | 121 | 1. Screenshot 1 |
| 49 | 122 | |
| @@ -52,15 +125,162 @@ | ||
| 52 | 125 | 3. Screenshot 3 |
| 53 | 126 | |
| 54 | 127 | 4. Screenshot 4 |
| 55 | 128 | |
| 129 | +5. Screenshot 5 | |
| 56 | 130 | |
| 131 | +== Available Filters == | |
| 132 | +apply_filters( 'guaven_sqlcharts_table_empty_cell'); | |
| 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 | + | |
| 57 | 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. | |
| 58 | 145 | |
| 59 | -= 1.0.3= | |
| 60 | -* One more new report added. + Some little fixes. | |
| 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. | |
| 61 | 149 | |
| 62 | -= 1.0.2= | |
| 63 | -* Added some screenshots | |
| 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. | |
| 64 | 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 | + | |
| 210 | += 2.3.2= | |
| 211 | + | |
| 212 | +* Added: Enable/disable Stackedness of bar charts | |
| 213 | + | |
| 214 | += 2.3.1= | |
| 215 | + | |
| 216 | +* Added: Enable/disable Legend section in Charts | |
| 217 | + | |
| 218 | += 2.3.0= | |
| 219 | + | |
| 220 | +* Added: One new chart - Polar Area | |
| 221 | + | |
| 222 | +* Added: New small feature - Round Y Axis tick values | |
| 223 | + | |
| 224 | +* Improvement: Hashing DB Remote password | |
| 225 | + | |
| 226 | += 2.2.2= | |
| 227 | + | |
| 228 | +* Added: Custom color support for PIE charts | |
| 229 | + | |
| 230 | +* Added: Insert custom chart parameters via Shortcode attributes | |
| 231 | + | |
| 232 | += 2.2.1= | |
| 233 | + | |
| 234 | +* Small improvements | |
| 235 | + | |
| 236 | += 2.2.0= | |
| 237 | + | |
| 238 | +* Added width-height support | |
| 239 | + | |
| 240 | +* Added "Zero point" to line chart | |
| 241 | + | |
| 242 | +* Bugfixes | |
| 243 | + | |
| 244 | += 2.1.2= | |
| 245 | + | |
| 246 | +* New feature: Remote Database Connection | |
| 247 | + | |
| 248 | +* Setting custom & fixed colors for charts | |
| 249 | + | |
| 250 | +* Small improvement in table-view component | |
| 251 | + | |
| 252 | += 2.1.1= | |
| 253 | + | |
| 254 | +* Fixed small bug in Area Charts | |
| 255 | + | |
| 256 | +* Chart library has been updated to the latest version | |
| 257 | + | |
| 258 | += 2.1.0= | |
| 259 | + | |
| 260 | +* Dynamic filters added: You can use dynamic date/number/text filters at frontend. | |
| 261 | + | |
| 262 | +* Table-view support added. | |
| 263 | + | |
| 264 | += 2.0.4= | |
| 265 | + | |
| 266 | +Now you can add custom arguments to the SQL query. | |
| 267 | + | |
| 268 | += 2.0.0= | |
| 269 | + | |
| 270 | +* New non-Google Local Charts added. | |
| 271 | + | |
| 272 | +* Use multiple mysql queries in one graph. | |
| 273 | + | |
| 65 | 274 | = 1.0.0= |
| 275 | + | |
| 66 | 276 | * Uploaded to Wordpress.org |
| 277 | + | |
| 278 | += 1.0.2= | |
| 279 | + | |
| 280 | +* Little fixes | |
| 281 | + | |
| 282 | += 1.0.5= | |
| 283 | + | |
| 284 | +* Added WP 4.7 compatibility | |
| 285 | + | |
| 286 | +* Fixed "multiple charts in one page" issue. | |
