· 8 years ago · Mar 08, 2018, 09:36 AM
1 1 #!/usr/bin/perl
2 2 #Mike Jackson
3 3 #Homework 7, Spring 2011, CS3030 Brad Peterson.
4 4 print "\n";
5 5 print "Enter a number from choices below and hit enter. \n";
6 6 print " 1) Create a new account. \n";
7 7 print " 2) List the times that account has logged in. \n";
8 8 print " 3) Log in/out of an existing account. \n";
9 9 print " 4) Drop and recreate the necessary database tables.\n";
10 10 print " 5) Quit. \n";
11 11 #prepare simple prepares a sql statement
12 12 #execute();Execute the statement in the database
13 13 use DBI;
14 14 use DateTime;
15 15 use Date::Parse;
16 16
17 17 #Can't call do value on undefind method.
18 18 $dbh = DBI->connect('DBI:mysql:W00004992','W00004992','Mikecs!') or die "Couldn't Connect do mysql Database Weber State.";
19 19
20 20 #$dbh->do('DROP TABLE usersDB');
21 21 #$dbh->do('DROP TABLE logTimes');
22 22 $dbh->do('CREATE TABLE IF NOT EXISTS usersDB (username VARCHAR(30) NOT NULL PRIMARY KEY, password VARCHAR(30))');
23 23 $dbh->do("CREATE TABLE IF NOT EXISTS logTimes (username VARCHAR(30), dbtime DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00')");
24 24
25 25 #You need the prepare and execute if you are doing select statemeents
26 26 #otherwise you can just use the do()
27 27
28 28 chomp($input=<STDIN>);
29 29
30 30 if ( $input == 1 ) {
31 31 print "Creating a new account. A user and password.\n";
32 32 print "Enter new user: ";
33 33 chomp($newuser=<STDIN>);
34 34 print "Enter the password: ";
35 35 chomp($newpassword=<STDIN>);
36 36 $dbtime = localtime;
37 37 $dbh->do("INSERT INTO usersDB VALUES ('$newuser', '$newpassword' )");
38 38 $selectquery = $dbh->prepare("SELECT * FROM usersDB");
39 39 $selectquery->execute();
40 40 while (@results = $selectquery->fetchrow_array())
41 41 {
42 42 print "user: $results[0] password: $results[1]\n";
43 43 }
44 44 }
45 45 if ( $input == 2 ){
46 46 print "Listing the times an account has logged in.\n";
47 47 print "Enter searched user: ";
48 48 chomp($loginuser=<STDIN>);
49 49 print "Enter searched password: ";
50 50 chomp($loginpassword=<STDIN>);
51 51 $logintime = $dbh->prepare("SELECT * FROM usersDB"); #WHERE username='$loginuser' AND '$loginpassword' ");
52-- INSERT -- 25,1 Top
53 51 $logintime = $dbh->prepare("SELECT * FROM usersDB"); #WHERE username='$loginuser' AND '$loginpassword' ");
54 52 $logintime->execute();
55 53 print "Successful in finding the following users.\n";
56 54 while (@results = $logintime->fetchrow_array())
57 55 {
58 56 print "user: $results[0]\n";
59 57 }
60 58
61 59
62 60 }
63 61 if ( $input == 3 ) {
64 62 print "Log in/out of an exiting account. If returns blank your username or password werent entered correctly.\n";
65 63 print "Login user: ";
66 64 chomp($loginuser=<STDIN>);
67 65 print "Login password: ";
68 66 chomp($loginpassword=<STDIN>);
69 67 $loginquery = $dbh->prepare("SELECT * FROM usersDB WHERE username='$loginuser' AND password='$loginpassword' ");
70 68 $loginquery->execute(); #while statement will display name if successful.
71 69 $dbtime = localtime;
72 70 $dbh->do("INSERT INTO logTimes VALUES ('$loginuser', '$dbtime')");
73 71 $loginquery->execute();
74 72 while (@results = $loginquery->fetchrow_array())
75 73 {
76 74 print " users that have been logged in: $results[0]\n";
77 75 }
78 76
79 77 }
80 78 if ( $input == 4 ) {
81 79 print "Drop and recreate the necessary database tables.\n";
82 80 print "Enter a number 1 for usersDB and 2 for logTimes\n";
83 81 chomp($dropT=<STDIN>);
84 82 if ( $dropT == 1 ) {
85 83 $dbh->do('DROP TABLE usersDB');
86 84 print "usersDB table has been dropped.\n";
87 85 }
88 86 if ( $dropT == 2 ) {
89 87 $dbh->do('DROP TABLE logTimes');
90 88 print "logTimes table has been dropped.\n";
91 89 }
92 90 }
93 91 if ( $input == 5 ) {
94 92 print "Quiting Program.\n";
95 93 exit 0
96 94 }
97-- INSERT --