Okay, I guess I might as well take the plunge. Is there really, very, extremely simple way to learn MYSQL?

I can create flat file databases with IDs, records etc. Call up the fields into a web page and so have a Perl 'database driven' website (simple but works). Fine. I assume MYSQL is just another way of doing that. A table is a table is a table....

After looking at a few tutorials on the web they tend to lose me about the second paragraph. Does anyone know of a tutorial where I could gain some very basic knowledge?

I'd like to avoid using PHP - nearly every tutorial uses it as though the only way to use MYSQL is with PHP.

Ted is duhing and ummming and oh my gawding about this.....so have mercy  

MySQL is used a lot from PHP these days ... but it's equally applicable from Perl too.

MySQL from Perl - a starter's guide

Why use MySQL

Plain text files can work very well EXCEPT:
1. They're inefficient to search and edit when they get big
2. You get all sorts of file locking issues when multiuser
3. Complicated to program as you have to deal with formatting
An database that you talk to through (My)SQL largely overcomes these issues.

a) You define your data by columns so there's no problem knowing what's a column delimiter and what's really a part of the data

b) You access your data through a single process (known as the mysql daemon) so that you have no locking issues.  As it's just one process it can't conflict with others

c) It uses its own clever internal storage engine techniques that make data lookup and alteration much more efficient.

What you need

To connect Perl to MySQL, you need to add two modules from the CPAN - the DBI module (which provides functions that let Perl talk to a database) and the DBD module for MySQL (that provides the MySQL specifics).  See my extra resources links at the end of this item for more details.

Of course, you need Perl itself, and you need access to a MySQL server - either download install and run it on your local machine, or access it on another system via a network.   The MySQL folks have designed MySQL to be downloadable and installable for newcomers in a quarter of an hour and it is pretty easy.  But to stress - you do need to have access to a running MySQL daemon before you try to access it from Perl ... that's exactly like you need to have a web server running before you can access a web site.

MySQL has a login protocol so you can test initially with the default admin account "root" but you should quickly add a user account too.

What to code

In your Perl, pull in the DBI module, and connect to it - for example

Code:

use DBI; $db_handle = DBI -> connect('DBI:mysql:test:localhost',                'trainee','abc123', {RaiseError => 1})                or die ("connection: $DBI::errstr\n");

That connects to the database daemon running on your local machine with a login called trainee and a password of abc123. It uses the database called "test" that's looked after by the daemon and already exists when you install MySQL.

To create a table and put some sample data in it:

Code:

%demodata = ( "Swansea" => "Paddington",        "Weymouth" => "Waterloo",        "Brighton" => "Victoria",        "Hastings" => "Charing_Cross",        "Margate" => "Victoria"); $db_handle -> do("CREATE TABLE arrivals (afar CHAR(20), near CHAR(20))"); foreach (keys %demodata){   $db_handle -> do("INSERT INTO arrivals VALUES( \"$_\", \"$demodata{$_}\" )"); }

That's a fairly rough demo - it just shoves some constant fields in to give you an idea.   I can tell you a lot more about database use and design on our MySQL course of course (yes, very suitable for Perl users of MySQL) - but that would be advertising!

Looking up data - here's some code ... after connecting in the same way as in the data entry example, you would go on to run something like:

Code:

$getkey = $db_handle -> prepare("SELECT * FROM arrivals"); $getkey -> execute; while (@row = $getkey->fetchrow) {        print "@row\n";        }

You'll notice it's not just a single read to bring the results back - there could be a huge set so - just as you read from a file, line by line, using a file handle you read back from an SQL query, row by row, using a result set.   The parallels are remarkable.

What next

I've just opened the door a fraction for you there and you'll start to see the bright light flood out.  There's so much more that you can do. SQL is a really effective way of organising data that goes a hundred steps beyond the first three or four I've outlined above.

Examples ...
Linking / Joining tables together
Filtering of just the record you want from a result set
Sorting results
Calculating on results and returning summaries ...

I've gathered together a lot of our resources that relate to using MySQL from Perl at http://www.wellho.net/resources/P308.html ... there's some further "nuggets" and quite a few examples there.

Graham, thank you very much. You're a good teacher, you get straight to the point without tons of waffle.

In the first reading I saw item: b) and it immediately cleared up enough so that when I read thru the examples I could make sense of them. I can see the similarities of when I read in a flat-file database and the differences when it's SQL. You're right it's like a door and the light floods out. Yeah! Great!  

I'll check out the resources.

I've redone my local testbed server from Indigo Perl to Nushpere Technolgy Platform, which has MYSQL. After doing a few exercises and play arounds I should pick up enough to inspire me for further digging.

Very much appreciated, thanks again - Ted

A little update on this

Right , I think I see - from the little bit you've given me....

In order to create a usable model I need to map out (before beginning):

DB Name
|
Main catagories
|
Sub-catagories
|
Sub-sub-catagories
|
Variances within the sub-sub-catagories

Unique ID's would need to be used at each stage so I know where I am and where I want to go.

Queries would follow the UID path or be resricted to certain UID's and not pick up others as needed.

So I need to gain some understanding of relational databases before I actually begin building a database that is more than one table.

Well Graham, that oughta keep me busy for a bit.
           

A couple of thoughts:

a) You may be getting the table structure (also known as the Metadata) mixed up with the data itself and the MySQL user accounts.

b) Planning out the tables and arranging the data correctly is vital.  I don't know if you saw our latest newsletter and the article there on Bristol Blue Glass, but it talks somewhat about a fairly straighforward table design and there's a worked example too.  Download from our downloads directory

Thanks Graham, I'll read through that - Ted

---