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:
Perl/mysql script

 



manunkind
Novice

May 20, 2001, 12:04 PM

Post #1 of 21 (4547 views)
Perl/mysql script Can't Post

Hello,

I came to a stand-still here with this script. An example is at the bottom. As you can see, I'm building a script for a multiple search front end page for a mysql database. If you look at my code, you'll see what I'm trying to do. The problem is that the first $build segment that is used and added to $SQL cannot start with an "and", but the rest have to. How do I get rid of it for only the first one? They all have to to have "and" in it to work, the query could start with ANY of them. Any ideas on how to make the first one without the "and" but remain listed as having it? I started a "$count" type thing, but that won't work for only the first one.

Here's some partial code to see:

$SQL = "select * from members where ";

$build = "and member_id = '$member_id' ";
if ($member_id ne "") {$SQL = $SQL.$build;}
$build = "and last_name = '$last_name' ";
if ($last_name ne "") {$SQL = $SQL.$build;}
$build = "and dob like '$dob' ";
if ($dob ne "") {$SQL = $SQL.$build;}
$build = "and username = '$username' ";
if ($username ne "") {$SQL = $SQL.$build;}
$build = "and business_name = '$business_name' ";
if ($business_name ne "") {$SQL = $SQL.$build;}
$build = "and mailing_city = '$mailing_city' ";
if ($mailing_city ne "") {$SQL = $SQL.$build;}
$build = "and mailing_state = '$mailing_state' ";
if ($mailing_state ne "") {$SQL = $SQL.$build;}
$build = "and mailing_zip = '$mailing_zip' ";
if ($mailing_zip ne "") {$SQL = $SQL.$build;}
$build = "and physical_city = '$physical_city' ";
if ($physical_city ne "") {$SQL = $SQL.$build;}
$build = "and physical_state = '$physical_state' ";
if ($physical_state ne "") {$SQL = $SQL.$build;}
$build = "and physical_zip = '$physical_zip' ";
if ($physical_zip ne "") {$SQL = $SQL.$build;}
$build = "and business_type = '$business_type' ";
if ($business_type ne "") {$SQL = $SQL.$build;}
$build = "and description like '%$description%' ";
if ($description ne "") {$SQL = $SQL.$build;}
$build = "and membership_type = '$membership_type' ";
if ($membership_type ne "") {$SQL = $SQL.$build;}
$build = "and member_since like '$member_since' ";
if ($member_since ne "") {$SQL = $SQL.$build;}
$build = "and duration = '$duration' ";
if ($duration ne "") {$SQL = $SQL.$build;}
$build = "and expiration = '$expiration' ";
if ($expiration ne "") {$SQL = $SQL.$build;}
$build = "and chapter = '$chapter' ";
if ($chapter ne "") {$SQL = $SQL.$build;}
$build = "and chapter_leader = '$chapter_leader' ";
if ($chapter_leader ne "") {$SQL = $SQL.$build;}
$build = "and committee like '%$committee%' ";
if ($committee ne "") {$SQL = $SQL.$build;}
$build = "and listed_directory = '$listed_directory' ";
if ($listed_directory ne "") {$SQL = $SQL.$build;}
$build = "and enhanced = '$enhanced' ";
if ($enhanced ne "") {$SQL = $SQL.$build;}
$build = "and paid = '$paid' ";
if ($paid ne "") {$SQL = $SQL.$build;}



freddo
User

May 22, 2001, 10:04 AM

Post #2 of 21 (4535 views)
Re: Perl/mysql script [In reply to] Can't Post

Hello Manunkind,

that's not a perl solution, but more a little trick i use, change the select clause to be:
select select * from members where 1
with a trailling space, so you can keep your "and" everywhere since 1 is true (now it will list everybody if there's no parameter filled, so i added the "and 0;" for that case), so here's my try at it:

Code
#!/usr/bin/perl 

$SQL = "select * from members where 1 ";
@checks = ( member_id, last_name, dob, username );

$member_id = 123;
$last_name = "";
$dob = "bouh!";
$username = "freddo";

for (@checks) {
$params .= "and $_ = '$$_' " if ($$_ ne "");
}

$SQL .= ( $params ? $params.";" : "and 0;" );
print $SQL;

if you need more help, tell me.
freddo

;-- Real Programmers dont eat cache


freddo
User

May 22, 2001, 10:19 AM

Post #3 of 21 (4535 views)
Re: Perl/mysql script [In reply to] Can't Post

Btw, think to escape the content of $$_, some malicious user could fill in an SQL request as an argument, like
';select username, password from members;
(the name of the tables and the fields are generally guessed user,users, username, usernames, etc etc and member(s), customer(s), etc etc) it would result to a query like:
select * from members where 1 and member_id = '';select username, password from members;'
asking for the pass when you dont expect them... Smile

freddo

;---
Real programmers don't eat cache.


rGeoffrey
User / Moderator

May 22, 2001, 12:27 PM

Post #4 of 21 (4529 views)
Re: Perl/mysql script [In reply to] Can't Post

Your are looking at 23 separate variables to add upto 23 clauses to your where. Is there really a good reason you don't have them as 23 values in a single hash?

If you are willing to convert to using a hash this will work...


Code
my %big_hash = (member_id => 1234, 
mailing_state => 'PA',
member_since => '01/01/70',
committee => 'travel'
);

$SQL = "select * from members\n where " .
join ("\n and ",
(map { ($big_hash{$_}) ? "$_ = '$big_hash{$_}'" : () }
qw (member_id last_name username business_name
mailing_city mailing_state mailing_zip
physical_city physical_state physical_zip
business_type membership_type duration
expiration chapter chapter_leader listed_directory
enhanced paid
)
),
(map { ($big_hash{$_}) ? "$_ like '$big_hash{$_}'" : () }
qw (dob member_since)
),
(map { ($big_hash{$_}) ? "$_ like '\%$big_hash{$_}\%'" : () }
qw (description committee)
),
);

print $SQL, "\n\n";

I suppose you know a better name for %big_hash (like %cgi_data), but I was lazy about picking a name.

This will look in the hash for the 23 known columns and add the correct '=', 'like', or 'like %%' line to your query if needed and will join them together with 'and'. By doing the join you avoid the extra 'and' before the first item. And by looking at only the 23 accepted choices you don't have to worry about someone giving a strange column name to break things.

Also I dropped the ' ne "" ' from the if statements as they are not needed. If something is not the empy string, then it has some value, and as long as that value is not the number 0 everything is fine. You might need to add that back if a valid value will be the number 0.

You can do it without the hash, but building the individual parts of the where become harder to do inside a loop.

--
Sun Sep 9, 2001 - 1:46:40 GMT, a very special second in the epoch. How will you celebrate?


manunkind
Novice

May 23, 2001, 8:49 PM

Post #5 of 21 (4520 views)
Re: Perl/mysql script [In reply to] Can't Post

Thank you for your time guys. Before I had any replies I added a few more fields to the database and just kept it where you search one at a time. (removed all the "ands")

And frankly, I'm pretty much a beginner and I don't understand alot of what you guys wrote. :)

I understood the basics, but not the "hash" things and the "$_, $$__" things. I have alot to learn yet.

So, I guess I'll just leave it as it is for now. I thought it would be simpler. I wish I could just post the whole code and you guys can tell me where to insert it all at.

Anyway, you guys rock and thank you for trying to help me!



freddo
User

May 24, 2001, 12:00 PM

Post #6 of 21 (4515 views)
Re: Perl/mysql script [In reply to] Can't Post

Hello Manunkind,

Ok, let's go thru the code, if you wish:

Code
01|#!/usr/bin/perl 
02|$SQL = "select * from members where 1 ";
03|@checks = ( member_id, last_name, dob, username );
04|$member_id = 123;
05|$last_name = "";
06|$dob = "bouh!";
07|$username = "freddo";
08|for (@checks) {
09| $params .= "and $_ = '$$_' " if ($$_ ne "");
10|}
11|$SQL .= ( $params ? $params.";" : "and 0;" );
12|print $SQL;

------------------------------

Well, i guess lines 00 and 01 are ok for you.

------------------------------

On 02 i assign values into the @checks array, it is the same as:

$checks[0] = "member_id";
$checks[1] = "lastname";
$checks[2] = "dob";
$checks[3] = "username";


------------------------------

On 03 to 07 i assign the variables we will check.

------------------------------

Line 08 is the same as:

for each $_ (@checks) {

which means that the for will loop thru each data in the array @checks and put its content into the default input variable $_ (perl sometimes assumes you use it with certain command when you dont provide any argument). The first time it runs, $_ will be "member_id", then "last_name" and so on...
You can also consider it as:

for ($i = 0; $i++; $i < @checks) {
$_ = $checks[$i];


------------------------------

The next line 09 (that will then be looped thru all the elements of @checks) is a little trickier, let's have a look at it:

$params .= "and $_ = '$$_' " if ($$_ ne "");

You can read it this way if you wish:

if ($$_ ne "") {
$params .= "and $_ = '$$_' ";
}


So, what the hell are those strange $_ and $$_, i will explain it with the following example:

$apple = "green";
$myvar = "apple";
print "$myvar\n"; # prints apple
print "$$myvar\n"; # prints green, same as using $apple


In our case we use $_ instead of $myvar, so the on the first loop, if we make the replacement, it looks like:
if ($member_id ne "") {
$params .= "and member_id = '$member_id' ";
}


Appending the SQL statements to the variable $params, and so on for all the elements of @checks, this is called symbolic reference.

------------------------------

Line 11 is also an if statement you can expand it to somthing like:

if ( ($params ne "") or ($params !=0) ) {
$SQL .= "$params ;";
} else {
$SQL .= "and 0;";
}


The "and 0" is here in case all the parameters are empty, so the SQL statement will become:
select * from members where 1 and 0;
returning nothing.

In the sample program i made the sql statement will be:
select * from members where 1 and member_id = '123' and dob = 'bouh!' and username = 'freddo' ;

------------------------------

I hope this explain you a little about how the sample works, although you didnt talked about that i guess that you use this in a CGI script, so the program would become:

Code
$SQL = "select * from members where 1 "; 
@checks = ( member_id, last_name, dob, username );
for (@checks) {
$params .= "and $_ = '".param($_)."' " if (param($_) ne "");
}
$SQL .= ( $params ? $params.";" : "and 0;" );
print $SQL;

or something close (i didnt checked that one).

I also recommand to always have a look to scripts with the Deparse Module, like that:

Code
administrator@COKE /cygdrive/h/scripts.pl/perlguru/manunkind 
$ perl -MO=Deparse,-q,-sCT manunkind.pl
$SQL = 'select * from members where 1 ';
@checks = ('member_id', 'last_name', 'dob', 'username');
$member_id = 123;
$last_name = '';
$dob = 'bouh!';
$username = 'freddo';
foreach $_ (@checks) {
$params .= 'and ' . $_ . q[ = '] . $$_ . q[' ] if $$_ ne '';
}
$SQL .= $params ? $params . ';' : 'and 0;';
print $SQL;
manunkind.pl syntax OK

It's always a good way to learn.

Dont hesitate to ask for more info
freddo

;---
Real programmers´butcher dont understand when they just ask for 3735928559.


manunkind
Novice

May 24, 2001, 1:27 PM

Post #7 of 21 (4510 views)
Re: Perl/mysql script [In reply to] Can't Post

freddo, you are awesome! I printed all this out and will experiment with it later today.

Thank you very much!!



rGeoffrey
User / Moderator

May 24, 2001, 1:48 PM

Post #8 of 21 (4507 views)
Re: Perl/mysql script [In reply to] Can't Post

Here is another version of my code that takes a few extra characters to do the same thing...


Code
#!/usr/local/bin/perl 

my %big_hash = (member_id => 1234,
mailing_state => 'PA',
member_since => '01/01/70',
committee => 'travel'
);

my @uses_equals =
qw (member_id last_name username business_name
mailing_city mailing_state mailing_zip
physical_city physical_state physical_zip
business_type membership_type duration
expiration chapter chapter_leader listed_directory
enhanced paid
);
my @uses_like =
qw (dob member_since);
my @almost_like =
qw (description committee);
my @wheres;

foreach my $item (@uses_equals) {
push (@wheres, "$item = '$big_hash{$item}'") if ($big_hash{$item} ne '');
}
foreach my $item (@uses_like) {
push (@wheres, "$item like '$big_hash{$item}'") if ($big_hash{$item} ne '');
}
foreach my $item (@almost_like) {
push (@wheres, "$item like '\%$big_hash{$item}\%'") if ($big_hash{$item} ne '');
}

my $SQL = "select * from members\n where " . join ("\n and ", @wheres);

print $SQL, "\n\n";

Rather than have 23 separate variables I have one hash (%big_hash) that has all 23 values in it. For testing I just put 4 values in the hash, but I expect that this would be reading from a form and you can fill the hash at least as easily as you were filling the 23 separate variables.

You have three types of where clauses. Most of them are the simple kind with an equal sign and the names fo those columns are in the @usees_equals array. The others are in the other two arrays. By using qw we don't have to put quotes around each word so the list is much easier to make.

In my first version I used map to get the where clauses for each group, making one long list to feed to join. This time around I have removed the maps and replaced them with three separate foreach loops to make it easier to see what is going on. I also added $item rather than $_ for readability. So this time we get the @wheres array with each where clause that should be included and then send it on to join.

I have also switched from the


Code
(some conditional here) ? do the true thing : do the false thing

statements to the more straight forward


Code
do the true thing if (some conditional here)

And finally we build $SQL by starting with the stuff up to 'where' and then add each individual where clause which we have placed in the @wheres array. The individual wheres are joined together so that an 'and' will be between each, and not in front of the first one. The extra new lines are there to make the output easier for me to read.

So this version should be easier to understand, but it does introduce four arrays and $item that were not in the original.

--
Sun Sep 9, 2001 - 1:46:40 GMT, a very special second in the epoch. How will you celebrate?


freddo
User

May 24, 2001, 2:08 PM

Post #9 of 21 (4506 views)
Re: Perl/mysql script [In reply to] Can't Post

Hi rGeoffrey,

heh, i finally understand what the use i can have with join, that's very smart i would have never thought of that before.

btw, i really prefer your second solution, i didnt even dared to look the first one in detail yet, it looked so scaring. But i will do right now, i bet i have more to learn there... (and in many other things Smile)

freddo

;---
Real programmers´butcher dont understand when they just ask for 3735928559.


manunkind
Novice

May 24, 2001, 2:41 PM

Post #10 of 21 (4501 views)
Re: Perl/mysql script [In reply to] Can't Post

Hi again guys,

Well, freddo's code worked, but it won't pick up any of the wilcard (%) searches. If it uses all the straight (=) searches, it works perfectly. For example: searching for dob of "%%%%-06-%%" will not work.

So, I guess I need to use the other code with all the hashes and stuff??

Here's what I have now: (thanks freddo)

#!/usr/bin/perl

use CGI;
use DBI;


$query = new CGI;

$member_id = $query->param('member_id');
$last_name = $query->param('last_name');
$dob = $query->param('dob');
$username = $query->param('username');
$business_name = $query->param('business_name');
$mailing_city = $query->param('mailing_city');
$mailing_state = $query->param('mailing_state');
$mailing_zip = $query->param('mailing_zip');
$physical_city = $query->param('physical_city');
$physical_state = $query->param('physical_state');
$physical_zip = $query->param('physical_zip');
$business_type = $query->param('business_type');
$description = $query->param('description');
$membership_type = $query->param('membership_type');
$member_since = $query->param('member_since');
$duration = $query->param('duration');
$expiration = $query->param('expiration');
$chapter = $query->param('chapter');
$chapter_leader = $query->param('chapter_leader');
$committees = $query->param('committees');
$listed_directory = $query->param('listed_directory');
$enhanced = $query->param('enhanced');
$paid = $query->param('paid');


$SQL = "select * from members where 1 ";
@checks = ( member_id, last_name, dob, username, business_name, mailing_city,
mailing_state, mailing_zip, physical_city, physical_state, physical_zip, business_type,
description, membership_type, member_since, duration, expiration, chapter, chapter_leader,
committees, listed_directory, enhanced, paid );

for (@checks) {
$params .= "and $_ = '$$_' " if ($$_ ne "");
}

$SQL .= ( $params ? $params.";" : "and 0;" );

$database = "members";
$driver = "DBI:mysql";

my $dbh = DBI->connect("$driver:$database", "<username>", "<password>")
or die "Can not connect to the database";

my $sth = $dbh->prepare($SQL);
$sth->execute;

print "Content-type: text/html\n\n";

print <<HTML;

<html><head><title>Testing</title></head>
<body><center><b>Your search has produced the following results:</b></center>
<p>
<table cellspacing="3" width="4500">
<tr>
<td><b>Member ID<b></td>
<td><b>First Name<b></td>
<td><b>Last Name<b></td>
<td><b>Title<b></td>
<td><b>Email Address<b></td>
<td><b>Date of Birth<b></td>
<td><b>Username<b></td>
<td><b>Password<b></td>
<td><b>Business Name<b></td>
<td><b>Mailing Street<b></td>
<td><b>Mailing City<b></td>
<td><b>Mailing State<b></td>
<td><b>Mailing Zip<b></td>
<td><b>Physical Street<b></td>
<td><b>Physical City<b></td>
<td><b>Physical State<b></td>
<td><b>Physical Zip<b></td>
<td><b>Phone Number<b></td>
<td><b>Fax Number<b></td>
<td><b>URL<b></td>
<td><b>Employees<b></td>
<td><b>Business Type<b></td>
<td><b>Business Description<b></td>
<td><b>Membership Type<b></td>
<td><b>Dues<b></td>
<td><b>Member Since:<b></td>
<td><b>Duration<b></td>
<td><b>Expiration Date<b></td>
<td><b>Chapter<b></td>
<td><b>Chapter Leader<b></td>
<td><b>Listed in Directory?<b></td>
<td><b>Enhanced Listing?<b></td>
<td><b>Committees<b></td>
<td><b>Paid?<b></td>
</tr>

HTML
my $ref;

while($ref = $sth->fetchrow_hashref()){

print <<EndHTML2;
<tr>
<td>$ref->{'member_id'}</td>
<td>$ref->{'first_name'}</td>
<td>$ref->{'last_name'}</td>
<td>$ref->{'title'}</td>
<td>$ref->{'email'}</td>
<td>$ref->{'dob'}</td>
<td>$ref->{'username'}</td>
<td>$ref->{'password'}</td>
<td>$ref->{'business_name'}</td>
<td>$ref->{'mailing_street'}</td>
<td>$ref->{'mailing_city'}</td>
<td>$ref->{'mailing_state'}</td>
<td>$ref->{'mailing_zip'}</td>
<td>$ref->{'physical_street'}</td>
<td>$ref->{'physical_city'}</td>
<td>$ref->{'physical_state'}</td>
<td>$ref->{'physical_zip'}</td>
<td>$ref->{'phone'}</td>
<td>$ref->{'fax'}</td>
<td>$ref->{'url'}</td>
<td>$ref->{'employees'}</td>
<td>$ref->{'business_type'}</td>
<td>$ref->{'description'}</td>
<td>$ref->{'membership_type'}</td>
<td>$ref->{'dues'}</td>
<td>$ref->{'member_since'}</td>
<td>$ref->{'duration'}</td>
<td>$ref->{'expiration'}</td>
<td>$ref->{'chapter'}</td>
<td>$ref->{'chapter_leader'}</td>
<td>$ref->{'listed_directory'}</td>
<td>$ref->{'enhanced'}</td>
<td>$ref->{'committees'}</td>
<td>$ref->{'paid'}</td>
</tr>
EndHTML2

}

print qq(</table></body></html>);

$dbh->disconnect();

So, I printed out the other code by rGeoffrey and will try to make that work.

I'll let you know how it comes out.

Thanks to both of you!!!



freddo
User

May 24, 2001, 5:15 PM

Post #11 of 21 (4497 views)
Re: Perl/mysql script [In reply to] Can't Post

Hey Manukind,

You're welcome, and I'm very happy to know someone appreciate what i do (had a really bad day, today) Smile.

freddo

;---
Real programmers´butcher dont understand when they just ask for 3735928559.


manunkind
Novice

May 24, 2001, 6:36 PM

Post #12 of 21 (4495 views)
Re: Perl/mysql script [In reply to] Can't Post

No problem freddo!

I know what it's like to do this for no money and to just enjoy helping people. I'm a moderator at one help board and a member at alot.

Thanks again!



manunkind
Novice

May 24, 2001, 6:46 PM

Post #13 of 21 (4495 views)
Re: Perl/mysql script [In reply to] Can't Post

Hi rGeoffrey,

Well, I typed it in and it wouldn't pick up anything at all. All the searches I tried came up blank, even single ones. The only thing that I might have screwed up was the "%big_hash" part. The rest you had typed out as an example for me, but the big-hash one you had input in there so there was no template for me to go by. So, I just typed it how I thought it would go. Did I screw it up?? I checked for syntax 2 or 3 times, but I can't figure out why it picks up nothing now. Here's the new code for you to see what I did wrong. :)

#!/usr/bin/perl

use CGI;
use DBI;

$query = new CGI;

$member_id = $query->param('member_id');
$last_name = $query->param('last_name');
$dob = $query->param('dob');
$username = $query->param('username');
$business_name = $query->param('business_name');
$mailing_city = $query->param('mailing_city');
$mailing_state = $query->param('mailing_state');
$mailing_zip = $query->param('mailing_zip');
$physical_city = $query->param('physical_city');
$physical_state = $query->param('physical_state');
$physical_zip = $query->param('physical_zip');
$business_type = $query->param('business_type');
$description = $query->param('description');
$membership_type = $query->param('membership_type');
$member_since = $query->param('member_since');
$duration = $query->param('duration');
$expiration = $query->param('expiration');
$chapter = $query->param('chapter');
$chapter_leader = $query->param('chapter_leader');
$committees = $query->param('committees');
$listed_directory = $query->param('listed_directory');
$enhanced = $query->param('enhanced');
$paid = $query->param('paid');

my %big_hash = (member_id, last_name, dob, username, business_name, mailing_city,
mailing_state, mailing_zip, physical_city, physical_state, physical_zip,
business_type, description, membership_type, member_since, duration, expiration,
chapter, chapter_leader, committees, listed_directory, enhanced, paid);

my @uses_equals = qw (member_id last_name username business_name mailing_city
mailing_state mailing_zip physical_city physical_state physical_zip
business_type membership_type duration chapter chapter_leader listed_directory
enhanced paid);

my @uses_like = qw (dob member_since expiration);
my @almost_like = qw (description committees);
my @wheres;

foreach my $item (@uses_equals) {
push (@wheres, "$item = '$big_hash{$item}'") if ($big_hash{$item} ne '');
}
foreach my $item (@uses_like) {
push (@wheres, "$item like '$big_hash{$item}'") if ($big_hash{$item} ne '');
}
foreach my $item (@almost_like) {
push (@wheres, "$item like '\%$big_hash{$item}\%'") if ($big_hash{$item} ne '');
}

$SQL = "select * from members\n where " . join ("\n and ", @wheres);


$database = "members";
$driver = "DBI:mysql";

my $dbh = DBI->connect("$driver:$database", "<username>", "<password>")
or die "Can't connect to the database";

my $sth = $dbh->prepare($SQL);
$sth->execute;




freddo
User

May 24, 2001, 7:25 PM

Post #14 of 21 (4491 views)
Re: Perl/mysql script [In reply to] Can't Post

Hi again Manunkind,

here's my try at your last question, i bet there will be some typos or bugs, but it´s 4:30am here, and i dont have apache, nor mysql around. So if it cores too much, tell me and i´ll get a look at it at home this w-e.

Code
#!/usr/bin/perl 
use CGI;
use DBI;

# next come an array of hashes, the order have its importance for the display
# the hashes value are the following:
# field: the name of the field used in both the form, and in you db
# name: the english name for that field
# check: the type of check you wish for that field, empty means no check
# show: show the field in the result table ( 1/0 = yes/no )
@db_infos = (
{ field => "member_id", name => "Member ID", check => '=', show => 0 },
{ field => "first_name", name => "First Name", check => '', show => 1 },
{ field => "last_name", name => "Last Name", check => '=', show => 1 },
{ field => "title", name => "Title", check => '', show => 0 },
{ field => "email", name => "Email Address", check => '', show => 0 },
{ field => "dob", name => "Date of Birth", check => '=', show => 0 },
{ field => "username", name => "Username", check => '=', show => 1 },
{ field => "password", name => "Password", check => '', show => 0 },
{ field => "business_name", name => "Business Name", check => '=', show => 0 },
{ field => "mailing_street", name => "Mailing Street", check => '', show => 1 },
{ field => "mailing_city", name => "Mailing City", check => '=', show => 1 },
{ field => "mailing_state", name => "Mailing State", check => '=', show => 1 },
{ field => "mailing_zip", name => "Mailing Zip", check => '=', show => 1 },
{ field => "physical_street", name => "Physical Street", check => '', show => 0 },
{ field => "physical_city", name => "Physical City", check => '=', show => 0 },
{ field => "physical_state", name => "Physical State", check => '=', show => 0 },
{ field => "physical_zip", name => "Physical Zip", check => '=', show => 0 },
{ field => "phone", name => "Phone Number", check => '', show => 0 },
{ field => "fax", name => "Fax Number", check => '', show => 0 },
{ field => "url", name => "URL", check => '', show => 0 },
{ field => "employees", name => "Employees", check => '', show => 0 },
{ field => "business_type", name => "Business Type", check => '=', show => 0 },
{ field => "description", name => "Business Description", check => '=', show => 0 },
{ field => "membership_type", name => "Membership Type", check => '=', show => 0 },
{ field => "dues", name => "Dues", check => '', show => 0 },
{ field => "member_since", name => "Member Since", check => '=', show => 0 },
{ field => "duration", name => "Duration", check => '=', show => 0 },
{ field => "expiration", name => "Expiration Date", check => '=', show => 0 },
{ field => "chapter", name => "Chapter", check => '=', show => 0 },
{ field => "chapter_leader", name => "Chapter Leader", check => '=', show => 0 },
{ field => "listed_directory", name => "Listed in Directory?", check => '=', show => 0 },
{ field => "enhanced", name => "Enhanced Listing?", check => '=', show => 0 },
{ field => "committees", name => "Committees", check => '=', show => 0 },
{ field => "paid", name => "Paid?", check => '=', show => 0}
);

# Creating the SQL statements:
for (@db_infos) {
$check = $query->param($_->{field});
push (@params, " ", $_->{field}, " ", $_->{check}, " '$check' ")
if ( ($_->{check}) && ($check ne "") );
}
$SQL = "select * from members\n where ".join(" and ", @params);

# Fetching the results from mysql
$dbh = DBI->connect("DBI:mysql:members", "<username>", "<password>")
or die "Can not connect to the database";
$sth = $dbh->prepare($SQL);
$sth->execute;
$dbh->disconnect(); # i dont think fetchrow_hashref() needs a connection

# print the http headers, top of page, and the table results
print <<HTML;
Content-type: text/html

<html><head><title>Testing</title></head>
<body><center><b>Your search has produced the following results:</b></center>
<p>
<table cellspacing="3" width="4500">
HTML

# Print the headers on a row
print "<tr>";
print "<td><b>", $_->{name}, "</b></td>" for (@db_infos);
print "</tr>";

# Print found records, only the fields that we specified "show-able"
while($ref = $sth->fetchrow_hashref()){
print "<tr>"; # new row
print ($_->{show}?"<td>". $ref{$_->{field}} ."</td>":"") for (@db_infos);
print "</tr>"; # next row
}

# End of table, and page
print "</table></body></html>";

# This is the
__END__
my only friend, the
__END__



You may want to have a look to perldoc perldsc, the Perl Data Structures Cookbook.

Note that for convenience i also attached the script to the message.

I hope this helps,
freddo

;---
Real programmers´butcher dont understand when they just ask for 3735928559.


freddo
User

May 24, 2001, 7:28 PM

Post #15 of 21 (4491 views)
Re: Perl/mysql script [In reply to] Can't Post

Btw, if you need some explainations (if that thing works), i´ll try to clarify the whole script, but for now, i´m off to bed.

´night
freddo

;---
Real programmers´butcher dont understand when they just ask for 3735928559.


ygjohn
Deleted

May 24, 2001, 11:27 PM

Post #16 of 21 (4488 views)
Re: Perl/mysql script [In reply to] Can't Post

WOW.. im impressed by you and the people here!!...
very patient and helpful..
i like it..

there is a god!! =)


###
When i died, I'll go to heaven, coz i spent my life in hell.
###


freddo
User

May 25, 2001, 1:33 AM

Post #17 of 21 (4486 views)
Re: Perl/mysql script [In reply to] Can't Post

That's the perlguru's way... Cool

;---
Real programmers´butcher dont understand when they just ask for 3735928559.


freddo
User

May 25, 2001, 2:01 AM

Post #18 of 21 (4482 views)
Re: Perl/mysql script [In reply to] Can't Post

I got it (thanks to perl.com's recipe of the day),


Code
$ perl -MTime::Local -e 'print timegm(40, 46, 1, 9, 9 - 1, 2001 - 1900);' 
1000000000

freddo Smile

;---
Real programmers´butcher dont understand when they just ask for 3735928559.


rGeoffrey
User / Moderator

May 25, 2001, 9:03 AM

Post #19 of 21 (4479 views)
Re: Perl/mysql script [In reply to] Can't Post

You are right, it is broken filling %big_hash. Try this...


Code
use CGI; 
my $query = new CGI;
my %big_hash = map {$_ => $query->param($_)} $query->param();

which is about the same as doing...


Code
use CGI; 
my $query = new CGI;
my %big_hash;
foreach my $key ($query->param()) {
$big_hash{$key} = $query->param($key);
}

For more explanation look to this thread from the past. And then you don' t need to fill the 23 separate variables and can save 22 lines of code.

While we are here let us consider what was actually happening as %big_hash was being filled. It was written as...


Code
my %big_hash = (member_id, last_name, dob, username, business_name, mailing_city,  
mailing_state, mailing_zip, physical_city, physical_state, physical_zip,
business_type, description, membership_type, member_since, duration, expiration,
chapter, chapter_leader, committees, listed_directory, enhanced, paid);

If we change the spacing we see that this is the same as...


Code
my %big_hash = (member_id => 'last_name', 
dob => 'username',
business_name => 'mailing_city',
mailing_state => 'mailing_zip',
physical_city => 'physical_state',
physical_zip => 'business_type',
description => 'membership_type',
member_since => 'duration',
expiration => 'chapter',
chapter_leader => 'committees',
listed_directory => 'enhanced',
paid);

So notice that you set all the odd items as keys and the following even items as their values, except for the last item ('paid') that has no value. Remember hashes are not quite arrays and they need an even number of things so they get key => value pairs. The '=>' sybmol is just a fancy comma. By using it we don't have to put quotes around thing just before it, in this case the key, so it is easier to type (and in my editor the keys are in a different color from the strings used for the values).

On the topic of creating the HTML to show your answers you can take the 92 lines with two very large repetitive here documents and reduce it to this...


Code
print <<HTML; 
Content-type: text/html

<html><head><title>Testing</title></head>
<body><center><b>Your search has produced the following results:</b></center>
<p>
<table cellspacing="3" width="4500">
<tr>
HTML

print map {"<th>$_</th>\n"} ('Member ID', 'First Name', 'Last Name',
'Title', 'Email Address', 'Date of Birth',
'Username', 'Password', 'Business Name',
'Mailing Street', 'Mailing City', 'Mailing State',
'Mailing Zip', 'Physical Street', 'Physical City',
'Physical State', 'Physical Zip', 'Phone Number',
'Fax Number', 'URL', 'Employees', 'Business Type',
'Business Description', 'Membership Type', 'Dues',
'Member Since:', 'Duration', 'Expiration Date',
'Chapter', 'Chapter Leader', 'Listed in Directory?',
'Enhanced Listing?', 'Committees', 'Paid?');
print "</tr>\n\n";

my $ref;
while ($ref = $sth->fetchrow_hashref()) {
print "<tr>\n";
print map {"<td>$ref->{$_}</td>\n"} qw (member_id first_name last_name title
email dob username password business_name
mailing_street mailing_city mailing_state
mailing_zip physical_street physical_city
physical_state physical_zip phone fax url
employees business_type description
membership_type dues member_since duration
expiration chapter chapter_leader
listed_directory enhanced committees paid);
print "</tr>\n\n";
}

print "</table></body></html>\n";

I changed your header row to use TH tags because that does bolding automatically. And then for each $ref returned by the database we print an opening TR, then loop through the keys and for each one print its value inside TD tags, and finally print the closing TR. You could feed that last map a combination of @uses_equals, @uses_like, and @almost_like but unfortunately they would not be in the right order.

This will print a table with all 23 columns. If you want to leave some of them out you just need to remove them from the list fed to the two maps in the printing.


--
Sun Sep 9, 2001 - 1:46:40 GMT, a very special second in the epoch. How will you celebrate?


manunkind
Novice

May 25, 2001, 2:06 PM

Post #20 of 21 (4476 views)
Re: Perl/mysql script [In reply to] Can't Post

Thanks again! I printed it out and will get something working this weekend. I'm determined now! :)





manunkind
Novice

May 27, 2001, 4:57 PM

Post #21 of 21 (4463 views)
Re: Perl/mysql script [In reply to] Can't Post

rGeoffrey,

Worked like a charm this time!

Thank you very much!!!


 
 


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

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