MySQL Table adding with Perl

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

I was just looking for some assistance with a script. I've only just begun scripting with Perl due to my roommate converting me, so please don't flame.

use DBI;

#Welcoming Phrase

    print "\n\n Welcome to the world.\n\n";

#Load username database

    print " Are you a new user? (y/n)\n\n";
    my $choice=<STDIN>;
    chomp $choice;

if ($choice eq "n") {

    print " What is your username?\n\n";
    my $username = <STDIN> ;
    chomp $username;
    my $connect = DBI->connect(
        "dbi:mysql:database=Champ;host=localhost", 'root','pass');
    print "User $username Active\n";
}

if ($choice eq "y") {
    
    print " What would you like your username to be?\n\n";
    my $newuser = <STDIN>;
    chomp $newuser;
    my $connect = DBI->connect(
        "dbi:mysql:database=Champ;host=localhost", 'root','pass');
    my $sql = "INSERT INTO test (A,B,C,D) VALUES (?,?,?,?)";
    $statement = $connect->prepare($sql);
    $statement = execute(4,4,4,4);
    print "User $newuser Active\n";
}
[download]

Everything works fine, except the table doesn't get created. Therefore the values aren't created. Again I'm not that experienced so you may need to explain advanced symbols, etc. Any help on why it doesn't work/what to fix is greatly appreciated.

Comment on MySQL Table adding with Perl Download Code

Replies are listed 'Best First'.
Re: MySQL Table adding with Perl by Anonymous Monk on Mar 06, 2013 at 20:28 UTC
Did you create the table before inserting information into it?	[reply]
Re^2: MySQL Table adding with Perl by Anonymous Monk on Mar 06, 2013 at 20:29 UTC
No. Thanks :)	[reply]
Re^3: MySQL Table adding with Perl by Anonymous Monk on Mar 06, 2013 at 20:51 UTC
Still not working use DBI; #Welcoming Phrase print "\n\n Welcome to the world.\n\n"; #Load username database print " Are you a new user? (y/n)\n\n"; my $choice=<STDIN>; chomp $choice; if ($choice eq "n") { print " What is your username?\n\n"; my $username = <STDIN> ; chomp $username; my $connect = DBI->connect( "dbi:mysql:database=Champ;host=localhost", 'root','pass'); print "User $username Active\n"; } if ($choice eq "y") { print " What would you like your username to be?\n\n"; my $newuser = <STDIN>; chomp $newuser; my $connect = DBI->connect( "dbi:mysql:database=Champ;host=localhost", 'root','pass'); my $dbh->do("DROP TABLE IF EXISTS $newuser"); $maketable = ("CREATE TABLE $newuser (A,B,C,D,E)"); my $sql = "INSERT INTO $newuser (A,B,C,D,E) VALUES (?,?,?,?,?)"; $sth = $dbh->prepare($maketable); $sth = execute(); $statement = $connect->prepare($sql); $statement = execute(4,4,4,4,0); print "User $newuser Active\n"; [download]	[reply] [d/l]
Re^4: MySQL Table adding with Perl by Corion (Patriarch) on Mar 06, 2013 at 21:03 UTC
Re^5: MySQL Table adding with Perl by Anonymous Monk on Mar 06, 2013 at 21:17 UTC
Some notes below your chosen depth have not been shown here
Re: MySQL Table adding with Perl by punch_card_don (Curate) on Mar 07, 2013 at 03:15 UTC
Maybe there's more to it, but.... a new table for each individual user? Normally, before ths script runs, you would have created a database with something like a Users table in it, with columns A B C D. Then you would insert the values for each user into new rows of the existing table. A separate table for each user is going to get very difficult to manage very quickly. Time flies like an arrow. Fruit flies like a banana.	[reply]
Re: MySQL Table adding with Perl by Anonymous Monk on Mar 07, 2013 at 09:39 UTC
so please don't flame Are you sure about that? Its cold outside, how will you keep warm? use strict/warnings to cut your development time in half! strict and warnings have also been called "training wheels" but they're more like "sanity checks" or seatbelts , professionals use them long after they've learned to ride a bike See Re: DBD::mysql trouble See ExtUtils::MakeMaker::prompt, you could write `use ExtUtils::MakeMaker qw' prompt '; ... if( prompt('Are you new?', 'n') =~ /y/ ){ my $username = prompt('Your name is?','John Smith'); $username =~ s/\s+$//; ... } else { my $username = prompt('Who are you','John Smith'); $username =~ s/\s+$//; ... }` [download] Or even `if( prompt('Are you new?', 'n') =~ /y/ ){ CreateNewUser(); } else { ActivateOldUser(); }` [download] where CreateNewUser and ActivateOldUser do all that prompt/DBI stuff from before	[reply] [d/l] [select]
Re^2: MySQL Table adding with Perl by Anonymous Monk on Mar 07, 2013 at 13:48 UTC
Making users isn't my issue. The DBI stuff is. You should read the topic before commenting. `use Strict; use Warnings;` [download] Was not copied from my script, but are above the `use DBI;` code. I didn't add them in because its a standard for nearly all perl scripts. Thanks... I guess.	[reply] [d/l] [select]
Re^3: MySQL Table adding with Perl by marto (Cardinal) on Mar 07, 2013 at 14:01 UTC
`use Strict; use Warnings;` [download] Should be: `use strict; use warnings;` [download]	[reply] [d/l] [select]
Re^3: MySQL Table adding with Perl by Anonymous Monk on Mar 07, 2013 at 14:34 UTC
Making users isn't my issue. The DBI stuff is. I think Corion covered that well, which is why I chose to comment on other aspects I didn't add them in because its a standard for nearly all perl scripts. Thanks... I guess. Sure, you're welcome, but :) Read more... (575 Bytes) If you take the time to clean up the code you post and make sure it compiles and runs , then someone might focus on answering the real question instead of pointing out the typos and such You should read the topic before commenting. Sure, that is a reasonable practice, although its not a requirement :) However, surely you're aware I must have read the topic to notice issues in your code, and offer some suggestions? What did you think of my suggestions, have you heard about the programming rule of three? You repeat (or copy/paste) `print/<STDIN>/chomp` and according to the rule of three, this means you should turn it into a function/sub/procedure ... Good luck	[reply] [d/l] [select]