The road to using Mysql

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 (calling 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

Code:

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

Code:

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.

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

Here's question 2 from Project Euler The Euler Project :

Each new term in the Fibonacci sequence is generated by adding the previous two terms. By starting with 1 and 2, the first 10 terms will be:

1, 2, 3, 5, 8, 13, 21, 34, 55, 89, ...

Find the sum of all the even-valued terms in the sequence which do not exceed four million.

Hmmm. Looking at the Perl solution (again from AJSWiki Perl Euler solutions ) we have the following code:

Code:

We see that the Perl program adds to $sum where the variable $f is an even number, and loops until the value shifted into memory (which is added to each previous value to produce the Fibonacci series) exceeds 4,000,000.

So how can we accomplish this in MySql? Again (as per my first post on the Euler Project) we will use variables to iterate. In order to get the Fibonacci sequence we can use:

Code:

I used 40 as a limit after a bit of trial and error. Remember to reset the variables every time you run a query like this.
We can then do a sum of those values brought back where the Fibonacci value is less than 4 million, by using an inner select:

Code:

But we're summing up every term by doing this - the problem states we should only sum up the even-valued term. Easily modified (see entry on number 1 solution) by using the MOD function to only sum up even numbers:

Code:

Hope this is of use to someone!
Regards
Mark

Hi, I've recently come across the Euler Project The Euler Project that sets mathematical challenges in whatever coding language you prefer to use. Now I'm not claiming that Mysql is particularly apt for these challenges, but I wondered if I could possibly solve one or two merely by the use of Mysql (not using any other scripting language such as Php or RoR).

Here's the first question:

If we list all the natural numbers below 10 that are multiples of 3 or 5, we get 3, 5, 6 and 9. The sum of these multiples is 23.

Find the sum of all the multiples of 3 or 5 below 1000.

First I'm going to see a way it can be done in Perl (the following is adapted from a solution I found at Euler oneliners in Perl ) and then see how I can apply this in Mysql:

Code:

For my Mysql code I will start by seeing how we test if a number is divisible by another number in Mysql. One way is to use the function MOD that returns the exact remainder after division. It should therefore return 0 when the number is divisible by your test figure. So select mod(9,3); returns 0 whereas select mod(8,2); returns 2.
Next we need a way to iterate up to 999 (ie all numbers below 1000). I tend to use a dummy table populated with an ascending count for this type of thing; however it's possible to use a variable technique so long as you join to a table with enough rows in it. For example, if we have a table orders with 20,000 records, we can bring back just 20 row-numbered records like this:

Code:

So to bring back all those where the row_num is divisible by 3 we put the above into an inner select:

Code:

From there it's an easy step to add in a [strong]OR[/strong] clause to include numbers divisible by 5, and to sum the result.

Code:

This gives an answer of: 233168. It came back in 0.08 of a second too, so wasn't too inefficient! I hope to look at another Euler teaser soon!