Do we set it to blank or keep the existing value for this field?<\/em>). I decided to try and create a new PHP class based on the RoR active records approach.<\/p>\nA strange approach to some maybe, but I decided to write the code to use the class first and then tried to get the class functioning around it. Here is the code…<\/p>\n
\n\n<?php\n\nclass data_access_test {\n\n public function index() {\n $ao = new DataAccess('my_database','user');\n print $ao->find('1')->first_name(); # Expected output dave\n }\n\n}\n\n?>\n<\/code>\n<\/pre>\nOK, so in case you are not clear on what we are saying here. We start by initialising the class with 2 variables. The first is the name of the database and the second is the table we wish to access. We then call the find<\/strong> method and pass a specific id of a row in the table. By chaining the methods we are also able to call the first_name<\/strong> method which without a variable assumes we wish to get (if we passed a variable it would assume we wish to set). OK, so now it’s time to get started on the class itself.<\/p>\n\n\nprivate $_dbl; # Your database class to actually talk to the DB\nprivate $_database; # User defined var for the DB we need to look at\nprivate $_table; # User defined var for the table we need to look at\nprivate $_internal_errors = array(); # Store any errors in setting up object\nprivate $_primary_key_fields = array(); # Primary key holder for $database.$table\nprivate $_set_primary_key = false; # False by default, if 2 fields create key then we set to true\nprivate $_fields; # Holder of the available fields in our table\nprivate $_type; # As above, see desc query on your table for example of values\nprivate $_null; # See Above\nprivate $_key; # See Above\nprivate $_default; # See Above\nprivate $_extra; # See Above\nprivate $_row_data; # Holder of all of this row's data\n<\/code>\n<\/pre>\nTo start with we define all of the variables that our class will need to function correctly. Read the comments for more detail on what each variable will be used for. Now, for the nitty-gritty of the object which will happen at the point of initialisation.<\/p>\n
\n\n\/*\n * Set globals to define host user and password\n *\/\npublic function __construct($database = '', $table = '') {\n $this->dbl = new Database;\n\n if ($database != '' && $table != '') {\n $this->_database = $database;\n $this->_table = $table;\n\n if (!$this->setTableData()) {\n $this->_internal_errors[] = 'Database and table not recognised';\n }\n } else {\n $this->_internal_errors[] = 'Require database and table at initialisation';\n }\n}\n\n\/*\n * General\n *\/\nprivate function setTableData() {\n $sql = \"SHOW COLUMNS FROM \" . $this->dbl->mysqlEscape($this->_database) . \".\" . $this->dbl->mysqlEscape($this->_table);\n\n list(\n $this->_fields,\n $this->_type,\n $this->_null,\n $this->_key,\n $this->_default,\n $this->_extra\n ) = $this->dbl->returnRows($sql);\n\n if ($this->_fields) {\n $primary_count = 0;\n\n \/\/ Set the primary keys\n foreach ($this->_fields as $key => $value) {\n if ($this->_key[$key] == 'PRI') {\n $this->_primary_key_fields[] = $value;\n $primary_count++;\n }\n }\n\n if ($primary_count > 2 || !in_array('auto_increment', $this->_extra)) {\n $this->_set_primary_key = true;\n }\n\n return 1;\n }\n}\n<\/code>\n<\/pre>\nThe construct of the class sets up our database object so that we can talk to the database. We check that the user has passed the 2 required variables, if they haven’t we assign an error message to our internal errors variable. I find it useful to have an internal errors variable that way if there are any issues you can simply check this variable to diagnose the problem. If we have both the database<\/strong> and table<\/strong> we can go about setting up our object, this is all done in the setTableData<\/strong> method.<\/p>\nThis method begins by doing a DESC query on the table. We use the return variables to set our fields<\/strong>, type<\/strong>, null<\/strong>, key<\/strong>, default,<\/strong> and extra<\/strong> properties. The final thing this method does is check the primary keys. I had a number of problems with my original DAO files when I tried to generate the files for a table with multiple primary keys. By setting our primary_key_fields<\/strong> variable we can make a decision as to whether to allow the user to set and get this variable. The decision I’ve made is you cannot set the primary field if there is only 1 primary key. If there are more primary keys then I set my set_primary_key<\/strong> flag to true to indicate that we can allow this field setting.<\/p>\nSo how do we set and get variables on the fly when we don’t have the methods already set? Well, we can do this by using the __call<\/strong> method.<\/p>\n\n\n\/*\n * Use name to check what field we are setting or getting\n *\/\npublic function __call($name = '', $arguments = '') {\n \/\/ Only proceed if there are no internal errors\n if (count($this->_internal_errors) == 0) {\n \/\/ Check that this is a valid field\n if (in_array($name, $this->_fields)) {\n \/\/ We have an argument so set, otherwise get\n if (!empty($arguments[0])) {\n if (!in_array($name, $this->_primary_key_fields) || $this->_set_primary_key === true) {\n if ($this->checkFieldDateType($name, $arguments[0]) == 1) {\n $this->setData($name, $arguments[0]);\n } else {\n $this->_internal_errors[] = $name . ' cannot be set as it fails the type check';\n }\n } else {\n $this->_internal_errors[] = $name . ' cannot be set as it\\'s a primary key';\n }\n } else {\n return $this->getData($name);\n }\n } else {\n $this->_internal_errors[] = $name . ' is not a recognised field in this table';\n }\n }\n}\n<\/code>\n<\/pre>\nI’ll now talk you through this method and then show you the get<\/strong> and set<\/strong> methods. The first thing this method does is to check the internal_errors<\/strong> property, we don’t want to try and set something if we’ve had a problem setting up the object. We then check to see if an argument has been passed, if it has then we can assume the user wants to set a variable. We check whether the method name is a primary key and whether we can set it. If allowed we finally check the argument against the data type set for this field (I won’t go into detail about this method but it’s a good way to ensure data integrity by ensuring it matches the type of data acceptable for that field) and then pass to our set<\/strong> method. If there are no arguments then we simply send it to the get<\/strong> method.<\/p>\n\n\nprivate function getData($key = '') {\n return $this->_row_data[$key];\n}\n\n\/*\n * Setters\n *\/\nprivate function setData($key = '', $value = '') {\n $this->_row_data[$key] = $value;\n return $this;\n}\n<\/code>\n<\/pre>\nFairly straightforward forward huh? Our set<\/strong> method simply uses the name<\/strong> passed to the __call<\/strong> method (aka table field) as the key for our internal variable _row_data<\/strong>. The return $this<\/strong> allows us to chain methods in this object. The get<\/strong> method is even more straightforward and just returns the value currently assigned to this key (aka table field). Ok, so if we look back at our original code we still need to define our find<\/strong> method.<\/p>\n\n\n\/*\n * Find record using unique id\n *\/\npublic function find($record_id = '') {\n if ($record_id != '' && count($this->_primary_key_fields) == 1) {\n $sql = \"SELECT * \n FROM \" . $this->dbl->mysqlEscape($this->_database) . \".\" . $this->dbl->mysqlEscape($this->_table) . \" \n WHERE \" . $this->_primary_key_fields[0] . \" = '\" . $record_id . \"'\";\n\n $results = $this->dbl->returnRows($sql, 'non_list');\n\n foreach ($results[0] as $key => $value) {\n if (!is_numeric($key)) {\n $this->_row_data[$key] = $value;\n }\n }\n }\n return $this;\n}\n<\/code>\n<\/pre>\nOur find<\/strong> method checks that the record id is available and that there is only 1 primary key. A query is then generated to return all of the information in that table row. The columns of the rows are used as keys in our internal _row_data<\/strong> variable and the value is the cell data. When we now call a get<\/strong> on any of the columns we will get the data we require…bingo!<\/p>\nMy class also has methods for creating new rows and updating rows. If you’ve come this far I’m sure you can figure these out for yourself. This class is far from perfect but it’s a good start. One of the problems you may have already noted is what if we want to set an empty value? Also what if we want to return multiple rows? If you have any ideas on it please share them with me \ud83d\ude42<\/p>\n","protected":false},"excerpt":{"rendered":"
As a developer, you can become a little complacent with the framework and language you’re using. You’ve invested a lot of time in getting yourself up and running so why look at other solutions to problems you’ve already solved? Well, there is more than 1 way to skin a cat, and the other methods out […]<\/p>\n","protected":false},"author":1,"featured_media":373,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8],"tags":[25,27,98,109,124,309,313,326,350,353,372],"class_list":["post-346","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-php","tag-access","tag-active","tag-classes","tag-conversion","tag-data","tag-objects","tag-on","tag-php","tag-rails","tag-record","tag-ruby"],"_links":{"self":[{"href":"https:\/\/blog.nickbennett.co.uk\/index.php\/wp-json\/wp\/v2\/posts\/346","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.nickbennett.co.uk\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.nickbennett.co.uk\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.nickbennett.co.uk\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.nickbennett.co.uk\/index.php\/wp-json\/wp\/v2\/comments?post=346"}],"version-history":[{"count":4,"href":"https:\/\/blog.nickbennett.co.uk\/index.php\/wp-json\/wp\/v2\/posts\/346\/revisions"}],"predecessor-version":[{"id":1453,"href":"https:\/\/blog.nickbennett.co.uk\/index.php\/wp-json\/wp\/v2\/posts\/346\/revisions\/1453"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.nickbennett.co.uk\/index.php\/wp-json\/"}],"wp:attachment":[{"href":"https:\/\/blog.nickbennett.co.uk\/index.php\/wp-json\/wp\/v2\/media?parent=346"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.nickbennett.co.uk\/index.php\/wp-json\/wp\/v2\/categories?post=346"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.nickbennett.co.uk\/index.php\/wp-json\/wp\/v2\/tags?post=346"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}