Phrap - a super-light PHP database wrapper using PDO for basic CRUD

If that doesn’t sound sexy, well, that’s because it isn’t. 

I often write light web-service type apps that require simple DB access, but are far to simple to spin up a big ORM like Doctrine. Phrap is a tiny library (script) that provides simple read/write/delete to the DB using PDO to handle escaping and such.

It reads, it writes, it deletes. It doesn’t do associations, joins or validations, unless you feel like forking and adding. It keeps you from having to write a lot of SELECT * FROM blah blah blah and $stmt->prepare() every time you need something from your database, yet its stupid small so it doesn’t drag on your resources (much).

This was something I needed for myself but I also used this little project to teach myself PHPUnit, and yes, I now see some of the benefits of testing your software.

Speaking of which, read the tests to get a better idea how it works.

Get it Fresh on the GitHubs

A little Phrap:

class FileUpload extends Model
{
    public $table = "files";
    public $id;
    public $filename;
    public $userid;
    public $email;
 }

// Create
$dbh = new DB($database);
$file = new FileUpload($dbh);
$file->create();
$file->set(array('filename' => 'heffalump.txt', 'codswoddle' => 'phiminster'));
$file->save();

// Query
$result = $file->find('first');
$result = $file->find('all');
$result = $file->find('first', array('email' => 'dazza@email.com'));
$result = $file->query(
     'SELECT * FROM files WHERE email = :email ORDER BY userid LIMIT 1',
     array(':email' => 'dazza@email.com')
 );

// Delete manually by id
$file->delete(1);

// Set id in object then delete it
$file->find('first');
$file->delete();

Leave a comment

I spent a very frustrating hour this morning trying to get the JSON gem to work on Ruby 1.9.2 p136 installed via RVM on Snow Leopard. I thought I would post my solution to help any other unfortunate souls.

The RVM docs suggest installing a sandboxed version of Iconv like so:

$ rvm package install iconv

This didn’t work. I got archictecture errors during compile. I then tried to install the iconv gem, which again didn’t work. It couldn’t seem to find the iconv lib files.

In the end, I installed icon with Homebrew and linked it to Ruby like so:

$ brew install libiconv
$ rvm remove 1.9.2
$ rvm install 1.9.2 --with-iconv-dir=/usr/local/Cellar/libiconv/1.13.1

This compiled Ruby with the Homebrew installed iconv libraries and the json gem worked just fine after.

View comments

I’m currently working on a large print project. It’s a membership directory for a non-profit organization and I’m laying it out with Adobe® InDesign. The client handed me a CSV dump of their member database for the directory. I’ve mentioned more times than I can count how much I dislike repetition (cutting, pasting, rinse, repeat) so I cooked up some Ruby scripts to parse the CSV into various XML and plaintext formats. 

Hash it.

To make life easier I converted the entire CSV file into an array of hashes like so:

require 'csv'

def csv_to_array(file_location)
 csv = CSV::parse(File.open(file_location, 'r') {|f| f.read })
 fields = csv.shift
 csv.collect { |record| Hash[*fields.zip(record).flatten ] } 
end

test = csv_to_array('test.csv')

This gets the CSV into a nice array loaded with hashes which names each entry with its corresponding field:

{"name"=>"Larry", "company"=>"Google"}
{"name"=>"Curly", "company"=>"Apple"}
{"name"=>"Moe", "company"=>"Microsoft"}

Re-shape it

Getting the CSV into an array of hashes is pretty convenient. I can now rip through the hashes and do whatever formatting I need. For instance, I needed to create a plaintext file that converted each row in the CSV into a Name, Company format followed by a line break, which could easily be pasted into an InDesign text box:

def csv_to_text(file_location)
  out = ""
  rows = csv_to_array(file_location) # get our CSV into an array of hashes
  rows.each do |row|

  # Use a HEREDOC to get the formatting we want

tmp = <<HERE      
#{row["name"]}, #{row["company"]}
HERE

  out = out + tmp
  end

  save_text(file_location, out) # save a text file

end

This is all pretty simple, but it saves on a lot of cutting and pasting text from fields in Excel or Numbers, the kind of thing I really dislike. You can also do a lot of text transformation during the same process. Many of the addresses in the CSV needed to be made consistent. I wanted common words like Street, Boulevard, Avenue to get transformed into common abbreviations like St., Blvd., and Ave. We need a function:

def process_address(data)
  if data.nil?
    return data
  end

  data.squeeze!(" ") # remove any double spaces
  data.strip!        # remove trailing spaces
  data.gsub!(/Res: /, '')
  data.gsub!(/Court/,'Ct.')
  data.gsub!(/Suite/, 'Ste.')
  data.gsub!(/Drive/, 'Dr.')
  data.gsub!(/Road/, 'Rd.')
  data.gsub!(/Street/, 'St.')
  data.gsub!(/Lane/, 'Ln.')
  data.gsub!(/Avenue/, 'Ave.')

  data  
end

Now we can pass our rows through process_address when writing to the text file to get consistent abbreviations. (I could probably optimize that function by dropping the abbreviations into a hash and iterating through them.)

These are pretty simple little scripts, but they help to quickly automate some very tedious tasks. They’re also an example of how scripting languages can help you tackle data entry issues when working with your design tools. For this same project I also needed to generate a large XML file that InDesign could parse and use to build the member directory on its own. I’ll cover that chunk of code in my next post.

Leave a comment