MySQL/PHP/HTML Language Reference
MySQL:
Description General form
---------------- ------------------------------------------------------------
Create database create database dbName;
Export/import mysqldump -u userName -p dbName > pathName
mysql -u userName -p dbName < pathname
Grant privileges grant create, insert, ... on dbName.* to 'userName'@'localhost'
identified by 'password' [with grant option];
Load table load data local infile 'pathName' into table tableName
fields terminated by 'delimiter';
Set password set password for userName@hostName = password('new-password');
Read/execute statements from file: source pathName
Retrieve dbName select database();
Retrieve userName select user();
Use a database use dbName;
SQL:
Description General form
---------------- ------------------------------------------------------------
Alter table structure
ALTER TABLE tableName alteration1 [, alteration2]...
with such alterations as:
ADD [COLUMN] columnDescription
ADD INDEX [index] (column, ...)
ADD PRIMARY KEY (column, ...)
ADD [CONSTRAINT name] FOREIGN KEY [fkName]
reference definition
DROP PRIMARY KEY
DROP FOREIGN KEY fkName
DROP {INDEX | KEY} indexName
MODIFY [COLUMN] columnDescription
RENAME [TO] newTblName
Create a table CREATE TABLE tblname(columnS) [LIKE tblName] [SELECT ...];
where a column includes name, dataType[,length, [constraint]]
Create an index CREATE [UNIQUE] INDEX index_name ON table_name
(index_column_name [(length)] [ASC|DESC], ...)
Insert row INSERT [INTO] table [(columnS) VALUES (values)[,(values)]...;
Retrieve data SELECT columns FROM tables
[WHERE conditions]
[GROUP BY groupType] [HAVING where_definition]
[ORDER BY orderType];
Change data UPDATE tableName
SET column1 = expression1[,column2 = expression2]...
[WHERE condition]
Removing rowS DELETE FROM tableName [WHERE condition]
Removing table DROP TABLE tblName;
Removing user DROP USER userName;
Removing view DROP VIEW viewName;
Summary Functions(for nonNull values in selected rows):
Description General form
------------------- --------------------------------------------------------
Average AVG(expr)
Count COUNT(expr)
Maximum MAX(expr)
Minimum MIN(expr)
Sum SUM(expr)
String Functions:
Description General form
------------------- -------------------------------------------------------
Concatenate strings CONCAT((str1,str2,...)
Pad characters RPAD (string,length[,'chars']
Length LENGTH(string)
Extract substring SUBSTRING(str,pos[,len])
Find string position LOCATE(charS,str,start)
Date Functions:
Description General form
------------------ --------------------------------------------------------
Difference between DATEDIFF (curdate(), dateStr)
(Re)format date DATE_FORMAT(dateStr, formatCodes) where format codes for
numeric day and month name would be '%d %M'
Database Metadata:
Description General form
------------------- ---------------------------------------------------------
List tables SHOW TABLES [FROM dbName];
Display table info. SHOW CREATE TABLE tblName; (also: DESCRIBE tblName;)
Display view info. SHOW CREATE VIEW viewName;
Display index info. SHOW INDEX FROM tblName;
PHP:
Description General form
------------------- ---------------------------------------------------------
Allocating memory:
Constant: define ('CONSTANT_NAME', constantValue);
Variable: $charIdentifier='a', $intIdentifier=1, ..., $stringIdentifier="hello";
Console output:
echo value;
print(value);
printf("formatSpecifer(s)", value1[, value2]...);
Data types:
null, int(eger), string, double, bool, ...
Data type functions (is_*):
is_null( ), is_int( ), is_string( ), is_double( ), is_bool( ), is_numeric( ), ....
Changing data type: settype($variableName, dataType )
Type Casting: (dataType) $variableName
File functions:
Open: $fp = fopen("pathName", 'fileMode');
[file mode values: 'r', 'r+', 'w', 'a', ...
Close fclose($fp);
Write: fwrite($fp, $outputString[, length]);
fputs($fp, $outputString[, length]); -- alias
Read: $strVar = fgets($fp[, length]);
$strVar = fgetcsv($fp,[, length] [, delimiter]);
Location: ftell($fp);
file pointer location relative to start of file
Seek: fseek($fp, $pointerLocation);
sets the file pointer location
EOF: feof($fp)
Read all: $fileArray = file("pathName");
returns an array with each element containing one line from file
Control Structure - if/else
if (booleanExpression) {statement block}
[else if (booleanExpression) {statement block} ] ...
[else {statement block}]
Control Structure -- switch statement
switch (expression) {
case value1:
statement block 1
[break;]
case value2:
statement block 2
[break;]
...
[default:
statement block n; ]
}
Repetition Structure - while loop
while (booleanExpression) {
statement block
}
Repetition Structure - for and foreach loop
for (initial statement; loopCondition; updateStatement) {
statement block
}
foreach ($arrayName as [ $key => ] $simpleVar) {
statement block
}
Built-in string functions:
Function General Form
-------------------------- ------------------------------------------------------
Concatenation (.)
Extract substring substr(strArg, start[, length])
Format for presentation sprintf("formatSpecifer(s)", arg1[, arg2]...)
Replace substring substr_replace(strArg, strReplacement, start[,length])
String length function strlen(strArg)
Trimming strings trim(strArg[,listOfChars])
Changing case strtolower(), strtoupper(), ucfirst()
Find position of substring strpos(sourceString,substring[,index])
Convert newLine to break nl2br(strArg)
User-defined Functions
function functionName ( [formal parameter list] ) {
statements
[return expression;]
}
Function Call:
[variable = ] functionName( [arguments or actual parameter list] );
Array
$arrayName = array() or
$arrayName = array(value1[, value2]...) or
$arrayName[index] = value;
Class
The general form or syntax for a class is:
class classIdentifier {
classMembersList statements
};
classMembersList property syntax:
[ private|public|protected] $propertyName [ = initialValue];
classMembersList constructor syntax:
public function __construct([parameterList])
{[parent::construct;]statementS}
classMembersList method syntax:
[private|protected|public]function functionName ([parameterList]) {
statementS}
Object
The general form for instantiating an object is:
$objectName = new ClassName(argumentList);
Accessing an object:
$objectName -> {propertyName = value | methodName(argumentList)};
MySQL & PHP API:
close connection $db -> close( )
connect $db = new mysqli("localhost", "userName", "password", "database");
count $rowCountVar = $resultVar -> num_rows; and
exit script exit([$status]);
fetch $rowArrayVar = $resultVar->fetch_assoc();
include file include($path)
query $resultVar = $connectVar -> query($sqlString)
HTML:
link or anchor; attribute href for URL or anchor name
unpaired inserts line br/eak
for user input; attributes are name, action, method
unpaired an input element; attributes are name, type{checkbox,
radio,text,password}, value, size, maxsize, onClick
label for an input element
[selected], attribute value
[multiple], attribute name (used with option tags)