Menu
I am creating entity (of entity-view-controller)(in other words, model of MVC) classes which theoreticlly match the databse table I have. Is there a tool which reads mysql table and creates a model class code? (NOT on execution, a code output is required) I expect an output like
class{ public $columnname1; public $columnname2; public $columnname3; public $columnname4; public $columnname5; public $columnname6; function __construct(&$columnname1, &$columnname2){...} function insert(&$columnname1, &$columnname2){} function delete(&$columnname1){} ... } A tool which would also create insert,update and delete by id functions would help me a lot.
The tool may be free or paid.
390
PDO can fetch results into an object.
Design a class which matches your database/query structure, and use PDO::FETCH_INTO to fetch the result set into an already instantiated object. Misread the question, my bad.
To generate the class itself from the database structure, there are several projects (I haven't tested, but this came up on a very simple search).
65
The following code is what I've used for a long time to create PHP models for MySQL and DB2 tables. I've got stubs in place for MSSQL, PGSQL and SQLite but have never needed to complete them.
Here's the code generator class:
<?php /** * @license http://opensource.org/licenses/MIT The MIT License * @version 1.0.0_20130220000000 */ /** * This class will generate PHP source code for a "model" that interfaces with * a database table. * * @license http://opensource.org/licenses/MIT The MIT License * @version 1.0.0_20130220000000 */ class db_code_generator{ private $closing_tag; private $columns; private $database; private $host; private $password; private $port; private $table; private $type; private $username; /** * Constructor. By default we will try to connect to a MySQL database on * localhost. * * @param string $database The name of the database the user wants to connect * to. * * @param string $table The name of the table to generate code for. * * @param string $username The username we should use to connect to the * database. * * @param string $password The password we need to connect to the database. * * @param string $host The host or server we will try to connect to. If the * user doesn't give us a host we default to localhost. * * @param string $port The port we should try to connect to. Typically this * will not be passed so we default to NULL. * * @param string $type The type of database we are connecting to. Valid * values are: db2, mssql, mysql, pgsql, sqlite. */ public function __construct($database = NULL, $table = NULL, $username = NULL, $password = NULL, $host = 'localhost', $type = 'mysql', $port = NULL, $closing_tag = TRUE){ $this->database = $database; $this->table = $table; $this->username = $username; $this->password = $password; $this->host = $host; $this->port = $port; $this->type = $type; $this->closing_tag = $closing_tag; } /** * Generate the code for a model that represents a record in a table. * * @return string The PHP code generated for this model. */ public function get_code(){ $this->get_data_definition(); $code = $this->get_file_head(); $code .= $this->get_properties(); $code .= $this->get_ctor(); $code .= $this->get_dtor(); $code .= $this->get_method_stubs(); $code .= $this->get_file_foot(); return $code; } /** * Create the code needed for the __construct function. * * @return string The PHP code for the __construct function. */ private function get_ctor(){ $code = "\t/**\n"; $code .= "\t * Constructor.\n"; $code .= "\t *\n"; $code .= "\t * @param mixed \$id The unique id for a record in this table. Defaults to NULL\n"; if ('db2' === $this->type){ $code .= "\n\t * @param string \$library The library where the physical file resides. Defaults to LIBRARY\n"; } $code .= "\t *\n"; $code .= "\t * @see base_$this->type::__construct\n"; $code .= "\t */\n"; if ('db2' === $this->type){ $code .= "\tpublic function __construct(\$id = NULL, \$library = 'LIBRARY'){\n"; $code .= "\t\tparent::__construct(\$id, \$library);\n"; }else{ $code .= "\tpublic function __construct(\$id = NULL){\n"; $code .= "\t\tparent::__construct(\$id);\n"; } $code .= "\t}\n\n"; return $code; } /** * Connect to the requested database and get the data definition for the * requested table. */ private function get_data_definition(){ try{ switch ($this->type){ case 'db2': $this->get_data_definition_db2(); break; case 'mssql': $this->get_data_definition_mssql(); break; case 'mysql': $this->get_data_definition_mysql(); break; case 'pgsql': $this->get_data_definition_pgsql(); break; case 'sqlite': $this->get_data_definition_sqlite(); break; } }catch(PDOException $e){ } } /** * Get data definition information for a DB2 table. */ private function get_data_definition_db2(){ $con = new PDO("odbc:DRIVER={iSeries Access ODBC Driver};SYSTEM=$this->host;PROTOCOL=TCPIP", $this->username, $this->password); $sql = "SELECT COLUMN_NAME, COLUMN_SIZE, COLUMN_TEXT, DECIMAL_DIGITS, ORDINAL_POSITION, TYPE_NAME FROM SYSIBM.SQLCOLUMNS WHERE TABLE_SCHEM = '". strtoupper($this->database) ."' AND TABLE_NAME = '". strtoupper($this->table) ."'"; $statement = $con->prepare($sql); if ($statement->execute()){ while ($row = $statement->fetch()){ if (NULL !== $row['DECIMAL_DIGITS']){ $decimal = $row['DECIMAL_DIGITS']; }else{ $decimal = NULL; } if ('DECIMAL' === $row['TYPE_NAME'] && NULL !== $row['DECIMAL_DIGITS'] && '0' !== $row['DECIMAL_DIGITS']){ $type = 'float'; }else if ('DECIMAL' === $row['TYPE_NAME']){ $type = 'integer'; }else{ $type = strtolower($row['TYPE_NAME']); } if ('1' === $row['ORDINAL_POSITION']){ $key = 'PRI'; }else{ $key = NULL; } $this->columns[$row['COLUMN_NAME']] = array('allow_null' => TRUE, 'decimal' => $decimal, 'default' => NULL, 'extra' => NULL, 'key' => $key, 'length' => $row['COLUMN_SIZE'], 'name' => $row['COLUMN_NAME'], 'text' => $row['COLUMN_TEXT'], 'type' => $type); } ksort($this->columns); } } /** * Get data definition information for a MS SQL table. */ private function get_data_definition_mssql(){ return "The code for generating MS SQL models is not yet implemented.\n"; } /** * Get data definition information for a MySQL table. */ private function get_data_definition_mysql(){ $dsn = "mysql:host=$this->host;"; if (NULL !== $this->port){ $dsn .= "port=$this->port;"; } $dsn .= "dbname=$this->database"; $con = new PDO($dsn, $this->username, $this->password); $sql = "SHOW COLUMNS FROM $this->table"; $statement = $con->prepare($sql); if ($statement->execute()){ while ($row = $statement->fetch()){ $this->columns[$row['Field']] = array('allow_null' => $row['Null'], 'decimal' => NULL, 'default' => $row['Default'], 'extra' => $row['Extra'], 'key' => $row['Key'], 'length' => NULL, 'name' => $row['Field'], 'text' => NULL, 'type' => $row['Type']); } ksort($this->columns); } } /** * Get data definition information for a PostgreSQL table. */ private function get_data_definition_pgsql(){ return "The code for generating PostgreSQL models is not yet implemented.\n"; } /** * Get data definition information for a SQLite table. */ private function get_data_definition_sqlite(){ return "The code for generating SQLite models is not yet implemented.\n"; } /** * Create the code needed for the __destruct function. * * @return string The PHP code for the __destruct function. */ private function get_dtor(){ $code = "\t/**\n"; $code .= "\t * Destructor.\n"; $code .= "\t */\n"; $code .= "\tpublic function __destruct(){\n"; $code .= "\t\tparent::__destruct();\n"; $code .= "\t}\n\n"; return $code; } /** * Generate the code found at the end of the file - the closing brace, the * ending PHP tag and a new line. Some PHP programmers prefer to not have a * closing PHP tag while others want the closing tag and trailing newline - * it probably just depends on their programming background. Regardless it's * best to let everyone have things the way they want. */ private function get_file_foot(){ $code = ''; if ($this->closing_tag){ $code .= "}\n?>\n"; }else{ $code .= '}'; } return $code; } /** * Generate the code found at the beginning of the file - the PHPDocumentor * doc block, the require_once for the correct base class and the class name. * * @return string The code generated for the beginning of the file. */ private function get_file_head(){ $code = "<?php\n"; $code .= "/**\n"; $code .= " * Please enter a description of this class.\n"; $code .= " *\n"; $code .= " * @author XXX <[email protected]>\n"; $code .= " * @copyright Copyright (c) ". date('Y') ."\n"; $code .= " * @license http://www.gnu.org/licenses/gpl-3.0.html GPLv3\n"; $code .= " * @version ". date('Ymd') ."\n"; $code .= " */\n\n"; $code .= "require_once('base_$this->type.php');\n\n"; $code .= "class ". strtolower($this->table) ." extends base_$this->type{\n"; return $code; } /** * Generate the code for a delete method stub. * * @return string The PHP code for the method stub. */ private function get_method_stub_delete(){ $code = "\t/**\n"; $code .= "\t * Override the delete method found in the base class.\n"; $code .= "\t *\n"; $code .= "\t * @param mixed \$id The unique record ID to be deleted.\n"; $code .= "\t *\n"; $code .= "\t * @return bool TRUE if a record was successfully deleted from the table, FALSE otherwise.\n"; $code .= "\t */\n"; $code .= "\tpublic function delete(\$id){\n"; $code .= "\t\treturn parent::delete(\$id);\n"; $code .= "\t}\n\n"; return $code; } /** * Generate the code for an insert method stub. * * @return string The PHP code for the method stub. */ private function get_method_stub_insert(){ $code = "\t/**\n"; $code .= "\t * Override the insert method found in the base class.\n"; $code .= "\t *\n"; $code .= "\t * @param array \$parms An array of data, probably the \$_POST array.\n"; $code .= "\t * @param bool \$get_insert_id A flag indicating if we should get the autoincrement value of the record just created.\n"; $code .= "\t *\n"; $code .= "\t * @return bool TRUE if a record was successfully inserted into the table, FALSE otherwise.\n"; $code .= "\t */\n"; $code .= "\tpublic function insert(\$parms, \$get_insert_id = FALSE){\n"; $code .= "\t\treturn parent::insert(\$parms, \$get_insert_id);\n"; $code .= "\t}\n\n"; return $code; } /** * Generate the code for an update method stub. * * @return string The PHP code for the method stub. */ private function get_method_stub_update(){ $code = "\t/**\n"; $code .= "\t * Override the update method found in the base class.\n"; $code .= "\t *\n"; $code .= "\t * @param array &\$parms An array of key=>value pairs - most likely the \$_POST array.\n"; $code .= "\t *\n"; $code .= "\t * @param integer \$limit The number of records to update. Defaults to NULL.\n"; $code .= "\t *\n"; $code .= "\t * @return bool TRUE if a record was successfully updated, FALSE otherwise.\n"; $code .= "\t */\n"; $code .= "\tpublic function update(\$parms, \$limit = NULL){\n"; $code .= "\t\treturn parent::update(\$parms, \$limit);\n"; $code .= "\t}\n\n"; return $code; } /** * Create method stubs for create, delete and update. * * @return string The PHP code for these stubs. */ private function get_method_stubs(){ $code = $this->get_method_stub_delete(); $code .= $this->get_method_stub_insert(); $code .= $this->get_method_stub_update(); return $code; } private function get_properties(){ $code = ''; if (count($this->columns)){ foreach ($this->columns AS $index => $col){ $code .= "\t/**\n"; if (NULL !== $col['text']){ $code .= "\t * $col[text]\n"; }else{ $code .= "\t * Description\n"; } $code .= "\t * @var ". $col['type']; if (NULL !== $col['length']){ $code .= " ($col[length]"; if (NULL !== $col['decimal']){ $code .= ",$col[decimal]"; } $code .= ")"; } $code .= "\n\t */\n"; $temp_name = str_replace('#', '_', $col['name']); $code .= "\tpublic \$$temp_name;\n\n"; } } return $code; } } ?> and here's a simple page to use it:
<?php /** * @license GPLv3 (http://www.gnu.org/licenses/gpl-3.0.html) * @version 1.0.0_20130220000000 */ require_once('db_code_generator.php'); $table_type = array(); $table_type['db2'] = 'DB2/400 (db2)'; $table_type['mssql'] = 'Microsoft SQL Server (mssql)'; $table_type['mysql'] = 'MySQL (mysql)'; $table_type['pgsql'] = 'PostGRESQL (pgsql)'; $table_type['sqlite'] = 'SQLite (sqlite)'; $database = (isset($_POST['database'])) ? $_POST['database'] : 'my_database'; $host = (isset($_POST['host'])) ? $_POST['host'] : 'localhost'; $username = (isset($_POST['username'])) ? $_POST['username'] : 'root'; $password = (isset($_POST['password'])) ? $_POST['password'] : ''; $table = (isset($_POST['table'])) ? $_POST['table'] : ''; $type = (isset($_POST['type'])) ? $_POST['type'] : 'mysql'; $library = (isset($_POST['library'])) ? $_POST['library'] : 'LIBRARY'; $file = (isset($_POST['file'])) ? $_POST['file'] : 'STATES'; //--------------------------------------------------------------------------- ?> <div class="data_input"> <form action="" method="post"> <fieldset class="top"> <legend>Code Generator</legend> <label for="host">Hostname or IP: <input id="host" maxlength="32" name="host" tabindex="<?php echo $tabindex++; ?>" title="Enter the database host name" type="text" value="<?php echo $host; ?>" /> </label> <br /> <label for="username">Username: <input id="username" maxlength="32" name="username" tabindex="<?php echo $tabindex++; ?>" title="Enter the database username" type="text" value="<?php echo $username; ?>" /> </label> <br /> <label for="password">Password: <input id="password" maxlength="32" name="password" tabindex="<?php echo $tabindex++; ?>" title="Enter the database password" type="password" value="<?php echo $password; ?>" /> </label> <br /> <label for="type">Type: <select id="type" name="type" tabindex="<?php echo $tabindex++; ?>"> <?php foreach ($table_type AS $key=>$value){ echo('<option '); if ($key == $type){ echo 'selected="selected" '; } echo 'value="'. $key .'">'. $value .'</option>'; } ?> </select> </label> <br /> </fieldset> <fieldset class="top"> <legend>PostGRESQL/MSSQL/MySQL Parameters</legend> <label for="database">Database: <input id="database" maxlength="100" name="database" tabindex="<?php echo $tabindex++; ?>" title="Enter the database name" type="text" value="<?php echo $database; ?>" /> </label> <br /> <label for="table">Table: <input id="table" maxlength="100" name="table" tabindex="<?php echo $tabindex++; ?>" title="Enter the table name" type="text" value="<?php echo $table; ?>" /> </label> <br /> </fieldset> <fieldset class="top"> <legend>DB2 Parameters</legend> <label for="library">Library: <input id="library" maxlength="10" name="library" tabindex="<?php echo $tabindex++; ?>" title="Enter the library name" type="text" value="<?php echo $library; ?>" /> </label> <br /> <label for="file">Physical File: <input id="file" maxlength="10" name="file" tabindex="<?php echo $tabindex++; ?>" title="Enter the file name" type="text" value="<?php echo $file; ?>" /> </label> <br /> </fieldset> <fieldset class="bottom"> <button tabindex="<?php echo $tabindex++; ?>" type="submit">Generate!</button> </fieldset> </form> </div> <?php if (isset($_POST['host'])){ if ('db2' == $_POST['type']){ $_POST['database'] = strtoupper($_POST['library']); // Library $_POST['table'] = strtoupper($_POST['file']); // Physical file $_POST['host'] = 'db2_host'; $_POST['username'] = 'db2_username'; $_POST['password'] = 'db2_password'; } $object = new db_code_generator($_POST['database'], $_POST['table'], $_POST['username'], $_POST['password'], $_POST['host'], $_POST['type']); echo('<textarea rows="75" style="margin-left : 50px; width : 90%;" onfocus="select()">'. $object->get_code() .'</textarea>'); } ?> If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
