Thursday, January 17, 2013

PostgreSQL fails to install on Windows

Problem:
Needing a database with a little more flexibility than MySQL, I decided to give PostgreSQL Server a try. While installing the latest version on Windows 7 (64 bit), I ran into an issue where the installer would freeze after entering the port to use. Originally I thought my download was corrupt, so I downloaded it again and tried an older version, but all of them would quit right after asking for the port.

Solution:
I still don't know exactly why the freeze happened, but upon restarting my computer, the installer completed successfully. My guess is that something I had uninstalled earlier in the day causing something in the system to need a reset.

Yes, another one of those, "Restart your computer" fixes, but it worked.

Wednesday, January 16, 2013

Sphinx Search - Error 1067: The process terminated unexpectedly.

Problem:

Installing Sphinx Search on my computer to do some testing, I bypassed the heavy documentation in hopes of a quick start-up. Instead, on trying to start the Sphinx Search on Windows, I was met by an error reading:

Windows could not start the SphinxSearch service on Local Computer.
Error 1067: The process terminated unexpectedly.

Solution:

This is generally caused by the configuration file being setup incorrectly. Ensure you have configured the conf file correctly before starting the Sphinx Search service. To determine exact errors, trying running the search daemon from the sphinx bin directory.

C:\sphinx\bin> searchd
C:\sphinx\bin>indexer --rotate --all

If you're still not sure how to get going with Sphinx Search, check out this fantastic video walk through:




Saturday, January 12, 2013

PHPActiveRecord Fix to Support Foreign Characters Charset

Problem: 
While developing a multilingual application for a client using PHPActiveRecord, I noticed that foreign characters were not getting read properly from the database due to an incorrect charset.

Solution:
The problem is two fold. First, the database connection used needs to be set to have a UTF8 charset. Second, the documentation for UTF8 charset support for PHPActiveRecord is wrong.

From PHPActiveRecord documentation:
1 $config->set_connections(array(
2   'development' => 'mysql://user:pass@localhost/mydb;charset=utf8')
3 );


The correct version / the fix:
1 $config->set_connections(array(
2   'development' => 'mysql://user:pass@localhost/mydb?charset=utf8')
3 );
Notice that the end has a ? before charset and NOT a semi-colon. This is because this path is parsed using PHP parse_url which doesn't recognize the content following the semi-colon as a query.

Friday, January 11, 2013

MySQL Garbled Foreign Character Fix

Problem:
Working with a client to migrate their data into a better planned and more normalized database structure, we ran into the issue of foreign characters getting messed up once in the new system due to failing to set the correct character type.

Solution:
Luckily, Michael Chu had an awesome solution. Thanks, Michael! As he mentions in his solution, this doesn't always seem to work, but I had a lot of luck with it.

For varchar fields:

ALTER TABLE table_name MODIFY `field_name` BINARY(255);
ALTER TABLE table_name MODIFY `field_name` VARCHAR(255) CHARACTER SET utf8;

For text fields:
ALTER TABLE table_name MODIFY `field_name` BLOB;
ALTER TABLE table_name MODIFY `field_name` TEXT CHARACTER SET utf8;

Note:
There have been times when this does not work and I've also read that using the CONVERT keyword can help make the process better.

In some cases, I have had to run this to make it appear correctly.

UPDATE table_name SET col_name = CONVERT(CONVERT(CONVERT(col_name USING latin1) USING binary) USING utf8);

Grocery CRUD Column Naming Conflict Solution

Problem:
While working on a quick project for a client, I ran into an issue where related tables were being joined together had conflicting column names. Because both tables shared some column names, the field names were not unique to each table causing the query to fail due to ambiguity. Some might think it is related to flexigrid which is used in grocery, but it has nothing to do with flexigrid.

Solution:
After digging around in undocumented grocery CRUD, I decided to look online to see if anyone had the same problem but I didn't seem to find any helpful results until I struck pure gold. Thank you, Eran, you saved me!

Eran's Original Forum Post

In order to fix this issue I added the following function to the grocery_CRUD_Model class in the grocery_crud_model.php file:
/**
         * We use this function to make sure that the format for each field is <table_name>.<field>
         * @param string $field
         * @return string
         */
function long_field_name($field)
{
if ((!is_null($this->table_name)) && (trim($this->table_name) != '') && (strpos($field, '.') === false)) {
         return "$this->table_name.$field";
} else {
         return $field;
}
}

This code adds the table name to the field name if it doesn't already exists.

I added a call to this function in the following functions: like() and or_like() (both are in the grocery_crud_model.php file)
function like($field, $match = '', $side = 'both')
{
         $this->db->like($this->long_field_name($field), $match, $side);
}
function or_like($field, $match = '', $side = 'both')
{
         $this->db->or_like($this->long_field_name($field), $match, $side);
}

I also changed the _get_field_names_to_search() function from the grocery_crud.php file:
protected function _get_field_names_to_search(array $relation_values)
{
  if(!strstr($relation_values[2],'{'))
          return $this->_unique_join_name($relation_values[0]).'.'.$relation_values[2];
  else
  {
   $relation_values[2] = ' '.$relation_values[2].' ';
   $temp1 = explode('{',$relation_values[2]);
   unset($temp1[0]);
  
   $field_names_array = array();
   foreach($temp1 as $field)
        list($field_names_array[]) = explode('}',$field);
  
   //New Code - this was added to make sure that relations using more than one field in the
   //$related_title_field paramter, will be using the unique table name infront of the field name 
   foreach ($field_names_array as $key => $field) {
           $field_names_array[$key] = $this->_unique_join_name($relation_values[0]).'.'.$field;
   }
   return $field_names_array;
  }
}

JavaScript file in HTML head wont run

Problem:
Recently while developing I ran into an issue where I was dynamically adding JavaScript files to my page using PHP. The problem was that none of those JavaScript files would execute their code.

Solution: 
After looking for any errors in developer tools and not getting any, I was stumped. I first tested by adding console logging code to the JavaScript files to ensure they never got run. Once I had done this I went about removing the statements from the page to see if placing things back line by line would improve the situation. Finally, I noticed that the type value was incorrectly set in the script tag. Once this was changed from "text/javscript" to "text/javascript" my JavaScript files began to run correctly.

Monday, January 7, 2013

View a Google Cached Web Page

Problem:
Recently I was working with a web site and suddenly the information on it changed overnight. I really needed to know what was on the old page but had failed to take my own screen shot.

Solution:
Google keeps a limited time cached copy of the web site. You can access the cached page using the following URL (changing out example.com for your website) :

http://webcache.googleusercontent.com/search?q=cache:http://example.com/

Try it!