Friday, November 20, 2009

MySQL Quick Reference

This is just a simple quick reference for normal mysql routines that I forget from time to time and have to lookup.

MySQL

Index
es (from SitePoint)

Adding a “normal” index via CREATE INDEX:

mysql> CREATE INDEX [index_name] ON tablename (index_columns);
Example mysql> CREATE INDEX fname_lname_age ON people (firstname,lastname,age);

Adding a unique index via CREATE INDEX:
mysql> CREATE UNIQUE INDEX [index_name] ON tablename (index_columns);
Example: mysql> CREATE UNIQUE INDEX fname_lname_age ON people (firstname,lastname,age);

Adding a “normal” index via ALTER TABLE:
mysql> ALTER TABLE tablename ADD INDEX [index_name] (index_columns);
Example: mysql> ALTER TABLE people ADD INDEX fname_lname_age (firstname,lastname,age);

Adding a unique index via ALTER TABLE:
mysql> ALTER TABLE tablename ADD UNIQUE [index_name] (index_columns);
Example:mysql> ALTER TABLE people ADD UNIQUE fname_lname_age (firstname,lastname,age);

Adding a primary key via ALTER TABLE:
mysql> ALTER TABLE tablename ADD PRIMARY KEY (index_columns);
Example: mysql> ALTER TABLE people ADD PRIMARY KEY (peopleid);

Adding a “normal” index via CREATE TABLE:
mysql> CREATE TABLE tablename (
rest of columns,
INDEX [index_name] (index_columns)
[other indexes]
);

Example:
mysql> CREATE TABLE people (
peopleid SMALLINT UNSIGNED NOT NULL,
firstname CHAR(50) NOT NULL,
lastname CHAR(50) NOT NULL,
age SMALLINT NOT NULL,
townid SMALLINT NOT NULL,
INDEX fname_lname_age (firstname,lastname,age)
);

Adding a unique index via CREATE TABLE:
mysql> CREATE TABLE tablename (
rest of columns,
UNIQUE [index_name] (index_columns)
[other indexes]
);

Example:
mysql> CREATE TABLE people (
peopleid SMALLINT UNSIGNED NOT NULL,
firstname CHAR(50) NOT NULL,
lastname CHAR(50) NOT NULL,
age SMALLINT NOT NULL,
townid SMALLINT NOT NULL,
UNIQUE fname_lname_age (firstname,lastname,age)
);

Adding a primary key via CREATE TABLE:
mysql> CREATE TABLE tablename (
rest of columns,
INDEX [index_name] (index_columns)
[other indexes]
);

Example:
mysql> CREATE TABLE people (
peopleid SMALLINT NOT NULL AUTO_INCREMENT,
firstname CHAR(50) NOT NULL,
lastname CHAR(50) NOT NULL,
age SMALLINT NOT NULL,
townid SMALLINT NOT NULL,
PRIMARY KEY (peopleid)
);

Dropping (removing) a “normal” or unique index via ALTER TABLE:
mysql> ALTER TABLE tablename DROP INDEX index_name;
Example: mysql> ALTER TABLE people DROP INDEX fname_lname_age;

Dropping (removing) a primary key via ALTER TABLE:
mysql> ALTER TABLE tablename DROP PRIMARY KEY;
Example: mysql> ALTER TABLE people DROP PRIMARY KEY;

-----------------------------------------------------------------------

Add Column
mysql> ALTER TABLE tablename ADD COLUMN [Col_Name] [Col_Definitions]

Add TinyINT Column
mysql> ALTER TABLE customers ADD COLUMN age tinyint unsigned NOT NULL default '0';

Add Varchar Column
mysql> ALTER TABLE customers ADD COLUMN first_name varchar(40) NULL;


Drop Column
mysql> ALTER TABLE tablename DROP COLUMN [Col_Name]
Example
mysql> ALTER TABLE tablename DROP COLUMN age;

-----------------------------------------------------------------------

Import/Export DB and Tables

DB Import Example

mysql -u [username] -p [password] [database] < [filename]
~$ mysql -u my_user -p < customers_backup.sql

Full DB Export Example
mysqldump [options...] -u [username] -p [password] [database] > [filename]
~$ mysqldump -u my_user -p customers > customers_backup.sql

Full DB Export with Drop Table
mysqldump [options...] -u [username] -p [password] [database] > [filename]
~$ mysqldump --add-drop-table -u my_user -p customers > customers_backup.sql

DB Export with Specific Tables
mysqldump -u [username] -p [password] [databasename] [table1 table2 ...] > [filename]
~$ mysqldump --add-drop-table -u my_user -p customers account_info blog_entries > customers_backup.sql

-----------------------------------------------------------------------

Table Stats

SHOW TABLE STATUS
SHOW TABLE STATUS [ {FROM | IN} DB_NAME ] [LIKE '%' | WHERE
The following example uses a database name of "stats" and a table name of "Server"
mysql> SHOW TABLE STATUS FROM `stats` WHERE `Name` = 'Server'


-----------------------------------------------------------------------

Misc

Create User
CREATE USER user [IDENTIFIED BY [PASSWORD] 'password'];
mysql> Create USER my_username IDENTIFIED BY 'my_password';

Create Database
$ mysqladmin -u -p create
~$ mysqladmin -u my_username -p create my_database

Drop/Delete Database
$ mysqladmin -u -p drop
~$ mysqladmin -u my_username -p drop my_database

Monday, November 16, 2009

Simple Optimizing Tips for PHP Part II - Recursive Functions

As we write PHP code (or any language for that matter) we begin to start thinking about optimizing code. But even though our intentions are for "the greater good", we sometimes miss things that should be obvious.

Today's article takes a look at:

Recursive Functions

Functions are one of the most important tools we have for developing reusable, structured code. When we build our functions, we are mainly looking at ways to reduce redundancy and create effective ways to perform the same operations we use in either 1 script or all our scripts.

In our attempts to reduce redundancy, we inadvertently create more redundancy or miss opportunities to simplify how our code executes.

Consider this situation. We have a multidimensional array set that we want to display not only in a div, but also divs inside of divs. One method we could solve this with involves something like the following code:


$array = array("info" => array("employees" => array("name" => array(array("first" => "John",
"last" => "Doe",
),
array("first" => "Bob",
"last" => "Henry",
),
),
),
"departs" => array("building_1" => array("Sales",
"PR",
"HR",
),
"building_2" => array("Developers",
"Management",
),
),
),
"misc" => "Created in 2009",
);

function display_information(array $data_array) {
foreach($data_array as $key => $value) { // info
if(is_array($value)) {
foreach($value as $sub_key => $sub_value) {
if(is_array($sub_value)) {
foreach($sub_value as $sub_key2 => $sub_value2) {
if(is_array($sub_value2)) {
foreach($sub_value2 as $sub_key3 => $sub_value3) {
if(is_array($sub_value3)) {
foreach($sub_value3 as $sub_key4 => $sub_value4) {
echo "$sub_key4 => $sub_value4";
}
} else {
echo "$sub_key3 => $sub_value3";
}
}
} else {
echo "$sub_key2 => $sub_value2";
}
}
} else {
echo "$sub_key => $sub_value";
}
}
} else {
echo "$array_val";
}
}
}
While the above code works exactly as we need it to, it isn't exactly written the most efficient or optimized way. If you look at the code above, you can already start to see the redundancy involved with it. It's the exact same foreach statement whenever our condition of "is_array" rings true, and the exact same statement when it rings false. Plus, since we have to keep track of where we are in each of our loops, we get to the point to where our foreach loop parameters are extremely difficult to keep track of and the code in general is just a mess.

So how can we optimize the code above to be less redundant, and in return create a quicker, less line, and optimized solution? We do it by creating a recursive function.

A recursive function is just a method that calls itself from within itself. It helps reduce redundancy by applying similar logic to a result set without having to rewrite the exact same methods over and over again (as in the above code).


$array = array("info" => array("employees" => array("name" => array(array("first" => "John",
"last" => "Doe",
),
array("first" => "Bob",
"last" => "Henry",
),
),
),
"departs" => array("building_1" => array("Sales",
"PR",
"HR",
),
"building_2" => array("Developers",
"Management",
),
),
),
"misc" => "Created in 2009",
);

function display_information(array $data_array) {
foreach($data_array as $key => $value) { // info
if(is_array($value)) {
display_information($value);
} else {
echo "$array_val";
}
}
}


Nice isn't it? We take a 29 line function from our original example and turned it easily into a 9 line optimized function. This not only reduces the lines dedicated our function, but it also makes it easier to read, faster, and above all optimized!

Recursive functions can be used in many instances. The key for the developer is to recognize when it's needed by carefully examining the code and determine if it's needed. This example (while a bit exaggerated) will hopefully open your eyes and also teach you just one more simple thing you can do to create more optimized code for PHP!

Tune in next time for my next optimizing article on, Commenting.

Thursday, November 12, 2009

Zend Studio 7.0.2 - Lot of new stuff, same old issues

I've been a Zend Studio IDE user for about 5 years now. I've always loved the IDE since it was so well tuned for PHP projects. Last year I made the blind leap of faith into using Zend Studio 6.0 and was extremely disappointed in what it provided to the developer. The release had so many bugs and never really stood up well against their previous 5.x release...

When 6.0 was released with all the bugs and issues it contained, I went out of my way to write a 3 page bug list for the team. Why? Because I love Zend and their IDE. It is what makes my life as a developer much easier. I was hoping that pointing out the "features" of 6.0 that was causing so much pain for me to even consider using their product would be enough to send them a message: Change it, or lose yet another developer.

Today I installed Zend Studio 7.0.2 Professional, totally expecting to see these issues resolved. While the IDE shows a drastic change in speed, the same errors and exceptions remain, thus making working in Zend Studio a much more complicated headache than it ever should be.

Granted I would never be able to create the things Zend has for an IDE, however this is not even the case this time. Zend has decided (as of their 6.0 release) to use the Eclipse engine for their IDE. It was a mistake for their 6.0 version, and it's still a mistake today.

The project management piece of Zend Studio (which is the most used functionality for me) is still broken, and management of files is still a clustered mess.

Sigh. While there are a small amount of issues with the 5.5.1 build (which is what I STILL use today), these are NOTHING compared to working with 7.0.2.

I hate to say it, but Zend Studio has lost another developer to use their IDE. I'm done. Their 5.x release was so GREAT! It made life so nice! But now they have continued to stick with the failed philosophy of using Eclipse as their engine (which is actually a decent enough engine), but they just cannot seem to make it work with Zend Studio.

I cannot recommend this IDE to anyone, and I definitely cannot justify spending the amount of cash they (Zend) are calling me asking me to purchase for 7.0.2. There is just no way.

I'll continue to use 5.5.1. Unfortunately though, I can only see nothing but doom for Zend's IDE user base, which is really too bad...

Monday, November 9, 2009

Simple Optimizing Tips for PHP - Part I

As we write PHP code (or any language for that matter) we begin to start thinking about optimizing code. But even though our intentions are for "the greater good", we sometimes miss things that should be obvious. This article is one of many to come that hopes to point these things out to you, and in the process train your mind to think of quicker steps you may otherwise haven't really thought about.

Today's article takes a look at:

Loop Arrays
When we use loops, the goal is simple: iterate through an array set, and do something with the results. Most developers understand this quite well but they build the basic looping structure without considering how poor methods of iteration will affect their code's performance.

Example:

$sql = "SELECT * FROM `user`";
$rst = $dbh->fetch_rows($sql);

for($i = 0; $i < count($rst); $i++) {
// do something
}


For some, the problem is quite clear. For others, the code above seems quite valid and perfectly fine. However, the above can cause serious latency in your application(s) if you follow this type of constructing loops enough times.

The issue exists with the loop piece $i < count($rst);

Consider that the query we used returns 2000 records. While the for loop is completely valid, the issue exists in that, for each iteration, you are now counting the number of results ($rst) in each and every iteration!. This means that if we have 2000 records, it's going to get a count of our $rst array 2000 times!

What's worse is that when you issue the count() function on a big array, it must load this array into memory, check it, and then finally clear the tmp data. This in turn puts a small hamper on the server when done once, but when we are doing it 2000 consecutive times just for the sake of iteration, the overhead becomes very evident.

The solution for this is quite simple. You should first just get a count of the number of records, assign it to a value before the loop, and then use this for your iteration. For the PHP engine, is much faster to check a flat numeric value 2000 times rather than issue the count() sequence (explained above) 2000 times.


The Full Solution

$sql = "SELECT * from `user`";
$rst = $dbh->fetch_rows($sql);

$total_users = count($rst);
for($i = 0; $i < $total_users; $i++) {
// do something
}


Where the above will find some flack from experienced users is the situation where you may really need a count of an array. Such situations include: removing a key from an array, adding a new key to an array, popping values off an array, etc...

For these situations, the solution is not to use a for loop, but rather a foreach loop. Since a foreach loop iterates one array key at a time, we can remove/add array keys without the need to keep a count. Plus, if you really want to keep track of your $i variables count you can just assign $i before foreach loop and increment it within the foreach.

Example:

$sql = "SELECT * FROM `user`";
$rst = $dbh->fetch_rows($sql);

$i = 0;
foreach($rst as $key => $value) {
echo "|| $key => $value => $i || ";
$i++;
}


While the above can be optimized a hundred different ways, the point is that we should be taking into consideration how a script in PHP operates. Instead of just forcing it to carry out redundant operations and continue to put more overhead/waste of processor cycles on our server, why not try to optimize the code and make a cleaner, faster application?

Tune in next week for my next article: Recursive Functions.

Wednesday, November 4, 2009

PHP Coding Standards

I wrote this a few years ago in order to create a more standard approach to developing PHP code. Enjoy...

/**
* @author Jonathon Hibbard
*/

PHP Coding Standards

Contents
1 PHP Coding Standards
2 Benefits of Coding Standards
3 Coding Standard Used in this Document
4 Identifier Naming
5 Keep Functions to a Maximum of One Page
6 Whitespace and Indentation
7 Placement of Parenthesis and Curly Braces
8 Line Up Like Values
9 Add a Space between Variable Definitions
10 File Structure and File Naming (More Work Needed in this section)
11 Comments
12 A Full Example
13 Conclusion
14 References



PHP Coding Standards
This document provides a complete coding standard to resolve conflicts that may arise when developers share and/or debug code. It is important for every developer to understand the benefits of such standards, how to use them and how not doing so affects others. Each developer adapts to their own methods and forcing them to change usually results in a negative result.

With that being said, it is encouraged rather than required that you follow this standard. Coding standards do not have to be a difficult thing to conform to. They actually do more good than bad, especially in an environment where such standards do not already exist. This document's intension is to put standards to use and make for a more team-friendly development environment.


Benefits of Coding Standards
Standards benefit both developers and businesses alike. It applies conventions that make it significantly easier for both current and subsequent developers to understand how the application they work on operates. It also helps with how to fix or extend existing code for new needs.

Example:
$a = $b * c;

The above is 100% valid and works, but it's very unclear to its true meaning. What are the variables true representations? How hard is it going to be when we have more variables like $aa, $az, $z, or others that obfuscate this further?

A Standard-Correct Example:
$weekly_pay = $hours_worked * $pay_rate

This describes both the intent and meaning of the variables, the end result and makes it very easy for anyone else to decipher who may stumble upon this line.

Coding standards also are beneficial in that when used, they can enable a transparency to the code, eases debugging and can increase code maintainability.

Quote from the Java Standards Webpage:
  "Why have code conventions?
Code conventions are important to programmers for a number of reasons:
* 80% of the lifetime cost of a piece of software goes to maintenance.
* Hardly any software is maintained for its whole life by the original author
* Code conventions improve the readability of the software,
allowing engineers to understand new code more quickly and thoroughly."



Coding Standard Used in this Document
The coding standard used in this document is very closely tied to the C++ (and C) language called the Kernighan and Ritchie (K&R) standard.

There are other standards (including the ANSI C++ standard, Java standard, Pascal Standard and hundreds others) that developers use, but this standard is much more universal in many ways. The primary way being that it conforms to other adapted models outside of programming (such as the MySQL and SQL database server's standards) and has been widely used for over a decade now.


Identifier Naming
Identifiers are names given to variables, functions, objects, etc.

Given this understanding, you should follow these rules when defining an Identifier:
1) Do not use a leading underscore. They are usually reserved for compiler or internal variables of a language and can mislead a developer into thinking it's a SYSTEM identifier instead of a custom built one. Example of an incorrect name: $_first_name;

2) Do not use names that are like standard system identifiers as these words are reserved words for a reason, and doing so will make the application more difficult to understand. Example of an incorrect name: $for;

3) Give descriptive meaning to identifier names. Example of a bad name: $fn Example of a good name: $first_name. However, make sure to keep in mind not to get too descriptive since this will result in a lot of typing for the rest. Try to keep it short, but to the point. This rule can also be relaxed a little more for instances where short variable names such as $i are used in for loops.

4) Give descriptive meaning for functions that contain a mix of verbs and nouns separated by underscores.
Bad Example:
function first_name() {

Good Example:
function get_first_name() {

Once again, this is considered the K&R standard. The other method is the ANSI C++ Standard, which capitalizes the first letter in each word (getFirstName). However, since we are using the K&R Standard in this document, it is required you follow the K&R naming conventions. Function names can easily be dismissed by a developer who is in the heat of the moment in code. It's very easy to call a function "doit" instead of "get_first_name".


Keep Functions to a Maximum of One Page
This is not an absolute rule as much as it is a helpful practice. Functions that exceed 1 page are by default a little harder to decipher both during creation as well as debugging. Scrolling up and down a page can cause confusion as to what function you are in, where the braces line up, and also how something in the beginning of the function worked. This becomes more evident when opening a file in a console editor like VI or EMACs.

Plus, if you find yourself writing more than a page for a function, it may be a very good indication that you might need to break the function down to 2 or more separate functions, that the function could instead be used as a recursive function, or simply that the method you are choosing is not the quickest route.

This does not mean that you should obfuscate your code in order to meet this requirement. If the function absolutely must extend a page (and usually this is the case when formatting arrays correctly) then go ahead and do it. Just keep it in your head that if you can put a piece of code in its own separate function to be used later, do it.


Whitespace and Indentation
Most modern editors today allow you to define how many spaces a tab can be. They also offer the ability to decide if you would rather use the tab (\t) character when the TAB key is pressed, or if the TAB should be interpreted as X number of spaces.

The preferred method is that all TABS be handled as spaces with 2 spaces as the default (4 is usually the system default). If you are used to the standard Window's tab of 4 spaces, this may seem a little awkward at first. Rest assured, it will reduce the number of column space your code is taking up, but also give it a more structured and easier flow.

Finally, it should also be common practice to change how new lines are handled with your IDE. The default should be set to treat new lines as the Unix \n (new line) instead of the Microsoft default \r (Return). The reason is that \n is a universal default supported in all Operating Systems, so no matter what OS your code is opened up in, the new lines will be nicely available on each new line instead of one concurrent line.

Placement of Parenthesis and Curly Braces
This is perhaps one of the most difficult subjects, not because of its complexity, but more of an infringement of a developer's habit. The ANSI C++ standard has us put curly braces on their own separate lines after ifs, loops, function declarations, etc. The K&R standard, however, has us put curly braces on the same line.

Example ANSI C++ Standard
if (this_condition == true)
{
//do something
}
else {
//do something else
}

K&R 1TBS Standard Example

if(this_condition == true) {
//do something
} else {
//do something else
}

This document requires you to use the K&R Standard as was first defined in the beginning of this text. It is very important that this method be used in order to reduce the need of other developers to reformat your code in order to bring it up to scope with this standard.


Line Up Like Values
When you have a list of variable declarations, or a large array definition, it's a good idea to line these values up. It helps with readability and is key to making your code clean for other developers.

Bad Example
$first_name = 'John';
$last_name = 'Doe';
$city = 'Cincinnati';

Good Example

$first_name = 'John';
$last_name = 'Doe';
$city = 'Cincinnati';

As you can see, the second example makes it much more legible. One more exaple would be for an array;

Bad Example:
Array('first_name' => 'John', 'last_name' => 'Doe', 'city' => 'Cincinnati', 'address' => array('street' => 'PO Box 20', 'zip' => '45241'));

Good Example:

Array('first_name' => 'John',
'last_name' => 'Doe',
'city' => 'Cincinnati',
'address' => array('street' => 'PO Box 20',
'zip' => '45241',
),
);



Add a Space between Variable Definitions
When defining a variable to be equal to a value, always make sure to put a space between the variable and the equal sign, and also a space between the equal sign and the value.

Bad Example:
$i=0;
function foo($bar=1){
}

Good Example:
$i = 0;
function foo($bar = 1) {
}


Filesystem Naming Conventions (Files/Directories)
Both files and directories should be named by default by all lower-case. They should be descriptive in their meaning, but also limited in length. This is not only because both Linux and Unix servers are case-sensitive file systems, but also because even web addresses typed in capitalized lettering will, by default, be translated as lower-case values (IE: WWW.GOOGLE.COM will be switched to www.google.com on Enter).

A typical file structure should have the following structure:

/admin/
/apps/
|- classes
|- includes
/css/
/js/
/utils/
/xml/


The admin directory is where backend application files that would require a login and admins a page should go. The apps directory has 2 sub directories: includes and classes. Includes are files that are included simply to reduce repetition (such as header or footer files), whereas classes is where files containing class definitions are stored. The css directory, as the name implies, is where style sheet (css) files will go. The js directory is where JavaScript files would go. The utils directory is where either 3rd party applications or reusable applications (such as thumbnail generators) would go. The xml directory is where xml files will go.

Example of a bad filename
1.php

Example of a GOOD filename
list_users.php


Comments
Comments play a major role in any development environment. It not only helps out an individual better debug their own code, but it is basically a manual to how your code works. Commenting should follow the DOC standard for commenting.

The following is an example of this:

/**
* A brief description of what you are commenting goes here.
*/


Commenting is vital with any team. Over commenting can cause your code to be very confusing, under commenting could leave many questions regarding what your code actually does.

You should always comment in these circumstances:

* Whenever you define a function or class
* Whenever you define a variable that does not describe itself properly.
* Whenever you write a "hack".
* Whenever a block of code is foreign to you.
* Every new page you create should have a description at the top indicating its purpose.
* If something you do is not 100% and needs to be rewritten in the future.
* If something you do is not 100% obvious.


The PHPdoc comment is very intuitive and supported by most popular IDE's.

Some of the most popular keys used with this commenting style are:

@author // Indicates the author's name
@param // Describes a parameter for a function
@copyright // The copyright information (if applicable).
@return // The return value of a function
@todo // Gives you the ability to write-up a "todo" list.



A Full Example

/**
* @author John Doe
* This class' purpose is to provide a control area for handling database connections,
* errors and results.
*
* @copyright ACME
* @todo This class requires a new method of error control.
*/
class db_handle {
/**
* This is the database handler variable.
*/
public $dbh;

/**
* This is the constructor of the class.
* @param string $strDSN // String containg connection information
* @return object // Returns the db_handle object.
*/
public function __construct($strDSN) {
// Do Something.
}
}



Conclusion
This is the end of the current Coding Standards document. There are a few other concepts such as private/public variables, unit naming, and other high-end programming techniques that can be applied, but are not due to this not being about "how to program", but instead is about coding standards.


References
http://en.wikipedia.org/wiki/Coding_standard - Wikipedia Definition of a Coding Standard
http://en.wikibooks.org/wiki/C%2B%2B_Programming/Code_Style - Code Standards
http://en.wikipedia.org/wiki/Indent_style - K&R Coding Style Definition
http://dn.codegear.com/article/10280/Code - Great Article relating to the K&R Coding Style