NOTES: Inserting MySql with 0 as primary key

This is just a reminder to avoid inserting (usually in a data dump) rows with 0 as an ID on an auto incremeted primary key. This will result on the inserted row's primary key to be incremented with the last inserted ID and wont insert it as a 0 primary key.

Took me awhile to figure out a why my data dumps are not populating correctly.

++i VS i++

Upon studying the basics of PHP to brush up my knowledge since I plan on taking the Zend Certification :D, I came accross wondering whats the difference between using $i++ and ++$i in our loops.

I have always used $i++ since its the traditional way of incrementing a loop but upon further research it seems like using ++$i is a tad bit faster. An explanation of this is because $i++ makes a "copy" of $i then increment then return whereas  ++$i increments $i then return it.

This bit of code will explain it a little clearer:

$i = 0;

$t .= "First instance: "  . ++$i  . PHP_EOL;
$t .= "Second instance: " . $i++  . PHP_EOL;
$t .= "Third instance: "  . $i    . PHP_EOL;

$i++;

$t .= "Fourth instance: " . $i    . PHP_EOL;
print nl2br($t);

This will print:

First instance: 1
Second instance: 1
Third instance: 2
Fourth instance: 3

As you can see, $i++ doesn't return the value right away; It makes a copy and return it at the end of the print execution where as ++$i returns the value of $i right away like what you can see in "First instance: 1"

MySql GROUP BY queries in older MYsql version

I decided to document this little situation I had recently with an SQL statement since its been a couple of times I encountered this kind of problem.

This may not be the best example but here it is
Heres the table:

Table: Deceased

Id        |         Name          |          Death           |

1         |         Franz           |          1989            |

2         |         Franz           |          1700            |

3         |         Franz           |          2010            |

5         |         John             |          2006            |

6         |         John             |          1980            |

Requirement: We want to query the person in a group with the same name who died the most recent.
So if we were to query the table above we want to get the result:
row: Id: 3, Franz, 2010
row: Id: 5: John, 2006

So how would we do this? There maybe many solutions out there but this is the sql I thought of out of hand:

SELECT *, MAX(Death) as Recent  FROM Deceased
GROUP BY Name HAVING Death IN (Recent)

This SQL above may work if you have MYsql's ONLY_FULL_GROUP_BY mode disabled but will give you headaches if it is on or if you have an older version as of mysql (<= MySQL 5.0.23). Learned that the hard way. :D

So heres my solution if you have only_full_group_by mode turned on:

SELECT * FROM Deceased

GROUP BY Name, Death HAVING Death IN (SELECT MAX(Death)  FROM Deceased

GROUP BY Name)

OR

SELECT * FROM Deceased WHERE

Id IN (SELECT IF(MAX(Death), Id, null)  FROM Deceased

GROUP BY Name)

Yep a subquery and yep it may be slow. A little explanation on the last query. I used Id with a sub query because I realize that if I used the death column, there may be people who died on the same year that is not the result of a MAX therefore those years are not unique so we have to compare against the ID. One catch is a subquery needs to have 1 column so we have to use an IF().

MySQL's FIND_IN_SET()

I recently discovered a neat function in MySql called find_in_set(str, strlist). It basically takes an argument X as your string, and searches the inputed string list.

Say for example you have a column in your table named "country" and it stores the country a specific person visited to separated by a comma (,). Sample table below:

Table: Person

|   Name                   |    Country                            |

|   Michael Jabroni    |    USA, Brazil, Japan           |

To use find_in_set() now you can have a select that looks like this:
SELECT * FROM Person WHERE FIND_IN_SET('Brazil', Country)

Seemed useless right because why not use LIKE '%Brazil%' instead.
Say instead of having comma as a delimiter someone used a colon (:) instead for the database.

Table: Person

|   Name                   |    Country                                                       |

|   Michael Jabroni    |    USA:Japan:Africa:China:South Africa           |

|   John Maldoe        |    China:South Africa                                       |

(I know Africa is not a country,  but for the sake of example.)
Further more, if you want to select a person that ONLY visited Africa, you wont be able to do so because it will also select people that visited South Africa. In addtition, you cannot use find_in_set() because it requires a comma as its delimiter.

One solution you can do is used another MySql string function called REPLACE() with FIND_IN_SET().
A revised select below:

SELECT * FROM Person WHERE FIND_IN_SET( 'Africa', REPLACE(Country, ':', ',') )

This will replace all instance of a collon (:) to a comma(,).
Now you can select only people who visited Africa.

Taking advantage of Exceptions in PHP

Another post about errors.

Exceptions are often overlooked because of the extra setup you need to do in order to create a good design even though I myself am not a guru of any object oriented design principles. Nevertheless, a programmer should take advantage of its capabilities.

 Here is a simple way of using an extended exception in php.

$franz = 'alien';

 try{

   if($franz != 'human'){

     throw new My_Extended_Exception('Error, you are not a human. Go back to your planet', 306);

   }

} catch (Exception $e) {

   echo $e->logError()->outputError();

}

The neat part with extending an exception is you can play with the class and do your own logging. As an example.

class My_Extended_Exception extends Exception

{

  protected $_errorMessage;

  public function __construct($message, $code)

  {

    parent::__construct($message, $code);

  }

  public function logError()

  {

    $this->_errorMessage = $this->message . ' code:' . $this->code . ' on line: ' . $this->getLine() . ' in file: ' . $this->getFile() . PHP_EOL;

     file_put_contents("errors.txt", $this->_errorMessage, FILE_APPEND | LOCK_EX);

    

     return $this;

  }

   

   // sends the error to the browser;

   public function outputError()

  {

    if($this->_errorMessage){

      return $this->_errorMessage;

    }

  }

}

A little about the code. By extending our exception we added a little ability to log our error messages then also output it to the browser. We basically automate the process and should have control on our errors.
Another big plus using an exception is the native methods of the error class. Here I used 2 which is getLine() and getFile(). Pretty self explanatory which will get the line number of the error and also the file where it came from. There are more of these methods in php.net documentation. I also passed the $message and $code argument to the parent class which is Exception in order to access the message and code property directly (eg: $this->message).

And thats it! hope it helped some.

Changing error messages in Zend Framework's Zend_Form

I was figuring a way to simply change an error message in Zend_Form specifically the Zend_Form_Element_EmailAddress because I dont like the default error message "is not a valid email address in the basic format local-part@hostname".

A simple way to do this is to use the setMessage() method of the Zend_Validate_EmailAddress. Here's my solution below:

$email = new Zend_Form_Element_Text('Email');

$email->setValidators(array(
                 array('EmailAddress', true),

               ));

$email->getValidator('EmailAddress')->setMessage('Ooops, %value% is wrong!!!', Zend_Validate_EmailAddress::INVALID_FORMAT);

In the code above, I have set the EmailAddress (Zend_Validate_EmailAddress) validator as a validator of my form's email element. In order to mess with the message, we need to retrieve the validator object by using the getValidator method of the Zend_Form_Element. From here you can access the set method setMessage and all you have to do is replace it with your new message, and target the message key which is Zend_Validate_EmailAddress::INVALID_FORMAT. Remember that setMessage overrides the default value of the INVALID constant.


Thats pretty much it!

PHP's array_map and trim

I often used the trim function of PHP to arrays. If you look at the documentation of trim in php.net or are familiar with PHP, you would know that it accepts string as its main argument.

To get to the point I always wanted to use trim and apply it to the elements of the array. One work around is to loop through each element and apply the trim to each element but an easier way is to use PHP's native function array_map(). An easy implementation from the code below.

$myArray = array('one', ' two ', ' three', 'four ');

$trimmedArray = array_map('trim', $myArray);

var_dump($trimmedArray);

That's it! One thing to note, if you have memory problems like me :) remembering syntax
you may get confused array_map with array_walk and or array_filter. Array_map() applies
the call back to each element of the array where as array_walk is most often used for walking over the array and just simply printing it. Although you can use array_walk also if you prefer.
Array_filters just filters the element using the call back and return the elements that satisfies you callback filter.

Hope it helps someone.