Re: CSV dB script help
From: James Hunt (jameskorea2003_at_hotmail.com)
Date: 06/02/04
- Next message: Andrea Spitaleri: "sorting and resorting hash"
- Previous message: Aldric L'Hernault: "Re: Am I a programmer or a scripter ?"
- In reply to: James Hunt: "CSV dB script help"
- Next in thread: Keith Keller: "Re: CSV dB script help"
- Reply: Keith Keller: "Re: CSV dB script help"
- Reply: Tad McClellan: "Re: CSV dB script help"
- Reply: David K. Wall: "Re: CSV dB script help"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Date: Wed, 2 Jun 2004 09:48:43 -0600
I am sorry about the lengthy post. It will not happen again.
--
JAMES HUNT
"James Hunt" <jameskorea2003@hotmail.com> wrote in message
news:C7idnWmMlvVCzSDdRVn-jw@comcast.com...
> Ok this is kinda long ... so i will get to the point ... A friend of mine
> started perl script to handle a csv database. And now I am trying to
finish
> it. I am currently working on the search records feature. For some
reason
> it always returns all of the records. I have dug through to code and I
can
> not seem to figure out why it returns all of the records. at the bottom I
> have included the script ..... the search form ... and then the csv file
> that i built as a test.
>
> Even some perldoc references will help.
>
> Thanks,
> James Hunt
>
> ---------- database_script.pl ----------
> #!/usr/bin/perl
>
> #################################################################
> # This script is used toread a spreadsheet in csv format and perfom querys
> on arbitrary data sets.
> # The form submitted to this will use the name of the fields as the keys
for
> searches.
> # It uses the order of those keys for the order it displays them.
> # This sscript assumes that any 6 #'s are dates in YYMMDD format This is
> done in the useredit
> # and the quicksort.
> # Current Bugs: Need to be able to sort by other than numbers, Need
validity
> set to "D" for users,
> # Need click on CASN for other search, and user edit.
> # Original Author: Joshua Olson
> #################################################################
>
> use CGI ':all';
>
> # The path and file name must be passed as the first two element in a
comma
> group.
> # i.e. ....scriptname.pl?/path/,file_name.csv
> # $param[0] is the path name
> # $param[1] is the file name
>
> $searchpage = "query.html";
>
> @params = split(/\,/,$ENV{'QUERY_STRING'});
> my(@data)=loadtable($params[0],$params[1]);
>
> # Start off with everything and whittle down from there.
>
> my(@match)=(0..$#data);
>
> # Default Header line for html pages.
>
> print header('text/html');
>
> # $params[2] is the all purpose item
>
> if(!$params[2] || $params[2] =~ /edit/i || $params[2] =~ /search/i)
> {
> search(\@match,\@data);
> }
> elsif($params[2] =~ /update/i)
> {
> update(\@data,$params[0],$params[1]);
> print "Update Elsif Has Arrived";
> }
>
> # If there isnt anything else being done then sort on the key.
>
> elsif($params[2])
> {
> quicksort($params[2],3, $#params, \@match, \@data);
> }
>
> # Login Check For Updating
>
> if($params[2] =~ /edit/i || $params[2] =~ /update/i)
> {
> if($params[3] eq "user" && $params[4] eq "passwd")
> {
> # Superuser Edit
> printpage(\@match, \@data,'edit');
> }
> else
> {
> # User Level Edit
> printpage(\@match, \@data,'useredit');
> }
> }
> else
> {
> # If no edits then display data normally
> printpage(\@match, \@data, '');
> }
>
> #################################################################
> # sub update #
> # #
> # The subfunction takes the input from the form created by the #
> # printpage() sub and prepares the date for the writeupdate() #
> # sub. #
> # #
> # You pass $array by ref and the $dir and $file strings and the #
> # form from printpage() was submitted. #
> # #
> # This sub results in extracting all the entries to update and #
> # updated them in the array then called the writeupdate() and #
> # wrote them to $dir$file. #
> # #
> #################################################################
>
> sub update
> {
> # We need the array, so that memory isnt wasted
> my($array,$dir,$file)=@_;
>
> # Get the names of the fields
> @fields=param();
>
> # New Blank Entry
> @toupdate= ();
>
> # The last one is far undefined
> $last=-1;
>
> # Pull the numbers out of each of the field names
> foreach $i(@fields)
> {
> # This is what actually pulls it out
> $i =~ /\s(\d+)/;
>
> # No need to duplicate these numbers
> if($last != $1)
> {
> # Push it into the array
> push @toupdate,$1;
> }
>
> # Make sure we do not dup, so update last
> $last=$1;
> }
>
> # Now do the update to the array
> foreach $num(@toupdate)
> {
> foreach $imakey(@key)
> {
> # Same keys just a different format
> $$array[$num]{$imakey}=param("$imakey $num");
> }
> }
>
> writeupdate($array,$params[0],$params[1]);
> }
>
> #################################################################
> # sub writeupdate
> #
> # This takes the entire array and writes it to a file in csv
> # format.
> #
> # This passes a ref to the array and, the $path ending with a
> # "/" and the $filename in theory it's .csv, but as long as
> # it's in the format it doesn't matter what you call it.
> #
> # This sub results in $dir$file being written to.
> #
> #################################################################
>
> sub writeupdate
> {
> my($array,$dir,$file)=@_;
>
> # Open it or die with custom error
> open(UPDATEME,">$dir$file") || die "Cannot Open $fname for Writing: $!";
>
> # Select the file for writing
> select(UPDATEME);
>
> # Print the keys (col names)
> print join(',',@key);
> print "\n";
>
> # Print the entry
> foreach $imakey(@key)
> {
> # Do each key
> foreach $imakey(@key)
> {
> print $$array[$i]{$imakey}.',';
> }
> print "\n";
> }
>
> select(STDIO);
>
> # Close that file down
> close(UPDATEME);
> }
>
> #################################################################
> # sub quicksort
> #
> # This passes the numbers first, last the string (which is what
> # we will sort by) and the ref to arrays list and array where
> # list is what we're sorting.
> #
> # Results in a sorted list
> #
> #################################################################
>
> sub quicksort
> {
> # Get the variables we need
> my($key,$first,$last,$list,$array)=@_;
>
> # If they are eq then sort is done
> if($first<$last)
> {
> # Need to divide the array into pieces
> $cuthere = partition($key,$first,$last,$array);
>
> # Recursively continue until done
> quicksort($key,$first,$cuthere-1,$list,$array);
> quicksort($key,$cuthere,$last,$list,$array);
> }
>
> # Return in the new order
> @$list=@params[$first..$last];
> }
>
> #################################################################
> # sub partition
> #################################################################
>
> sub partition
> {
> my($key,$first,$last, $array)=@_;
>
> #Get the last one comparing
> $x=$$array[$params[$last]]{$key};
> chomp($x);
>
> #Start early so when you add one it's at the beginning
> $i=$first-1;
>
> #Check em all, the first to the almost last, last one is the key.
> for $j($first..$last-1)
> {
> #How can we tell that it is a date?
> #This doesnt work Right now.
> if($key eq "this is a date????")
> {
> #pull out the value for this entry and chomp it
> $temp=$$array[$params[$j]]{$key};
> chomp($temp);
>
> #rip $x apart!
> #assuming the format YYMMDD
> $temp =~ /(\d\d)(\d\d)(\d\d)/;
>
> $xyear =$1;
> $xmon = $2;
> $xday = $3;
>
> #rip $temp apart!
> #assuming the format YYMMDD
> $temp =~ /(\d\d)(\d\d)(\d\d)/;
>
> #If the year is higher than the year we're looking for it's a match
> if ($1 > $xyear)
> {
>
> }
> #Otherwise we need to look at the month
> elsif ($1 == $year)
> {
> #Same here as long as it's higher
> if($2 > $mon)
> {
>
> }
> #Same month? keep going
> elsif($2 == $mon)
> {
> #so, now $1==$year && $2==$mon, so day has to be greater
> if($3 >= $day)
> {
> #I get the feeling I should do something here.....
> }
> }
> }
> }
> #Sure, it sorts numbers, but can it julian fries!?
> #Still need strings!!!
> if ($$array[$params[$j]]{$key} <= $x)
> {
> $i++;
> #SWITCH!
> ($params[$i],$params[$j])=($params[$j],$params[$i]);
> }
> }
> $i++;
> #Now put the last one in it's place!!!
> ($params[$i], $params[$last]) = ($params[$last], $params[$i]);
>
> #Return where last now is
> return $i;
> }
>
> #################################################################
> # sub search #
> #################################################################
> sub search
> {
> #Need my variables
> my($match,$table)=@_;
>
> #The search keys that were sent by the form. These keys must match the
keys
> in the table file
> #exactly (i.e. NAME != Name)
> if(param())
> {
> my(@skeys) = param();
> }
> else
> {
> print "HELLO";
> my(@skeys) = $params[3];
> }
>
> #Do a search on each key sent
> foreach $key(@skeys)
> {
> if(param($key) ne "" && !(param($key) =~ /submit/i || param($key) =~
> /edit/i))
> {
> #This is the value of the key sent
> $temp = param($key);
>
> #Replace all spaces with ntohing hte g is for greedy...or global if you
> wanna be boring
> $temp =~ s/ //g;
> $temp =~ s/\*/.*/g;
> $temp =~ s/\?/./g;
>
> #In case they're searching ultiple items in a string
> @item = split (',',$temp);
>
>
> #Once for each item
> for $i(0..$#item)
> {
> #Go through each onestill left in the list
> #If it's the first time, the whole list is still here.
> for $j(@match)
> {
> #Match the pattern, don't see if they're equal
> #Do ranges?
> if($$table[$j]{$key} =~ /^$item[$i]/i)
> {
>
> #If it matches put in into the array
> push (@matches,$j);
> }
> }
> }
> #All our temp matches are now allt he matches that still work
> @$match=@matches;
> }
> }
> }
>
> #################################################################
> # sub loadtable
> #################################################################
> sub loadtable
> {
> #Read the variables
> my($dir,$file)=@_;
> my(@table);
>
> open(FILE,"<$dir$file") || die "Cannot open $fname for reading:$!";
>
> #Read the first line because that has all the column names
> $line = <FILE>;
>
> #Get rid of the windows end lines
> chomp($line);
> chop($line);
>
> #These names will be our keys
> @key =split(',',$line);
> #This reads in each line of the file starting with the second line
> while(<FILE>)
> {
> #Ger rid of windows end lines
> chomp();
> chop();
>
> #Make sure there's something we want (Anything) in this line
> if(!($_ =~ /[\d\w]/))
> {
> next;
> }
>
> #Stick it into an array and break it at the commas
> @line=split(/,/);
>
> #force entry to have anon hash for each line
> #very important otherwise everything will have the same values
> $entry = {};
>
> #Stick those elements into the proper key.
> for $i(0..$#line)
> {
> #Check for quoted spots, they're just one entry inside the quotes even if
> there's commas
> #And drop the double quote, we don't need it anymore
> if($line[0]=~ s/\"//)
> {
> #Well we know there's at least two parts,the first quote and the second
> $entry->{$key[$i]}= shift @line;
> for $j(0..$#line)
> {
> #Keep an eye out for that second quote
> if($line[0]=~ s/\"//)
> {
> #If we find it we're done with this loop.
> $j=$#line;
> }
> #Hehehe, Guess we got a little happy removing comma's, so now we need to
put
> some back in
> $entry->{$key[$i]}.= ',';
> $entry->{$key[$i]}.= shift @line;
> }
> }
> else
> {
> #Shift is very useful!
> $entry->{$key[$i]}= shift @line;
> }
> }
> #So, is push. Add that new line onto the end of the array
> push @table, $entry;
> }
> close(FILE);
>
> #This gives the whole file back now that it's in an easily accessable (and
> searchable) format
> return @table;
> }
>
> #################################################################
> # sub printpage
> #################################################################
> sub printpage
> {
> my($matches,$table,$edit)=@_;
>
> print <<HEAD;
> <html>
> <head>
> <title>Query Results</title>
> </head>
> <body bgcolor="#809CAA">
> Return to query
> <table align=center border=1.
> <tr align=center>
> HEAD
>
> #If you're editing you need a form
> if($edit){
> print "<form action=\"$ENV{'SCRIPT_NAME'}?$params[0],$params[1],UPDATE\"
> method=post name=\"edit\">";
> }
> #And you need if you're a guy with a password you can add new entries
> if($edit eq "edit")
> {
> printrow ($#$table+1,$table,$edit);
> }
> #We need to know what col's what so printout the names
> foreach $colname (@key)
> {
> #If we're not editing let them do a sort on them too.
> if($edit eq "edit")
> {
> #Making it pretty too, if somone reads the source in the browser
> print "\t\t\t<td>$colname</td>\n";
> }
> else
> {
> #Can't pass spaces in the address line so make 'em into +es
> $as_param=$colname;
> $as_param =~ s/ /+/g;
>
> #Call itself in for the sort.
> print"\t\t<td><a
> href=\"$ENV{'SCRIPT_NAME'}?$params[0],$params[1],$as_param";
>
> #Note: These #'s are the number's of the entry not any value of the table
> #This adds all the elements in our matches to the address line and those
are
> what's sorted on
> foreach $i(@$matches)
> {
> print ','.$i;
> }
> #Now actually print the name of the col
> print"\">$colname</a></td>\n"
> }
> }
>
> print "\t</tr>\n";
>
> #Print all those entries that matched
> foreach $i(@$matches)
> {
> printrow ($i,$table,$edit);
> }
>
> #Again if we're editing then we need a button to submit the changes
> if($edit eq "edit") {
> print "<input type=submit name=submit value=Udate></form>";
> }
>
> print "</table>\n</body>\n</html>";
>
> }
>
> #################################################################
> # sub printrow
> #################################################################
> sub printrow
> {
> #Get those variables
> my($entry,$table,$edit)=@_;
>
> print "\t</tr>\n";
>
> #Go through and write in the order of the keys
> foreach $j(@key)
> {
> print "\t\t<td>\n";
>
> if($edit)
> {
> print "\t\t\t<input type=\"text\" name=\"$j $entry\" length=6
> value=\"$$table[$entry]{$j}\">\n";
> }
> else
> {
> print "\t\t\t<a
>
href=\"$ENV{'SCIPT_NAME'}?$params[0],$params[1],SEARCH,$$table[$entry]{$j}\"
> >$$table[$entry]{$j} \;\n</a>";
> }
> print "\t\t</td>\n";
> }
> print "\t</tr>\n";
> }
>
> ---------- query.html ----------
> <html><head><title>Query Form</title>
> <script language=JavaScript>
> function login() {
> var login = prompt("What is your user name?","user");
> var password = prompt("What is your password?","passwd");
> document.forms['ednquery'].action+= ",EDIT,"+login+","+password;
> }
> </script>
> </head>
> <body>
> <form
action="database_script.pl?/Inetpub/wwwroot/cgi-bin/edndb/,dBdata.csv"
> method=POST name="dBquery">
> <table border=1 align=center>
> <tr align=center>
> <td>Number</td>
> <td>Name</td>
> <td>Status</td>
> </tr>
> <tr>
> <td><input type=text name="Number" size=5></td>
> <td><input type=text name="Name" size=10></td>
> <td><input type=text name="Status" size=10></td>
> </tr>
> <tr>
> <td><input type=submit name="submit" value="Submit"></td>
> <td><input type=reset name="clear" value="Clear"></td>
> <td><input type=submit name="submitedit" value="Edit"
> onClick="login();"></td>
> </tr></table>
> </form></body></html>
>
> ---------- dBdata.csv ----------
> Number,Name,Status,
> 55,Tom,M,
> 58,Ed,M,
> 53,Larry,O,
> 51,Bob,M,
>
>
- Next message: Andrea Spitaleri: "sorting and resorting hash"
- Previous message: Aldric L'Hernault: "Re: Am I a programmer or a scripter ?"
- In reply to: James Hunt: "CSV dB script help"
- Next in thread: Keith Keller: "Re: CSV dB script help"
- Reply: Keith Keller: "Re: CSV dB script help"
- Reply: Tad McClellan: "Re: CSV dB script help"
- Reply: David K. Wall: "Re: CSV dB script help"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Relevant Pages
|
