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: Beginner:
How to implement a checkbox that will send data to db

 

First page Previous page 1 2 Next page Last page  View All


terrykhatri531
User

Jul 9, 2014, 9:53 AM

Post #1 of 40 (2092 views)
How to implement a checkbox that will send data to db Can't Post

Hi,

Now I have a situation where I have to use a check box.

The db table column has a Boolean data type i.e. True or False


Code
  
Column | Type | Modifiers
----------------+--------------------+--------------------------------------

Discontinued | boolean | default false



select distinct("Discontinued") from "Products";
Discontinued
--------------
f
t
(2 rows)


Will appreciate if you Gurus help me implement it in my ADD and EDIT forms :


Code
  
my @options = ("True", "False");

<form action="" method="post">

Discontinued :<input type="checkbox" name="discontinued" value=$options><br>

</form>








If its checked it should send a TRUE value to the database else False.


I did try to implement it using a list box :


Code
# get Discontinued 
my $sql = qq!SELECT DISTINCT("Discontinued") AS disc
FROM "Products" ORDER BY 1 DESC !;
my $dr = $dbh->selectall_arrayref($sql);

# Make up a pulldown menu for Discontinued
my $discs = qq!<option value="">Select option</option>!;
for my $row (@$dr) {
$discs .= qq!<option value="$row->[0]">$row->[0]</option>\n!;
}


Code
 
Discontinued :<select name="discontinued"> $discs </select><br/>


But if I select 1 i.e. True the update goes through and if select 0 i.e. False it sends a null value which is throwing the following error :


Code
DBD::Pg::db do failed: ERROR:  invalid input syntax for type boolean: "" at /usr/share/perlproj/cgi-bin/editprod.pl line 44


Any I idea why it will send 1 but not 0 ? Any suggestions would adding some regex validation would help ?

Many thanks in advance

Rgds

Terry

Please Note : I will post it to PerlMonks as well.


(This post was edited by terrykhatri531 on Jul 9, 2014, 11:51 AM)


Zhris
Enthusiast

Jul 9, 2014, 3:42 PM

Post #2 of 40 (2059 views)
Re: [terrykhatri531] How to implement a checkbox that will send data to db [In reply to] Can't Post

Hi Terry,


Quote
invalid input syntax for type boolean: ""


As far as I can tell, the actual value you are attempting to update with is blank as indicated by the error message. You haven't shown us the correct portions of code to aid you in discovering why.

If possible, show us the HTML source of the update form, and separately the section of code that reads the parameter and updates the database.

Regards,

Chris


(This post was edited by Zhris on Jul 9, 2014, 3:52 PM)


terrykhatri531
User

Jul 9, 2014, 6:20 PM

Post #3 of 40 (1957 views)
Re: [Zhris] How to implement a checkbox that will send data to db [In reply to] Can't Post

Hi Chris,

Here is the HTML/Form part:


Code
 
<form action="" method="post">
Employee ID : $hr->{'ProductID'}<br/>
<input type="hidden" name="prodid" value="$hr->{'ProductID'}"/>
Product Name :<input name="productname" value="$hr->{'ProductName'}"/><br/>
Supplier Name :<select name="suppliername"> $supps </select><br/>
Category Name :<select name="categoryname"> $cats </select><br/>
Quntity Per Unit :<input name="quantityperunit" value="$hr->{'QuantityPerUnit'}"/><br/>
Unit Price :<input style=text-align:"right" name="unitprice" value="$hr->{'UnitPrice'}"/><br/>
Units In Stock :<input name="unitsinstock" value="$hr->{'UnitsInStock'}"/><br/>
Units On Order :<input name="unitsonorder" value="$hr->{'UnitsOnOrder'}"/><br/>
Reorder Level :<input name="reorderlevel" value="$hr->{'ReorderLevel'}"/><br/>
Discontinued :<select name="discontinued"> $discs </select><br/>
<input type="submit" name="go" value="UPDATE"/>
</form>




Here is the UPDATE action code :


Code
 
if ( ($action eq "UPDATE") && ($prodid =~ /\d+/)) {

my @data=();
my @fields = qw!productname suppliername categoryname quantityperunit
unitprice unitsinstock unitsonorder reorderlevel discontinued !;
for my $f (@fields){
push @data,$q->param($f) || '' ;
}
push @data,$prodid;

my $sql = qq!UPDATE "Products"
SET "ProductName" = ?,
"SupplierID" = ?,
"CategoryID" = ?,
"QuantityPerUnit" = ?,
"UnitPrice" = ?,
"UnitsInStock" = ?,
"UnitsOnOrder" = ?,
"ReorderLevel" = ?,
"Discontinued" = ?
WHERE "ProductID" = ? !;

my $cnt = $dbh->do( $sql,undef,@data);
$msg = "$cnt Record updated - $sql, @data";
} else {
$msg = "Please complete form";
}




A PerlMonk commented that "Handling a checkbox with CGI is as easy as the following:"


Code
 
my $boolean = $cgi->param('boolean') || 0;

# or better:

my $boolean = $cgi->param('boolean') ? 1 : 0;

# etc




How can I implement it in my form, I would like to use checkbox for a change :)

Many thanks for your help.

Rgds

Terry


Zhris
Enthusiast

Jul 9, 2014, 6:39 PM

Post #4 of 40 (1940 views)
Re: [terrykhatri531] How to implement a checkbox that will send data to db [In reply to] Can't Post

Hi Terry,

I ideally need the compiled HTML source not the HTML template. I want to see what the discontinued select menu looks like you are posting (with values). You can retrieve this by right clicking the web page and clicking view source. In your current code, if the value of discontinued is 0 (false), then it will be translated to blank '' via the code $q->param($f) || '', which is possibly your problem, although your code in your original post suggests otherwise.

Here is how you could roughly implement the checkbox, but you should really consider reorganizing your code:


Code
<input type="checkbox" name="discountinued" value="1" /> Discontinued?<br />



Code
for my $f (@fields){  
if ( $f eq 'discontinued' ) {
push @data, $q->param($f) ? 1 : 0; # or possibly 't' : 'f';
}
else {
push @data, $q->param($f) || '';
}
}


Regards,

Chris


(This post was edited by Zhris on Jul 9, 2014, 7:03 PM)


terrykhatri531
User

Jul 9, 2014, 7:11 PM

Post #5 of 40 (1906 views)
Re: [Zhris] How to implement a checkbox that will send data to db [In reply to] Can't Post

Hi Chris,

Sorry about that here is the html source :


Code
 
</select><br/>
Quntity Per Unit :<input name="quantityperunit" value="10,000"/><br/>
Unit Price :<input style=text-align:"right" name="unitprice" value="10.0000"/><br/>
Units In Stock :<input name="unitsinstock" value="100"/><br/>
Units On Order :<input name="unitsonorder" value="100"/><br/>
Reorder Level :<input name="reorderlevel" value="100"/><br/>
Discontinued :<select name="discontinued"> <option value="">Select option</option><option value="1">1</option>
<option value="0">0</option>
</select><br/>
<input type="submit" name="go" value="UPDATE"/>
</form></div><hr/>




Thanks Chris for your help.

Rgds

Terry


Zhris
Enthusiast

Jul 9, 2014, 7:15 PM

Post #6 of 40 (1899 views)
Re: [terrykhatri531] How to implement a checkbox that will send data to db [In reply to] Can't Post


Quote
<option value="0">0</option>


As suspected, you are using 0 for the false value, which is later translated to blank ''.

Implement the code I suggested in my previous post, with the current drop down menu or desired checkbox (code will work for both).

Regards,

Chris


(This post was edited by Zhris on Jul 9, 2014, 7:18 PM)


terrykhatri531
User

Jul 9, 2014, 7:55 PM

Post #7 of 40 (1883 views)
Re: [Zhris] How to implement a checkbox that will send data to db [In reply to] Can't Post

Hi Chris,

I fixed this problem from the database side by getting the results as 'f' and 't' instead of 0 and 1.


Code
 
# get Discontinued
my $sql = qq!SELECT DISTINCT("Discontinued")::CHAR AS disc
FROM "Products" ORDER BY 1 DESC !;




Now I will try to implement checkbox lets see how it goes.

Rgs

Terry


Zhris
Enthusiast

Jul 9, 2014, 8:09 PM

Post #8 of 40 (1872 views)
Re: [terrykhatri531] How to implement a checkbox that will send data to db [In reply to] Can't Post

Hi Terry,

You shouldn't even have to run that query, you already know there are just two choices, true or false.

Its purely personal preference, but I would stick to using 0 and 1 as values, they are much easier to handle in Perl since they represent false and true, whereas the strings 'f' and 't' both represent true. This will be especially helpful when it comes to validating the user supplied input i.e. you should never directly store the user supplied value in the database when dealing with booleans.

When you come to implementing a single checkbox, remember if its checked, it will supply the value to the script, otherwise it will supply nothing (undefined), you'll need to handle this appropriately regardless of what values you choose to represent true and false.

Regards,

Chris


(This post was edited by Zhris on Jul 9, 2014, 8:19 PM)


terrykhatri531
User

Jul 9, 2014, 11:00 PM

Post #9 of 40 (1748 views)
Re: [Zhris] How to implement a checkbox that will send data to db [In reply to] Can't Post


In Reply To
Hi Terry,

You shouldn't even have to run that query, you already know there are just two choices, true or false.

Chris















Right how about this :


Code
 
Discontinued :<select name="discontinued">
<option value="False" selected>False</option>
<option value="True">True</option>
</select>


Code
 
Rgds

Terry


Zhris
Enthusiast

Jul 10, 2014, 4:47 AM

Post #10 of 40 (1556 views)
Re: [terrykhatri531] How to implement a checkbox that will send data to db [In reply to] Can't Post

Hi Terry,

When I say true or false I am speaking figuratively. Its entirely up to you what values you use as long as you handle them appropriately. But my choice would be to use 0 for false and 1 for true for the reasons above.

Examples of true values in Perl:

Code
true 
1
anystring
(1 or more spaces)

Examples of false values in Perl:

Code
0 
undef
(blank/nothing)


Regards,

Chris


(This post was edited by Zhris on Jul 10, 2014, 4:48 AM)


terrykhatri531
User

Jul 11, 2014, 3:40 AM

Post #11 of 40 (1525 views)
Re: [Zhris] How to implement a checkbox that will send data to db [In reply to] Can't Post

Hi Chris,

In my template I have the following line in TMPL_LOOP but when I click on "Details" button nothing happens :




Code
 
<td><input type="submit" name="go" value="Details" src="orddetails.pl?orderid<!-- TMPL_VAR NAME=OrderID -->" /></td>




Please help me on this.

Many thanks

Terry


Zhris
Enthusiast

Jul 11, 2014, 4:51 AM

Post #12 of 40 (1478 views)
Re: [terrykhatri531] How to implement a checkbox that will send data to db [In reply to] Can't Post

Hi Terry,

It looks like you have multiple buttons per page and have attempted to use the src attibute to handle each individually. The src attribute cannot be used in this way, it is used to specify the URL of an image to use as the button. Also, if you haven't wrapped your button in a form, then nothing will happen. Finally note that there is no = between orderid and your template param in the query string.

If I have assumed correctly, the following alternatives may suffice:

Use a link instead (best option):

Code
<td> 
<a href="orddetails.pl?go=Details&orderid=<!-- TMPL_VAR NAME=OrderID -->">Details</a>
</td>


Use multiple forms that post to the appropriate URL (update: I don't think this will actually work, I haven't tested but, I don't think you can mix GET and POST params together):

Code
<td> 
<form method="post" action="orddetails.pl?orderid=<!-- TMPL_VAR NAME=OrderID -->">
<input type="submit" name="go" value="Details" />
</form>
</td>

Similar to the above, but uses a hidden field for order id:

Code
<td> 
<form method="post" action="orddetails.pl">
<input type="hidden" name="orderid" value="<!-- TMPL_VAR NAME=OrderID -->" />
<input type="submit" name="go" value="Details" />
</form>
</td>


Use a single form but incorporate the order id into the name of the button, handling this in your script shouldn't be too tricky:

Code
<form method="post" action="orddetails.pl"> 
...
<td>
<input type="submit" name="go_<!-- TMPL_VAR NAME=OrderID -->" value="Details" />
</td>
....
</form>


Similar to the above, but incorporate into the value instead:

Code
<form method="post" action="orddetails.pl"> 
...
<td>
<input type="submit" name="go" value="Details for order <!-- TMPL_VAR NAME=OrderID -->" />
</td>
....
</form>


Regards,

Chris


(This post was edited by Zhris on Jul 11, 2014, 1:46 PM)


terrykhatri531
User

Jul 11, 2014, 7:29 AM

Post #13 of 40 (1370 views)
Re: [Zhris] How to implement a checkbox that will send data to db [In reply to] Can't Post

Thanks Chris that's great !!

I have another question, once I get the orderdetail.pl loaded how can I save the orderid so I can use it in my if loops for other actions within the same script, I seem to lose it when I call it in another if statements, I am already declaring the following in the beginning :


Code
 
# get form parameters
my $q = new CGI;
my $action = $q->param('go');
my $orderid = $q->param('orderid');


Many thanks

Rgds

Terry


terrykhatri531
User

Jul 11, 2014, 11:39 AM

Post #14 of 40 (1299 views)
Re: [terrykhatri531] How to implement a checkbox that will send data to db [In reply to] Can't Post

 Ok there was a problem with script and a monk at perlmonks helped me fixed that.

I have one more thing to implement that is pagination, I will get back to you on that if you can help !!!

Rgds

Terry


(This post was edited by terrykhatri531 on Jul 11, 2014, 1:15 PM)


Zhris
Enthusiast

Jul 11, 2014, 1:50 PM

Post #15 of 40 (1267 views)
Re: [terrykhatri531] How to implement a checkbox that will send data to db [In reply to] Can't Post

Hi Terry,

Glad you were able to fix your most recent issue, I haven't been as active on here today as I have the past few days.

For pagination, I advise you look into using Data::Page or something similar.

Also for the postgresql side of things, LIMIT and OFFSET can probably be used to actually select the range of necessary results.

Regards,

Chris


(This post was edited by Zhris on Jul 11, 2014, 1:51 PM)


terrykhatri531
User

Jul 14, 2014, 4:39 AM

Post #16 of 40 (1057 views)
Re: [Zhris] How to implement a checkbox that will send data to db [In reply to] Can't Post

Thank Chris for your hints in advance I have done the pagination but it looks ugly just the page numbers :) may be I should try to link it to css to make it look better.

Rgds

Terry


(This post was edited by terrykhatri531 on Jul 14, 2014, 6:23 AM)


Zhris
Enthusiast

Jul 14, 2014, 6:28 AM

Post #17 of 40 (974 views)
Re: [terrykhatri531] How to implement a checkbox that will send data to db [In reply to] Can't Post

Hi Terry,

(update: Looks like you edited your last post, which no longer describes a problem you were having, therefore below is possibly irrelevant).

I don't understand what you mean / what the problem is. Are the template variables ProductID and OrderID not being populated? Do you need to group these 2 together so you can have lots of products / orders on one page (use a TMPL_LOOP)? Please explain your problem in more detail.

Regards,

Chris


(This post was edited by Zhris on Jul 14, 2014, 6:49 AM)


terrykhatri531
User

Jul 14, 2014, 7:18 AM

Post #18 of 40 (936 views)
Re: [Zhris] How to implement a checkbox that will send data to db [In reply to] Can't Post

Hi Chris,

What I was trying to do is to pass 2 values to another script which I have done as under, that's solved :


Code
 
<td>
<a href="orddetails.pl?go=Details&prodid=<!-- TMPL_VAR NAME=ProductID -->&orderid=<!-- TMPL_VAR NAME=OrderID -->">Edit</a>
</td>


Now its sending 2 correct values to the other script as under :


Code


Many thanks

Terry


terrykhatri531
User

Jul 15, 2014, 5:04 AM

Post #19 of 40 (678 views)
Re: [terrykhatri531] How to implement a checkbox that will send data to db [In reply to] Can't Post

Hi Chris,

I have done pagination but I want to link the page numbers to css stylesheet so it may look pretty.

Here is my code that generates simple page number links :


Code
  
for (my $i=0;$i<$pagenum;$i++)
{
print "<a href='vieword2.pl?page=$i'>$i</a> ";
}


So as to link the numbers to css stylesheet I have changed the above code to the following but what is happening now is its creating page links far far below down the one page number to another in vertical order, in other words its not stacking up the page numbers side by side like 1 2 3 4 ... in horizontal order instead its doing it like this vertically:

1

then a big space gape far below down the page then next page number

2

and so on.


Code
  
for (my $i=0;$i<$pagenum;$i++)
{
print"<ul class=\"tsc_pagination tsc_paginationA tsc_paginationA08\">
<li><a href='vieword2.pl?page=$i'>$i</a></li></ul>";
}



The following dummy code generates the page numbers side by side


Code
  
print"<ul class=\"tsc_pagination tsc_paginationA tsc_paginationA08\">
<li><a href=\"#\">1</a></li>
<li><a href=\"#\">2</a></li>
<li><a href=\"#\" class=\"current\">3</a></li>
<li><a href=\"#\">4</a></li>
<li><a href=\"#\">5</a></li></ul>";


Since you are so experienced what do you think where the problem is.

Many many thanks is advance.

Rgds.

Terry






(This post was edited by terrykhatri531 on Jul 15, 2014, 5:25 AM)


FishMonger
Veteran / Moderator

Jul 15, 2014, 6:34 AM

Post #20 of 40 (671 views)
Re: [terrykhatri531] How to implement a checkbox that will send data to db [In reply to] Can't Post

The <ul> tag needs to be outside of the loop.


Code
print q(<ul class="tsc_pagination tsc_paginationA tsc_paginationA08">); 
for my $i (0 .. $pagenum)
{
print qq(<li><a href='vieword2.pl?page=$i'>$i</a></li>");
}
print q</ul>);



(This post was edited by FishMonger on Jul 15, 2014, 6:35 AM)


terrykhatri531
User

Jul 15, 2014, 7:07 AM

Post #21 of 40 (665 views)
Re: [FishMonger] How to implement a checkbox that will send data to db [In reply to] Can't Post

Thanks Ron,

That did the trick !
Terry


(This post was edited by terrykhatri531 on Jul 15, 2014, 7:13 AM)


terrykhatri531
User

Jul 15, 2014, 8:54 AM

Post #22 of 40 (658 views)
Re: [terrykhatri531] How to implement a checkbox that will send data to db [In reply to] Can't Post

 Hi Chris,

Any idea why HTML::Template does not read the css link :


Code
 
<head>
<!-- Pagination CSS -->
<link type="text/css" rel="stylesheet" href="/usr/share/perlproj/cgi-bin/css/pagination2.css" />
<title>Employees Listing</title>
</head>


The code I am trying with above css file is :


Code
 
<ul class="tsc_pagination tsc_paginationA tsc_paginationA08">
<li><a href="viewemp.pl">View Employees</a></li>
<li><a href="addemp.pl">Add Employee</a></li>
<li><a href="editemp.pl">Edit Employee</a></li>
<li><a href="updatephoto.pl">Update Photo</a></li>
</ul>


The output shows up as bulleted links instead of pretty hover over boxes which css file is supposed to do, it works in my other scripts which do not use HTML::Template.

Many thanks.

Terry


(This post was edited by terrykhatri531 on Jul 15, 2014, 11:27 PM)


terrykhatri531
User

Jul 15, 2014, 11:35 PM

Post #23 of 40 (480 views)
Re: [terrykhatri531] How to implement a checkbox that will send data to db [In reply to] Can't Post

The above lines are added in Template itself !


Zhris
Enthusiast

Jul 16, 2014, 12:43 AM

Post #24 of 40 (454 views)
Re: [terrykhatri531] How to implement a checkbox that will send data to db [In reply to] Can't Post

Hi,

I think you need to provide the URL not path to the stylesheet under the link tag / href attribute (client side doesn't have server side access).

Chris


terrykhatri531
User

Jul 16, 2014, 1:20 AM

Post #25 of 40 (440 views)
Re: [Zhris] How to implement a checkbox that will send data to db [In reply to] Can't Post

Great Chris, that did the job !!

Another question after showing output for UL stuff it puts so much gape/space down below before print anything else, any idea how I can reduce that space ?

Many thanks

Terry


(This post was edited by terrykhatri531 on Jul 16, 2014, 1:30 AM)

First page Previous page 1 2 Next page Last page  View All
 
 


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

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