This sounds like it's not a Perl questions, but rather a database design issue. It sounds to me like you have a one-to-many relationship with each unit having one or more drives. To handle that, you should create a separate "drives" table with the key being the unique ID for the unit and the rest of the values being the information you want to insert for each drive:

You'll need to put a unique constraint on the Unit/Drive combination

Cheers,
Ovid

Update: D'oh! I missed the MySQL reference. Unfortunately, MySQL barely qualifies as a database, so you'll be limited in the constraints you can put on it.

Join the Perlmonks Setiathome Group or just click on the the link and check out our stats.

I don't get it.

To "insert drives for a unit", do you insert/update a row in the drives table or insert/update the number of drives in the table for the unit? If it's the latter, I don't see why there's a problem.

Do you have one table for units, and another for drives? I assume you do, because make/model is important to you. Well, assuming you want info on every drive in every system, relational database design says you should have one table for the systems, and another table for the drives. You give each system a unique identifier, and make sure that each entry for a drive contains a field that tells you what system the drive sits in (by containing that system's identifier).

Why are you worried about control structures? You don't want to hard-code cases for different numbers of drives? Perl can easily loop over all the members of an array. So, if you store the information for the drives in an array of hashes (see perldoc perlreftut and read up on references for more info), the task is pretty simple.

anyhow, assuming that setup, and that you have a table for systems, and anotehr table for drives in your MySQL database, it's not that hard to code. For each drive, get all the info and push a reference to that info, as a reference to a hash, into an array.

# I assume @systems is already initialized and full of data.
# the important thing is that you do this loop for each 
# system.

my @drives;

#loop over the systems

foreach (@systems) {
    while ( THERE ARE MORE DRIVES ) {
    #get drive info
    push @drives, {system=>SYSTEMID,
                    make=>MAKE,
                    model=>MODEL,
                    capacity=>CAPACITY,
                    status=>STATUS
                   };
 }
[download]

Then, when inserting the drives (the logic's similar for updating) you can just do this:

# prepare once, execute often

my $sth= $db->prepare("INSERT INTO drives (system_id, make, model, cap
+acity, status, last_update) VALUES (?,?,?,?,?,NOW())");

foreach my $drive (@drives) {
    $sth->execute($drive->{system}, $drive->{make}, $drive->{model}, $
+drive->{capacity}, $drive->{status});
}
[download]

That way, you don't have to hard-code any numbers. But obviously this assumes a lot of background setup. Your drives table should have an auto_increment ID field, the last_update should be a DATETIME or a TIMESTAMP datatype, and so forth.

I hope this gives you something to work with!

note updated to improve grammar and be more explicit (OK, verbose =)

perl -e 'print "How sweet does a rose smell? "; chomp ($n = <STDIN>); 
+$rose = "smells sweet to degree $n"; *other_name = *rose; print "$oth
+er_name\n"'
[download]

My apologies, it seems my question was a bit vague. I think Ovid is correct, I couldn't really see the trees for the forest, it is a DB design question. My orginal thought was to create a table for each unit and a column for each stat that was collected including the drives. Perhaps a seperate table for drives using an unique id for each unit would be more apropriate. You're idea has helped immensely in clearing this up, thank you. I'll go back to the design of the DB and that should make this task easier.

IMO, you've singled out the best part of that book (the early chapters on relational DB design). The rest really isn't all that worthwhile. I recommend *borrowing* that book from a library, if possible, and digesting the first few chapters. Then, if you're using Perl to interact with ANY DBMS, get the DBI book from O'Reilly.

This has been another unsolicited review from unky arturo

perl -e 'print "How sweet does a rose smell? "; chomp ($n = <STDIN>); 
+$rose = "smells sweet to degree $n"; *other_name = *rose; print "$oth
+er_name\n"'
[download]