| 1 |
=== SQL Chart Builder === |
| 2 |
Contributors: elvinhaci |
| 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.5 |
| 7 |
Donate link: https://guaven.com/service/small-thankyou-premium-support-service/ |
| 8 |
License: GPLv2 or later |
| 9 |
License URI: http://www.gnu.org/licenses/gpl-2.0.html |
| 10 |
|
| 11 |
Turn your SQL queries into beautiful dynamic charts with a visual SQL builder, live autocomplete and no-code date/input filters. |
| 12 |
|
| 13 |
== Description == |
| 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. |
| 16 |
|
| 17 |
### 11 chart types: Pie, Doughnut, Polar Area, Radar, Line, Area, Stepped Line, Bar, Horizontal Bar, Stacked Bar, Scatter |
| 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 |
|
| 83 |
= Website = |
| 84 |
[https://guaven.com/my-sql-charts/](https://guaven.com/my-sql-charts/) |
| 85 |
|
| 86 |
= Documentation = |
| 87 |
[https://guaven.com/my-sql-charts/#docs](https://guaven.com/my-sql-charts/#docs) |
| 88 |
|
| 89 |
= Bug Submission and Forum Support = |
| 90 |
[Contact Page](https://guaven.com/contact/solution-request/) |
| 91 |
|
| 92 |
= Please Vote if you liked our plugin = |
| 93 |
Your votes really help us. Thanks. |
| 94 |
|
| 95 |
|
| 96 |
== Installation == |
| 97 |
|
| 98 |
1. Upload 'guaven_sqlcharts.zip' to the '/wp-content/plugins/' directory |
| 99 |
2. Unzip it. |
| 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. |
| 102 |
|
| 103 |
|
| 104 |
|
| 105 |
== Frequently Asked Questions == |
| 106 |
|
| 107 |
= Will my old charts keep working after updating to 3.0? = |
| 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 |
|
| 119 |
== Screenshots == |
| 120 |
|
| 121 |
1. Screenshot 1 |
| 122 |
|
| 123 |
2. Screenshot 2 |
| 124 |
|
| 125 |
3. Screenshot 3 |
| 126 |
|
| 127 |
4. Screenshot 4 |
| 128 |
|
| 129 |
5. Screenshot 5 |
| 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 |
|
| 140 |
== Changelog == |
| 141 |
= 3.0.5 = |
| 142 |
* 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. |
| 143 |
* FIX: Date/time X axis: the axis now labels only the dates present in the data instead of evenly spaced computed dates. |
| 144 |
|
| 145 |
= 3.0.4 = |
| 146 |
* 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. |
| 147 |
* 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. |
| 148 |
* 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. |
| 149 |
* SECURITY: The shortcode "params" attribute is validated and can no longer inject JavaScript into the chart script. |
| 150 |
* The chart save handler now also requires manage_options and the gvn_schart post type. |
| 151 |
|
| 152 |
= 3.0.3 = |
| 153 |
* 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. |
| 154 |
* 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. |
| 155 |
* 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. |
| 156 |
* 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. |
| 157 |
|
| 158 |
= 3.0.2 = |
| 159 |
* FIX: "Value labels" option now works for Bar, Horizontal Bar, Stacked Bar, Line, Area, Stepped Line, Radar and Scatter charts (previously only circular charts). |
| 160 |
* 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). |
| 161 |
* 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. |
| 162 |
* IMPROVED: Clearer hints in the chart builder for the label, color and value-label fields. |
| 163 |
|
| 164 |
= 3.0.1 = |
| 165 |
* 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. |
| 166 |
* Tested up to WordPress 7.1.2. |
| 167 |
|
| 168 |
= 3.0.0= |
| 169 |
* NEW: Completely redesigned admin chart builder — tabbed UI (Chart & Data / Dynamic Filters / Style / Advanced) with visual chart-type cards. |
| 170 |
* 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). |
| 171 |
* 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. |
| 172 |
* 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). |
| 173 |
* NEW: X/Y column mapping via dropdowns auto-filled from the columns detected in your SQL query. |
| 174 |
* NEW: Fixed professional default color palette (Tableau 10) replaces random colors; override via the guaven_sqlcharts_default_palette filter. |
| 175 |
* IMPROVED: Proper "Chart" admin labels and menu icon for the chart post type (no more generic "Post" wording). |
| 176 |
* NEW: Built-in per-chart-type usage guides with one-click example queries. |
| 177 |
* NEW: 4 chart types added — Radar, Stepped Line, Stacked Bar, Scatter (11 types total). |
| 178 |
* NEW: Color pickers for chart colors. |
| 179 |
* IMPROVED: Upgraded to the latest Chart.js v4, bundled locally in the plugin (no third-party URLs). |
| 180 |
* IMPROVED: Modern front-end styling for the table view, dynamic filter form and datepicker. |
| 181 |
* IMPROVED: Shortcode width/height attributes now work as documented; charts are responsive by default. |
| 182 |
* IMPROVED: Forbidden-SQL check is now case-insensitive with word-boundary matching. |
| 183 |
* 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. |
| 184 |
* REMOVED: Google Charts options (deprecated since 2.x) and leftover internal debug code. |
| 185 |
|
| 186 |
= 2.3.8= |
| 187 |
* 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. |
| 188 |
|
| 189 |
= 2.3.7= |
| 190 |
* Fixed small security issue |
| 191 |
* More escape/sanitize related security improvements |
| 192 |
|
| 193 |
= 2.3.5= |
| 194 |
|
| 195 |
* 2 bug fixes on chart rendering X axis values |
| 196 |
|
| 197 |
= 2.3.4= |
| 198 |
|
| 199 |
* Backend improvements on existing features |
| 200 |
|
| 201 |
= 2.3.3= |
| 202 |
|
| 203 |
* Fixed: PHP warning issue |
| 204 |
|
| 205 |
= 2.3.2= |
| 206 |
|
| 207 |
* Added: Enable/disable Stackedness of bar charts |
| 208 |
|
| 209 |
= 2.3.1= |
| 210 |
|
| 211 |
* Added: Enable/disable Legend section in Charts |
| 212 |
|
| 213 |
= 2.3.0= |
| 214 |
|
| 215 |
* Added: One new chart - Polar Area |
| 216 |
|
| 217 |
* Added: New small feature - Round Y Axis tick values |
| 218 |
|
| 219 |
* Improvement: Hashing DB Remote password |
| 220 |
|
| 221 |
= 2.2.2= |
| 222 |
|
| 223 |
* Added: Custom color support for PIE charts |
| 224 |
|
| 225 |
* Added: Insert custom chart parameters via Shortcode attributes |
| 226 |
|
| 227 |
= 2.2.1= |
| 228 |
|
| 229 |
* Small improvements |
| 230 |
|
| 231 |
= 2.2.0= |
| 232 |
|
| 233 |
* Added width-height support |
| 234 |
|
| 235 |
* Added "Zero point" to line chart |
| 236 |
|
| 237 |
* Bugfixes |
| 238 |
|
| 239 |
= 2.1.2= |
| 240 |
|
| 241 |
* New feature: Remote Database Connection |
| 242 |
|
| 243 |
* Setting custom & fixed colors for charts |
| 244 |
|
| 245 |
* Small improvement in table-view component |
| 246 |
|
| 247 |
= 2.1.1= |
| 248 |
|
| 249 |
* Fixed small bug in Area Charts |
| 250 |
|
| 251 |
* Chart library has been updated to the latest version |
| 252 |
|
| 253 |
= 2.1.0= |
| 254 |
|
| 255 |
* Dynamic filters added: You can use dynamic date/number/text filters at frontend. |
| 256 |
|
| 257 |
* Table-view support added. |
| 258 |
|
| 259 |
= 2.0.4= |
| 260 |
|
| 261 |
Now you can add custom arguments to the SQL query. |
| 262 |
|
| 263 |
= 2.0.0= |
| 264 |
|
| 265 |
* New non-Google Local Charts added. |
| 266 |
|
| 267 |
* Use multiple mysql queries in one graph. |
| 268 |
|
| 269 |
= 1.0.0= |
| 270 |
|
| 271 |
* Uploaded to Wordpress.org |
| 272 |
|
| 273 |
= 1.0.2= |
| 274 |
|
| 275 |
* Little fixes |
| 276 |
|
| 277 |
= 1.0.5= |
| 278 |
|
| 279 |
* Added WP 4.7 compatibility |
| 280 |
|
| 281 |
* Fixed "multiple charts in one page" issue. |
| 282 |
|
