comment on

You need to read about database normalisation.

Essentially, what you have here is not a large complicated data structure containing many fields, but a very simple data structure containing very few, as so:

table stats (
  jobid INT,
  try INT,
  item VARCHAR(255),
  value VARCHAR(255), # or maybe TEXT or equivalent
  PRIMARY KEY (jobid, try)
);
[download]

Or similar. You would then be able to retrieve all the contents of the hash by:

select item, value from stats where jobid=273118 and try=1
[download]

It also allows you to get nice aggregate stats as so:

select owner, count(*) as n from stats where try=1 group by owner orde
+r by n
[download]

Which would give you a nicely sorted list of all the different owners in all the jobs, and a count of how many jobs they own.

Even better, its trivial to code for to load and unload from the hash..

foreach (keys %hash) {
    $dbi->do('insert into stats (jobid,try,item,value) (?,?,?,?)',$job
+id, $try, $_, $hash{$_});
}
[download]

etc.

Read a good book on SQL, or if you alreayd know SQL decently, on database normali|sz|ation and what it means for your data structures.

Create them well, and the database will end up doing all the work for you.

In reply to Re: MySQL Table Creation using DBI by PhiRatE
in thread MySQL Table Creation using DBI by blink

Are you posting in the right place? Check out Where do I post X? to know for sure.
Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
<code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>
Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
Want more info? How to link or How to display code and escape characters are good places to start.


good chemistry is complicated, and a little bit messy -LW
	PerlMonks