Short Code Snippets

The following page contains nothing but a bunch of short little code snippets. See the full API Documentation for more (there are a lot of features and functions of the SmartDB that are not listed below).

 

Quick Reference

Initialization / Database Level (executed from a SmartDatabase object)

Table Level (executed from a SmartTable object)

Column Level (executed from a SmartColumn object)

Row Level (executed from a SmartRow object)

Cell Level (executed from a SmartCell object)

Underlying Database Management (i.e. MySQL structure and permissions)

See also - Sample Lookup Arrays (WHERE clauses)

 

Initialization / Database Level:

Create a DbManager object:

//It is recommended that the dbManager be stored in a global for easy access from anywhere within the project
$GLOBALS['dbManager'] = new DbManager_MySQL("localhost", "Username", "Password", "DatabaseName");

Create a SmartDatabase object:

//The location of the XML database schema file for this project
$dbSchemaPath = "/my/path/database/db.xml";

//It is recommended that the SmartDatabase be stored in a global for easy access from anywhere within the project
$GLOBALS['db'] = new SmartDatabase($GLOBALS['dbManager'], $dbSchemaPath);

Create an XML database schema file from an existing database:

//The location of the XML database schema to write
$destinationPath = "/my/path/database/db.xml";

//Typically this is a one time getting started operation. Make changes in the XML file hereafter. 
$GLOBALS['db'] = new SmartDatabase($GLOBALS['dbManager']);
$GLOBALS['db']->ReadDatabaseStructure();
$GLOBALS['db']->WriteXmlSchema($destinationPath);

Advanced: Create multiple simultaneous database connections:

//The SmartDb seamlessly works with multiple active connections to multiple databases 
$GLOBALS['anotherDbManager'] = new DbManager_MySQL("localhost", "Username", "Password", "AnotherDatabaseName");
$GLOBALS['anotherDb'] = new SmartDatabase($GLOBALS['anotherDbManager'], $dbSchemaPath);

Advanced: Load more XML schemas into the SmartDatabase. as needed:

//store parts of a database schema in separate files and lazy-load (for LARGE projects)
$GLOBALS['db']->LoadXmlSchema("/my/path/database/db-extended.xml");

 

Synchronize The Schema

This will update the actual underlying database (i.e. MySQL) to match the database structure defined in the XML schema:

$GLOBALS['db']->SyncStructureToDatabase(true); //true prints results

 This function compares all tables, columns, and properties of the connected database (DbManager) with your project's XML schema. This will:

  • Create tables, columns, and indexes
  • Remove columns and indexes (but not tables. This way, all tables in the actual database don't have to be SmartDb tables)
  • Update columns and indexes
  • Create dated backup tables before making any updates

* Be careful when renaming a column. A renamed column will be removed and recreated in the underlying database. If the column had data, it would be lost. You can either rename the column by some other means (i.e. phpMyAdmin), or just give the column an alias within your XML schema.

 

Table-Level:

Get a new row from a table

The following 2 examples are identical and get a new row from the "Invoice" table:

$row = $GLOBALS['db']['Invoice']->GetNewRow();
$row = $GLOBALS['db']['Invoice'](); //shortcut for GetNewRow()

 

Lookup a single row in a table

Lookup the Invoice row with InvoiceId = 123 (the following 4 examples are identical). The returned row may or may not Exist():

$row = $GLOBALS['db']['Invoice'](123); //gets the row with the primary key column=123
$row = $GLOBALS['db']['Invoice']->LookupRow(123); //gets the row with the primary key column=123
$row = $GLOBALS['db']['Invoice']->LookupRow([
   'InvoiceId' => 123
]);
$row = $GLOBALS['db']['Invoice']['InvoiceId']->LookupRow(123); //Column-level lookup example

Note - an Exception is thrown if multiple rows are returned, so you better be looking up on a unique column! Use LookupRows() otherwise.

 

Lookup any number of rows in a table (0 or more)

Lookup all male customers:

$rowsArray = $GLOBALS['db']['Customer']->LookupRows([
   'Sex' => 'Male'
]);

Lookup all female customers under the age of 30: (click here for more on lookup arrays)

$rowsArray = $GLOBALS['db']['Customer']->LookupRows([
   'Sex' => 'Female,
   'Age' => array('<' => 30)
]);

Lookup all customers with the name of "mike" (i.e. "mike and ike" would NOT match), and set the final row-count in $numRowsFound

$rowsArray = $GLOBALS['db']['Customer']->LookupRows([
   'Name' => 'mike'
], [
   'row-count' => &$numRowsFound //OUT variable- $numRowsFound will be set when the function returns
]);

 Lookup all customers with "mike" anywhere in their name (i.e. "mike and ike" would match), and sort by age descending:

$rowsArray = $GLOBALS['db']['Customer']->LookupRows([
   'Name' => ['like' => '%mike%'] //% is a wildcard and will match anything when used with 'like' or 'not like'
], [
   'sort-by' => ['Age' => 'desc']
]);

 Lookup all invoices with an Amount > 100 or an Amount <= 5:

$rowsArray = $GLOBALS['db']['Invoice']->LookupRows([
   'Amount' => [
      'OR' => [
         '>' => 100,
         '<=' => 5
      ]
   ]
]);

Lookup customers with CustomerId = 5, 6, 7, 8, 12, or 50:

$rowsArray = $GLOBALS['db']['Customer']->LookupRows([
   'CustomerId' => [
      'OR' => [5, 6, 7, 8, 12, 50]
   ]
]);

Click here for more sample lookup arrays (like the above)

 

Get all rows from a table

Get ALL rows in the Invoice table:

$rowsArray = $GLOBALS['db']['Invoice']->GetAllRows();

Get all rows in the Customer table sorted by name, but limit only the first 100 results:

$rowsArray = $GLOBALS['db']['Customer']->GetAllRows([
   'sort-by' => 'Name',
   'limit' => 100
])

Once you have an array of rows, you can do a foreach on them to iterate of each row:

foreach($rowsArray as $row){
   echo $row['Name']() . ' - ' . $row['EmailAddress']();
}

 

Delete rows from a table

If we have a row object, we can call Delete() directly on the row:

$row = $GLOBALS['db']['Invoice'](123); //gets the Invoice row with the primary key column=123
$bool = $row->Delete();

 Delete row with a CustomerId = 25 (the following 2 examples are identical):

$bool = $GLOBALS['db']['Customer']->DeleteRow([
   'CustomerId' => 25
]);
 $GLOBALS['db']['Customer']->DeleteRow(25); //delets the Customer row where the primary key column=25

Delete Customer rows where the Age=25 and the Sex=Male: (click here for more on lookup arrays)

$numRowsDeleted = $GLOBALS['db']['Customer']->DeleteRows([
   'Age' => 25,
   'Sex' => 'Male'
]);

 

Delete all rows from a table

Delete all rows from the "Log" table:

$numRowsDeleted = $GLOBALS['db']['Log']->DeleteAllRows();

 

Count all rows in a table

Count all rows on the Invoice table:

$count = count($GLOBALS['db']['Invoice']);
$count = $GLOBALS['db']['Invoice']->GetAllRows([
   'return-count-only' => true
]);

 

Lookup values from a single column

Lookup all of the Amount values for Invoices created before January 1, 2011: (click here for more on lookup arrays)

$valuesArray = $GLOBALS['db']['Invoice']->LookupColumnValues([
   'DateCreated' => ['<' => '2011-01-01']
], 'Amount');

 

 

Column-Level

Lookup a single row based on a column value

Lookup the Customer row with email address = "jfrost@winter.com"

$row = $GLOBALS['db']['Customer']['EmailAddress']->LookupRow('jfrost@winter.com');

Note - an Exception is thrown if multiple rows are returned, so you better be looking up on a unique column! Use LookupRows() otherwise.

Lookup any number of rows (0 or more) based on a column value

Lookup all Customer rows with EmailVerified = 1:

$rowsArray = $GLOBALS['db']['Customer']['EmailVerified']->LookupRows(1);

 Lookup all Customer rows with EmailVerified = 0, sorted by the Customer's EmailAddress, descending:

$rowsArray = $GLOBALS['db']['Customer']['EmailVerified']->LookupRows(0, [
   'sort-by' => ['EmailAddress' => 'desc']
]);

  (click here for more on lookup arrays)

Get all values from a particular column

Get all Customer EmailAddress values in an array:

$valuesArray = $GLOBALS['db']['Customer']['EmailAddress']->GetAllValues();

Get all Customer EmaillAddress values in an array, sorted by the Customer's Name, ascending:

$valuesArray = $GLOBALS['db']['Customer']['EmailAddress']->GetAllValues([
   'sort-by' => 'Name'
]);

 

Delete rows based on a column value

Delete all Customer rows where EmailVerified = 0

$numRowsDeleted = $GLOBALS['db']['Customer']['EmailVerified']->DeleteRows(0);

 

Set all cells in a particular column to a value

Set all cells in the Customer's EmailVerified column to 1:

$numRowsUpdated = $GLOBALS['db']['Customer']['EmailVerified']->SetAllValues(1);

 

Get an aggregate value from a particular column

Get the maximum, minimum, and average Amount values from the Invoice table:

$maxValue = $GLOBALS['db']['Invoice']['Amount']->GetMaxValue();
$minValue = $GLOBALS['db']['Invoice']['Amount']->GetMinValue();
$averageValue = $GLOBALS['db']['Invoice']['Amount']->GetAggregateValue('avg')

 Count the number of distinct Names we have in the Customer table:

$distinctNames = $GLOBALS['db']['Customer']['Name']->GetAggregateValue('count-distinct');

 

Get (or set) column properties

$displayName = $GLOBALS['db']['Customer']['EmailAddress']->DisplayName; //returns "Email Address", as defined in XML
$dataType = $GLOBALS['db']['Invoice']['Amount']->DataType; //returns "float", as defined in XML
$defaultValue = $GLOBALS['db']['Customer']['EmailVerified']->DefaultValue; //returns "0", as defined in XML
$GLOBALS['db']['Invoice']['Amount']->IsRequired = false; //set the amount Column as not required (overrides XML)

 

 

Row-Level

$row below could be a new row, or it could have been looked up from within a partciular table (see above).

Commit: update an existing row or insert a new row

$newRow = $GLOBALS['db']['Customer'](); //get a Customer new row
$newRow['Name'] = "Peter Piper"; //set the Customer's name
$newRow->Commit(); //insert the row
$existingRow = $GLOBALS['db']['Customer'](422); //get Customer with primary key (CustomerId) = 422
$existingRow['Sex'] = "Female"; //update the Customer's sex
$existingRow->Commit(); //update the row

Delete a row

$bool = $row->Delete();

Check if the row exists in the underlying database

$bool = $row->Exists();

Check if the row's data has been modified and needs committed

Note: this is typically not necessary. If you Commit() a row that is not dirty, nothing happens (i.e. no SQL calls).

$bool = $row->IsDirty();

Reset all values of a row from the database

This will re-get the row from from the underlying database.

$row->Refresh();

Unset all values of a row (excluding the primary key column(s))

This can be useful to clear default values set on new rows, for example.

$row->UnsetNonKeyColumnValues();

Print all values of a row and its status (i.e. IsDirty, Exists, etc.) for debugging

echo $row;
echo $row->ToString();

 

Check if a row has errors (i.e. "Email Address is required.")

$errors = $row->HasErrors(); //returns string. empty string if there are no errors

 

Copy all of a row's values to a new row (excluding primary key columns)

The copied row will not exist and will need to be Commit()'d to the database

$rowCopy = $row->GetShallowClone();

 

Lookup primary key IDs based on the cells that have been set on the row

$row = $GLOBALS['db']['Customer']();
$row['Name'] = 'Jack Frost';
$row['EmailVerified'] = 1;
$matchingIdsArray = $row->LookupKeys();

 

Set values of a row from an assoc array ($_POST or $_GET included)

This does not set key columns. See SetColumnValues() and SetKeyColumnValues() for options.

//the following Table-Column array structure is used by the auto-generated form objects.
//so if you use the Get*FormObject() functions to print your forms, $_POST and $_GET will look something like this: 
$array = [
   'Customer' => [
       'Name' => "Jack Frost",
       'EmailAddress' => "jfrost@winter.com",
       'EmailVerified' => 1
   ],
   'Invoice' => [
       'Amount' => 199.99
   ]
];

//get a new Customer row to capture the info from the array (array could be $_POST or $GET)
$Customer = $GLOBALS['db']['Customer'](); //new Customer row
$Customer->SetNonKeyColumnValues($array); //$array could be $_POST or $_GET
echo $Customer['Name'](); //prints "Jack Frost"

//get a new Invoice row to capture the info from the array (array could be $_POST or $_GET)
$Invoice = $GLOBALS['db']['Invoice'](); //new Invoice row
$Invoice->SetNonKeyColumnValues($array);
echo $Invoice['Amount'](); //prints 199.99

 

 

Cell-Level

Set data on a row's cells

$row = $GLOBALS['db']['Customer'](); //get new customer row
$row['Name'] = "Jack Frost";
$row['Email Address'] = "jfrost@winter.com"
$row->Commit(); //save new row

 

Get data from a cell in a row

The following 2 examples are identical and return the Name column of the Customer with a primary key (CustomerId) = 23

$row = $GLOBALS['db']['Customer']->LookupRow(23); //lookup existing customer row
$name = $row['Name']->GetValue();
$row = $GLOBALS['db']['Customer'](23); //lookup existing customer row
$name = $row['Name']();

 Get a value for use in HTML (i.e. stripslashes() and htmlspecialchars() executed before returned):

$name = $row['Name']->GetValue(true); //the 'true' is a shortcut for htmlspecialchars() 
$name = $row['Name'](true); //identical to GetValue(true) above
$date = $row['DateCreated'](true); //on date columns, passing 'true' will run strtotime() before returning the value
echo date('M d, Y - g:i:s a', $date); //print a friendly looking date

 

Get an HTML form object from a cell

These functions will set the form object's value using the current cell's value. The name of the form object is set to be used with $row->SetNonKeyColumnValues($_POST), though all attributes can be overridden and you can do everything the long way if need be.

Get a 'text' form object with the row's EmailAddress value preset. Force the 'size' attribute in the form object's HTML to be 50:

echo $row['EmailAddress']->GetTextFormObject([
   'size' => 50
]);

Get a 'checkbox' form object with the 'checked' attribute matching the current EmailVerified value:

echo $row['EmailVerified']->GetCheckboxFormObject();

Get a 'select' form object with the row's current value preset:

echo $row['Sex']->GetSelectFormObject([
   'Male' => 'I am a man',
   'Female' => 'I am a woman'
]);

Get a pair of radio form objects with labels and with the row's current value preselected:

echo $row['Sex']->GetRadioFormObject("I am a man", "Male");
echo $row['Sex']->GetRadioFormObject("I am a woman", "Female");

All available form object types:

  • Checkbox
  • Colorpicker (for use with jQuery UI, or similar)
  • Datepicker (for use with jQuery UI, or similar)
  • Hidden
  • Password
  • Radio
  • Select
  • Slider (for use with jQuery UI, or similar)
  • Text (single line)
  • Textarea (multi-line)

 

Get an HTML label for a cell (for use with a form object)

echo $row['EmailAddress']->GetFormObjectLabel();

 

Get a row/rows that is/are related to a particular cell

Get the Customer row that owns a particular Invoice:

$invoiceRow = $GLOBALS['db']['Invoice'](236); //lookup Invoice with primary key (InvoiceId) = 236
$customerRow = $invoiceRow['CustomerId']->GetRelatedRow('Customer'); //get the Customer row with a matching CustomerId

Get all Invoice rows for a particular customer:

$customerRow = $GLOBALS['db']['Customer'](155); //lookup Customer with primary key (CustomerId) = 155
$invoiceRowsArray = $customerRow['CustomerId']->GetRelatedRows('Invoice'); //get all Invoice rows with CustomerId = 155

 

Underlying Database Management (i.e. MySQL structure and permissions):

Note - The functions below are not common operations! Learn about using XML to synchronize your database.

Database-Level Operations - applied to the underlying database (i.e. MySQL)
* The connected user (within the DbManager) needs to have sufficient SQL privileges to perform these operations.

Create a new database:

$bool = $GLOBALS['dbManager']->CreateDatabase("NewDatabase");

Check if a database exists:

$bool = $GLOBALS['dbManager']->DatabaseExists("SomeDatabase");

Copy a database:

$bool = $GLOBALS['dbManager']->CopyDatabase("SomeDatabase", "NewDatabase", $options);

Drop a database:

$bool = $GLOBALS['dbManager']->DropDatabase("SomeDatabase");

 

Table-Level Operations - applied to the underlying database (i.e. MySQL)
* The connected user (within the DbManager) needs to have sufficient SQL privileges to perform these operations.

Check if a table exists:

$bool = $GLOBALS['dbManager']->TableExists("SomeDatabase", "SomeTable");

Drop a table:

$bool = $GLOBALS['dbManager']->DropTable("SomeDatabase", "SomeTable");

 * You should create tables by using the XML schema and $GLOBALS['db']->SyncStructureToDatabase(true);

 

User-Level Operations - applied to the underlying database (i.e. MySQL)
* The connected user (within the DbManager) needs to have sufficient SQL privileges to perform these operations.

Create a new database user:

$bool = $GLOBALS['dbManager']->CreateUser("Username", "Password", "localhost");

Check if a database user exists:

$bool = $GLOBALS['dbManager']->UserExists("Username", "localhost");

Grant database user permissions:

$bool = $GLOBALS['dbManager']->GrantUserPermissions("SomeDatabase", "Username", "localhost");

Revoke database user permissions:

$bool = $GLOBALS['dbManager']->RevokeUserPermissions("SomeDatabase", "Username", "localhost");

Drop a database user:

$bool = $GLOBALS['dbManager']->DropUser("Username", "localhost");

 

See the full API Documentation for more!

See also Sample Lookup Arrays (WHERE clauses) | The SET Datatype