[newbie] dbi question

[tsheets]

I am new to perl, and expecially dbi, but am trying to figure the best
way to accomplish the following:

I have an HTML form, that lets the user select which fields to include
in the results. Also, a text box for the where clause.

for example, the table contains firstname, lastname, phone, address, and
zip.

Lets say you only want firstname, lastname, and zip (checkboxes), where
lastname = smith.

So far, I have been able to figure out which boxes are checked, and
store that in a variable to pass to the select statement, and build the
resulting HTML table with the correct headings. But, I am not sure how
to go about processing only the fields selected with the data returned
from the query.

I have read several examples, and the perldoc, but about the closest
thing I have understood is getting the entire row, and only printing out
the interesting fields. Is this my best bet, or am I just not
understanding something (like bind_columns maybe)? The fact that any
number or combination of boxes can be checked really has me stumped.

I would love to post some code to clarify, but I haven't gotten far
enough to even do that!!

Also, if there's a more appropriate group, feel free to point me in the
right direction.

Thanks for your help.

Tim

 

[Keith Keller]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

for example, the table contains firstname, lastname, phone, address, and
zip.

Lets say you only want firstname, lastname, and zip (checkboxes), where
lastname = smith.

So far, I have been able to figure out which boxes are checked, and
store that in a variable to pass to the select statement, and build the
resulting HTML table with the correct headings. But, I am not sure how
to go about processing only the fields selected with the data returned
from the query.

I have read several examples, and the perldoc, but about the closest
thing I have understood is getting the entire row, and only printing out
the interesting fields. Is this my best bet, or am I just not
understanding something (like bind_columns maybe)? The fact that any
number or combination of boxes can be checked really has me stumped.

I would love to post some code to clarify, but I haven't gotten far
enough to even do that!!

Umm, you must have *some* sort of code if you've figured out the
above.

Also, if there's a more appropriate group, feel free to point me in the
right direction.

There's a dbi mailing list, which IIRC you can subscribe at
dbi.perl.org. To very quickly answer your question, though,
you'd probably like to construct your select statement to select
only the rows that the user selects. To continue your example,
your select might be something like

SELECT firstname,lastname,zip FROM tablename WHERE lastname='Smith'

But this is really an SQL question, and not too appropriate for
clpm. The DBI list might be more appropriate--it's not really
about SQL either, but they can help you decide if there's a more
appropriate way to extract the data you want using Perl.

- --keith

- --
(e-mail address removed)-francisco.ca.us
(try just my userid to email me)
AOLSFAQ=http://wombat.san-francisco.ca.us/cgi-bin/fom

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iEYEARECAAYFAj9YAMsACgkQhVcNCxZ5ID9yHwCfV80Nn6NtEdMSu3HIap/GHO9f
zg0AmgJh2t0o9VzyVf9Dufl95iLVFH8R
=P9ki
-----END PGP SIGNATURE-----

 

[John Bokma]

I am new to perl, and expecially dbi, but am trying to figure the best
way to accomplish the following:

I have an HTML form, that lets the user select which fields to include
in the results. Also, a text box for the where clause.

for example, the table contains firstname, lastname, phone, address, and
zip.

Lets say you only want firstname, lastname, and zip (checkboxes), where
lastname = smith.

So far, I have been able to figure out which boxes are checked, and
store that in a variable to pass to the select statement, and build the

Be *extremely* careful with building a select statement from a form.
Never ever trust what a user enters. Check each field and make sure it
matches *only* characters that are allowed. Don't try to fix silently
but report an error (for example if you expect a number make sure it is
a number, don't throw away non-digits but warn if you get anything that
is not a digit, see also URL in my sig).

resulting HTML table with the correct headings. But, I am not sure how
to go about processing only the fields selected with the data returned
from the query.

I have read several examples, and the perldoc, but about the closest
thing I have understood is getting the entire row, and only printing out

you can do:

SELECT Name, Address, Phone FROM table WHERE Name='John';

this gives all rows with Name John and only the columns Name, Address
and Phone. So you can specify which columns you want.

the interesting fields. Is this my best bet, or am I just not
understanding something (like bind_columns maybe)? The fact that any
number or combination of boxes can be checked really has me stumped.

You could do something like:

@cols = ();
@checkboxes = qw(name phone address);

foreach checkbox (@checkboxes)
push(@cols, checkbox) if checked(checkbox);
}

query = "select " . join(", ", @cols) . " from table ...";

(pseudo perl)

Note that I specify the column names. Don't just get all checkboxes and
build the query based on the names (e.g. someone could create a checkbox
with a weird name which could change the query in a bad way).

HTH,

 

[tsheets]

tsheets wrote:

Be *extremely* careful with building a select statement from a form.
Never ever trust what a user enters. Check each field and make sure it
matches *only* characters that are allowed. Don't try to fix silently
but report an error (for example if you expect a number make sure it is
a number, don't throw away non-digits but warn if you get anything that
is not a digit, see also URL in my sig).

Thanks for the advise. I haven't yet taken steps to validate the data,
but, at this point, I am going for basic function and am the only one
with access to the system. I will definitely add that in.

you can do:

SELECT Name, Address, Phone FROM table WHERE Name='John';

this gives all rows with Name John and only the columns Name, Address
and Phone. So you can specify which columns you want.

Actually, that is the part I wasn't quite getting. I thought the DBI
fetchrow function was grabbing the entire row, not just the columns I
specified in the select statement.

You could do something like:

@cols = ();
@checkboxes = qw(name phone address);

foreach checkbox (@checkboxes)
push(@cols, checkbox) if checked(checkbox);
}

query = "select " . join(", ", @cols) . " from table ...";

(pseudo perl)

I'll have to play with that. It's much more elegant than what I was
doing. I was checking the status of each checkbox and concatonating the
ones that were "true" and doing some clean-up, etc. ex...
$selectFields .= ",Name" if(param("selName"));
$selectFields .= ",Phone" if(param("selPhone"));

Then clean off the leading ',' and split on the remaining ',' and stuff
it into an array for later processing!

Like I said, your method is much more elegant.

Ahhh well, Live and Learn, right??

Thanks for the help!

Tim