MarkFightsCode

A start in SEO.

2011-12-27T04:30:00.000-08:00

A long time since I posted here. I have to admit my attention has been grabbed by the necesity of finding things to do that may secure me future contracts.
My latest project has been UpSpainNews which is a directory of start-ups from Spain. One thing puzzled me about this site - why was my website not in the first few pages of Google search? It is (so far as I know) the only web directory specifically on Spanish start-ups in the English language. Of course you can come across some of those entries if they are listed in Crunchbase or in AngelLists, but my listing is fairly unique. Yet Google doesn't recognise this if you search for "Spanish start-ups".
So the last week or two I've been looking into the murky world of SEO. The first thing I realised I didn't do, was to put relevant meta-tags on my web-site. I've now entered a line in my html code as follows:

<meta name="description" content="A directory of Spanish start-ups, the best way to read up on those start-ups in English" />

With luck, the Google robots will pick this description up in the next week or two. It means that when my page is listed on a google search, the description will be shown as above ie "A directory of Spanish start-ups, the best way to read up on those start-ups in English". Instead of the current text which is "About this site · Directory of Startups · Company entries; General Up news; Contact. Startup of the week. Screenshot. Adtriboo - source talent! Internet jobs" and is obviously a mish-mash from the site text.
That in itself won't put me on page 1. I also have to get more backlinks to the site, and (perhaps more importantly) get articles published on other sites that link back. I have made a start by donating articles to EUStartUps.com and hope to continue along this line.
I hope to edit this article in a week or two with better news on my Google page ranking.

EDIT 5 Jan. The google bots have updated their records, and my site now makes it onto Page 2 for the search "Spanish startups". Also, I come in top of Page 1 when I search on "Spanish startups directory". :) In order keep this good position, I will now try to build up links on high ranking sites, and perhaps continue to write articles for other sites that link to my site. I've made a start with this entry Spanish start-ups to look out for in 2012. Happy reading!

Running cron jobs for Drupal on a shared host.

2011-05-07T08:44:00.000-07:00

Ok, this isn't normally the hardest thing in the world to set up, but as I didn't have shell access I didn't get around to doing this (and ran the cron jobs manually).
In fact, setting up automatic cron jobs via Cpanel is quite easy (and you can read more about this - here
On Cpanel, in the "Advanced Section" you can click on cronjobs. There are fields you can enter to determine how often the cron job is run. For example, if you enter 15 in the Minute box, the job can be run at 15 minutes past the hour. The top box allows common settings eg once a week, or once a hour, so you don't need to enter criteria directly into every box. The most important task though is to enter your command line correctly. If you have lynx enabled on your Linux server, you may wish to use that, but the wget command is probably more common on nix boxes. So for my job at www.mygreatsite.com I put in the following command:

wget -O - -q -t 1 http://www.mygreatsite.com/cron.php

The -t 1 switch merely states try again once upon failure.
Anyway after pressing the "Add Cron job" button, that's all that needs to be done. The crontab program will perform your regular updates from now until the program is modified, or the server blows up!
If only other IT tasks were so easy to set up and then leave alone.

How to replicate a "GROUP BY" clause in Excel

2010-12-27T04:37:00.000-08:00

As a Data analyst, I often use the “group by” clause in sql to aggregate records against a key field. For instance, to bring back records where a field has greater than 10 entries I’d use a statement like “select city, count(*) from city_sales group by city having count(*) > 10”
However I’m currently in a role where I can’t use a database :( So, can Excel do the business? Well yes, pivot tables can provide a workaround. It’s a bit more involved than using a decent database (and obviously you’d need that database if you’re dealing with hundreds of thousands of rows) but at least it’s an option.
Here’s a short example. Supposing I have various sales records for different cities (see data below). I’ve just listed a few records, but there could be 100s or 1000s of records. Supposing I wanted to see the total sales figure, for those cities with greater than 3 sales?

What I’d do is create a couple of pivot tables. The first one involves putting the city in the row label, and then putting the inv_num field in the data values area. Note that I have to change the “Value field settings” here to show the count (and not the sum). It’s then easy enough to change the sort by to a descending sort on the count of inv_num.

I now create a similar pivot table, but this time I put the Amount field into the Data Value, and this time keep the formula as “Sum of”.

It’s now a simple matter to copy the relevant data, and then use VLOOKUP to bring back the sum of sales for those cities you’ve brought back.

Remember I can also put a field in the Report filter (equivalent to a Where clause in sql) so I could (for example) select only cities from a specified area.
I still prefer sql by far for this type of calculation, but if Excel is your only tool, at least we have this option of the pivot tables.
Hope this is of use to someone out there!
Mark

Getting Wordpress to use stylesheet on new theme

2010-10-24T04:04:00.000-07:00

Sometimes you miss the b***** obvious. I'd started making a theme from scratch in Wordpress, put the correct reference to the link in the header file, and made sure the css file was correctly named and in the correct theme folder. But no matter how many times I reloaded the page, the css rules were not imported.
It turns out I hadn't called the header php page from index.php . I should have put this <?php get_header(); > in the index.php file. All that time wasted due to a simple oversight! It is an occupational hazard when programming, but it's so frustrating. :(

How to use a graphic to link elsewhere in WordPress

2010-10-09T01:48:00.000-07:00

I wanted to change a graphic image into a clickable link, on a Wordpress site (La Liga referee statistics )I've been developing and hosting for a Spanish colleague.

The first step was to enter the code for the text link on the page

<a href="http://www.losarbitroscuentan.com/index.php?page_id=8790" title="Los Arbitros Cuentan - Primera - Click!" id="test-logo">Primera Division</a>

You can see that the id is specified as "test-logo" The page_id (so you go to the correct link) is obtained within Wordpress editor, looking at the pages and pressing right-click, to copy the link location, and then pasting into Notepad - or merely view the URL in your browser.

Now to allocate that link-text to a graphic, we change the relevant css file. This is normally the style.css but this may change. Also, if you are using a theme, you will need the edit the css file in that theme's folder.

#test-logo {
background-image:url(/wp-content/uploads/2010/10/graphicname.jpg);
display:block;
height:37px;
width:190px;
}

So far so good, and clicking on the image will take you to your desired screen. Well, each time you save changes to a css file you should reload the webpage, so it replaces the css info in the cache with the updated data.
Looks promising, but the only problem now is we have both text and graphic conflicting with each other!
There is an awesome technique we can use in css/html - "text-indent" to take the text completely off the screen ie to hide the text

#test-logo {
background-image:url(/wp-content/uploads/2010/10/graphicname.jpg);
display:block;
height:37px;
text-indent:-9999px;
width:190px;
}

Pretty cool hey? Many thanks to this technical site for pointing me in the right direction to accomplish this!

Back in town.

2010-09-14T03:44:00.000-07:00

I've been busy. Moved house (now live in central London). And set up these two websites for a Spanish friend Blinds in Spain and Referee stats in La Liga First one was put together in Drupal and the second one in WordPress.

Writing a count distinct query in MS Access

2010-04-17T06:53:00.000-07:00

I had to do this recently in my new job, and was annoyed to find that Access doesn't support the code

select columnA, count (distinct columnB) from table group by columnA

I did a bit of googling around, but wasn't too happy with the workarounds given. Some discussion, with possible solutions are given here:
discussion on stackoverflow
Well to cut a long story short, I devised my own workaround:

select Job,sum(pp) as number_distinct_fruits

from

(select Job, Fruit, 1 as pp

from Jobtable group by Job, Fruit) t

group by Job

This was based on data like the following
JobA Apple

JobA Apple

JobB Apple

JobB Pear

JobB Plum

JobC Apple

JobC Plum

JobC Plum

JobD Plum

JobD Banana

JobD Plum

JobD Cherry

The results came out as expected:

Job Number_distinct_fruits
==== ======================

JobA 1

JobB 3

JobC 2

JobD 3

This worked ok on my test data, but there are two things to watch out for. Firstly, the code will produce a grouped record for nul/blank entries in the Fruit field. If you don't want this as part of your count, insert a Where clause (Where Fruit is not null) in the inner select. I also found that the Access editor kept chamging the parenthesis for square brackets, so you may have to keep editing this when opening the query. Even so, it's quite an effective workaround I feel.

Using Perl and Mysql to create a webpage

2009-11-21T02:46:00.000-08:00

The normal way to produce a website that accesses data from a Mysql database is via the scripting language PHP. I wondered if it were possible to use Perl instead, and found that with the use of a couple of modules, it's not so hard to get started. (Here's a brief introduction to using modules with web programming.
Here's the Perl I wrote (I called the file empnew.cgi). I made sure the program was executable, and placed the file in a directory on my web server (make sure your path points to this directory):


#!/usr/bin/perl
 use CGI;
 use HTML::Template;
use DBI;
use strict;

my $dsn = 'dbi:mysql:one:localhost:3306:mysql_socket=/opt/lampp/var/mysql/mysqld.sock';
# the database here is called 'one'

my $CGI = CGI->new();
my $user = 'root';
my $pass = 'passwordx';

my $dbh = DBI->connect($dsn, $user, $pass)
or die "Can’t connect to the DB: $DBI::errstr\n";

my $sth = $dbh->prepare("select empno, ename, job, sal from emp order by ename");
$sth->execute;

# Prepare a data structure for HTML::Template
my $rows;
push @{$rows}, $_ while $_ = $sth->fetchrow_hashref();

 #
 # initiate the template and substitute the values:

 my $template = HTML::Template->new(filename => 'empnew.tmpl',
     die_on_bad_params => 0, );
 # call param to fill in the loop with the loop data by reference.
 $template->param(ROWS => $rows);

print $CGI->header();
 # print the template
 print $template->output();

$dbh->disconnect();

The module CGI deals quickly and efficiently with http headers.

The module HTML::Template that is called, allows you to state the Html layout in a separate file. In our example this calls empnew.tmpl



<html>

<head>

<title>Employee Listing</title>

</head>

<body>

<h1>Employees</h1>

<table border=1>

<tr>

<td bgcolor="#ffff00"><font COLOR="#800500"><b>Emp Number</b></font></td>

<td bgcolor="#ffff00"><font COLOR="#800500"><b>Emp Name</b></font></td>

<td bgcolor="#ffff00"><font COLOR="#800500"><b>Job</b></font></td>

<td bgcolor="#ffff00"><font COLOR="#800500"><b>Salary</b></font></td>

</tr>



<tr>

<td></td>

<td></td>

<td></td>

<td></td>

</tr>



</table>

</body>

</html>

There are basically 3 new kinds of tags to be aware of when making a .tmpl file for access by HTML::Template. These are

# * VARIABLES defined by <tmp_var>
# * LOOPS defined by <tmpl_loop>
# * CONDITIONALS you can use <tmpl_if> <tmpl_unless> or <tmpl_else>

Variable tags such as <tmpl_var name="FOO"> will be replaced by whatever value is assigned to parameters when calling the HTMP::Template to the parameter FOO

The other tags can be standard html formatting stuff. Here's a good introduction to using the HTML::Template

Finally, in order to access data from the Mysql database we need to use the DBI module. You can see from the Perl script that we connect to the database using parameters given (not a good idea from the security pov to have the password in this script in real life - better to bring in as a parameter from a file that can't be seen by public eyes), prepare the sql script, and then execute. For more information on starting with DBI see this tutorial

Once this is setup, it's fairly simple to change the layout by editing the tmpl file directly without needing to change the program code.

Lastly - want to introduce some way of bringing back different results according to a parameter you pass over? Simply modify the previous Perl code as follows ($sal here to be parameter you can change according to your program, the ? serves as a placeholder for this argument passed to the sql execution - this helps stop sql injection as quote characters etc are escaped):

 my $sal = 2900;
  
 my $dbh = DBI->connect($dsn, $user, $pass)
   or die "Can’t connect to the DB: $DBI::errstr\n";
  
 my $sth = $dbh->prepare("select empno, ename, job, sal from emp where sal > ? order by empno");
  my $rc = $sth->execute($sal);
  
  # Prepare a data structure for HTML::Template

I hope this has been of interest! Good luck with your web programming!

NB. Whilst reproducing this entry I had a little difficulty in getting Blogger to print the HTML source code. There's a conversion utility on this site that allows you to do this.

Perl snippets

2009-11-14T09:36:00.000-08:00

I have a bad habit of writing down useful tricks/snippets and then losing the piece of paper. I've decided to start using this entry as a kind of jotter, to be added to at regular intervals. Here we go...

To take an array that only contains numbers, leave out duplicates and sort in numeric order (the default sort function orders by ASCII value) :

#!/usr/bin/perl

my @array = (1, 3,4, 5, 4, 6,7,4, 20 , 21, 20, 19, 19,18);

my %hash = map { $_, 1 } @array;
# or a hash slice: @hash{ @array } = ();
# or a foreach: $hash{$_} = 1 foreach ( @array );

my @unique = keys %hash;
my @sorted =sort { $a<=>$b; } @unique;

foreach $item(@sorted) {
print " $item \t";
}
print "\n";

To work through lines fed in from a file, ignoring blank lines but outputting the first word of the line otherwise:

while (<>) {
next if /^$/;
($primero)=split;

{print "Found: $primero \n"; }
}

How many elements in an array ? (ok, basic stuff here :) )

my @fruits = ("apples", "oranges", "guavas", "passionfruit", "grapes");

my $last = $#fruits +1; # ie last element of index +1

Or simply force the array to a scalar context to find out the number of elements:

my $last = @fruits;

Using Mysql to find prime factors

2009-08-31T13:00:00.000-07:00

On my Mysql blog I've come up against the poser from the Euler Project : "What is the largest prime factor of the number 600851475143 ".

I think I've cracked this in Mysql, but I'm not 100% confident (well it works in this case, but will it always work?)

Anyway, here it is (I use the table Orders only as it contains plenty of rows):

*** set variables ***
set @var1=2;
set @varreset=2;
set @bignuma= 600851475143;

*** sql code ***
select a1factor from
(select
case when mod(@bignuma,@var1) = 0 then @var1:=@varreset else @var1:=@var1+1 end as a1factor,
case when mod(@bignuma,@var1) = 0 then @bignuma:=@bignuma/@var1 end as new_factor
from orders where @var1<@bignuma ) tmp
where new_factor is not null
order by a1factor desc;

Passwords

2009-08-29T01:31:00.000-07:00

Something that I find tiresome on many computer systems, is having to remember a password; worse still is when the password has to change after a periodic interval. The temptation is to use a common password across many systems, yet this is very dangerous. Now it may be the case that few systems nowadays store passwords unencrypted (although it's surprising that some still do), but it's often possible to break password security and log into your details. This is something to be aware of when coding. For instance, it's often recommended to store password fields as a hash, so intruders cannot retrieve directly the password even with full access to the database. To test that a user knows the correct password you retrieve the stored hash and then check against the password he/she inputs (after converting this password to a hash). Fairly secure? Well no, hackers have built up lists of hash values (normally on what is known as a rainbow table) and can use brute force to try and log in ie automatically try to log in thousands of times until the correct hash value is found. (Or perhaps they can use common tools like John the Ripper to crack weaker passwords). Salting the hash (prefixing a unique value to the password before hashing) makes security a lot safer (although still not 100%) - read here www.codinghorror.com/ ) Any other measures the coder should take? Restrict the number of attempts in one session a visitor can use to enter their password - reading up on the use of captchas is particularly useful here. Ensure that the password is of a minimum length (the longer it is, the harder it is to crack the password); better still enforce the use of non alphabetical characters like numbers or _ or @ or * within the password. Also (and this is commonly overlooked) restrict the in-company access to that database table. Lastly, and it won't make you popular with users, force the changing of passwords after a certain passage of time.
This all leads to the problem of having to remember your own passwords, especially when they keep changing. I personally write some (although not those relating to bank or email log-ins) down in my diary, suitably disguised. Luckily there are various secure password managers out there for example http://keepass.info/ You can find more choices at http://lifehacker.com/5042616/five-best-password-managers - it's also worthwhile searching on Lifehacker using the keyword 'password' to find many useful articles on how to create a strong password, how to be more secure etc.

Well I never..

2009-07-18T13:23:00.000-07:00

I never knew there was a Guitar-Tube !

Paco de Lucia clip

Duplication versus effort

2009-07-03T09:05:00.001-07:00

As someone who’s used SQL over many years, I have a habit of importing data into a database whenever I want to list duplicates (or sole entries as opposed to duplicates). Although this is normally easily done in sql (via the Group By clause) it can be a bad habit, as other programs can also accomplish this for you.
For example, if you program in unix or linux, and you want to show unique lines in a file (ie remove duplicate entries), you can use the following command:
sort yrfilename | uniq
That’s not all, you can also use switches to manipulate the data further:
sort yrfilename | uniq –d # show the duplicate records
sort yrfilename | uniq –u # displays only the nonduplicate entries
sort yrfilename | uniq – c # count for each record
Now not everyone is on a Unix based operating system, but never fear, there are various ways to look for duplicate entries in Excel too (I’d be surprised if there wasn’t an easy way to do it in Powershell either). A useful way to do this on a data set (in more modern versions of Excel) is to choose the Data Menu, then select the Advanced Filter option. Here you can either filter the list in-place, or copy to another location (my preferred solution). So long as you select the “Unique records only” option, you lose the duplicate lines. Alternatively you can just select the “Remove Duplicates” menu option in the Data Menu, and you’ll be given the choice of which columns to check for duplicate information.
Incidentally, in older versions of Excel, I would use conditional formatting to show which fields were duplicated (I’d sort the column first). If the entries I were checking were in column A, I’d use something like || Formula Is = countif(A:A, A2) > 1 || and copy down.
I imagine there are lots of different ways of accomplishing the rooting out of duplicate entries (Perl, sed, awk anyone?). I think it’s always good to have an alternative way of achieving what you want – one day the database may be down, or Excel disabled by a virus. The more options you have under your belt the better!

Remember the bankers?

2009-06-23T13:11:00.000-07:00

This made me laugh...

youtube clip - Strongbow Bankers

Learning from other websites.

2009-06-21T07:42:00.000-07:00

I already knew how to look at html code on a lot of websites. You press right-click and then select "View Page Source". However, what I didn't realise, was that it's possible in many cases to view the CSS values too (eg how big the font is, what padding and/or margin is used etc.). This page explains different ways to view a website's CSS. The option I took was to use a firefox add-in provided by Chris Pederick at this address.

Using avatars on forums

2009-06-21T02:57:00.000-07:00

I spend quite a bit of time (in fact too much) on forums (or should that be fora?). Anyway, I wanted to devise a couple of nice unique avatars for use in mentioned forums. After looking at the way stackoverflow.com produced unique avatars for each user, I was directed to this site, that explains how to generate your avatar. Basically you're going to need the MD5 hash of your email address. To get this on linux, you'd enter something like this on the command line:
echo youremail@yahoo.co.uk | md5sum

This will produce a string of numbers and hexadecimal characters. You can then use on the gravatar site as follows:

http://www.gravatar.com/avatar/99[various]9f4028c2c.jpg?d=monsterid
OR
http://www.gravatar.com/avatar/99[various]9f4028c2c.jpg?d=identicon
OR
http://www.gravatar.com/avatar/99[various]9f4028c2c.jpg?d=wavatar

Cool hey?
My wavatar is up above. Sexy hey???