Sunday, August 1, 2010

Generating PHP Table Objects from MySql

I needed to generate some PHP table objects for a couple MySql tables so wrote up the query below real quick. I am sure there are better ways to do this through IDE's, tools, or PHP. There will also be some kinks that can be worked out such as turning database fields such as create_date into getCreateDate instead of getCreate_date but this did the trick for me. It will generate class members, getters and setters.
SET @table = 'post';
SET @db = 'blog';

SELECT '<?php'

UNION ALL

SELECT
    CONCAT('class ',
             CONCAT(
                    UCASE(SUBSTRING(@table,1,1)),
                        SUBSTRING(@table,2)),
            '{')

UNION ALL

SELECT
    CONCAT('\tprotected $_', COLUMN_NAME, ';')
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    table_name = @table
    and table_schema = @db

UNION ALL

SELECT '\n'

UNION ALL

SELECT
    CONCAT('public function get',
        CONCAT(UCASE(SUBSTRING(COLUMN_NAME,1,1)),
                     SUBSTRING(COLUMN_NAME,2)),
        '() {',
        '\n\t return $this->_', COLUMN_NAME, ';\n','}' )
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    table_name = @table
    and table_schema = @db

UNION ALL

SELECT
    CONCAT('public function set',
        CONCAT(UCASE(SUBSTRING(COLUMN_NAME,1,1)),
                     SUBSTRING(COLUMN_NAME,2)),
            '($', COLUMN_NAME, '){',
        '\n\t$this->_',COLUMN_NAME, '= ',
            CASE
                WHEN data_type = 'smallint'
                  OR data_type = 'int'
                    THEN '(int)'
                WHEN data_type = 'varchar'
                  OR data_type = 'text'
                  OR data_type = 'enum'
                    THEN '(string)'
                ELSE ''
            END,
            ' $',COLUMN_NAME, ';',
            '\n\treturn $this;',
    '\n}' )
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    table_name = @table
    and table_schema = @db

UNION ALL

SELECT '}';
When used it will generate something like the following:
<?php

class Post {
    
    protected $_id;
    protected $_title;
    protected $_post;
 
    
    public function getId() {
        return $this->_id;
    }
    public function getTitle() {
        return $this->_title;
    }
    public function getPost() {
        return $this->_post;
    }
    
    public function setId($id) {
        $this->_id= (int) $id;
        return $this;
    }
    public function setTitle($title) {
        $this->_title= (string) $title;
        return $this;
    }
    public function setPost($post) {
        $this->_post= (string) $post;
        return $this;
    }
    
}

No comments:

Post a Comment