asked on

Starting from the beginning in order to make query

I have an ascii file with 2000 records.
The fields are :
KP LENGTH 4 (TYPE STRING)
PERPE LENGTH 40 (TYPE STRING)

I want to make parameter query based on KP and I also want to do it using Mysql.
Can I have all the relative steps with the relative code in order to do it.
(I use PWS)
How can I know where Mysql put the databases?
Thank you.

maneshr

vage78,

"..I want to make parameter query based on KP and I also want to do it using Mysql..."

i am not clear what exactly you are looking for here.

do you want to search the text file or the MySql DB???

"..How can I know where Mysql put the databases?."

why would you want to know that??

as long as you can connect to the MySql DB, retrieve the records and search it, you should be ok.

how will knowing the location of the DB file change the above sequence in any way?

please provide as much more detail as you can.

This will help you get a more accurate answer, faster.

vage78

ASKER

Hi manesh
I want to make searches in my ascii file
I want also import it in Mysql (if it is useful) in order to use a select command in my perl script.

maneshr

vage78,

"...I want to make searches in my ascii file.."

That can be easily done.

"..I want also import it in Mysql (if it is useful) in order to use a select command in my perl script. .."

what edo you mean when you say "import"???

what will the select statement return that the ASCII search will not do??

why do you want to perform the whole process twice??

why not just search from the DB, rather than ASCII file??

please provide details on the same.

cgrey

Also please indicate how your ASCII records are stored.

Are they newline delimited (i.e. one line per recored) or is the entire file a flatfile with true length delimiting? (ie. do you pad your records with space to fill our the field length and not have any newlines between records) ?

vage78

ASKER

Yes there is one line per record.
To be more specific I have take it from a Unix system via ftp and I want to work with it in my PC with PWS and Perl.
I decide to use Mysql just for my first test because later I will try with more big files.
I want for example ask in a text box
Enter KP :
(and execute this select command)
select * from myfile where KP = [entered_KP]

maneshr

vage78,

"...I want for example ask in a text box
Enter KP :
(and execute this select command).."

so if you are anyway going to use the DB, why do you want the ASCII file searched???

do you download the info from the UNIX system in an ASCII file???

Can you pl explain, in a step-by-step method, how the ASCII file fits in the picture??

That will help you get a more accurate answer, faster.

cgrey

Searching the ASCII file really isn't a major problem, though it will be either time consuming (relatively) or memory intensive depending on what method you take.

However, I have to echo maneshr's last comment ("...please explain....") as I'm not exactly tracking with what you want yet.

vage78

ASKER

I want to have all the relative code in order to make parameter query in KP field.
My finally output I like to be like this
ENTER KP : (12271 for example)
and coming out all the relative records.
I hope now it is OK.
Anyway I'm waiting for the relative code.

cgrey

Ok...let me see if I understand:

You want to take the ASCII file
Have the script input it into the mysql database
Be able to query the database based on the KP entry

Is this correct?

If so, is the KP field unique or can there be duplicates?

vage78

ASKER

Hi cqrey
My problem is very simple
I have already an ascii file with the 2 fields KP (unique) and PEREP
Now I want to make parameter query based on KP field. And of course prompting to user for
"Please enter KP code :"
and giving output the relative records.
And I also ask how can I do through a Select statement.
Now I thing is O.K.

cgrey

$searchval="1001";

open (FILE,"<data");

while (<FILE>) {
$kp=substr($_,0,4);
$perpe=substr($_,4,40);
if ($kp eq $searchval) {
print $perpe;
last;
}
close (FILE)

Your mysql query would be (assuming that the fields in your mysql database are named KP and PERPE in the table MYTABLE)

SELECT PERPE
FROM MYTABLE
WHERE KP='$searchval'

vage78

ASKER

Hi cquery
Can I have the all code starting from scratch in order to make parameter query on KP field.
Thank you

cgrey

Do you also need code for the mysql query? This is for querying the ascii flat file.

Here are the contents of the kp.data file:
0001ABCDEFGHIJKLMNOabcdefghijklmnopqrstuvxyz
1002abcdefghijklmnopqrstuvxyzABCDEFGHIJKLMNO
20039999999999999999999999999999999999999999

Here is the script for querying the ASCII data:
#!/usr/bin/perl
print "Input KP:";
while (!$searchval) {
$searchval = <STDIN>;
chomp $searchval;
print "Error, you must input a value or \'E\' to exit.\nInput KP:" unless $searchval;
}

chomp $searchval;
die "Exit Condition Met" if $searchval eq uc('e');
open (FILE,"<kp.data");

while (<FILE>) {
chomp;
$kp=substr($_,0,4);
$perpe=substr($_,4,40);
if ($kp eq $searchval) {
print "$perpe\n";
last;
}
}
close (FILE);

vage78

ASKER

Thanks cquery,
Is this code complete? Do I need to put
print "content/type: html" etc ...
or do I need to define in my code any module for ascii files?
If I'm trying throught Mysql how is the full code?
Thank you

cgrey

Umm....you never stated that this was for cgi. All this will do is take keyboard input and search the ascii file for the correct line.

If you want this to be for a web site this is going to get more complicated. Content-type header is only needed if you are outputting to web browser.

Do you have modules for mysql and DBI installed?

vage78

ASKER

Yes cquery I have already installed modules for mysql and DBI.
I have put print "Content-type: text/html\n\n"; (as second line in your code)
but it doesn't work. My web browser is searching for a long time but finally doesn't appear nothing.
What can I do?
I'm waiting for your answer.
Thanks a lot.

cgrey

This script will not work as a cgi script. You need to call this script from a form. Put this script in your /cgi-bin and name it getkp.pl. Call it using http://yourdomain.com/cgi-bin/getkp.pl

BTW, Don't you think this question has become worth just a tad bit more than 30 points by now?

#!/usr/bin/perl

# Set this to the full path and location of your datafile.
$data_loc="./kp.data";

print "Content-type: text/html\n\n<HTML><BODY>";

%FORM=&get_input;

if (!$FORM{'SEARCHVAL'}) {
print qq~
<BR>
<FORM ACTION="/cgi-bin/getkp.pl" METHOD=GET>
Input KP:<INPUT TYPE="TEXT" SIZE="5" MAXLENGTH="4" NAME="SEARCHVAL">
<INPUT TYPE="SUBMIT" VALUE="Search">
<INPUT TYPE="RESET" VALUE="Clear"><BR>
</FORM>
</BODY>
</HTML>
~;
exit();
}

open (FILE,"<$data_loc");

while (<FILE>) {
chomp;
$kp=substr($_,0,4);
$perpe=substr($_,4,40);
if ($kp eq $FORM{'SEARCHVAL'}) {
print qq~
<B>Entry Found!</B><BR>
KP: $FORM{'SEARCHVAL'}<BR>
PERPE: $perpe<BR>
<A HREF="/cgi-bin/getkp.pl">Return to search</A><BR>
~;
$found_flag=1;
last;
}
}
print qq~
<BR>
KP Entry $FORM{'SEARCHVAL'} was not found. Sorry.<BR>
<A HREF="/cgi-bin/getkp.pl">Return to search</A><BR>
~ unless $found_flag;

print "</BODY></HTML>";

close (FILE);
exit();

# --------------------------
# This sub will process the form variables into a hash

sub get_input {

my $input = '';
my %in = ();
($ENV{'REQUEST_METHOD'} eq "POST") and read(STDIN, $input, $ENV{'CONTENT_LENG
TH'});
($ENV{'REQUEST_METHOD'} eq "GET") and ($input = $ENV{'QUERY_STRING'});

my @pairs = split(/&/, $input);
foreach (@pairs) {
my ($name, $value) = split /=/;
# Un-Webify plus signs and %-encoding
$value =~ tr/+/ /;
$value =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg;
$value =~ s///g;
$value =~ s/\n/<br>/g;
$value =~ s/\r//g;
$in{$name} = $value;
}
return %in;

}

vage78

ASKER

Thanks cquery,
It works very good, take 50 points for it.
But I want ask you if I want to return much more fields in response of that KP which part of my code I have to change?
For example I want to return also another 3 fields
REGION, MOTO, FR what can I do ?
And another question can I continue ask you in the same question more questions in order no to loose my question's history?

ASKER CERTIFIED SOLUTION

cgrey

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Computer101

vage78,
I can see that you have logged onto e-e recently. You have many open questions (13) to include this. When do you expect to start cleaning these up. I have posted this in all your open questions to assist you.

If you have a problem, feel free to e-mail me at computer101@experts-exchange.com

Thank You
Computer101
Community Support Moderator

Netminder

Netminder

Force/accepted by

Netminder
Community Support Moderator
Experts Exchange