in reply to Re^3: Using perl to Create a Table in a Database
in thread Using perl to Create a Table in a Database

So you mean like this...?

#!/usr/bin/perl
use strict;
use CGI qw(:standard);
use DBI;

print "Content-type: text/html\n\n";

my $database="assignments";
my $username="web320";
my $password="";

my $fieldname=param('fieldname');
my $fieldtype=param('fieldtype');
my $fieldlength=param('fieldlength');
my $table=param('table');
my $field=param('field');
my $f;


for ($f=1;$f<=$field;$f++)
{
    my $fieldname=param('fieldname' .$f);
    my $fieldtype=param('fieldtype' .$f);
    my $fieldlength=param('fieldlength' .$f);
}

#connect to database
my $dsn="DBI:mysql:$database:localhost";
my $dbh=DBI->connect($dsn,$username) or die print "doesn't work"; #if 
+use password, put ,$password

my $query = "CREATE TABLE $table ($fieldname $fieldtype ($fieldlength)
+)";

#prepare and execute
my $sth=$dbh->prepare($query) or die print "bad query";
#$sth->execute();

print $query;

[download]

the only problem i have left now is that it only prints the tablename ($table) and not $fieldname, $fieldtype, $fieldlength i.e. CREATE TABLE test (())

Replies are listed 'Best First'.
Re^5: Using perl to Create a Table in a Database
by kcott (Archbishop) on Nov 04, 2010 at 17:57 UTC

    Yes - like that - you're almost there :-)

    The variables $fieldname, $fieldtype and $fieldlength are lexically scoped (with my) and do not exist outside of the for loop. This is good - don't change that bit.

    You'll need to build your query as you iterate through the for loop. Start the process before you enter the loop:

    ...
my $query = "CREATE TABLE $table";
for ($f=1;$f<=$field;$f++)
...

    [download]

    Now, on each pass through the loop, add the appropriate syntax for each column after you've gathered the data:

        my $fieldname=param('fieldname' .$f);
    my $fieldtype=param('fieldtype' .$f);
    my $fieldlength=param('fieldlength' .$f);
    $query .= "...
}

    [download]

    When you exit the loop, $query should contain your complete CREATE TABLE statement.

    -- Ken

[reply]
[d/l]
[select]

      Thanks so much kcott! I figured it out with your help

[reply]

In Section Seekers of Perl Wisdom