Perform CRUD operations on MySQL table records

by Anthony Amolochitis package author 325 - 4 months ago (2015-10-09) Comment

I believe this ORM builder will help you with your crud operations. What it does is build a basic php library. You set the database, username, and password. Then run the script in your browser once. The library will be built in the local directory of the program. It will build a class for the field name, data row object, and the basic query strings needed up front. Your code will run fast too since there will be no looping each time a query is needed. Just access it immediately.

A sample database named test with a table called tester.

-- Table structure for table tester

CREATE TABLE IF NOT EXISTS tester ( uid int(10) NOT NULL AUTO_INCREMENT COMMENT 'Primary Key', someVarChar varchar(45) NOT NULL COMMENT 'Var Char Field', someIntField int(20) NOT NULL COMMENT 'Integer Field', PRIMARY KEY (uid) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Table comments for testing' AUTO_INCREMENT=1 ;

After running the script, this is the code it will generate.

Filename : TestLibrary.php

Output would be :

<?php //<editor-fold defaultstate="collapsed" desc="tester">

/
* Field Name Definitions Class
* Table comments for testing
*/
Class TesterField
{   
    / @var INT(10) */ var $uid = "uid"; 
    / @var VARCHAR(45) */ var $someVarChar = "someVarChar"; 
    / @var INT(20) */ var $someIntField = "someIntField";     
}

/
 * Field Name Definitions Class
 * Table comments for testing
 */
Class TesterData
{    
        
    / @var INT(10) */ var $uid = "" ;
    / @var VARCHAR(45) */ var $someVarChar = "" ;
    / @var INT(20) */ var $someIntField = "" ;   

        
    function SetData(&$row)
    {
        $fld = new TesterField();
    
        $this->uid = isset($row[$fld->uid]) ? $row[$fld->uid] : "" ;
        $this->someVarChar = isset($row[$fld->someVarChar]) ? $row[$fld->someVarChar] : "" ;
        $this->someIntField = isset($row[$fld->someIntField]) ? $row[$fld->someIntField] : "" ;
    }

    
    /
     *@param array $row
     *@return \TesterData
     */
    public static function GetData(&$row)
    {
        $data = new TesterData();
        $data->SetData($row);
        return $data ;
    }
}

/
 * Field Name Definitions Class
 * Table comments for testing
 */
Class TesterTableQuery
{
    const DatabaseName = "test";
    
    /
     * Return DatabaseName.Tablename : test.tester
     */
    public static function GetTableName()
    {   return self::DatabaseName . ".tester" ; }
        
    
    /
     * Returns create table statement.  
     * @return string
     */
    public static function ReturnCreateTableSql()
    {
        $tableName = self::GetTableName();
        $sql       = "
            CREATE TABLE IF NOT EXISTS $tableName 
            ( 
             `uid` INT (10)  NOT NULL AUTO_INCREMENT , 
            `someVarChar` VARCHAR (45) NOT NULL COMMENT '' , 
            `someIntField` INT (20) NOT NULL COMMENT ''  
             PRIMARY KEY (`uid`)  
            ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='' AUTO_INCREMENT=1 " ;
        return $sql ;
    }

    
    /
     * Select by primary key field named uid 
     * @param int $primaryKey
     * @return string
     */
    public static function ReturnSelectSql($primaryKey)
    {
        $tableName = self::GetTableName();
        $field     = new TesterField() ;
        $sql       = "SELECT * FROM $tableName WHERE $field->uid = $primaryKey " ;
        return $sql ;
    }
    
    /
     * Select all fields all rows
     * @return string
     */
    public static function ReturnSelectAllSql()
    {
        $tableName = self::GetTableName();
        $sql       = "SELECT * FROM $tableName " ;
        return $sql ;
    }        
       
        
    
    /
     * Return delete statement to delete by field named uid 
     * @param int $primaryKey 
     * @return string
     */
    public static function ReturnDeleteSql($primaryKey)
    {
        $tableName = self::GetTableName();
        $field     = new TesterField() ;
        $sql       = "DELETE FROM $tableName WHERE $field->uid = $primaryKey " ;
        return $sql ;
    }
        
    
    /
     * Returns an update statement.  
     * Must pass in object to type TesterData
     * @param TesterData $TesterData
     * @return string
     */
    public static function ReturnUpdateSql(&$TesterData)
    {
        $tableName = self::GetTableName();
        $field     = new TesterField() ;
        $sql       = "
            UPDATE $tableName 
            SET  $field->someVarChar = '".$TesterData->someVarChar."',
                     $field->someIntField = '".$TesterData->someIntField."'
            WHERE $field->uid = '".$TesterData->uid."' " ;
        return $sql ;
    }
        
    
    /
     * Returns an insert statement.  
     * Must pass in object to type TesterData
     * @param TesterData $TesterData
     * @return string
     */
    public static function ReturnInsertSql(&$TesterData)
    {
        $tableName = self::GetTableName();
        $field     = new TesterField() ;
        $sql       = "
            INSERT INTO $tableName 
            ($field->someVarChar,
                    $field->someIntField)
            VALUES
            ( '". $TesterData->someVarChar ."' ,
                     '". $TesterData->someIntField ."' )
            " ;
        return $sql ;
    }    
}

//</editor-fold>

// ?>

by Bharat Parmar package author 170 - 4 months ago (2015-09-22) Comment

There is very short code available for the same. Please check for the same.