Perl to store value to MySQL
Hey I have this Perl .cgi script. When it runs, you have to enter some information, and it then calculates your total energy expenditure (TEE) minus 20%. What I would like to do is to get that piece of information into a row together with the username of the one (Table: ett, rows: $HB_minus_20 (that is what it's called in the script, and username), who have signed on, so I will be able to read that information later without them having to enter it into that cgi script everytime they enter my homepage.
How would I do that? I am not good at Perl-scripting at all yet though.
I have 2 fields in my database-table, one called "kcal" and the other called "username"
I already have DBI and DBI::MySQL installed!
I assume it would look something like this:
"use strict;
use DBI;
my $dbh = DBI->connect("DBI:mysql:da
"dbusername", "dbpassword", {'RaiseError' => 1}
);
my $sth = $dbh->prepare("INSERT INTO tee; kcal, username VALUES ?,?");
$sth->execute(1, $HB_minus_20, $session_['username']);"
Please help me!! Thanks :)
How would I do that? I am not good at Perl-scripting at all yet though.
I have 2 fields in my database-table, one called "kcal" and the other called "username"
I already have DBI and DBI::MySQL installed!
I assume it would look something like this:
"use strict;
use DBI;
my $dbh = DBI->connect("DBI:mysql:da
"dbusername", "dbpassword", {'RaiseError' => 1}
);
my $sth = $dbh->prepare("INSERT INTO tee; kcal, username VALUES ?,?");
$sth->execute(1, $HB_minus_20, $session_['username']);"
Please help me!! Thanks :)
I'm not a perl Guru, but I don't believe you need a prepare for an insert statement
Try:
my $userName = $session_['username'];
$dbh->do("Insert into tee (kcal, username ) values('$HB_minus_20', '$userName' ");
and see if that does what you are looking for. You should be able to do it without setting the extra variavble, but for readability in the example I added it in there.
Try:
my $userName = $session_['username'];
$dbh->do("Insert into tee (kcal, username ) values('$HB_minus_20', '$userName' ");
and see if that does what you are looking for. You should be able to do it without setting the extra variavble, but for readability in the example I added it in there.
ASKER
Ahh thank you! Where would I place it in my script? At line 309?
ASKER
By the way, it would also need to connect to my MySQL database. How would I do that in Perl?
In PHP I do it like this
session_start();
include ('config.php');
error_reporting(E_ERROR | E_WARNING | E_PARSE | E_NOTICE);
$connection = mysql_connect($server, $dbusername, $dbpassword) or die(mysql_error());
$db = mysql_select_db($db_name,$
But I don't know much about Perl sadly :( Could you please help me with that? :) Thanks!
In PHP I do it like this
session_start();
include ('config.php');
error_reporting(E_ERROR | E_WARNING | E_PARSE | E_NOTICE);
$connection = mysql_connect($server, $dbusername, $dbpassword) or die(mysql_error());
$db = mysql_select_db($db_name,$
But I don't know much about Perl sadly :( Could you please help me with that? :) Thanks!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I couldn't find my other post again at all, so I thought it had been deleted! But thanks! I found it! I just read through your links, and tried to make it connect to my database
my $dsn = 'dbi:mysql:MY DB NAME:localhost:3306';
my $user = 'MY DB USER';
my $pass = 'MY DB PASS';
my $dbh = DBI->connect($dsn, $user, $pass)
or die "Cant connect to the DB: $DBI::errstr\n";
and placed it right below "#!/usr/bin/perl", but it gives me an 500 Error. I also remembered to upload it in Ascii and chmod 755
my $dsn = 'dbi:mysql:MY DB NAME:localhost:3306';
my $user = 'MY DB USER';
my $pass = 'MY DB PASS';
my $dbh = DBI->connect($dsn, $user, $pass)
or die "Cant connect to the DB: $DBI::errstr\n";
and placed it right below "#!/usr/bin/perl", but it gives me an 500 Error. I also remembered to upload it in Ascii and chmod 755
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Oh I see, thanks!
Well it gives me this error:
"Can't locate object method "connect" via package "DBI" at metabocalc.cgi line 16."
#!/usr/bin/perl
use CGI::Carp 'fatalsToBrowser';
# set the data source name
# format: dbi:db type:db name:host:port
# mysqls default port is 3306
# if you are running mysql on another host or port,
# you must change it
my $dsn = 'dbi:mysql:MYDBNAME:localh
# set the user and password
my $user = 'MYDBUSER';
my $pass = 'MYDBPASS';
# now connect and get a database handle
my $dbh = DBI->connect($dsn, $user, $pass) //THIS IS LINE 16
or die "Cant connect to the DB: $DBI::errstr\n";
Is it written wrong? Thanks for the help
Well it gives me this error:
"Can't locate object method "connect" via package "DBI" at metabocalc.cgi line 16."
#!/usr/bin/perl
use CGI::Carp 'fatalsToBrowser';
# set the data source name
# format: dbi:db type:db name:host:port
# mysqls default port is 3306
# if you are running mysql on another host or port,
# you must change it
my $dsn = 'dbi:mysql:MYDBNAME:localh
# set the user and password
my $user = 'MYDBUSER';
my $pass = 'MYDBPASS';
# now connect and get a database handle
my $dbh = DBI->connect($dsn, $user, $pass) //THIS IS LINE 16
or die "Cant connect to the DB: $DBI::errstr\n";
Is it written wrong? Thanks for the help
You didn't load the DBI module. Add this after line 2 (after the "use CGI::Carp" line):
use DBI;
use DBI;
ASKER
Ahh yes that was correct. The page loads now and seems to work great, except the part where it should store the $HB_minus_20 in the database, once you click "Submit."
Instead, my page looks shows this at the bottom
"Your daily kcal:(?)
Minus 20%: 2011
my = ; my DBI::db=HASH(0x7e3870) = DBI->connect("DBI:mysql:da
I think the code here should be enclosed in () or [] or something. I am not sure how it that would work in Perl :)
Instead, my page looks shows this at the bottom
"Your daily kcal:(?)
Minus 20%: 2011
my = ; my DBI::db=HASH(0x7e3870) = DBI->connect("DBI:mysql:da
I think the code here should be enclosed in () or [] or something. I am not sure how it that would work in Perl :)
...
<tr>
<td>Minus 20%:</td><td>$HB_minus_20</td>
</tr>
</table>
my $userName = $session_['username'];
my $dbh = DBI->connect("DBI:mysql:databasename;localhost",
"dbusername", "dbpassword", {'RaiseError' => 1}
);
my $sth = $dbh->prepare('INSERT INTO tee (kcal, username) VALUES (?, ?)');
$sth->execute($HB_minus_20, $userName);
$dbh->disconnect();
];
...
You should use one of the HTML template module to make the handling of HTML and perl code easier: HTML::Template or Template Toolkit.
But continuing this way, the new code should have been added after the closing square bracket on line 310.
But continuing this way, the new code should have been added after the closing square bracket on line 310.
ASKER
Thanks! It seems we are getting pretty close!
It gives me this error now;
"Software error:
DBD::mysql::st execute failed: Column 'username' cannot be null at metabocalc.cgi line 515."
I think what it needs is to know what user is signed on. On my other pages, I have a login-system, where it redirects you to a login-page if you have not signed on. Fx my index top looks like this
<?php
//prevents caching
header("Expires: Sat, 01 Jan 2000 00:00:00 GMT");
header("Last-Modified: ".gmdate("D, d M Y H:i:s")." GMT");
header("Cache-Control: post-check=0, pre-check=0",false);
session_cache_limiter();
session_start();
require('config.php');
require('functions.php');
//this is group name or username of the group or person that you wish to allow access to
// - please be advise that the Administrators Groups has access to all pages.
if (allow_access(Users) != "yes")
{
include ('no_access.html');
exit;
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<link rel="shortcut icon" href="favicon.ico">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
But I can't just add that PHP-code to my .cgi script, can I?
It gives me this error now;
"Software error:
DBD::mysql::st execute failed: Column 'username' cannot be null at metabocalc.cgi line 515."
I think what it needs is to know what user is signed on. On my other pages, I have a login-system, where it redirects you to a login-page if you have not signed on. Fx my index top looks like this
<?php
//prevents caching
header("Expires: Sat, 01 Jan 2000 00:00:00 GMT");
header("Last-Modified: ".gmdate("D, d M Y H:i:s")." GMT");
header("Cache-Control: post-check=0, pre-check=0",false);
session_cache_limiter();
session_start();
require('config.php');
require('functions.php');
//this is group name or username of the group or person that you wish to allow access to
// - please be advise that the Administrators Groups has access to all pages.
if (allow_access(Users) != "yes")
{
include ('no_access.html');
exit;
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<link rel="shortcut icon" href="favicon.ico">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
But I can't just add that PHP-code to my .cgi script, can I?
Do,you can't just add php code, it won't work. I don't work with php, so don't know much about it.
The PHP::Session module might help:
http://search.cpan.org/~miyagawa/PHP-Session-0.27/lib/PHP/Session.pm
The PHP::Session module might help:
http://search.cpan.org/~miyagawa/PHP-Session-0.27/lib/PHP/Session.pm
ASKER
Oh I see! Thanks for your help, everyone! I think I got the help I needed! Have a nice day!
ASKER
It is at line 298, that $HB_minus_20 is, and at line 306 it gets printed on the page.
I can elaborate, if you do not get what it is I'm saying :)
Open in new window