Keith, I can't tell much from what I'm seeing that should cause a problem. I
might suggest a couple of steps for tracking it down, then I'll offer you a
bit of advice on safely inserting data that in taken from user input.
First, you have said that the print from the year prints the date in the
format that MySQL wants it, have you tried printing the entire query,
copying that to your query tool, and seeing if MySQL takes the input as the
query is produced? That might at least tell us if the problem is with how
the data is being seen by MySQL, or if it is elsewhere, and it will take all
the "should's" out of the troubleshooting that part.
When you are satisfied that the data can be inserted (maybe even before),
you need to be careful with the way you are handling the insert query -
especially if users are allowed to enter data. What if a user inputs a value
with an apostrophe? It will cause a failure the way you are doing this. To
safely handle user input, to be able to prepare a query and execute several
times with different data, or other reasons, you need to use placeholders
instead of putting your variables into your query. And one more thing, you
need to capture an error if it is produced - just like you have with
connection. To make your error messages more meaningful without the extra
typing, turn your entire query into a variable before you prepare. I would
handle your insert query like this:
</snip>
my $insert = qq{"INSERT INTO News
(ntitle, ndate, nurl, nblurb, nsource)
VALUES(?, ?, ?, ?, ?)};
my $inserth = $dbh->prepare($insert) || die "Can't
prepare\n$insert\n$DBI::errstr\n";
$inserth->execute($ntitle, $ndate, $nurl, $nblurb, $nsource) || die
"Can't execute\n$insert\nWith values:\n$ntitle, $ndate, $nurl, $nblurb,
$nsource
$DBI::errstr\n";
</snip>
Putting the error capturing like I have done will also tell us if there is a
problem inserting, if so exactly what the error is - no guesswork - and you
know what data you're dealing with.
As you see, the placeholders are not quoted when you prepare, and they are
arguments to $inserth->execute() in the same order as the query expects
them.
Hopefully with all that, you can narrow the error search and get to where
the problem is, and can write that so that a user can't screw you up by
putting in a value that will cause problems with the query.
If you get an error message, and want to go further, I'll be happy to see
what we can do.
Steve H.
-----Original Message-----
From: Keith A. Calaman [mailto:[EMAIL PROTECTED]]
Sent: Saturday, February 02, 2002 10:11 PM
To: [EMAIL PROTECTED]
Subject: pop-up box JOIN() to mysql not populating dates
I am resending this to the group because I haven't gotten a response nor
have I got it to work. I won't send it again but I thought someone who
could help may have missed it. I have pasted my code below. The problem I
am having is:
my $ndate = join('-', $nyear,$nmonth,$nday);
is printing back to my form the exact format that should be correct to
insert into mysql. However, when the INSERT runs the value does not get
inserted properly. $nyear,$nmonth,$nday are grabbed from values a user
selects via three pop-up select boxes. Anyone have any ideas? Also, I am
new to this so your comments on my code are welcomed and appreciated.
Thanks in advance.
#!/usr/bin/perl -w
use strict;
use diagnostics;
print "Content-Type: text/html\n\n";
use CGI;
use DBI;
#declare form variables
my $query = CGI->new();
my $ntitle = $query->param('ntitle');
my $nyear = $query->param('year');
my $nmonth = $query->param('month');
my $nday = $query->param('day');
my $nurl = $query->param('nurl');
my $nblurb = $query->param('nblurb');
my $nsource = $query->param('nsource');
my $tracker = $query->param('tracker');
my $hostname = 'www.host.com';
my $database = 'sd';
my $user = 'usr';
my $password = 'guess';
my $driver = 'mysql';
#convert form variables
my $ndate = join('-', $nyear,$nmonth,$nday);
#connect to database
my $dsn = "DBI:$driver:database=$database;host=$hostname";
my $dbh = DBI->connect($dsn, $user, $password) or die "Cant connect to
the DB:
$DBI::errstr\n";
#determine what to do
if ($tracker eq 0){
print <<EndOfHTML;
<html>
<body>
<FORM METHOD=POST ACTION=" ">
<INPUT TYPE="hidden" NAME="tracktype" VALUE="2">
<INPUT TYPE="hidden" NAME="tracker" VALUE="1">
<INPUT TYPE="hidden" NAME="ntitle" VALUE="$ntitle">
<INPUT TYPE="hidden" NAME="ndate" VALUE="$ndate">
<INPUT TYPE="hidden" NAME="nurl" VALUE="$nurl">
<INPUT TYPE="hidden" NAME="nblurb" VALUE="$nblurb">
<INPUT TYPE="hidden" NAME="nsource" VALUE="$nsource">
Title: $ntitle<br>
Date: $ndate<br>
URL: $nurl<br>
Blurb: $nblurb<br>
Source: $nsource<br>
<P><INPUT TYPE=submit>
</body>
</html>
EndOfHTML
}elsif($tracker eq 1){
my $sth = $dbh->prepare("INSERT INTO News
(ntitle, ndate, nurl, nblurb, nsource)
VALUES('$ntitle', '$ndate', '$nurl', '$nblurb', '$nsource')");
$sth->execute();
my $new = $dbh->prepare("select N_ID, NDate, NSource, NTitle, NBlurb, NURL
from News WHERE NApprove=0");
$new->execute();
my $news;
while (my @row = $new->fetchrow_array()) {
$news .= qq| <value="$row[0]"> $row[1] <i>$row[2]</i><br>
<a href="$row[5]"><b>"$row[3]"</b></a><br>$row[4]..<br><br>\n|;
}
print <<EndOfHTML;
<html>
<body>
$news
</body>
</html>
EndOfHTML
}elsif($tracker eq 2){
print("there!\n");
}else{
print("failure!\n");
}
$dbh->disconnect;
exit;
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
