MySQL Transaction in PHP (Commit & Rollback)

We all know that an SQL query will either execute completely or not at all (i.e. they are atoms). But, sometimes, we want several queries to be bundled together as response to business logic. Transaction processing will help you to ensure this and maintain database integrity.

If you are new to MySQL transaction with PHP, then this article will definitely help. :-)

Step One - Make sure the type of your database table is innoDB not MyISAM.
(Here is a screenshot of from phpMyAdmin)

Step Two - Start transaction $dbh->query('BEGIN');
$dbh is an pearDB object ($dbh = new pearDB(), if you are not familiar with pearDB, please click here.)

Step Three - Specify your queries which reflect the business logic. ($sqlA and $sqlB)
Step Four - 'ROLLBACK' to cancel $sqlA and $sqlB, and 'COMMIT' to confirm the changes.

Code Example:

(Click the image to Enlarge)


If you have any question or suggestion, feel free contact me on twitter: http://twitter.com/alexzang

AJAX & Web 2.0 Tutorial (Section 5) - Debug & Performance

1. AJAX applications don’t exist in a vacuum, while much of the code can reside on the client, the essence of AJAX applications is the interaction with the server, which necessarily involves load.

2. Debugging AJAX applications can be challenging for a couple reasons, including:
- Browsers suck
- Multiple languages involved
- Development environment probably doesn’t support debuging PHP & JavaScript
- Communication between client and server may notbe visible

3. Debugging Tools

- Firebug - Firefox Extension
(Supports step by step debugging, breakpoints and watches; Handles multiple JavaScript documents concurrently; Gives you somewhat nicer error messages)

- Ethereal/Wireshark (Industry standard tool for packet sniffing)
-Live HTTP Headers and HTTP Inspector
(built into Komondo, acts as a proxy for requests)
-Yahoo! Logger library (YAHOO.log)

AJAX & Web 2.0 Tutorial (Section 5) - Applications

1. If you ask someone what Ajax stands for, they might answer it doesn’t stand for anything, or they might say “Asynchronous JavaScript And XML”
2. Asynchronous means that you’re welcome to send requests one after another, but they’re not guaranteed to return in that order (really, they’re not guaranteed to return at all, but that’s rare)
3. Server load affects your scripts in two similarbut different ways, including:
   
   1. Everything gets slower
   2. Different parts of things get slower (So some scripts that originally took similar amounts of time can now take drastically different amounts of time)
4. While Ajax encourages more communication between client and server, it’s generally the same information you would provide anyways, developers simply need to maintain their normal level of vigilance.
   
5. There are many situations within Ajax applications where this is sub-optimal

1. A user could enter invalid login credentials, then hit the login button. Realize their mistake correct it then hit it again
2. Select something from a list to have further information populated by Ajax, then select another item
3. A user could click a button to login, then activate another page element that requires them to have logged in before access

AJAX & Web 2.0 Tutorial (Section 4) - Yahoo User Interface (YUI )

1. The Yahoo! User Interface Library is a set of utilities and controls that make your life easier;
2. They’re released under a BSD style license;
3. Apart from solving cross browser problems, the library also includes code to do a lot of nifty things;
4. The library is available at http://developer.yahoo.com/yui/
5. Please go download the library and set it up in your development environment; :-)
   
6. Auto Complete is one of those nice finishing touches that can make most websites just seem better. It’s also a perfect case of supplementing the user experience, rather than providing it, worst case scenario the user doesn’t receivethe drop down.

Sample Code:

<script type="text/javascript">
YAHOO.example.ACFlatData = function(){
var mylogger;
var oACDS;
var oAutoComp0,oAutoComp1,oAutoComp2;
return {
init: function() {
mylogger = new YAHOO.widget.LogReader("logger");
oACDS = new YAHOO.widget.DS_XHR("./sampleAutoComplete.php", ["\n", "\t"]);
oACDS.responseType = YAHOO.widget.DS_XHR.TYPE_FLAT;
oACDS.maxCacheEntries = 60;
oACDS.queryMatchSubset = true;
oAutoComp2 = new YAHOO.widget.AutoComplete
('ysearchinput2','ysearchcontainer2', oACDS);
oAutoComp2.delimChar = ";";
oAutoComp2.queryDelay = 0;
oAutoComp2.prehighlightClassName = "yui-ac-prehighlight";
},

validateForm: function() {
return false;
}
};
}();
YAHOO.util.Event.addListener(this,'load',YAHOO.example.ACFlatData.init);
</script>

AJAX & Web 2.0 Tutorial (Section 3) - JSON

1. JSON (JavaScript Object Notation) is a subset of how objects are represented in JavaScript
2. It’s presented as a lightweight information transfer protocol, similar in many respects to XML
3. What makes JSON useful in JavaScript is that it can be parsed very easily (eval())JSON can contain any number of elements, nested key value pairs, objects, arrays, etc.
4. Until a library is introduced the easiest way to transfer data between your script and PHP is to use simple GET & POST based requests for transmission, and JSON for receipt.
5. Different browsers interpret JavaScript differently, IE in particular goes off in its own direction (xmlHTTP request works differently, it’s difficult to re-use ajax objects, data is stored in linked lists rather than a hash table so it’s notably slower)
6. You could do some serious research, understand all the little differences, and write your code appropriately, or...

JSON Example:

<script type="text/javascript">
var name = "alex";
httpRequest = new XMLHttpRequest();
httpRequest.open('GET', 'http://localtest.luxplus.net/alfa/
ucase.php?text=' + name);
httpRequest.onreadystatechange = function()
{
if (httpRequest.readyState == 4)
{
responseJSON = eval("(" + httpRequest.responseText + ")");
alert(responseJSON.ucase);
}
}
httpRequest.send(null);
</script>

/* ucase.php */

<?php
$string = isset($_GET['text']) ? strtoupper($_GET['text']) :
strtoupper("default");
$data['ucase'] = $string;
$returnValue = json_encode($data);
echo $returnValue;
?>

AJAX & Web 2.0 Tutorial (Section 2) - Web Services

1. Web Services are a way for disparate applications to work with each other over the web;
2. In order for different applications to communicate with each other they need to agree on a protocol, webservices have three common protocols, JavaScript throws a fourth into the mix;
3. REST - Simple request protocol, looks identical to a form being filled out, response is a basic XML document;
4. XML-RPC - XML request-response protocol, format chosen is sub-optimal for parsing with DOM tools;
5. SOAP - Heavy but well defined XML based request-response protocol;
6. JSON - JavaScript Object Notation - information is passed back and forth in the JavaScript notation;
7. Ditch SOAP & XML-RPC, (SOAP is too heavy for frequent small requests, XML-RPC is just ugly);
8. REST - Light weight requests, some basic frameworks are available. Define your own response XML format;
9. JSON - The JSON format is relatively lightweight but still ‘new’ enough that it’s not too widely supported (JSON support was added in PHP 5.2.0);
10. It’s critical to remember that HTTP is a stateless protocol, every request must stand alone, independent of each other request. While technologies like sessions help can help when dealing with end users, they are inappropriate when using web services;
11. When developing web services leverage and re-factor existing code, rather than duplicating;
12. Objects are rather... different compared to other languages. Rather than defining a specific
    class, than instantiating instances of it, objects are created from functions that pull in their own required methods. This can be done on the fly, or by defining functions inside other functions.

AJAX & Web 2.0 Tutorial (Section 1) - Javascript Basics

1. Javascript has nothing to do with Java.
2. Javascript is a client side language, it is executed by the browser, not the server.
3. Javascript can be used to manipulate HTML documents, this allows the programmer to do all sort of nifty things. (Like AJAX)
4. Creating Valid HTML documents is the first step to giving yourself an easier JavaScript existence, things will start working sooner, and generally break in more predictable and understandable ways.
5. Many developers new to JavaScript look at the source code of their application to look for output, which seems like it should hold the answer, it doesn’t. When you modify the document using JavaScript you’re modifying the document in memory, the source doesn’t
   actually change.
6. Proper code needs a finished document with which to work, store your initialization code inside a function called in the body tag’s onload event. It’s good practice to encapsulate
   your code within a function anyways.
7. JavaScript variables are very similar to PHP
   
   1. Loosely Typed
   2. Number - Floating point number, stored internally using 64bit representation
   3. Boolean - true or false
   4. Strings - Encapsulated within quotes (no heredoc)
   5. Objects
8. Best way to convert text into a number is by using the Number() function, if it runs into a problem it will return produce.
9. To convert a value into an Integer use the parseInt(value, base) function. It accepts both
   the object containing the value you wish to convert, and the base at which the conversion should take place (avoiding those silly issues with leading zeros)

MySQL DB Design Tutorial (Section 4) - Naming Suffixed

This list of postfixes for data element names based on the ISO-11179 Standard

1. _id = Identifier, it is unique in the schema and refer to one entity anywhere it appears in the schema.
2. _data or dt = date, temporal dimension.
3. _nbr or num = tag number; this is a string of digits that names something.
4. _name or nm = this is an alphabetic name and it explains itself.
5. _code or _cd = A code is a standard maintained by a trusted source, usually outside of the enterprise.
6. _size = an industry standard or company scale for a commodity, such as clothing, shoes, envelopes or machine screws.
7. _tot = sum, an aggregated dimension which is logically different from its parts.
8. _seq = a sequential numbering.
9. _tally = a count of values.
10. _cat = Category
11. _cls or _class = a class
12. _type = an encoding that has a common meaning both internally and externally.
13. The differences among type, class, and category are an increasing strength of the algorithm for assigning the type, class, or category.
14. The thress terms are often mixed in actual usage.
15. _status = an internal encoding that reflects a state of being.
16. _addr or _loc = An address or location for an entity.
17. _img = An image data type, such as .jpg, .gif, .gif and so forth.

MySQL DB Design Tutorial (Section 3) - Column Constraints & Database Relationships

The following are the list of constraints that can be assigned to columns in MySQL:

1. Not NULL | NULL (Default vallue) - Primary key (implied)
2. DEFAULT - Not available in BLOG/TEXT; AUTO_INCREMENT; NULL Values
3. UNIQUE

Database Relationships

1. One-to-One (1:1) is when at most one instance of a entity A is associated with one instance of entity B.
2. One-to-Many (1:N) relationships is when for one instance of entity A, there are zero, one, or many instances of entity B, but for one instance of entity B, there is only one instance of entity A.
3. Many-to-Many (M:N) relationship is when for one instance of entity A, there are zero, one, or many instances of entity B and for one instance of entity B there are zero, one, or many instances of entity A.

MySQL DB Design Tutorial (Section 2) - Database Components

The following are a list of the more common table types that will be used in designing a database:

1. Stage Tables (Raw data in SQL, to be scrubbed), Base tables, Auxiliary tables, Archival tables (for data warehouse), Audit tables (for recovery and layers) and virtual tables (views, derived tables, CTE)

Types of encoding schemes

1. Measurement Encoding (a measurement encoding is given in some unit of measure, such as pounds, volts or liters) can be done in one of two ways: A. The column contains an implied unit of measure and the numbers represent the quantity in that unit; B. The column explicitly contains the unit.
2. Abbreviation Encoding (Abbreviation codes shorten the attribute values to fit into less storate space, but the reader easily understands them) is very handy, but as the set of values becomes larger, the possibility for misunderstanding increases. Example: Consider the ISO 3166 Country Codes, which come in two-letter, three-letter and non-abbreviation numeric forms.
3. Algorithmic Encoding (Algorithmic encoding takes the value to be encoded and puts it through an algorithm to obtain the encodings). Example: Encription is the most common example of an algorithmic encoding scheme.
4. Hierarchical Encoding (A hierarchy partitions the set of values into disjoint categories, then partitions those categories into subcategories, and so forth until some final level is reached). Example: The most common example is the ZIP code, which partitions the US geographically.

Define Encoding Schemes In MySQL

• col_name {CHAR | VARCHAR} (col_length) [CHARACTER SET charset_name] [COLLATE collation_name]

MySQL DB Design Tutorial (Section 1) - Introductions

Ratio Scales (Ratio scales are what people think of when they think about a measurement)

1. Ratio scales have an origin, an ordering, and a set of operations that can be expressed in arithmetic.
2. They are called ratio scales because all measurements are expressed as multiples of fractions of a certain unit or interval.
3. Examples: length, mass, and volume. (The unit is what is arbitrary; the weight of a bag of sand is still weight whether it is measured in kilograms or in pounds)

Relationship Checklist

1. Make sure that each table in your database has been reviewed for possible relationships with other tables.
2. For tables with relationships to other tables, make sure that the relationship has been properly identified. This would include: A. The type of participation between the tables; B. The degree of participation between the tables.
3. Make certain that each foreign key meets the following best practice suggestions: A. The name of the foreign key should match the name of the primary key it is related to (a data element has only one and only one name in a schema); B. The column specifications of the foreign key are identical to those of the primary key.

Data Integrity Related Terms

1. This level of integrity is a vital aspect of database design and the following lists some of the more important terms associated with integrity: A. Column Specification (general elements, physical elements, and logical elements); B. Data Integrity (table-level, column-level, and referential integrity)

PHP Basics (Section 5) - Object Oriented Programming

1. Prior to PHP5 OOP was a hack on top of the array implementation.
2. PHP5 changed eveything, in a great way.
3. Simple OO Example:

class person
{
public $age;
public $name;
function __construct($name, $age)
{
$this->name = $name;
$this->age = $age;
}
function birthday()
{
echo "Happy Birthday " . $this->name . "!";
$this->age++;
}
}

4. Public - Method or property can be accessed externally.
5. Private - Method or property is private to that class and can not be accessed externally.
6. Protected - Method or property is private and can also be accessed by extending classes
7. Final - Method can be overridden by extending classes.
8. Extending Example:

class friend extends person
{
private $relationship;
const MYNAME = "paul";
function __construct($name, $age, $relationship)
{
parent::__construct($name, $age);
$this->relationship = $relationship;
}
function showStatus()
{
echo "{$this->name} is my {$this->relationship}";
}
function fight()
{
$this->relationship = "enemy";
}
static function phoneCall()
{
echo friend::MYNAME . " the phone is for you";
}


9. Autoloading Example:

function __autoload($class_name)
{
require_once "/www/phpClasses/
{$class_name}.inc.php";
}
$a = new friend;

10. Special Functions: __construct(), __destruct(), __toString(), __sleep() __wakeup(), __call(), __get(), __set()
11. Design Patterns - Some time ago people realized that we were solving the same problems again and again, they then decided to sit down and come up with really good solutions to those problems, they’re called design patterns.
12. The advantages of design patterns are two fold: first they present a well defined solution to common problems, second they provide a common language for developers.

PHP Basics (Section 4) - Files

1. Files provide a simple temporary to permanent data store.
2. PHP5 presents two functions which make handling files, namely file_get_contents() and file_put_contents().
3. For large amounts of data, or in issues where a race condition is possible a database would be a better data store.
4. Steams are the way that PHP handles working with network resources.
5. Whenever you open up a file PHP creates a stream in the background.
6. Each stream consists of several components: file wrapper, one or two pipe-lines, an optional context, metadata about the stream.
7. Files can be locked to prevent race conditions:
   flock($fp, LOCK_SH); //Place Shared lock
   flock($fp, LOCK_EX); //
   Place Exclusive (write) Lock
   flock($fp, LOCK_UN); //Release lock
8. Placing and removing locks can inhibit performance as traffic increases, consider using a different data store.
9. Using stream context and a few other techniques a lot can be done without resorting to sockets, however for more detailed control sockets can be used.
   
10. Sockets allow read/write access to various socket connections, while things like HTTP may seem read only, at the protocol level information is being sent to identify the desired resource. Example:
    $fp = fsockopen
    ("example.preinheimer.com", 80, $errno, $errstr, 30)
    ;
    if (!$fp) {
    echo "$errstr ($errno)\n";
    } else {
    $out = "GET / HTTP/1.1\r\n";
    $out .= "Host: example.preinheimer.com\r\n";
    $out .= "Connection: Close\r\n\r\n";
    fwrite($fp, $out);
    while (!feof($fp)) {
    echo fgets($fp, 128);
    }
    fclose($fp);
    }

For more information, please read my another post Amazon S3 Makes My Document Management System Easier

PHP Basics (Section 3) - Security

Defense in Depth

1. When you plan with defense in depth, you plan for failure. Rather than internal functions assuming the data they receive is already validated or escaped, they will check and confirm.
2. Application that demonstrate defense in depth are not noly more resistant to attack when they are developed, they also remain more resistant over time as new attacks are developed, and as more code is added to them.

Least Privilege

1. Your PHP applications by default has a lot of power, they execute as the web user (often apache or www-data) with all the rights and privileges thereof.
2. If an attacker gains control of PHP through an application vulnerability this can be used and abused.

Validation vs Escaping

1. These terms are often confused, or used interchangeably.
2. Validation or Filtering is the process by which you subject data to a series of rules, either it passes (validates) or does not.
3. Escaping is the process by which you prepare data for a specific resource by "escaping" certain portions of the data to avoid confusion of instruction and data.

Validate Input

1. Whenever you receive data one of three things can be said about it: 1) It is valid, the user entered the data you want, in the format you desire; 2) It is invalid because the user either did not comply or did not understand the rules on the data you requested (eg. Poorly formatted postcode); 3) It is invalid because the user is attempting to compromise your application.
2. Data from the end user can not be trusted, it must be validated before it can be used.
3. Validate data first don't save it for last.
4. Fail early, tell the user what went wrong.

Whitelist vs Blacklist

1. There are two major approaches to data validation, the whitelist and blacklist approach.
2. Under the whitelist approach you select a series of valid characteristics (frequently characters) only data that follows these rules is accepted as valid.
3. Under the blacklist approach you select a series of invalid characteristics, any data that contains these characteristics is considered invalid.

XSS

1. Under a cross site scripting attack an attacker injects code into your page (forum post, shout box, etc) that contains code that re-writes the page to do something nefarious.
2. This can be prevented through proper escaping and data validation techniques

CSRF

1. Under a cross site request forgery attack a site exploits another sites persistent user trust relationship to make something happen.
2. iFrames are another common tool leveraged in this technique.

Sessions

1. Sessions provide safer state, it may not nesesarily be safe.
2. Basically, sessions combine cookies containing a session ID with a local(ish) data store corresponding to that session id.
3. If the session id is compromised, or the data store is not secure (/tmp on a shared machine) sessions are still vulnerable to attack.
4. Session IDs are very random.
5. Predicting them is hard, it’s much easier to: 1) Check out /tmp on a shared server, see what people have; 2)Intercept communications; 3)Implement session fixation.
6. To defend, implement some browser fingerprinting.

For more information, please read my another post PHP Application Security Rules

PHP Basics (Section 2) - Functions & String

Function

1. We use functions for several major reasons: readability, code separation, maintainability, modularity.
2. In PHP4 objects were thrown around ByVal, this meant that you made copies without even thinking about it.
3. In PHP5 objects are always passed BrRef unless you explicitly clone it, keep that in mind.
4. Return statements can exist anywhere anywhere within the function, and you can even have multiple return values.
5. Avoid situations where the same function may return nothing, or something.

String

1. Strings are the most commonly used variable type in PHP, because they are both central to web development, and the common method of data transmission from the user
2. Within strings many characters take a special meaning, matching quotes, back slashes, octal numbers, variables, if you wish to accept them as they are you mush escape them with the \ character.
3. strcmp() - Compare two strings. Returns > 0 if $string_1 is greater than $string_2, and 0 if they are equal.
4. strcasecrm() - Case insensitive version of strcmp()
5. substr() - Used to retrieve a portion of a string
6. number_format() - By default formats a number with the comma as the thousands separator, and no decimal.
7. preg_match() - Returns the number of matches found by a given search string under this format, also capable of returning the match.

Examples:

echo number_format("1234567.123"); //Shows 1,234,567
echo number_format("1234567.123", 3, ",", " "); //Shows 1 234 567.123

$string = "156 abc";
var_dump(preg_match("/\w\s\w/", $string)); // 1 (matches)

PHP Basics (Section 1) - Database

I'd like to blog my understanding of PHP based on my 10+ years experience in developing scalable web based applications. There will be 14 sections in total, including: database, functions, strings, security, design pattern, javascript and more... :-) Here is the first section --- DATABASE

1. Database cannot be tested by using specific code.
2. While PHP is loosely types, databases are not. Therefore, detailed Information about the data being stored is required for efficient storage and retrieval, common data-types include: int(signed integer number, 32bits), char(fixed length character string), varchar(variable length character string), float(signed floating point number, 64bits)
3. Most modern database systems fall into the relational category, the basis of which is the relationship between various tables
4. Relationships link records based on some common data, relationships can be one to one, one to many, many to many
5. DB systems can be configured to enforce those relationships to maintain referential integrity
6. DB are smart and fast, but the DB designer has foreknowledge of how the DB will be used
7. With this knowledge the designer can create an index on appropriate columns
8. This index instructs the DBMS to store additional information about the data in that column, to make locating data within it as fast as possible
9. Transactions allow you to merge multiple queries into one atomic operation, either they ALL execute successfully, or none do. (BEGIN TRANSACTION #name; ...queries here, COMMIT;) - Transactions are only available on InnoDB. :-)

So if you have a good idea which you think I need to improve (i.e. depth, examples, etc) , let me know and I will see what I can do.