Usage

I want to know if can do this:

CREATE TABLE IF NOT EXISTS `mydb`.`store` (
`id` INT NOT NULL,
`name` VARCHAR(45) NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB

CREATE TABLE IF NOT EXISTS `mydb`.`type` (
`id` INT NOT NULL,
`description` VARCHAR(45) NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB

CREATE TABLE IF NOT EXISTS `mydb`.`product` (
`id` INT NOT NULL,
`name` VARCHAR(45) NULL,
`type` INT NULL,
`store` INT NULL,
PRIMARY KEY (`id`),
INDEX `fk_product_type_idx` (`type` ASC),
INDEX `fk_product_store_idx` (`store` ASC),
CONSTRAINT `fk_product_type`
FOREIGN KEY (`type`)
REFERENCES `mydb`.`type` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_product_store`
FOREIGN KEY (`store`)
REFERENCES `mydb`.`store` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB

- Insert product, type and store in fewest lines (or one statement).
- Select product and retrieve type and store.
- Select stores by product type (i.e., How many stores have wood products?)
- Delete row(s)

Thank you in advance for your time and attention.

Currently it does not provide create table statements. What it does do is connect to a database and build classes for the following.

1. Class representing the data row for a table.
2. Class representing fields in a table row.
3. Class with public static functions that allows for executing the following queries:
A. selecting all elements in a table.
B. selecting a specific row in a table based on the primary key.
C. Updating a table row based on a primary key.
D. Insert a table row.

These classes are independent of the type of connection used to mysql databases. I had thought about building those in though as an upgrade.
Anyways, I will paste some sample code below to show you what is built.



//<editor-fold defaultstate="collapsed" desc="tracking_order_fileprocess_log">

/**
* Field Name Definitions Class
* File Processing Log For Record Keeping
*/
Class Tracking_order_fileprocess_logField
{
/** @var INT(11) */ var $uid = "uid";
/** @var INT(11) */ var $orderId = "orderId";
/** @var VARCHAR(250) */ var $personalDescription = "personalDescription";
/** @var BLOB(65535) */ var $mysqlErrorDescription = "mysqlErrorDescription";
/** @var TIMESTAMP(19) */ var $tstamp = "tstamp";
}

/**
* Data Definitions Class
* File Processing Log For Record Keeping
*/
Class Tracking_order_fileprocess_logData
{
/** @var INT(11) */ var $uid = "" ;
/** @var INT(11) */ var $orderId = "" ;
/** @var VARCHAR(250) */ var $personalDescription = "" ;
/** @var BLOB(65535) */ var $mysqlErrorDescription = "" ;
/** @var TIMESTAMP(19) */ var $tstamp = "" ;


function SetData(&$row)
{
$fld = new Tracking_order_fileprocess_logField();

$this->uid = isset($row[$fld->uid]) ? $row[$fld->uid] : "" ;
$this->orderId = isset($row[$fld->orderId]) ? $row[$fld->orderId] : "" ;
$this->personalDescription = isset($row[$fld->personalDescription]) ? $row[$fld->personalDescription] : "" ;
$this->mysqlErrorDescription = isset($row[$fld->mysqlErrorDescription]) ? $row[$fld->mysqlErrorDescription] : "" ;
$this->tstamp = isset($row[$fld->tstamp]) ? $row[$fld->tstamp] : "" ;
}

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

/**
* File Processing Log For Record Keeping
*/
Class Tracking_order_fileprocess_logTableQuery
{
const DatabaseName = "MyDb";

/**
* Return DatabaseName.Tablename : MyDb.tracking_order_fileprocess_log
*/
public static function GetTableName()
{ return self::DatabaseName . ".tracking_order_fileprocess_log" ; }

/**
* Select by primary key field named uid
* @param int $primaryKey
* @return string
*/
public static function ReturnSelectSql($primaryKey)
{
$tableName = self::DatabaseName . ".tracking_order_fileprocess_log" ;
$field = new Tracking_order_fileprocess_logField() ;
$sql = "SELECT * FROM $tableName WHERE $field->uid = $primaryKey " ;
return $sql ;
}

/**
* Select all fields all rows
* @return string
*/
public static function ReturnSelectAllSql()
{
$tableName = self::DatabaseName . ".tracking_order_fileprocess_log" ;
$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::DatabaseName . ".tracking_order_fileprocess_log" ;
$field = new Tracking_order_fileprocess_logField() ;
$sql = "DELETE FROM $tableName WHERE $field->uid = $primaryKey " ;
return $sql ;
}

/**
* Returns an update statement.
* Must pass in object to type Tracking_order_fileprocess_logData
* @param Tracking_order_fileprocess_logData $Tracking_order_fileprocess_logData
* @return string
*/
public static function ReturnUpdateSql(&$Tracking_order_fileprocess_logData)
{
$tableName = self::DatabaseName . ".tracking_order_fileprocess_log" ;
$field = new Tracking_order_fileprocess_logField() ;
$sql = "
UPDATE $tableName
SET $field->orderId = '".$Tracking_order_fileprocess_logData->orderId."',
$field->personalDescription = '".$Tracking_order_fileprocess_logData->personalDescription."',
$field->mysqlErrorDescription = '".$Tracking_order_fileprocess_logData->mysqlErrorDescription."'
WHERE $field->uid = '".$Tracking_order_fileprocess_logData->uid."' " ;
return $sql ;
}


/**
* Returns an insert statement.
* Must pass in object to type Tracking_order_fileprocess_logData
* @param Tracking_order_fileprocess_logData $Tracking_order_fileprocess_logData
* @return string
*/
public static function ReturnInsertSql(&$Tracking_order_fileprocess_logData)
{
$tableName = self::DatabaseName . ".tracking_order_fileprocess_log" ;
$field = new Tracking_order_fileprocess_logField() ;
$sql = "
INSERT INTO $tableName
( $field->orderId,
$field->personalDescription,
$field->mysqlErrorDescription
)
VALUES
( '". $Tracking_order_fileprocess_logData->orderId ."' ,
'". $Tracking_order_fileprocess_logData->personalDescription ."' ,
'". $Tracking_order_fileprocess_logData->mysqlErrorDescription ."'
) " ;
return $sql ;
}
}

//</editor-fold>

I will put this example usage here as well.

function AddToLog($msg='',$mysqlMsg='')
{


/**
* Create the data object
*/
$Tracking_order_fileprocess_logData = new Tracking_order_fileprocess_logData();

$Tracking_order_fileprocess_logData->orderId = $orderIdNumber ;

$Tracking_order_fileprocess_logData->personalDescription = addslashes($msg) ;

$Tracking_order_fileprocess_logData->mysqlErrorDescription = addslashes($mysqlMsg) ;

/**
* Get the insert statement
*/
$insertSql = Tracking_order_fileprocess_logTableQuery::ReturnInsertSql($Tracking_order_fileprocess_logData);

/**
* Execute it with you database connection preference
* $dbConnect is a mysqli wrapper object I have written
*/
$dbConnect->executeQuery($insertSql);