{"id":183,"date":"2018-08-27T17:35:33","date_gmt":"2018-08-27T17:35:33","guid":{"rendered":"https:\/\/mwoodruff.net\/articles\/?p=183"},"modified":"2018-08-27T17:36:26","modified_gmt":"2018-08-27T17:36:26","slug":"time-saving-php-pdo-code-generator","status":"publish","type":"post","link":"https:\/\/mwoodruff.net\/articles\/automation\/time-saving-php-pdo-code-generator\/","title":{"rendered":"Time-saving PHP PDO Code Generator"},"content":{"rendered":"

There are certain tasks I end up doing for every project, like creating database inserts, selects, and updates. I hate the tedium and repetitive nature of copy-pasting, or find and replace operations. The more work I do for clients, the more I realize I’m doing similar work for each client. This is my first step towards automating repetitive tasks so I can focus more time on the fun parts of development, like UI design and problem-solving.<\/p>\n

I created a simple script that generates PHP PDO code for you with just the table name, id field name, and a comma-separated list of non-automated (i.e. columns that the database doesn’t generate for you, like timestamps and auto-increment IDs) DB columns. It generates INSERT, UPDATE, and SELECT functions with basic formatting from your inputs.<\/p>\n

The code is simple, and I’ve left out any styling or flair so could be adapted easily to your own style of code. Alternatively, you can use my form below to generate what you need for PHP using PDO to work with a MySQL database, and just alter or remove code that doesn’t work for you.<\/p>\n

Want to try the Code Generator? I’ve hosted it, so you can try it live: PHP PDO Code Generator<\/a><\/p>\n

Code note: Uncomment the “pre” tags in the code to make it work!<\/p>\n

\n
\r\nbindparam(\":' . $column .'\", $' . $column . '); ' . \"\\n\";\r\n\t}\r\n\t\/\/Create static string variables for text with reserved characters\r\n\t$ds_str = \"$\"; \/\/Dollar sign for use in the result\r\n\/\/-------------------------------------------------------------------------------------Display Insert Function\r\n$html = <<Insert Function for Table {$table_name}<\/h3>\r\n\r\npublic function insert{$uc_table_name}({$var_list})\r\n{\r\n         global {$ds_str}db;\r\n         try\r\n         {\r\n\t\t{$ds_str}stmt = {$ds_str}this->db->prepare(\"INSERT INTO {$table_name}({$col_list}) VALUES({$param_list})\");\r\n\r\n{$bind_list}\r\n\t\t{$ds_str}stmt->execute(); \r\n\r\n\t\t{$ds_str}{$table_name}_id = {$ds_str}this->db->lastInsertId();\r\n\r\n\t\treturn {$ds_str}{$table_name}_id;\r\n         }\r\n         catch(PDOException {$ds_str}e)\r\n         {\r\n             echo {$ds_str}e->getMessage();\r\n         }\r\n}\r\n\r\nEOD;\r\n\/\/-------------------------------------------------------------------------------------Display Update Function\r\n$html .= <<Update Function for Table {$table_name}<\/h3>\r\n\r\npublic function update{$uc_table_name}({$ds_str}id,{$var_list})\r\n{\r\n         global {$ds_str}db;\r\n         try\r\n         {\r\n\t\t{$ds_str}stmt = {$ds_str}this->db->prepare(\"UPDATE {$table_name} SET {$update_list} WHERE `{$id_column}`=:{$id_column}\");\r\n\r\n{$bind_list}\r\n\t\t{$ds_str}stmt->bindparam(\":{$id_column}\", {$ds_str}_id);\r\n\t\t{$ds_str}stmt->execute(); \r\n\r\n\t\t{$ds_str}{$table_name}_id = {$ds_str}this->db->lastInsertId();\r\n\r\n\t\treturn {$ds_str}{$table_name}_id;\r\n         }\r\n         catch(PDOException {$ds_str}e)\r\n         {\r\n             echo {$ds_str}e->getMessage();\r\n         }\r\n}\r\n\t\r\nEOD;\r\n\/\/-------------------------------------------------------------------------------------Display Select Function\r\n$html .= <<Select Function for Table {$table_name}<\/h3>\r\n\r\npublic function get{$uc_table_name}({$ds_str}id)\r\n{\r\n         global {$ds_str}db;\r\n         try\r\n         {\r\n\t\t{$ds_str}stmt = {$ds_str}this->db->prepare(\"SELECT `{$id_column}`,{$col_list} FROM {$table_name} WHERE `{$id_column}`=:{$id_column} LIMIT 1\");\r\n\r\n\t\t{$ds_str}stmt->bindparam(\":{$id_column}\", {$ds_str}_id);\r\n\t\t{$ds_str}stmt->execute(); \r\n\t\t{$ds_str}count = {$ds_str}stmt->rowCount();\r\n\t\tif({$ds_str}count>0){\r\n\t\t\t{$ds_str}rows = {$ds_str}stmt->fetchAll(PDO::FETCH_ASSOC);\r\n\t\t\treturn {$ds_str}rows[0];\r\n\t\t}else return false;\t\t \t\t \r\n\t\t\r\n         }\r\n         catch(PDOException {$ds_str}e)\r\n         {\r\n             echo {$ds_str}e->getMessage();\r\n         }\r\n}\r\n\r\nEOD;\r\necho $html;\r\n}else{?>\r\n<\/pre>\n
\r\n\r\n\r\n\r\n  \r\n  \r\n  MYSQL Project Scripter<\/title>\r\n  <meta name=\"description\" content=\"\">\r\n  <meta name=\"viewport\" content=\"width=device-width, initial-scale=1\">\r\n  <meta name=\"author\" content=\"Michelle Woodruff\">\r\n  <\/head>\r\n  <body>\r\n\t<form action=\"\" method=\"POST\">\r\n\t\t<input type=\"text\" name=\"table_name\" placeholder=\"Table Name\"\/>\r\n\t\t<input type=\"text\" name=\"id_column\" placeholder=\"ID Column Name\"\/>\r\n\t\t<textarea name=\"columns\" id=\"\" cols=\"30\" rows=\"10\" placeholder=\"Enter your Comma-separated list of columns WITHOUT automatic fields like incrementing IDs or timestamps.\"><\/textarea>\r\n\t\t<input type=\"submit\" value=\"Create PHP Functions\"\/>\r\n\t<\/form>\r\n  <\/body>\r\n<\/pre>\n<pre class=\"lang:php\">\r\n  <?php\r\n}\r\n\r\n?>\r\n<\/pre>\n<\/div>\n<p>I hope this saves you time and helps you focus on the more fun elements of development!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>There are certain tasks I end up doing for every project, like creating database inserts, selects, and updates. I hate the tedium and repetitive nature of copy-pasting, or find and replace operations. The more work I do for clients, the more I realize I’m doing similar work for each client. This is my first step … <\/p>\n<p class=\"link-more\"><a href=\"https:\/\/mwoodruff.net\/articles\/automation\/time-saving-php-pdo-code-generator\/\" class=\"more-link\">Continue reading<span class=\"screen-reader-text\"> “Time-saving PHP PDO Code Generator”<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[50],"tags":[53,51,52,10],"class_list":["post-183","post","type-post","status-publish","format-standard","hentry","category-automation","tag-code-generator","tag-mysql","tag-pdo","tag-php"],"_links":{"self":[{"href":"https:\/\/mwoodruff.net\/articles\/wp-json\/wp\/v2\/posts\/183","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/mwoodruff.net\/articles\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mwoodruff.net\/articles\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mwoodruff.net\/articles\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mwoodruff.net\/articles\/wp-json\/wp\/v2\/comments?post=183"}],"version-history":[{"count":14,"href":"https:\/\/mwoodruff.net\/articles\/wp-json\/wp\/v2\/posts\/183\/revisions"}],"predecessor-version":[{"id":239,"href":"https:\/\/mwoodruff.net\/articles\/wp-json\/wp\/v2\/posts\/183\/revisions\/239"}],"wp:attachment":[{"href":"https:\/\/mwoodruff.net\/articles\/wp-json\/wp\/v2\/media?parent=183"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mwoodruff.net\/articles\/wp-json\/wp\/v2\/categories?post=183"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mwoodruff.net\/articles\/wp-json\/wp\/v2\/tags?post=183"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}