MySqueaks documentation

Introduction [menu]

MySqueaks is the short name for My Simplified Quality Expressions (SQX), a simple PHP class that generates SQL statements. This facilitates an easier understanding of the different parts and conditions of a SQL statement, making it more user-friendly for beginner programmers. The use of the object-orientated (OO) approach allows for a flexible implementation in any form of usage as well. MySqueaks is released under GPL - please open the LICENSE file using any plain text editor included to read and accept the license before you use MySqueaks.

Work on MySqueaks started as a fork project from my other open-source software, the frsb blogging system. I was thinking of a novel implementation for writing SQL statements, and I thought of encapsulating those "commands" in the form of an easy-to-use class. Coincidentally, I was taking a database management module in my University, and the course uses a combination of PHP and MySQL for the practical implementation. Hence, I thought that this would also be a good way to not only better my understanding of MySQL, but also ease the SQL implementation for beginners as well.

The contents of this documentation will touch on the various member functions and global constants as coded and recognised in the source code, organised into the parts shown in the menu on the left. The menu takes advantage of Javascript to position it at the top left corner regardless of where you scroll on the page, so you are highly encouraged to enable Javascript. If you prefer not to however, you can still click on the [menu] link at the section headers to bring you to the menu bar. Parts of the documentation that are in bold and green like this indicates the differences between 0.9.7 beta and 1.0.0/1.0.3/1.0.5.

Have fun! :)

Getting started and upcoming features [menu]

MySqueaks uses the "older" mysql_() functions instead of the newer mysqli_() ones available from PHP 5 onwards. Do note that MySQL support for PHP 5 is disabled by default, and you may require some additional steps to ensure that the mysql_() functions are supported under PHP 5 as well. Please refer to the online documentation for PHP for more details. MySqueaks had been tested in a Windows environment under PHP 4 (using easyPHP) and PHP 5 (using WAMP5) and functions normally. No further configuration is necessary if you are using either software to do web programming.

Many member functions will accept optional parameters such as this:

function_name(req_param [, opt_param [, opt_param2]] )

You can either simply specify only one parameter:

function_name(req_param)

or the optional one too:

function_name(req_param, opt_param)

Please note that PHP does not allow you to "skip" optional parameters like this:

function_name(req_param, , opt_param2)

You can use the SQX_SKIP constant variable in most situations below to ignore the optional parameter, i.e. leave it as the default value, and "skip" to the ones you want to change instead. Please also note that the parameters $no_quotes, $no_field_quotes and $no_value_quotes accept the value SQX_NO_QUOTES to *not* append the back quotes / "backticks" (`) for you.

Since 0.9.3 beta, "lazy-style" function names are introduced to make it a little quicker to type function names. As good as it seems to have English-sounding function names for better readability and understanding, "pro" programmers may prefer shorter function names without the need for capitalization to shave a couple of minutes off a very long programming job. Available "lazy-style" function names are listed below the "formal" function names like this:

GetLowerCaseValues($field);
lower()

So, instead of typing GetLowerCaseValues, one can just type lower. Parameters are mapped accordingly.

Since 1.0.0, MySqueaks does some simple checks for valid identifiers (i.e. table and column names, aliases) but does not perform any form of error-checking for values as yet; it simply accepts the arguments given and construct the corresponding SQL statement. Therefore, please be careful with the values you supply for the functions. Since 0.9.6 beta, the original single MySqueaks class is "split" into SqueaksCore and MySqueaks, which extends the former. Essentially, MySqueaks is an interface to the core functions within SqueaksCore. This move is to separate such functions with output functions such as Submit() and Tabulate(), hopefully with more to follow. Such a separation will allow for better code maintenance on either "sides".

Initialisation and general functions [menu]

The class name for MySqueaks is... MySqueaks. To start, simply create a new object like how you would do normally:

$sqx = new MySqueaks($dbh [, $dbname [, $tblname [, $engine [, $char_set [, $collate [, $comment]]]]]] );

$dbh is the MySQL database handler when you use mysql_connect() in PHP. Alternatively, you can pass the argument as a three-element array with the server name to connect to (usually "localhost"), user name and password in this order. You can also optionally supply a database name $dbname and MySqueaks will select that database.

Since 1.0.0, MySqueaks will automatically create the database if it does not exist. Also, you can "bind" a table to a MySqueaks object by specifying $tblname as well. If $tblname is specified but a table with such a name does not exist, MySqueaks will attempt to CREATE TABLE, using the parameters above in a similar fashion as CreateTable(). You can test for the table's existence by directly accessing its $Status variable. If $Status === null, then the table does not exist and you can directly use those CREATE TABLE functions to define your table first followed by calling Submit(). $Status is true if the table exists, and false if an error somehow occured.

The benefits of such a "binding" is to introduce some form of error-checking for invalid names and aliases and to eliminate the need to specify the default table name for functions that requires so. As such, the order of the parameters passed for these functions will be different and these differences will be highlighted below in this format (example):

$sqx->binded_function($column);

You can reset a MySqueaks object by using the Clear() function:

$sqx->Clear();

This will clear all the data stored in the object, except for the database handler ($dbh as specified above), the database name ($dbname as specified above) and other associated table metadata if you have "binded" the object to a table (see above). If you want to change these settings, use the ChangeDatabase() function:

$sqx->ChangeDatabase($dbh [, $dbname [, $tblname [, $engine [, $char_set [, $collate [, $comment]]]]]] );
chdb()

The ChangeDatabase() function performs similarly to the constructor function.

Note that ChangeDatabase() will also perform a Clear() since version 1.0.5. You can get MySqueaks to create a new database for you as well, by using the CreateDatabase() function:

$sqx->CreateDatabase($dbname [, $char_set [, $collate [, $commit]] );
newdb()

$dbname takes in the desired database name, and you can optionally specify the default character set and collation to use through $char_set and $collate respectively. Since 0.8.0 beta, you can specify the optional $commit parameter as false to delay the execution, as this executes the CREATE DATABASE query immediately by default. When the database is created, it will be selected automatically as well. Since 0.8.3 beta, IF NOT EXISTS is also included in the SQL query.

To do the actual querying, use the Submit() function:

$results = $sqx->Submit( [$return_type] );

By default (i.e. without setting the optional $return_type), the above line of code will return any rows returned by MySQL to the variable $results as an associative array, akin to passing the SQX_FETCH_ASSOC value. Submit() will always return results in arrays, even if there is only a single result (i.e. a single-element array). You can alternatively use the SQX_NUM_ROWS to get the number of rows affected by the query or SQX_NONE to simply get a true value. Since 0.9.3 beta, you need not specify SQX_NONE to "force" a correct return value when you Submit() SQL statements that do not return any rows such as INSERT statements. MySqueaks will do this automatically. Since 0.9.6 beta, MySqueaks can return the results as numeric arrays or objects using SQX_FETCH_ROW and SQX_FETCH_OBJECT for $return_type respectively. Since 1.0.0, MySqueaks also accepts SQX_AFFECTED_ROWS to return the number of rows affected for SQL statements such as INSERT or ALTER TABLE.

Better error handling is also included for 0.9.6 beta. For development using PHP 5, MySqueaks will throw a generic Exception which you can check for using:

try {
	$sqx->Submit();
} catch (Exception $e) { 
	echo $e->getMessage(); 
}

For PHP 4 lacking support for try... catch, Submit() will simply return false, which you can check for using an if statement. MySqueaks also allows you to specify your own error message using setError():

$sqx->setError($message [, $die [, $verbose]] );

$message contains the output you want to display, and it allows you to specify three "constants" which will be replaced with the actual values when an error has occured: SQX_QUERY for the SQL statement MySqueaks generates, SQX_ERROR for the MySQL error description (similar to mysql_error()) and SQX_ERRNO for the MySQL error number (similar to mysql_errno()). To reset the error message to the default one, set $message as false. $die is a boolean value which lets you determine if you want to halt the PHP execution or to continue and defaults to true. $verbose is also another boolean value which lets you determine if you want to display the error message or not. Please note that if $die is set to true, the error message will be displayed regardless of the value specified for $verbose.

Since 0.8.5 beta, you can alternatively use the Tabulate() function to generate a table for you:

echo $sqx->Tabulate( [$tblname [, $print_col_name]] );

If $tblname is set, MySqueaks will "name" the table using a <div> tag: if $tblname starts with a period ".", the table will be identified by the class name, else its id. If $print_col_name is set to false, the table column headers will not be shown. Lastly, to simply get the query statement generated by MySqueaks, use the GetQuery() function:

$results = $sqx->GetQuery( [$format] );
toString()

This assigns the variable $results with the entire query statement as a string. Since 0.8.5 beta, GetQuery() accepts an optional parameter with the argument true to return a nicely formatted query.

Since 0.9.7 beta, you can make use of the XML() function to generate a XML output:

$results = $sqx->XML( [$pad [, $sematic [, $wrapper]]] );

$pad is largely for aesthetics purposes: it lets you specify the number of tabs (that Tab key on your keyboard) to include for "padding" the XML tags. If $sematic is set to any value but false, the format of the XML tags will be as such: <COLUMN_NAME>RESULT</COLUMN_NAME>. If $sematic is not specified or set to false, the format will be as such: <field name="COLUMN_NAME">RESULT</field>. $wrapper lets you specify the parent tag to encapsulate the individual results and defaults to result. Please do note that if you are setting $sematic to true, ensure that your column names returned do not have spaces or else XML parsers may complain that it is not well-formed.

CREATE VIEW, SELECT and UNION [menu]

Use the Select() function to prepare a SELECT statement:

$sqx->Select($tblnames [, $no_quotes [, $exp [, $distinct]]] );
sel()

$sqx->Select($field_names);

Example:

$sqx->Select("table", SQX_SKIP, "name");

Result:

SELECT `name` FROM `table` ;

$tblnames and $exp can both be passed as an array. SQX_SKIP is used above to "skip" the $no_quotes parameter, i.e. MySqueaks will append the back quotes. $exp is used for specifying the exact column names you want, refer to the example to get a better idea. Since 0.8.0 beta, if you only want to select distinct rows, simply set $distinct to any value except false.

You can use the CreateView() function for a CREATE VIEW query as follows:

$sqx->CreateView($viewname);

$viewname should contain the name for the VIEW you want, following which you can use the functions below for the SELECT statement as per normal. To do a UNION clause, use the Union() function:

$sqx->Union($union);

$union should be a valid SELECT statement. If you are "recycling" the object for other SELECT statements without either CREATE VIEW or UNION, you can use the functions CloseView() and CloseUnion() respectively to "close and reset" these statements/clauses.

The list below shows you how you can specify your conditions using a particular comparison operator together with other related functions:

$sqx->Equals($log_op, $field, $value [, $no_field_quotes [, $no_value_quotes]] );
$sqx->NotEquals($log_op, $field, $value [, $no_field_quotes [, $no_value_quotes]] );
$sqx->Less($log_op, $field, $value [, $no_field_quotes [, $no_value_quotes]] );
lt()
$sqx->LessEquals($log_op, $field, $value [, $no_field_quotes [, $no_value_quotes]] );
lte()
$sqx->Greater($log_op, $field, $value [, $no_field_quotes [, $no_value_quotes]] );
gt()
$sqx->GreaterEquals($log_op, $field, $value [, $no_field_quotes [, $no_value_quotes]] );
gte()
$sqx->In($log_op, $field, $value [, $no_field_quotes [, $no_value_quotes]] );
$sqx->NotIn($log_op, $field, $value [, $no_field_quotes [, $no_value_quotes]] );
$sqx->Like($log_op, $field, $value [, $no_field_quotes [, $no_value_quotes, [$wildcard]]] );
$sqx->NotLike($log_op, $field, $value [, $no_field_quotes [, $no_value_quotes, [$wildcard]]] );
$sqx->NewBunch( [$log_op] );
bunch()
$sqx->CloseBunch( [$not_specific] );
$sqx->OrderAsc($field [, $no_quotes] );
asc()
$sqx->OrderDesc($field [, $no_quotes] );
desc()
$sqx->Limit($limits [, $limit2] );
lim()
$sqx->Match($tblname1, $field1, $tblname2, $field2);
$sqx->Join($originating_table, $originating_field, $join_table [, $join_field [, $subtype]] );
$sqx->JoinTo($field1 [, $field2] );
$sqx->GroupBy($fields [, $rollup] );
$sqx->Having();
$sqx->CloseHaving();
$sqx->Alias($name, $alias [, $set_column] );

$sqx->NewBunch( [$log_op [, $mode]] );

$sqx->Limit($limits [, $limit2 [, $mode]] );

Example:

$sqx->Select("`table`", SQX_NO_QUOTES, array("`name`", "SUM(`members`)"));
$sqx->Join("table", "name", "family", SQX_SKIP, SQX_LEFT);
$sqx->GreaterEquals("and", "cars", 2);
$sqx->Greater("or", "tv_sets", 3);
$sqx->Less("or", "home_bedrooms", "5");
$sqx->NewBunch("or");
$sqx->Greater("or", "income", 10000);
$sqx->CloseBunch();
$sqx->Like("and", "surname", "an", SQX_SKIP, SQX_SKIP, SQX_WILD_LEFT);
$sqx->GroupBy("postal_code");
$sqx->Having();
$sqx->GreaterEquals("and", "SUM(members)", 5, SQX_NO_QUOTES);
$sqx->CloseHaving();
$sqx->OrderAsc("surname");
$sqx->OrderDesc("name");
$sqx->Limit(array(5, 10));

Result:

SELECT `name`, SUM(`members`) FROM `table` 
LEFT JOIN `family` USING ( `name` )  WHERE ( 
	 `cars` >= 2 
	OR `tv_sets` > 3 
	OR `home_bedrooms` < 5 
	AND `surname` LIKE '%an' 
 ) OR ( 
	 `income` > 10000 
 ) GROUP BY postal_code HAVING ( 
	 SUM(members) >= 5 
 ) ORDER BY `surname` ASC, `name` DESC
 LIMIT 5, 10 ;

The function names, and the variables $field and $value, should be pretty self-explanatory. Since 0.9.6 beta, MySqueaks support NULL values by specifying $value as NULL. The Like() and NotLike() functions accept an additional $wildcard parameter that lets you specify if you want to append the % wildcard character on neither side, left side, right side or both sides of the string, using the values SQX_WILD_NONE, SQX_WILD_LEFT, SQX_WILD_RIGHT or SQX_WILD_BOTH respectively.

$log_op should only be one of the following string values: AND (the default value), OR, NOT or XOR. You may also want to take note that the $log_op specified for the first condition is ignored, logically. You can alternatively choose to pass $value as an array; in this case, $field becomes a table name that MySqueaks will look up for the respective column names to match the different elements of $value with. Do bear in mind that the $log_op specified will be used for all.

To separate your conditions into "bunches", you can use the NewBunch() function to start a new "bunch", and then use the CloseBunch() function if you want to end the current one and switch back to the previous "bunch". Refer to the example for a clearer picture. The NewBunch() function takes in an optional string variable $log_op, which should be either AND (the default value), OR, NOT or XOR. CloseBunch() will always return true unless the first "bunch" is reached; you can specify an optional true value for $not_specific such that it will always return a true value.

Use OrderAsc() and OrderDesc() to tell MySqueaks which columns should be sorted in the ascending and descending order respectively. Both functions will also accept arrays. The Limit() function tells MySqueaks the range of rows to return. If $limits is only an integer value 1 or greater, then the LIMIT clause will be LIMIT 0, $limits. If it is a two-element array as shown in the example, the clause would be LIMIT $limits[0], $limits[1]. Since 0.9.0 beta, Limit() supports an optional $limit2 parameter to make its usage more "natural" to how the actual LIMIT clause works. If $limit2 is specified, please make sure that $limits is not an array.

The following functions are added since 0.8.0 beta. Match() makes it simpler for you to perform a simple join between two tables. Rather than using Equals("and", $tblname1.$field1, $tblname2.$field2, SQX_NO_QUOTES, SQX_NO_QUOTES), you can just use Match() as shown above that will specify the extra parameters automatically. If you pass $field1 as an array, $field2 will be expected to be an array as well with at least the number of elements in $field1. MySqueaks will match the elements of $field1 with the corresponding element in $field2. Join() works similarly to Match() except that this operates on SQL's JOIN clause. If $join_field is not specified or "skipped" using SQX_SKIP, the tables will be referenced USING $originating_field, else MySqueaks will equate $originating_field ON $join_field. You can also optionally specify the type of JOIN to perform, please refer to the list of constants.

$sqx->Match($join_table1, $join_table2);

$sqx->Join($join_table [, $subtype [, $alias]] );

$sqx->JoinTo($field1 [, $field2] );

$sqx->Join($joined->JoinTo("c1", "c2"));

$sqx->Join($joined->JoinTo("c1"));
$sqx->Join($joined->JoinTo("c2"));

Lastly, GroupBy(), and Having() lets you specify the GROUP BY and HAVING clause respectively. GroupBy() can take in an array of values or simply a string of the column name. Since 0.8.3 beta, if you want to use the WITH ROLLUP clause with GROUP BY, set the second optional argument to any value but false. Use Having() before a set of conditions meant for the HAVING clause, and you may use CloseHaving() to "close" the set before you can continue specifying other conditions for the WHERE clause. Please note that you may only use Having() once, i.e. using it for the second time will only make the second set of conditions applicable for the HAVING clause, and the first set will be "forgotten".

UPDATE, INSERT and REPLACE [menu]

All three statements have a similar "preparation" statement:

$sqx->Update($tblnames [, $no_quotes] );
upd()
$sqx->Insert($tblname [, $no_quotes] );
ins()
$sqx->Replace($tblname [, $no_quotes] );
rpl()

Note the usage of the plural $tblnames for Update compared to the other two: this means that the Update() function allows for $tblnames as an array besides being a string variable. Since 0.9.7 beta, MySqueaks supports the VALUES clause using the Values() function:

$sqx->Values( [$values] );

$values must either be left unspecified or as an array of the column names you want to insert or replace data into, even if it has to be a single-element array. Values() must be used before you use the Set() function. To set values for either of the three statements, use the Set() function:

$sqx->Set($field, $value [, $no_field_quotes [, $no_value_quotes]] );

$sqx->Values( [$values [, $mode]] );
$sqx->Set($field, $value [, $mode [, $no_value_quotes]] );

You can pass $value as a string or numeric variable, or alternatively choose to pass $value as an array; in this case, $field becomes a table name that MySqueaks will look up for the respective column names to match the different elements of $value with. Please note that if you are using Values() as described above, $field will contain either a string or an array of the values to be inserted or replaced into the table, and $value becomes the equivalent of $no_value_quotes:

$sqx->Set($values [, $no_value_quotes] ); // only when Values() is used before Set()!

MySqueaks will "bunch" the values you specify into rows when using Values() and add empty values, i.e. '' for any missing columns. Example:

$sqx->Insert("tblname");
$sqx->Values(array("a", "b", "c"));
$sqx->Set("value 1");
$sqx->Set(array("values B", "values C", "values A"));
$sqx->Set("last value");

Result:

INSERT INTO `tblname` (`a`, `b`, `c`) VALUES 
	('value 1', 'values B', 'values C' ), 
	('values A', 'last value', '' ) ;

Note that we are adding data to three columns in the table, but we specified only five values. Therefore, a sixth empty value is added by MySqueaks to complete the second row. For the INSERT statement, you can tell MySqueaks what fields and values to update upon insertion of a duplicate entry by using the OnDuplicate() function:

$sqx->OnDuplicate( [$open] );

Any fields and values Set() after this line will be used for the ON DUPLICATE UPDATE clause of the INSERT statement. To reset this setting, assign $open the value false. Last but not least, the functions above in a SELECT statement can also be used in the UPDATE statement. Refer to MySQL documentation for more information. Usage of these clauses follow the explanations above.

DELETE and TRUNCATE [menu]

The DELETE statement is prepared using the Delete() function, and the USING clause set with the Using() function:

$sqx->Delete($tblnames [, $no_quotes] );
del()
$sqx->Using($tblnames [, $no_quotes] );

You can pass $tblnames as a string variable or as an array. Refer to the SELECT statement section above to know which functions to use for the WHERE clause, specifying which rows to delete. The TRUNCATE statement is prepared using the Truncate() function:

$sqx->Truncate($tblnames [, $no_quotes [, $commit]] );

$sqx->Truncate( [$commit] );

Refer to the above paragraph for an explanation of the $tblnames and $no_quotes parameters. The additional optional $commit parameter, when set to false, will not execute the query immediately (which is the default behaviour). You can use the Submit() function as explained above to execute the query within your web application at a later time.

CREATE TABLE [menu]

Use the CreateTable() function to prepare a CREATE TABLE statement:

$sqx->CreateTable($tblname [, $engine [, $char_set [, $collate [, $comment]]]] );
table()

$tblname is the desired table name. By default, MySqueaks will set the engine as MyISAM, so remember to include the parameter $engine for the required engine type. Use $char_set, $collate and $comment to set the CHARACTER SET, COLLATE and COMMENT respectively. Since 0.8.3 beta, IF NOT EXISTS is included in the SQL query. Also, if you want to change the default engine permanently, you can use the UseEngine() function:

$sqx->UseEngine($engine);

$engine should contain a valid engine type recognised by MySQL.

The list below shows you how you can create the required columns of a particular data type together with other related functions (please note that SQX_SKIP does not work for the optional parameters of these functions):

$sqx->TypeDecimal($name [, $default [, $unsigned]] );
dec()
$sqx->TypeInteger($name [, $subtype [, $default [, $unsigned]]] );
intg()
$sqx->TypePrecision($name [, $subtype [, $default [, $unsigned]]] );
precision()
$sqx->TypeChrono($name [, $subtype [, $default]] );
chrono()
$sqx->TypeBinary($name [, $subtype [, $default]] );
bin()
$sqx->TypeBlob($name [, $subtype] );
blob()
$sqx->TypeChar($name [, $subtype [, $default [, $char_set [, $collate [, $fulltext]]]]] );
char()
$sqx->TypeText($name [, $subtype [, $char_set [, $collate [, $fulltext]]]] );
text()
$sqx->TypeEnumSet($name [, $subtype [, $default [, $char_set [, $collate]]]] );
enums()
$sqx->PrimaryKey( [$auto_increment [, $name]] );
pk()
$sqx->PKI($field [, $name] );
$sqx->Unique( [$name] );
$sqx->Index();
$sqx->AllowNull();
$sqx->SetLength($length);
length()
$sqx->ForeignKey($table, $field [, $on_delete [, $on_update [, $name]]] );
fk()

Example:

$sqx->CreateTable("new_table", "InnoDB");
$sqx->TypeInteger("key");
$sqx->PrimaryKey();
$sqx->TypeChrono("manufactured_date", SQX_DATE);
$sqx->TypePrecision("price", SQX_DOUBLE, "5.00", SQX_UNSIGNED);
$sqx->SetLength(array(8, 2));
$sqx->TypeInteger("product_type", SQX_TINY);
$sqx->Index();
$sqx->ForeignKey("product", "ptype", "restrict");
$sqx->TypeEnumSet("grade", SQX_ENUM, array("excellent", "good", "bad"));
$sqx->TypeText("intro", SQX_MEDIUM);
$sqx->AllowNull();

Result:

CREATE TABLE IF NOT EXISTS `new_table` (
	`key` INT NOT NULL  AUTO_INCREMENT , 
	`manufactured_date` DATE NOT NULL , 
	`price` DOUBLE(8, 2) UNSIGNED  NOT NULL  DEFAULT '5.00' , 
	`product_type` TINYINT NOT NULL , 
	`grade` ENUM('excellent', 'good', 'bad') NOT NULL , 
	`intro` MEDIUMTEXT , 
	PRIMARY KEY (`key`)  , 
	INDEX (`product_type`)  , 
	FOREIGN KEY (product_type) REFERENCES product(ptype) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE = InnoDB ;

With the exception of the TypeDecimal() function, all other data type functions support an optional $subtype parameter for specifying the actual data type. Read up on the available constants and the actual value they represent here. Also, with the exception of TypeBlob() and TypeText() functions, all other data type functions support an optional second/third $default parameter for the default value of the column.

The numeric functions TypeDecimal(), TypeInteger() and TypePrecision() support a last optional third/fourth $unsigned parameter for specifying if the column contained unsigned and/or zerofill values: SQX_UNSIGNED for only unsigned values and SQX_UNSIGNED_ZF for unsigned zerofill values. You can specify the CHARACTER SET and/or COLLATE for TypeChar(), TypeText() and TypeEnum() functions using the optional parameters $char_set and $collate respectively. For TypeChar() and TypeText() functions, they support a last optional fifth/sixth $fulltext parameter for MySQL engines supporting the FULLTEXT clause. The default $default value for TypeEnumSet() function is "S", "Q" and "X"; you are highly recommended to change it. TypeEnumSet() is the only function that can take $default arguments in the form of arrays as well.

PrimaryKey(), Unique(), Index(), AllowNull(), SetLength() and ForeignKey() all affects the last column, and not the next one. PrimaryKey() accepts an optional $auto_increment value, which when assigned the value false, will not set AUTO_INCREMENT for the column. Since 0.9.3 beta, PKI() allows you to specify an integer field $field with an AUTO_INCREMENT attribute instead of having to type a line to specify the field and another to make it the primary key. Unique() and Index() should be pretty self-explanatory. By default, MySqueaks sets all columns as NOT NULL, you will need to use the AllowNull() function to over-ride the default assignment. SetLength() can be used to specify a length for all data types except ENUM and SET. $length should be an integer except when the last column specified is of type DOUBLE: $length must be passed as an array with two elements, the first element being the total number of digits and the second being the number of digits following the decimal point. Passing $length as an array will also work for columns of type FLOAT or DECIMAL.

You can use the ForeignKey() function when using the InnoDB engine for data integrity and consistency across tables. It requires you to specify the $table name and $field name, and optionally allows you to specify the action to take when the foreign key is being deleted or updated. CASCADE is used by default; the other allowable options under MySQL are RESTRICT, SET NULL and NO ACTION. Lastly, since 0.8.0 beta, PrimaryKey(), Unique() and ForeignKey() supports an optional $name parameter that lets you specify a name for that particular constraint.

$sqx->ForeignKey($join_tbl->JoinTo("field") [, $on_delete [, $on_update [, $name]]] );

ALTER TABLE [menu]

These functions are implemented since 0.9.0 beta. You can use AlterTable() to generate a ALTER TABLE statement in order to modify your table structure. AlterTable() accepts only a $tblname parameter:

$sqx->AlterTable($tblname);
alter()

To add columns, refer to the list for the "CREATE TABLE" section above. To set the arrangement of the column, use the Arrange() function after specifying the field:

$sqx->Arrange( [$column] );

Example:

$sqx->TypeInteger("number1");
$sqx->Arrange("textplace");
$sqx->TypeInteger("number2");

In the example, the integer field "number1" will be positioned after "textplace", not "number2". If $column is not specified, then the column will be inserted in front, i.e. ... ADD [column] FIRST. To drop columns, use the Drop() function:

$sqx->Drop($column [, $drop_type] );

To drop the primary key, simply set $column to SQX_PRIMARY_KEY and leave the optional $drop_type blank. For others, pass the name in as $column and you will also need to specify SQX_COLUMN, SQX_INDEX or SQX_FOREIGN_KEY for $drop_type to tell MySqueaks that you are dropping a column, index or foreign key correspondingly. Since 0.9.6 beta, MySqueaks allows you to CHANGE your table too:

$sqx->Change($column);

$column will contain the name of the old field, followed by the new column definition. Example:

$sqx->Change("oldfield");
$sqx->TypeInteger("newfield");

The example changes "oldfield" to the new integer field "newfield". To convert the current character set use by the table, you can use the AlterCharacterSet() function to do so:

$sqx->AlterCharacterSet($char_set [, $collate] );

OPTIMIZE, DROP and RENAME TABLE [menu]

These functions are implemented since 0.8.3 beta. You may use OptimizeTable(), DropTable() and RenameTable() to perform the corresponding queries as suggested by their names:

$sqx->OptimizeTable($tblname [, $no_quotes [, $commit]] );
$sqx->DropTable($tblnames [, $no_quotes [, $commit]] );
$sqx->RenameTable($from_tblnames, $to_tblnames, [, $from_no_quotes [, $to_no_quotes [, $commit]]] );

$sqx->OptimizeTable( [$commit] );
$sqx->DropTable( [$commit] );
$sqx->RenameTable($to_tblname, [$to_no_quotes [, $commit]] );

All three functions will execute the query immediately by default. If you would rather not to, set $commit to false. Except for OptimizeTable(), the other two functions can process multiple tables as well (evident from the plural variable name). In this case, the parameters will take in an array, and for the case of RenameTable(), the elements in $to_tblnames must be no less than $from_tblnames. Also, since 1.0.5, DropTable() will perform DROP TABLE IF EXISTS... instead of just DROP TABLE....

List of constants [menu]

MySqueaks comes with its own pre-defined constants (using PHP's define() method) to replace certain numeric or boolean values with meaningful variable names. The bold values below are the default values wherever applicable. You can choose to still use the numeric values or the variables listed below with the functions. Throughout the documentation, only these variables will be shown in place of the actual values. If your browser has the capability, you can mouse-over on the variable name to find out its actual value.

Sample code [menu]

The sample code can be found here. To view the example as an RSS feed, append ?rss=1 to the URL like this.

Download [menu]

Please go to the project page listed on SourceForge.net. Alternatively, should SF.net by down for some peculiar reason, you may also download MySqueaks here.