Variable Creation

[macchisp]

Hey,

I have a script that connects to two different databases. One database reads in the names of the fields, the other reads in the data of those fields. I have a form that someone can fill out and add another field and I have got the code right so the database will read in any added field by a user.

My problem is when I have the data, I do not know how to store it. Right now, I currently have this:

# SETUP VARIABLES
my $found_results =0;
my $id=0;
my $app=0;
my $url=0;
my $server=0;
my $keywords=0;
my $i;

while (my $row = $sth->fetchrow_hashref)
{
$found_results = 1;
$id = $row->{'column[0]'};
$app = $row->{'column[1]'};
$url = $row->{'column[2]'};
$server = $row->{'column[3]'};
$keywords = $row->{'column[4]'};
$count++;
}

Is there a way in which I can do this: (in psuedo/not correct code )

have for loop that creates variable names
for($i; $i<=$count; $i++)
{
my $field+"$i" (so it is like field1, field2, field3, etc...)
}

while (my $row = $sth->fetchrow_hashref)
{
$found_results = 1;
$field1 = $row->{'column[0]'};
$field2= $row->{'column[1]'};
$field3 = $row->{'column[2]'};
etc...
until the last field in the database (like field10 = $row->{'column[9]'};
}

If I did it this way I would have problems with the scope.

Is there a way to do this without having scope problems or am I dreaming something here?

Thanks for all the help.

[FishMonger]

Why are you allowing the users the capability of adding fields to your database? To me, that's an odd thing to allow and indicates that the database isn't setup correctly. Can you give us more details on your database and what you're trying to accomplish and why you're using this approach? We might be able to find a better approach.

[macchisp]

It's not that I'm allowing every user to add whatever field they want to add, I am giving administrator rights to one person who is not technically capable to make the change using perl.

The database is in the initial setup phase and the administrator does not know which fields he wants to have in the database. So what I am doing is being able to setup a script that will allow him to add fields when he figures out which fields he wants to add. I will not be maintaining this project or else I would just add the fields myself using perl.

So is there a way to do this?

[FishMonger]

the administrator does not know which fields he wants to have in the databaseThat's the main and most important problem. The database administrator should sit down with pencil and paper and design and normalize the database, prior to witting any perl code that interacts with the database.

One database reads in the names of the fields, the other reads in the data of those fields.
Why the 2 separate databases? The DBI module has methods for getting the count and names of the fields.
my $names = $sth->{'NAME'};
my $Fieldcount = $sth->{'NUM_OF_FIELDS'};

To answer your question on how to dynamically create variables, you can do this:
for $i (0..$Fieldcount) { ${"field$i"} = $$names[$i] }

[bustamelon]

It's not that I'm allowing every user to add whatever field they want to add, I am giving administrator rights to one person who is not technically capable to make the change using perl.

The database is in the initial setup phase and the administrator does not know which fields he wants to have in the database. So what I am doing is being able to setup a script that will allow him to add fields when he figures out which fields he wants to add. I will not be maintaining this project or else I would just add the fields myself using perl.

So is there a way to do this?


I have no solutions for this problem but I just wanted to chime in in the OP's defense. I think s/he's onto somwething. I am in the process of creating something similar. It's essentially a CMS tool, and everything is completely modular. The user (administrator) can add sections to the site and add fields to sections, and the forms are rendered in the CMS dynamically based on values in a db table. This means the END of a developer having to manually create new db tables and fields each time the user/client wants to customize things.

One table in the db is basically a pivot/join table that contains all the information about all the fields in each section. For instance, let's say there's a blog section, with 5 fields: author, title, date, body, status. The forms_fields table has 5 rows to accomodate this. In each row is info about the field: the datatype, the form/iinput type, the section it belongs to, etc. There's another table that contains options on which to join any field in the previous table -- so for example, let's say the author field in the blog section uses a select menu to choose from 5 existing authors, those 5 authors will exist as key/value pairs in the options table, using the row ID of the field from the forms_fields table as an identifier.

At the very least, even if I don't allow users to access this, it saves me from having to hard code forms constantly, and that's a beautiful thing.

[KevinADC]

To answer your question on how to dynamically create variables, you can do this:
for $i (0..$Fieldcount) { ${"field$i"} = $$names[$i] }

When I see people wanting to do something like that I think they should be using a hash. By "people" I mean the OP, not you fish.

[FishMonger]

Kevin,
I agree, using a hash would be better or an array, since $sth->{'NAME'} returns a reference to an array of the field names and $Fieldcount = $sth->{'NUM_OF_FIELDS'} gives us the total count of the fields/columns. I thought of bringing up the hash point, but elected to just answer the question of dynamically creating scalars. I don’t think we have enough background info on what the OP is doing to be able to provide the best recommendation.

Bustamelon,
I’m not convinced that using 1 or more database(s) to describe another database is the best approach. Everything you’re storing in those pivot “description databases” can be queried/extracted from the “main database”. The “need” to use multiple databases in this manor tells me that the database isn’t designed properly and/or it’s not normalized.

I can see the need to have a database that holds certain info on all of the other databases. For example, the company I work for has hundreds of databases. A few of them give us info such as the database names, server locations, and access rights to the other databases.

I’m not, by any means, an expert in database design, but from what you and macchisp describe, I see flawed design and logic.

macchisp,
I might be off base on my understanding of what you're doing and what you need from us, so if you can shed more light on the details, we will be able to provide a better solution.

[macchisp]

Sorry about the delay.

I am creating an application that maintains information about different programs we use on campus. The directors do not know which attributes of programs they want to keep track of. For example, we use an accounts portal where students can change their password. Right now, I am keeping track of the server that it is located on, the url, the name, and any keywords to search for. It's pretty basic right now and I want to add a piece so they can add fields to keep track of. Basically, I layed the framework of what it can do.

The information is stored in .csv files. This may not be the best way, I am aware of that, but it is how we do things.

I have a script that adds everything to a database: name, url, server location, and key words. That's just what they've given me so far as to what they want. They do not know any additional fields at this time. I am not aware of how to add field name headers in csv format, so what I've done was create another file with just the current field names.

Right now, I am trying to add a script that will let the administrator add which fields he/she wants to keep track of. In that script, I am making two database connections, one to the database of field names and the other to the database of information. But in order for this to work, when I am making the connection to the database of information, I need to specify the field names like this:

my $dbh = DBI->connect(qq{DBI:CSV:csv_eol=\n;csv_sep_char=\\~});
$dbh->{'csv_tables'}->{'Applications'} = {'file' => 'data/applications.csv','col_names' => ["id","url","server","keywords","name"]};

I have the database of names so I can connect to that database, get the field names, put them in an array, and instead of making the connection above, I can make it like this:

my $dbh = DBI->connect(qq{DBI:CSV:csv_eol=\n;csv_sep_char=\\~});
$dbh->{'csv_tables'}->{'Applications'} = {'file' => 'data/applications.csv','col_names' => [@column]};

The reason I am having a database with only the field names, is because I would like a script that adds fields so the adminstrator can add fields themselves. If the administrator added a field, the method where I explicitly stated the field names, "id","url","server","keywords","name", wouldn't work because the problem would arise when assigning the values to variables.

Right now, if the administrator didn't add any fields this is what I have:

while (my $row = $sth->fetchrow_hashref)
{
$found_results = 1;
#SETUP VARIABLES
$id = $row->{'id'};
$app = $row->{'name'};
$url = $row->{'url'};
$server = $row->{'server'};
$keywords = $row->{'keywords'};
$count++;
}

The above code is saying, if there is a row, take the value found at field name 'id' and assign it to variable $id...etc. But if the administrator adds a field to keep track of, I would need to add something like this:

1. Add field name to database of fields.
2. Read in database of field names and assign them to an array.
3. Read in database of information using array instead of "id","url", etc...
4. Assign values found in those spots to variables. (But I don't know how many variables to create and I don't want to create more than I need)
5. Display all the information.

Is there a way to create variables like this:

for($i=0; $i< total # of field names;$i++)
{
$field"$i";
}

Then assign values to them like this:
while (my $row = $sth->fetchrow_hashref)
{
$found_results = 1;
#SETUP VARIABLES
$field1 = $row->{'column[0]'};
$field2= $row->{'column[1]'};
$field3= $row->{'column[2]'};
$field4= $row->{'column[3]'};
$field5= $row->{'column[4]'};
...and so on for as many fields there are.
$count++;
}

I hope this is in depth and what you are looking for. I also hope it's not too confusing for you. Please let me know what I can explain better and I will do my best. Thanks for all the help so far.

macchisp

[KevinADC]

I think all you need to do is use a hash instead of trying to assign variable names like you are doing. Seems you understand perl code enough to know what a hash is and how to implement one.