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

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 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.