CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in LOG
IN

Home: Perl Programming Help: Intermediate:
Help with database record extraction!

 



CuzDesign
Deleted

Dec 14, 2000, 1:19 AM

Post #1 of 5 (461 views)
Help with database record extraction! Can't Post

Hello,
Currently I am working on a program taht will test a database for the proper vehicle stock number. If the stock number is found it will print the vehicle information for that vehicle only.

My Problem is the way the database is setup. Below is and example of how the database is setup:


Code
C0018,23328.00,00,CHEVROLET,SD,,IMPALA,NAVY BLUE/LT GRAY CUSTOM CLOTH,6,,2G1WF55K3Y9164675,6 WY PASS PWR SEAT,DETAIL,1 
,,,,,,,,,,,LEATH 60/40 - 4 WY ST ADJUST,FINISH LINE,
,,,,,,,,,,,3.8 LITER SFI V6 ENG.,,
,,,,,,,,,,,4 SPD AUTO TRANS,,
,,,,,,,,,,,9225/60R16 B/W RAD TIRES,,
,,,,,,,,,,,LEATH STRG WHL/RADIO CTRLS,,
,,,,,,,,,,,AM/FM STEREO W/CASS /CD,,
,,,,,,,,,,,CALIF EMISSIONS,,
C0149,25050.00,00,CHEVROLET,CP,,CAMARO,ARCTIC WHITE/EBONY,8,,2G1FP22G1Y2147216,BUCKET SEATS,DETAIL,1
,,,,,,,,,,,ELEC REAR WINDOW DEFOGGER,WHEELS,
,,,,,,,,,,,5.7L SFI-V8,,
,,,,,,,,,,,4 SPD AUTO TRANS,,
,,,,,,,,,,,16 CHRM ALUM WHLS,,
,,,,,,,,,,,P235/55 R-16 S/B RAD B/W TIRES,,
,,,,,,,,,,,AM/FM W/ CD,,
,,,,,,,,,,,CALIF EMISSIONS,,

The first field is the stock number. Now what I am trying to do is lets say I enter in stock number C0018, and I only want to show what are in fields 11, up untill the next vehicle stock number. What I keep getting is only the first entry in field 11 instead of all the entries in field 11.

If anyone could tell me what I could do to make this work I would be greatly appreciated. Here is the code I am using now and it finds the proper stock number but only prints the first part of the record.


Code
use CGI qw(param); 

$stock = param('stock');

print <<HTML;
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>Untitled</title>
</head>
<body>
<table width="500" cellspacing="0" cellpadding="0" border="0">
HTML

# Open the database
open (DATA,'<newinventory.txt') || die "Couldn't open vehicle file $!";
while (<DATA> ){
chomp;
my @list = split(/,/,$_);
if (lc($list[0]) eq lc($stock)){
@info = $list[11];
print <<HTML;
<tr>
<td><font face="Verdana, Arial" color="#000000" size="-1"><b>$list[0]</b></font>
<font face="Verdana, Arial" color="#000000" size="-2"><li>@info</li></font>
HTML
}
}
close (DATA);
print "</td>\n</tr>\n</table>\n</body>\n</html>";

Thanks in advance.

Ray



Rivotti
User

Dec 14, 2000, 3:48 AM

Post #2 of 5 (453 views)
Re: Help with database record extraction! [In reply to] Can't Post

Hi CuzDesign,

let me see if I understood:
the first line is the data of the vehicle the lines below are the description, right? Your problem is in data structure. Let me explain better: instead of using several lines for each vehicle you should only use one. Heres an example ( CSV style - fields separated by \t(tabs) lines end \n(end of line):

C0018\t23328.00\t00\tCHEVROLET\tSD\t\tIMPALA\tNAVY BLUE/LT GRAY CUSTOM CLOTH\t6\t\t2G1WF55K3Y9164675\t6 WY PASS PWR SEAT\tDETAIL\t1\tLEATH 60/40 - 4 WY ST ADJUST,FINISH LINE
3.8 LITER SFI V6 ENG.
4 SPD AUTO TRANS
9225/60R16 B/W RAD TIRES
LEATH STRG WHL/RADIO CTRLS
AM/FM STEREO W/CASS /CD
CALIF EMISSIONS\n # end of line

Now in your CGI file

use CGI qw(param);
$stock = param('stock');
print "Content-type: text/html\n\n"; # use this and you donīt need to use the header tags

open (DATA,'<newinventory.txt') || die "Couldn't open vehicle file $!";
while (<DATA> ){ ### now the fun part...
chomp;
# for each line split by \t, get each field and associate with a variable. $field1 would be C0018
($field1,$field2,$field3,$field4,$field5,$field6,$field7,$field8,$field9,$field10,$field11,$field12,$field13,$field14,$description) = split(/\t/,$_);

# Check if the $field1 is equal to $stock
if ($field1 eq $stock) {
# it is and print out
print '<tr><td><font face="Verdana, Arial" color="#000000" size="-1"><b>'.$field1.'</b></font>'
# forach
slipt and push to @lines
@lines = split(/
/,$description);
foreach $lines(@lines) {
print '<font face="Verdana, Arial" color="#000000" size="-2"><li>'.$lines.'</li></font>'
} # end foreach
} # end if
} # end while

# I used to identify each line in $description with
but you could use anything you want ( just donīt use \t or \n )
# you donīt need to use the </body> </html> tags

This is a way to do it. I recommend you to see the documentation of HTML::Template in http://search.cpan.org/doc/SAMTREGAR/HTML-Template-2.0/Template.pm. Itīs a lot better to work with HTML.

I hope I did help
Rivotti



CuzDesign
Deleted

Dec 14, 2000, 11:00 AM

Post #3 of 5 (450 views)
Re: Help with database record extraction! [In reply to] Can't Post

Hello Rivotti,
Thank you for your reply. One thing I forgot to mention is I am getting this data from a remote system. All information is sent to me in the format that I showed it here. The database is setup that way when I get the file. I can see where your way would be a way to do it. But the company I am pulling this information from will not change there database.

If annyone can help me with the way the database is setup now I would be greatly appreciated.

Thanks in advance.

Ray



Rivotti
User

Dec 15, 2000, 7:19 AM

Post #4 of 5 (437 views)
Re: Help with database record extraction! [In reply to] Can't Post

Hi again,

Try this:

$stock = param('stock');

open(INFILE,"<newinventory.txt") || die;
@trash = <INFILE>;
close(INFILE);

$record = 0;

foreach(@trash) {

if (($_ =~ /^,,,,,,,,,,,/)&&($record == 1)) {
$_ =~ s/\n//g;
$_ =~ s/^,,,,,,,,,,,//;
$_ =~ s/(,*)$//;
@list = (@list,$_);
}
else {
if($_ =~ /^$stock/) {
($first) = split(/,/,$_); # it should be ($first,$second,$third... bla bla bla). This is just an example
$id = $first;
$record = 1;
}
else {
$record = 0;
}
}
}

print '<tr><td><font face="Verdana, Arial" color="#000000" size="-1"><b>'.$id.'</b></font>'

foreach $list(@list) {
print '<font face="Verdana, Arial" color="#000000" size="-2"><li>'.$list.'</li></font>'
}

Sorry for the uncommented code but I'm with a lot of work



Mortimer
journeyman

Dec 23, 2000, 9:10 PM

Post #5 of 5 (400 views)
Re: Help with database record extraction! [In reply to] Can't Post

$stock = "C0018";

# Open the database
open (DATA,'<rayCSV.txt') || die "Couldn't open vehicle file $!";
while (<DATA> ){
chomp;
my @list = split(/,/,$_);
if (lc($list[0]) eq lc($stock)){
@info = split(' ',$list[11]);
print <<HTML;
<tr>
<td><font face="Verdana, Arial" color="#000000" size="-1"><b>$list[0]</b></font>
<font face="Verdana, Arial" color="#000000" size="-2"><li>@list[11..$#list] - @info[1,3]</li></font>
HTML
}
}
close (DATA);
print "</td>\n</tr>\n</table>\n</body>\n</html>";

Thanks in advance.

Ray
#########
Disregard Rivotti's answer (bless his cotton socks), he's completely off the rails. This is just a plain
old comma seperated values (csv) file. Each row is a record, and each space between the commas, plus the
ends, are members (fields). In this file there are fourteen fields to a record (line).

You're nearly there. I only made two adjustments for your code to work. If you want $list[11] to become an
array, (or any of the fields for that matter), you'll have to split() it:

@info=split(' ',$list[11]);

But this is unnecessary if all you want to do is print the line from $list[11].
(Excerpt from your code, adjusted).

<font face="Verdana, Arial" color="#000000" size="-2"><li>@list[11..$#list] - @info[1,3]</li></font>

Dave. (Wannabe Perl hacker).
www.dmscripts.com - davemortimer@one.net.au


 
 


Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives