Place entire text file into an array

[rob51383]

Greeting,
I got a pretty simple question here with probably a few line of code solution. Here is what I am doing in case there is an even better way...

I am making a CGI application with MySQL database. I have a page that needs to populate about 300+ HTML text boxes (Value="").

It would be nice if the data was easily searchable with such functions a database provides, but i think 300+ databse colums is a little too much overhead, so I will settle with the lessor:

I have a text document that looks like this:

100:VALUE:VALUE:
101:VALUE:VALUE:
102:VALUE:VALUE:

etc... etc...

I want to chunk the entire text document into an array that is split at, you guess it ":".

I did do a few searches but I did not find a useable solution, but I am sure there is one as this seems like a pretty common function...

Thanks in advanced!

 

[rob51383]

Answered my own question:

my @course;
my @temp;

open (FILE, "/home/dir/[URL unfurl="true"]www/file.txt")[/URL] or die "cannot open file for reading: $!";
my @file = <FILE>;
close (FILE);

foreach (@file)
{
    @temp = split (/:/, $_);
    @course = (@course,@temp);
}

Maybe there is a better way... We may all know soon...

 

[duncdude]

Hi Rob

Sorry - i did not see this post earlier

Be careful - you will almost certainly need to chomp the array

chomp (@file = <FILE>); ... otherwise you will end up with a nasty end-of-line character in your HTML

Very small point - if you use the implicit $_ you do not need to mention it in the split

i.e. @temp = split (/:/); will suffice

Can you explain a little further what you are doing?


Kind Regards
Duncan

 

[PaulTEG]

    @course = (@course,@temp);

    push (@course, @temp);

--Paul

 

[ChrisHunt]

Seems odd to have a database and to be storing information in a flat text file instead.

It would be nice if the data was easily searchable with such functions a database provides, but i think 300+ databse colums is a little too much overhead

Are you sure that you need 300 extra columns? Are you sure you couldn't do it by adding some extra tables to the database? There must be a better way to do this, but it's hard to know what it is without knowing more.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[rob51383]

Chris, you are correct.. Because MySQL is a relational database I could seperate the 300+ fields into 15 tables each with 20 fields each plus an index to relate them. The SQL to update and read the tables will grow as well as the CGI to parse the data.

The reason I need 300+ fields is I need to store 2 fields for 150+ courses. With the text file I managed to save ALOT of repetitive HTML formating to output the data. I simply included the course title into the text file and run a cycle on each line of the text file. Here is the final code:

open (FILE, "/home/yourdir/[URL unfurl="true"]www/yourfile.txt")[/URL] or die "cannot open file for reading: $!";
my @file = <FILE>;
close (FILE);

foreach (@file)
{
    @temp = split (/:/, $_);


print <<this;
   <tr height="20">
    <td width="98"></td>
    <td width="27" background="../frame-left.jpg" valign="top"></td>
    <td width="130" background="../bg.jpg"><p><input type="text" name="$temp[0]a" value="$temp[2]" id="$temp[0]a" readonly="1" style="width:90px;">&nbsp;<img src="../calendar/cal.gif" id="$temp[0]a_trigger" height="20" align="absmiddle" style="cursor: pointer; border: opx solid red;" title="Date selector">
    <script type="text/javascript">
        Calendar.setup({
            inputField     :    "$temp[0]a",     // id of the input field
            ifFormat       :    "%d%b%Y",       // format of the input field
            button         :    "$temp[0]a_trigger",  // trigger for the calendar (button ID)
            align          :    "Br",           // alignment (defaults to "Bl")
            singleClick    :    true
        });
    </script></p></td>
    <td width="130" background="../bg.jpg"><p><input type="text" name="$temp[0]b" value="$temp[0][2]" id="$temp[0]b" readonly="1" style="width:90px;">&nbsp;<img src="../calendar/cal.gif" id="$temp[0]b_trigger" height="20" align="absmiddle" style="cursor: pointer; border: opx solid red;" title="Date selector">
    <script type="text/javascript">
        Calendar.setup({
            inputField     :    "$temp[0]b",     // id of the input field
            ifFormat       :    "%d%b%Y",       // format of the input field
            button         :    "$temp[0]b_trigger",  // trigger for the calendar (button ID)
            align          :    "Br",           // alignment (defaults to "Bl")
            singleClick    :    true
        });
    </script></p></td>
    <td width="415" background="../bg.jpg" valign="middle"><p>$temp[0] - $temp[1]</p></td>
    <td width="35" background="../frame-right.jpg" valign="top"></td>
   </tr>
this


}

This also allows me to add as many courses as I need by simply adding to the text file. The trade off is the "Date" format a database can store is not available. Also the search functions will require more code, but I will not be adding a search function.

Thanks for the tip duncdude!

 

[ChrisHunt]

2 fields for 150+ courses" is two columns, 150+ rows. A very different thing from " 300+ database columns". You'd just use a simple SQL statement like

SELECT course_id,title
FROM   courses
ORDER BY course_id

to get the data back - I'm afraid I don't know the Perl syntax, being more of an Oracle guy.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[rob51383]

I do not need 300 rows, I need 300 columns. such as:

101start
101end
102start
102end
103start
103end
etc...

There will be 1 row for each person the that is being tracked.

 

[ChrisHunt]

That still looks like rows to me:
[tt]
course_id title
========= =======
101 This
102 That
103 Other
[/tt]
You then have a table of persons
[tt]
person_id name etc...
========= ======
1 Fred
2 Barney
[/tt]
and a third table to link them together:
[tt]
course_id person_id start end
========= ========= ====== ======
101 1 1/1/05 2/2/05
102 1 2/2/05 3/3/05
102 2 2/2/05 3/3/05
103 3 2/2/05 3/3/05
[/tt]
That's how a relational database works - it's not a matter of taking a flat file and distributing the columns between an arbitrary set of tables. It's a matter of identifying repeating groups and spinning them out into tables and relations.

You'll save yourself time in the long run if you get your database designed right, and managing an application that's partly in a mySQL database and partly in flat files looks like a whole lotta pain to me.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[rob51383]

Makes since, but it looks like the third table would have hundreds of thousands of records. There will be about 1,500 people to start with so if you take 150 courses x 1,500 people you have 225,000 records!

Then on top of that there is a complete troop rotation about every 3 years. (This is for the military that is "Utilizing" their enlisted talents...) The old records will need to remain in case someone rotates back to the European region so within a time span of 3 years you will have almost 1/2 million records! Also, the manpower for the division I am making the program for is growing and will possibly double in the next 2 years, so we are looking at almost a million records within 3 years.

-o- yah, I almost forgot. In addition to the 1,500 active duty there is about another 1,500 ASF (Auxiliary Security Forces) that will augment the first 1,500 in case of disaster, plus reserve personnel that all need to be tracked.

Can a mysql database perform efficiently under these conditions?

For all of the other data I separated the tables into sub-regions to relieve the pressure but I already have ALOT of data stored in the database and numerous tables.

 

[ChrisHunt]

Can a mysql database perform efficiently under these conditions?

I don't know, like I said - I'm an Oracle guy. I'd be surprised if it couldn't - any decent database should cope with those numbers, but it'll probably depend more on your exact MySQL set-up and the hardware you're running on.

A question for the mysql forum - forum436

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[duncdude]

Hi Rob

I'm afraid I have to agree with Chris - it looks to me like you've got your database set up incorrectly. You seem to be so focused on the way you are solving the problem now that you can't see the benefits of solving the problem the way Chris suggests. You are not taking advantage of the relational aspect of your database.

I'm simply saying that if you sit back and think about this for a while you will realise that Chris's solution is the obvious one.

There are only 1,500 people - a tiny database
There are only 150 courses - a very tiny table
... and a last table to take care of the dates


Kind Regards
Duncan

 

[rob51383]

I am just worried that a query to the database could take too long with such an amount of data stored.

I clearly understand the benefit of storing the data in a database but within a year or so of deployment there will be well over 1 million records in 1 table, plus about 20 other tables with thousands of records in the same database.

1,500 people, yes tiny.
150 courses, yes tiny.

But when you include 150 courses for 3,000 people (Remember 1,500 + 1,500 forces to augment) then you got troop rotation that is about 3,000 (Both augmented and primary) every 3 years.

The record number will be VERY large!

The only time a record can be removed is when a member retires or is kicked out in a manner they cannot re-enlist...

Now what really sucks to top it of is if this program is a success it may be deployed throughout the entire United States Military!! Now you are talking about tracking the records of every Security related job the Military has.

Now you can image that the 1 million record table just became a 50 million record table and I am thinking this can pose a problem...

Maybe if it gets to this point I will need to consider moving to a more powerful database?

 

[PaulTEG]

not VERY, and it'll still be quicker, and less prone to error than text files

--Paul

cigless ...

 

[rob51383]

I asked a friend of mine who works with MySql and confirmed that while that number of records will effect the query time it will still be faster than the text file.

I am going with the database. Thanks.

 

[duncdude]

Sensible move!

I'm just curious - does every troop have to complete every one of the 150 courses?


Kind Regards
Duncan

 

[rob51383]

Yes they do have to complete every course...

I know what you are thinking too because I tried to think a way out of 150 records for every person and that is only put them in as they complete them but since they have to do it either way it is easier to just create all 150 records when the new person is inserted into the system.

If I just put it in as they complete it I have to make a whole string of checks to see if it exist so I can make an "update" or "insert" Sql statement.

To top it off the "complete_by" field is used so the training department can assign the course and it will appear to the troop on his personal login page under "Assigned Courses".

The complete system, when completed is extremely elaborate. It basically covers every angel of an "MP" (Security Forces) department.

I was employed to do it because I was helping people out with their computers and someone noticed so I was volu-in-told to do it. I am guessing the system I am making can easily be sold (To the military) for $200,000 plus yearly maintenance contracts. I get E4 pay....

I was away for a while because there were some political issues going on with it that stopped developement, but now I am back. I only got 1 year experience with CGI so you will probably be seeing alot more of my post in the forums.

 

[ChrisHunt]

If I just put it in as they complete it I have to make a whole string of checks to see if it exist so I can make an "update" or "insert" Sql statement.

Dunno the mySQL (or perl) syntax for this but a good way to handle this kind of problem is:

Create a multicolumn unique index on the person_id & course_id in the big table (you'll need this for searches anyway)

When you need to enter a value, don't search for existing records, but insert a row regardless. This will be successful if there's no row already present.

If the insert fails, it means there's a row already present for that person/course - so do an update instead.


As I mentioned above, if you take time to get your database design right first - before you get into the CGI side of things - you'll save yourself time in the long run. If you have access to, erm, Access, or some other desktop relational database program it may be useful for prototyping your design.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[rob51383]

I have a strong working knowledge on relational databases so creating the database correctly is not the problem.

Every table I create has 2 unique identifiable fields so I can query on the records accordingly.

Each record has an auto increment "ID" number and the Social Security Number of the person the record belongs to. That way I can get a specific record by referencing the "ID" or I can get all records for one user by referencing the SSN.


The idea you have to avoid placing records in the table before the the member completes the section sounds like a good way to do it, but I think it is easier to do it from the beginning because it will be less CGI and the records will be there sooner or later...