Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl: the Markov chain saw

Re: Re: Re: Re: Re: Re: Re: Re: DBD::CSV - how to install? (FTP only)

by lwicks (Pilgrim)
on Dec 22, 2003 at 21:08 UTC ( #316485=note: print w/replies, xml ) Need Help??

in reply to Re: Re: Re: Re: Re: Re: Re: DBD::CSV - how to install? (FTP only)
in thread DBD::CSV - how to install? (FTP only)

Hi Jeff

Cheers for another useful reply.
As is obvious I am a newbie PERL coder, so my work is a bit ropey. Never used DBI or done stuff with SQL really, so the sum of it all is just plain scary!

Anyway, I shall take what you have kindly written and see what sort of mess I can create. Watch this space!


P.s. If you think my code at the moment is bad, you should have seen it before I started from scratch! :-)

Kia Kaha, Kia Toa, Kia Manawanui!
Be Strong, Be Brave, Be perservering!
  • Comment on Re: Re: Re: Re: Re: Re: Re: Re: DBD::CSV - how to install? (FTP only)

Replies are listed 'Best First'.
Re: Re: Re: Re: Re: Re: Re: Re: Re: DBD::CSV - how to install? (FTP only)
by lwicks (Pilgrim) on Dec 29, 2003 at 21:00 UTC
    Hi all,
    Here is my ammended code, does not work however, can someone help?

    Starts okay I get the form, but when I fill the form in I get stuck. Basically all I get is the following messages on the screen:


    Start main block

    parameters: ID first_name surname date_of_birth email ejudopass submit button

    As you can see the param() function seems to be where the problem is, what am I doing wrong?


    #!/usr/bin/perl -w # --------------------------------------------- # create-user.cgi - Create by Lance Wicks # # This is free open source software! Released under GPL # # Description: # This script adds a new user to the users database # # History: # ======== # 20 December 2003, Lance Wicks - Created initial file. # 23 December 2003, Lance Wicks - On the suggestion of Jeff Zucker, am + re-writing this code to use more sub-routines and some other good su +ggestions. my $DEBUG = 1; # If this is set to 1 then we see the debug messag +es. # The following 4 lines set strict PERL coding then load the CGI DBI a +nd DBD::Anydata modules. # -------------------------------------------------------------------- +------------------------- use strict; # force strict programming controls use CGI qw(:standard); # use the CGI.PM module use lib './MyLib'; # use the modules in MyLib, this is the DBD::Anydat +a used for database activities use DBI; # This calls the DBI module, which along with the line above +allows us to do database activities # Sub routines # -------------- sub collect_user_input { # This routine collects the data entered by the user from + the form and adds the initial values and returns the @user_data arra +y # ------------------------------------------------------- +-------------- print p("Start of collect_user_input") if $DEBUG; my @user_data; # initialise the array we will use to sto +re all these pieces of data $user_data[0] = param("ID"); # the next few lines alocate + the info from the completed form to variables. $user_data[1] = param("first_name"); $user_data[2] = param("surname"); $user_data[3] = param("date_of_borth"); $user_data[4] = param("email"); $user_data[5] = param("ejudopass"); # now give values to the other user data field variables $user_data[6] = "NO"; # ($active) the user is not immed +iately active, we will email them first. $user_data[7] = "now"; # ($last_login)set the last login +time to now $user_data[8] = "now"; #($create_data) set the date we cr +eated this user to now as well $user_data[9] = 0; # ($earnings) They have not earned any + thing yet so set it to Zero $user_data[10] = 50; # ($cash) This is their cash on hand +, lets give them 50 credits by default $user_data[11] = 1; # ($judoka_limit) They can create 1 J +udoka $user_data[12] = 0; # ($sensei_limit) They can NOT create + a sensei (YET) $user_data[13] = 0; # ($dojo_limit) They can not create a + Dojo $user_data[14] = 0; # ($team_limit)They can not create a +team $user_data[15] = "Novice"; # ($rank) They are a novice us +er to start with return @user_data; print p("END of collect_user_input") if $DEBUG; } # end of subroutine collect_user_data sub validate_user_input { # This sub routine receives the @user_data ar +ray from collect_user_input sub-routine, it validates it to make sure + it is safe for the database, then return the @user_data array # ------------------------------------------- +--------------------------------------- print p("Start of validate_user_input") if $D +EBUG; my @internal_user_data = @_; # add some data validation of user input here return @internal_user_data; print p("END of validate_user_input") if $DEB +UG; } # end of sub routine validate_user_input sub add_user_input_to_db { # This routine receives @user_data from validate_user_inp +ut and then adds it to the Database using SQL # ------------------------------------------------------- +--------------------------------------------- # Next connect to the database and check if they exist al +ready. print p("Start of add_user_input_to_db") if $DEBUG; my @internal_user_data = @_; # This line takes the user_d +ata passed to us from the previous routine (@_) and allocates it to o +ur internal user data array my $entered_id = $internal_user_data[0]; # just so that +it is clearer later we create a variable from the passed data # Use DBI to connect to the users.csv datafile #----------------------------------------------- my $dbh = DBI->connect('dbi:AnyData(RaiseError=>1):'); # +tell DBI we want to use the Anydata module in ./MyLibs $dbh->func( 'users', 'CSV', 'data/users.csv', 'ad_catalog +'); # Connect to the users.csv data file # select from the datafile the id for the user ID from th +e array paased from the previous sub routine my $sql = "SELECT id FROM users WHERE id = ?"; # th +is is the SQL command we want to execute my @params = ($entered_id); # Th +eese are the parameteres we will use in the SQL command above print "$sql\n[@params]\n" if $DEBUG; # if + we are in debug mode print the SQL statement my $sth = $dbh->prepare( $sql ); # pr +epare the SQL command $sth->execute( @params ); # ex +cecute the SQL using our parameters my @result = $sth->fetchrow_array; # this line takes the +results of the select and puts it in the array called RESULTS $dbh->disconnect(); # we are done with the datbase for no +w, so disconnect from it (MAY NOT BE NECESSARY) print p("result = @result")if $DEBUG; # Pr +ints the result of our SQL command if we are in debug mode. if (@result) { #if the result array is in existence (i +e we found the username) then... print p("Sorry this ID is in use a +lready"); } else { print p("about to insert the new record")if $D +EBUG; # if the user does not exist then add them! # so connect to the database my $dbh = DBI->connect('dbi:AnyData(RaiseError +=>1):'); # tell DBI we want to use the Anydata module in ./MyLibs $dbh->func( 'users', 'CSV', 'data/users.csv', +'ad_catalog'); # Connect to the users.csv data file # add the data into a new record my $sql = "INSERT INTO users VALUES ( ?,?,?,?, +?,?,?,?,?,?,?,?,?,?,?,? )"; # this is the SQL command we want t +o execute my @params = (@internal_user_data); + # Make the parameters those passed to us from the previous rou +tine (and originally from the user) print "$sql\n[@params]\n" if $DEBUG; + # if we are in debug mode print the SQL statement my $sth = $dbh->prepare( $sql ); + # prepare the SQL command $sth->execute( @params ); + # excecute the SQL using our parameters print p("Inserted Record")if $DEBUG; + # just a reference in debug mode } print p("END of add_user_input_to_db") if $DEBUG; } # end of sub routine add_user_input_to_db sub print_user_data_form { # This subroutine create the user data web pa +ge which is filled in, the data is then used by other routines. print p("START of print_user_data_form") if $ +DEBUG; print hr, start_form; # create a for +m using CGI.PM print p("User ID: ", textfield("ID"), " - Thi +s is the ID you will use to login to the system"); # what user +name do they want. print p("First Name: ", textfield("first_name +"), " - This is your REAL name"); # what is their real name print p("Surname: ", textfield("surname"), " +- This is your REAL last name"); # what is their real name print p("Date of Birth: ", textfield("date_of +_birth"), " - (DD/MM/YYYY) This is your date of birth, used if you ev +er need to prove who you are"); print p("Email address: ", textfield("email") +, " - the email address you would like to use."); print p("Password: ", password_field("ejud +opass"), " - Choose a password to use on the system"); print submit(-name=>'submit button'); print end_form, hr; + # end the form print p("END of print_user_data_form") if $DE +BUG; } # end of print_user_data_form # end of subroutines # ------------------- # main code block # --------------- print header(), start_html("e-Judo Test Area"), h1("CREATE NEW USER"); + # This line uses CGI.PM to to create the webpage print p("Start main block") if $DEBUG; print p("parameters: ", param()) if $DEBUG; print p("no parameters so print the form") if !(param()); if (param()){ # If there is a parameter(or parameters) then validate, + else show the login screen. # the following lines are excecuted if paramaters HAVE +been entered my @temp_variable = collect_user_data(); print p("collect user data gave us: @temp_variable") if + $DEBUG; my @temp_variable2 = validate_user_input(@temp_variable +); print p("validate user data gave us: @temp_variable2") +if $DEBUG; my @temp_variable3 = add_user_input_to_db(@temp_variabl +e2); print p("add user data to db gave us: @temp_variable3") + if $DEBUG; } else { # if there are no parameters (the form has not yet be +en filled in then.... print_user_data_form(); } print end_html; # this closes the web page properly print p("End of main block") if $DEBUG;
    Kia Kaha, Kia Toa, Kia Manawanui!
    Be Strong, Be Brave, Be perservering!

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://316485]
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others wandering the Monastery: (4)
As of 2017-07-27 04:57 GMT
Find Nodes?
    Voting Booth?
    I came, I saw, I ...

    Results (403 votes). Check out past polls.