Andrey Vystavkin Blog

One of the reasons I like Perl – it’s really fast on solving daily routines. The request was to get a list of shipping flags from one of the websites to Excel and paste it to the site’s database. Quick and dirty solution:

#!/usr/bin/perl
 
use strict;
use warnings;
use DBI;
use Spreadsheet::ParseExcel;
 
my $file = 'flags.xls';
 
my $xl_parser = Spreadsheet::ParseExcel->new();
 
my $workbook = $xl_parser->Parse($file);
my $worksheet = $workbook->worksheet('Sheet1');
 
my ($c_min,$c_max) = $worksheet->col_range();
my ($r_min,$r_max) = $worksheet->row_range();
 
my @flags;  # used for storing an array of flags;
 
for my $row($r_min..$r_max) {
 
    for my $col($c_min..$c_max) {
 
        my $cell = $worksheet->get_cell($row,$col);
        my $val = $cell->value;
 
        $val =~ s/^\s+(.*?)\s$//g;
 
        push @flags, $val;
        #some extras on different cells/etc/
 
    }   
}
 
my $dsn = "DBI:mysql:database=db_blah;host=host;port=blah_port";
my $dbh = DBI->connect( $dsn, 'usr_blah', 'pwd_blah') || die("Couldn't connect to database\n");
 
 
$dbh->do("INSERT INTO flags(name) VALUES(".$dbh->quote($_).")") for @flags;

Linux, Memo fun, Linux, perl

We’d have to use propset command from SVN.

$#: cd /path/to/repository/cake_project
$#: svn delete app/tmp/*
$#: svn propset svn:ignore '*' tmp/

Now, after you commit the changes your tmp/files won’t be commited to the SVN repo.

Linux, PHP cakephp, PHP, svn

rpm -qa | grep mysql (find out the version of mysql you got)Somehow managed to break mysqld file (wow! applauses).

Errors received from ‘mysql -u <username> -p <pass>’:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'

On using ‘rpm -e mysql-server’ was getting:

error reading information on service mysqld: No such file or directory
error: %preun(mysql-server-3.23.58-4) scriptlet failed, exit status 1

The solution ended up in:

• rpm -qa | grep mysql (find out the version of mysql you got)
• Get the right version of you RPM
• rpm -Uhv –force mysql-server-xx.x.rpm
• service mysqld restart

Linux, Memo

 find ./ | xargs grep -i search_string

Linux, Memo Linux, Memo, Tips

Nowadays, I was forced to work at the office doing Web Dev using Windows machine. It caused real headaches to setup some of the things from scratch to make in work like on my old Linux machine.

I haven’t thought that vim’ll look so weird from the Windows side, however gVim alternative seemed more adecvate, but the problem got me googling at the point of setting up correct fonts for the themes.

An old fonts vim plugin cause lots of errors, so I had to use SVN to get latest fixes for the Fonts.vim plugin. You can check it in the trunk of the repository; it worked perfectly for me, just copy-paste it to your \plugins\ directory, and play around the font-size.

Links, Linux Fonts, gvim, LAMP, Tips, Windows

As the project grows I had to work on some more complex queries to provide users with better searching facilities.

In this case, you might use two options:

• Straightforward find() function from App::Model (where you’ll have to handle the outpu of data yourself, and trying to fit the search results in your websites layout)
• Use Pagination functionality (which is designed for handling big chunks of data for you)

A simple example: from a small search menu, I need to get the data about item’s price, its type etc, so at this point, find() solution would look like:

$condition = array('OR' => array(
'Item.type' => $this->data['Item']['type'],
'Item.qty' => $this->data['Item']['qty'],
),
array('Item.price BETWEEN ? AND ?' => array($start_price,$end_price))
);
$this->('results', $this->Item->find('all', $condition));

Once you set results array in the view template, it will cause you few hours on how to rearrange data presentation, meanwhile you can use Pagination:


/*
* I'm going to use the same $condition
* the difference will be at the view level and the way of setting the data "results"
*/
$this->set('results', $this->paginate('Item', $condition);

And in the view you might add some code like:

echo $paginator->counter(array(
'format' => __('%page% of %pages%, showing %current%
records out of %count% total, starting on record %start%, ending on %end%', true)
));

foreach($results as $i => $item):
/*
* Items output
*/
endforeach;
?>
prev('<< '.__('Previous Page', true),
array(), null, array('class'=>'disabled'));?>
| numbers();?>
next(__('Next Page', true).' >>',
array(), null, array('class'=>'disabled'));?>

Last lines of the code would manage the results listing for you, which has to be defined in your Controller:

var $paginate = array(
'Item' => array(
'limit' => 5,
'order'=> array('Item.added' => 'ASC')
)
)


Done, now you can easily handle your search outputs.

Heap, Linux cakephp, IT, pagination, Tips