{"id":135,"date":"2013-03-21T11:20:27","date_gmt":"2013-03-21T13:20:27","guid":{"rendered":"http:\/\/blog.iordanov.info\/?p=135"},"modified":"2024-02-13T11:10:37","modified_gmt":"2024-02-13T13:10:37","slug":"google-visualization-datatable-php-helper-class","status":"publish","type":"post","link":"https:\/\/blog.iordanov.info\/?p=135","title":{"rendered":"google.visualization.DataTable php helper class"},"content":{"rendered":"<p><a href=\"https:\/\/blog.iordanov.info\/wp-content\/uploads\/2013\/03\/Montana_bg.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"alignleft size-medium wp-image-158\" alt=\"Montana_bg\" src=\"https:\/\/blog.iordanov.info\/wp-content\/uploads\/2013\/03\/Montana_bg-300x225.jpg\" width=\"300\" height=\"225\" srcset=\"https:\/\/blog.iordanov.info\/wp-content\/uploads\/2013\/03\/Montana_bg-300x225.jpg 300w, https:\/\/blog.iordanov.info\/wp-content\/uploads\/2013\/03\/Montana_bg.jpg 800w\" sizes=\"auto, (max-width: 300px) 100vw, 300px\" \/><\/a><br \/>\nIn this article I would like to present a helper class that I use in my php project <a href=\"http:\/\/homebook.etzaro.com\" target=\"_blank\" rel=\"noopener\"><span style=\"color: yellowgreen;\">Home<\/span><span style=\"color: #6dc6e7;\">Book<\/span><\/a>. I am very\u00a0happy\u00a0using it and I would like to share it with You. Everybody is free to use and modify it.<\/p>\n<p>I like <a title=\"Google Chart Tools\" href=\"https:\/\/developers.google.com\/chart\/interactive\/docs\/index\" target=\"_blank\" rel=\"noopener\">&#8220;Google Chart Tools&#8221;<\/a>\u00a0and I use them often in my web applications. To\u00a0achieve\u00a0good\u00a0performance of my work and readable code I\u00a0often\u00a0have to create helpers\u00a0classes\u00a0and methods\u00a0 that automate request and page generation. This is I suppose a common practice for many developers.<\/p>\n<h3>google.visualization.DataTable<\/h3>\n<p>All \u00a0<a title=\"Google Chart Tools\" href=\"https:\/\/developers.google.com\/chart\/interactive\/docs\/index\" target=\"_blank\" rel=\"noopener\">&#8220;Google Chart&#8221;<\/a>\u00a0are populated with data using a common JavaScript\u00a0<a href=\"https:\/\/developers.google.com\/chart\/interactive\/docs\/reference#DataTable\">DataTable<\/a>\u00a0class. Having a common data structure makes it easy to switch between chart types. This class exposes methods for sorting, modifying, and filtering data. There is a very good description of this class in <a href=\"https:\/\/developers.google.com\/chart\/interactive\/docs\/reference#DataTable\" target=\"_blank\" rel=\"noopener\">Google Visualization API Reference<\/a>.<\/p>\n<h3>GoogleVisualizationDataTable<\/h3>\n<p>This is the helper class\u00a0that I use in my php project\u00a0<a href=\"http:\/\/homebook.etzaro.com\" target=\"_blank\" rel=\"noopener\"><span style=\"color: yellowgreen;\">Home<\/span><span style=\"color: #6dc6e7;\">Book<\/span><\/a> to create and populate a <i>google.visualization.DataTable<\/i> using php. It provides a JSON string of the class instance, that can be used in all <a title=\"Google Chart Tools\" href=\"https:\/\/developers.google.com\/chart\/interactive\/docs\/index\" target=\"_blank\" rel=\"noopener\">&#8220;Google Chart Tools&#8221;<\/a>.\u00a0The source code of the class can be found in the resource link at the end of this article. It is very simple and there is no need of many explanation. If you have questions, I will be happy to respond to them in the comments. Here I would like to present a sample usage af this class.<\/p>\n<h3>Example usage of GoogleVisualizationDataTable.class.php<\/h3>\n<p>Define a class that extend GoogleVisualizationDataTable (in the example GVDTUsers class).<\/p>\n<p>In the class constructor setup:<\/p>\n<ul>\n<li>columns<\/li>\n<li>SQL query<\/li>\n<li>query parameters<\/li>\n<\/ul>\n<pre>class GVDTUsers extends GoogleVisualizationDataTable {\n\n    public function __construct($user_bank_id) {\n\n        $this-&gt;cols = array();\n\n        $this-&gt;cols[] = new GVColumn(\"id\", \"ID\", GVColumnType::STRING);\n        $this-&gt;cols[] = new GVColumn(\"name\", \"Name\", GVColumnType::STRING);\n        $this-&gt;cols[] = new GVColumn(\"birthday\", \"Birthday\", GVColumnType::DATE);\n        $this-&gt;cols[] = new GVColumn(\"is_admin\", \"Admin\", GVColumnType::BOOL);\n        $this-&gt;cols[] = new GVColumn(\"loan\", \"Loan\", GVColumnType::NUMBER);\n\n        $this-&gt;setSQL(\"SELECT \n                        id, \n                        name, \n                        \".GoogleVisualizationDataTable::GTDateSelectStr(\"birthday\").\", \n                        is_admin, \n                        loan\n                    FROM user\n                    WHERE loan&gt;0 AND  user_bank_id=?\n                    ORDER BY loan DESC\"); \n\n        $this-&gt;setBoundParamsArr(array('i', $user_bank_id));\n\n        $this-&gt;rows = array();\n    }\n\n}<\/pre>\n<p>In the page where you would like to use data create class instance width\u00a0suitable\u00a0parameters, call <em>GetData()<\/em>\u00a0metod to\u00a0retrieve\u00a0data and\u00a0finally\u00a0call <em>toJSON() <\/em>method that returns a JSON representation of the\u00a0DataTable\u00a0that can be passed to the\u00a0DataTable\u00a0<a href=\"https:\/\/developers.google.com\/chart\/interactive\/docs\/reference#DataTable_literal_constructor_example\">constructor<\/a>. For example:<\/p>\n<pre>$dt = new GVDTUsers($current_bank-&gt;getId());\n$dt-&gt;GetData();\n$dataJson = $dt-&gt;toJSON();\n\n&lt;script type=\"text\/javascript\"&gt;\n....\nvar userDataTableJS = new google.visualization.DataTable(&lt; php echo $dataJson &gt;);\nvar chart = new google.visualization.SteppedAreaChart(document.getElementById('chart_user_div'));\nvar chartOptionsPH = {...};\nchart.draw(userDataTableJS, chartOptionsPH);\u00a0\n....\n&lt;\/script&gt;<\/pre>\n<p>Important! If you have date\/datetime fileld, as in the example, 2 additional rows of code are needed to\u00a0remove <em><strong>&#8220;<\/strong><\/em> in JSON date field value.<\/p>\n<pre>$dataJson = preg_replace('\/\\\"new\/', 'new', $dataJson);\n$dataJson = preg_replace('\/\\)\\\"\/', ')', $dataJson);<\/pre>\n<p>That&#8217;s it!<\/p>\n<p>Here is a\u00a0promised <a title=\"GoogleVisualizationDataTable.class.php.zip\" href=\"http:\/\/homebook.etzaro.com\/upload\/GoogleVisualizationDataTable.class.php.zip\">source file GoogleVisualizationDataTable.class.php.zip<\/a>.<\/p>\n<p>Note: I am using a common class <em>Connect<\/em> that implements all required functionality for\u00a0database connection and data retreeval. In my case using mysqli for MySQL database.\u00a0<em id=\"__mceDel\"><\/em><em id=\"__mceDel\"><\/em><em id=\"__mceDel\">The class is common and different similar type of implementations can be found on the web.\u00a0<\/em><\/p>\n","protected":false},"excerpt":{"rendered":"<p>In this article I would like to present a helper class that I use in my php project HomeBook. I am very\u00a0happy\u00a0using it and I would like to share it with You. Everybody is free to use and modify it. &hellip;<\/p>\n<p class=\"read-more\"><a href=\"https:\/\/blog.iordanov.info\/?p=135\">Read more &raquo;<\/a><\/p>\n","protected":false},"author":1,"featured_media":164,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_hide_featured_image":false,"_jetpack_featured_image_duplicate_dismissed":0,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[32,25,31],"tags":[33,56,17,23,55],"class_list":["post-135","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-google-visualization","category-php","category-javascript","tag-google-visualization","tag-google-visualization-api","tag-html","tag-javascript","tag-php"],"jetpack_publicize_connections":[],"jetpack_shortlink":"https:\/\/wp.me\/p3jsZJ-2b","jetpack_sharing_enabled":true,"jetpack_likes_enabled":true,"jetpack_featured_media_url":"https:\/\/blog.iordanov.info\/wp-content\/uploads\/2013\/03\/images-11.jpeg","_links":{"self":[{"href":"https:\/\/blog.iordanov.info\/index.php?rest_route=\/wp\/v2\/posts\/135","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.iordanov.info\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.iordanov.info\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.iordanov.info\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.iordanov.info\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=135"}],"version-history":[{"count":25,"href":"https:\/\/blog.iordanov.info\/index.php?rest_route=\/wp\/v2\/posts\/135\/revisions"}],"predecessor-version":[{"id":365,"href":"https:\/\/blog.iordanov.info\/index.php?rest_route=\/wp\/v2\/posts\/135\/revisions\/365"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.iordanov.info\/index.php?rest_route=\/wp\/v2\/media\/164"}],"wp:attachment":[{"href":"https:\/\/blog.iordanov.info\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=135"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.iordanov.info\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=135"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.iordanov.info\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=135"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}