in reply to storing some values (of an array) to a DB: how does Perl talk to MySQL?

I see that your project is getting into a whole new big topic.

Books I recommend:

- Learning SQL by Alan Beaulieu
This book starts from ground zero and uses MySQL for the examples using the mySQL command tool.
Your project can be done without getting really fancy - read at least up to about page 75. And do the examples as you go! There are some minor mistakes and you will see those as you go along. But this is a great book.

- Programming the Perl DBI by Alligator Descartes & Tim Bunce.
This is more general book. Chapter 5, "Interactng with the Database" has some relevant stuff for you.

Right now you should focus on the first book that I recommended above.

Before you ask how to make Perl do some SQL thing, you need to understand clearly how you would do that SQL operation yourself from the command line tool!

Once you understand "Learning SQL", then I think the tutorials will help a lot and you will find that translating a simple SQL statement into the right syntax for the DBI will be a lot, a whole lot easier for you.

Since you started on the project some months ago, I have seen that you often try to take too big of a step at one time. Like going from "playing with bottle rockets in the backyard" to "let's fly to the moon"!

Along with that goes trying to program some really, really complex thing in a completely automatic fashion that you can do easily in a few minutes with a little manual guidance. The last code that I sent you was a good example of that principle.

You will need to draw some diagrams and think a whole lot about your tables structure. These should be created manually from the command line tool (at the MySQL> prompt).

Your Perl code is going to come down to populating those already existing tables with data. Again before writing program code, you should be able to insert a data record manually from the command line tool.

So your "homework" is to read at least first part of "Learning SQL". Then make a baby step, by writing some Perl code that does a record insertion like you will already know how to do from the command line tool. Then, do the same thing with some "real data". Pursue your database learning as an independent thing from the web page scraping. Get each major piece working by itself before trying to integrate the whole thing together.

Oh, another point, inevitably you are going to have a "real DBI" question. When you do, take out all of say the HTML parsing stuff and boil it down to a direct question with as few dependencies as possible. If you have an HTML parsing question, make a direct question about just that part.

  • Comment on Re: storing some values (of an array) to a DB: how does Perl talk to MySQL?

Replies are listed 'Best First'.
Re^2: storing some values (of an array) to a DB: how does Perl talk to MySQL?
by Perlbeginner1 (Scribe) on Dec 20, 2010 at 23:49 UTC
    hello Marshall,

    many thanks for the reply. As allways - your ideas were very helpful and supportive!

    Pursue your database learning as an independent thing from the web page scraping. Get each major piece working by itself before trying to integrate the whole thing together.


    right - this is a very good idea!

    i will follow your advices.

    you tipps are great!

    btw: i allready have some books here - among them the book, Programming the Perl DBI from Tim Bunce and by Alligator Descartes


    i will read this and try to digg deeper into the interaction of Perl and MySQL

    thx for advising these literature!!
    some months ago, I have seen that you often try to take too big of a step at one time. Like going from "playing with bottle rockets in the backyard" to "let's fly to the moon"!
    ;-> right said! ;-) thx again...

    have a great time!!

    many greetings
[reply]
      btw: i allready have some books here - among them the book, Programming the Perl DBI from Tim Bunce and by Alligator Descartes

      That is too detailed to start with, get the "Learning SQL" book I recommended and start there. The first 4 chapters are very important and I have actually worked through all the examples just I recommended for you. I know for sure that it is good because I have done it myself.

      I added a post to this thread, please consider that idea as well. If you can get to the .csv format, importing this into the MySQL DB is like 1/2 page of code.

      Update: I decided to strike-thru the rest of this post. Not because it is "wrong", but rather because the detail doesn't matter at this point and I think it was confusing. I think the OP should start with a single table. That table should be a .CSV file. And let's leave it at that for the moment.

      In DB lingo, what is called "normalization" is a big thing. Instead of a string "Hessen" appearing as a field, this would be essentially a reference to another table that defines the spelling (user presentation of "Hessen"). This saves space and allows the name to be changed. Also allows Hesse and Hessen to be the same thing. The main idea is that everything is defined once. You don't need that - or at least not in any first step.

      I think that a single table will do every thing that you need at the first step. This will be in "un-normalized" form. And that is fine.

      In your application, the space doesn't matter. You may need a hash table conversion from say Bavaria => Bayern prior to putting that name in the DB, if so then just do it. No German is going to be confused by this mapping.

      Keep it simple and just use one term within your DB. In America: TX, Tex, Texas all mean the same thing in terms of postal codes. TX or Texas would be preferred over Tex, but believe me the post office would deliver the mail. I have a module that maps all sorts of state names into official postal codes. There are many thousands of aliases. Some knot-head will put "Texass" and that gets translated to "TX". I am confident that Germany has some standard for postal codes for the Bundeslaender. A very fast "look-up" table. Use that result in your DB.

[reply]

In Section Seekers of Perl Wisdom