Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

CGI DBI Newbie 1

Status
Not open for further replies.
mte0910

mte0910

Programmer
Apr 20, 2005
232
US
Here is my code that works perfectly...The question is below...

#!C:/Perl/bin/perl.exe
use CGI;
CGI::ReadParse(*in);
$file = 'c:\CBC\UserQuery.txt';
print "Content-type:text/html\n\n";
print '<HTML><HEAD><TITLE>saveform.cgi results</TITLE></HEAD>';
print '<h2>Here are the results of your query.</h2>';
print '</BODY></HTML>';
open(FILE, ">$file")||die "Can't open $file";
flock(FILE, 2)||die "Can't lock $file";
foreach $i (keys %in) {
print FILE "$in{$i}\n";
}
print FILE "\n";
close(FILE) || die "Can't close $file";



#Step1:
use DBI;

my $data_source = q/dbi:ODBC:MEMLOGDB/;
my $user = q/NameChangedToProtectTheInnocent/;
my $password = q/CantTellYouThatEither/;

# Connect to the database (1st time)
my $dbh = DBI->connect($data_source, $user, $password)
or die "Can't connect to $data_source: $DBI::errstr";

my $sql1 = "SELECT csite,ccallid,cdatim,cansr,cagt,ctrxfr,cintcllid FROM UCBC WHERE CCLID = '8885551212' ORDER BY cdatim DESC";
my $sth1 = $dbh->prepare($sql1) or die "Can't prepare statement: $DBI::errstr";
$sth1->execute();
while ( my @row1 = $sth1->fetchrow_array ) {
print "@row1<br>\n";
}
$dbh->disconnect; close (OUT);
******************
So here is what I am doing...
I have a web page where the user inputs a phone number.
That phone number is then stored in a text file on the web sever.

Next I have a hard coded sql query that looks up records based on a phone number that is currently hard coded.

My question is, how do I get the phone number that the user entered, and make it the number I use in my look up?

Make sense?
 
Sort by date Sort by votes
Could, just wasn't sure how...
I am learning as I go here...
 
Upvote 0 Downvote
First thing to do is dump this code you posted:

Code: 
#!C:/Perl/bin/perl.exe
use CGI;
CGI::ReadParse(*in);
$file = 'c:\CBC\UserQuery.txt';
print "Content-type:text/html\n\n";
print '<HTML><HEAD><TITLE>saveform.cgi results</TITLE></HEAD>';
print '<h2>Here are the results of your query.</h2>';
print '</BODY></HTML>';
open(FILE, ">$file")||die "Can't open $file";
flock(FILE, 2)||die "Can't lock $file";
foreach $i (keys %in) {
       print FILE "$in{$i}\n";
}
print FILE "\n";
close(FILE) || die "Can't close $file";

ReadParse() is a nearly obsolete set of functions that provide backwards compatibility with the very very old cgi-lib.pm library. Right now you need to get up to speed with the CGI module and other good practices when writing CGI scripts.

Also, flock() does not work on Windows.



------------------------------------------
- Kevin, perl coder unexceptional! [wiggle]
 
Upvote 0 Downvote
OK, I've dumped the CGI I had and I now have something that is cleaner anyway. I still need a little help with the DBI side of this.

Code: 
my $Q1 = $Line1;
my $dbh = DBI->connect($data_source, $user, $password);
my $sql1 = "SELECT a1, b1, c1 FROM UCBC WHERE (a1 = ?) AND (c1 LIKE ?) ORDER BY c1 DESC";
my $sth1 = $dbh->prepare($sql1);
$sth1->execute($Q1);

While I figured out that adding the variable to the execute statement then made my ? contain the value, I now need to figure out how to have multiple variables in the SQL statement.

In other words, if the SQL statement has two (or more) variables (?) do I have to have something like...
$sth1->execute($Q1,Q2);
or does $Q1 simply have to containt both pieces of information? In which case, how do I define what is what?

I'm guessing none of this makes sense.
 
Upvote 0 Downvote
mte0910
Please let me put you in the picture.
I started using Perl many years ago and have found it to be a fantastic language to use. When I started, I bought a book which taught me the basics. Using the parse routine you used, I created quite a few scripts which did exactly what I wanted them to do. After asking a few questions on tek-tips, a few of the experts on here told me I was approaching the parse routines in the wrong way.
Having had no formal training with Perl I knew no better.

I very much appreciate the help I have been given over the years but somtimes the replies from the experts can be a little confusing. If you do not understand what they are telling you - have a google around to see if you can find the answer yourself if all else fails - ask again! The guys on here are very tollerant of us newbies and they will help us all they can.
They like to see that you have at least tried something, even if it is totally wrong.

KEVINADC is a good guy to have on your side as he quite often solves a complicated problem with a few lines of code, even if you do not understand his reply at all.

Some guidelines
Start you script with the shebang line and then issue
Code: 
use strict;
use warnings;
use CGI;
A new dbi object is started with
Code: 
my $query = new CGI;

receive vars from a form with code such as
Code: 
my $USERNAME=$query->param("zeus") || "";

Where $USERNAME is the var you use within the script and 'zeus' is the name of a field in your HTML form.

To put the incomming data into your sql table
you would issue
Code: 
my $tabvars=(field1,field2);
my $tabvals=('content1','content2');
my $makesql="INSERT INTO Table Name ($tabvars) VALUES ($tabvals)";
$saverec=$dbh->prepare("$makesql");
$saverec->execute();
I hope this makes sense and gives you a bit of a guide into how I work with MySQL tables.

Don't be surprised if the experts say I am doing it wrong LOL.
Tek-Tips is a great community and on the whole, the people here are a friendly bunch. For that I am forever grateful


Keith
 
Upvote 0 Downvote
If you're going to delve into database and DBI then reading the DBI documentation is a must:


I know its very long and some of it might prove difficult to understand at first. I don't have much practicle experience with DBI and sql so I'm in the same boat as far as not knowing how to use the DBI interface. When I need to do something with the DBI module I always have to refer back to the documentation and try a few things. If I really get stuck I post a question.

------------------------------------------
- Kevin, perl coder unexceptional! [wiggle]
 
Upvote 0 Downvote
Hey Kevin you are a valuable member of this forum.
I have posted questions and you are the one who comes up with a solution. I don't always understand your solution but you usually point me in the right direction and with some work, I usually get to a solution. I have many scripts working flawlessly (is that a word?) thanks to you, so please keep up the good work.

A Big Thankyou to you and your associates.

Keith
 
Upvote 0 Downvote
Thanks........ mom [smile]


------------------------------------------
- Kevin, perl coder unexceptional! [wiggle]
 
Upvote 0 Downvote
I'm American. See, we're not all bad...... [wink]

Seriously, I appreciate your kind words, it is good to know I have been able to help you and that you appreciate the help.

One of your fellow contry men (I think) helped me in the past, Dave Cross. He is a well known member of the perl community, the london perl mongers (last I checked), and while I don't see him around on forums anymore, he used to be quite active and was always generous and is pretty much my model for helping others.

I don't make any claim to being in his league when it comes to perl or being helpful, but he was an inspiration to me and took an interest in my perl education a number of years ago.




------------------------------------------
- Kevin, perl coder unexceptional! [wiggle]
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

1DMF
  • Locked
  • Question
DBI using DBD::ODBC on MS SQL 2008 R2 corrupting Unicode characters
Replies
0
Views
169
1DMF
1DMF
jcarrott
  • Locked
  • Question
Trying to use DBI::SQLite 1
Replies
6
Views
350
jcarrott
jcarrott
hg4372
  • Locked
  • Question
Calculate.pl Not working correctly. Please help - Newbie
Replies
2
Views
197
hg4372
hg4372
jcarrott
  • Locked
  • Question
DBI update function 1
Replies
1
Views
157
feherke
feherke
martinjburgess
  • Locked
  • Question
Newbie Question 1
2
Replies
27
Views
742
ProbablyDown
ProbablyDown

Part and Inventory Search

Sponsor

Back
Top