Thanks for the response and sorry for the delay we have had utility problems. I did use your suggestions on the errors. I got it to work. Can you comment on the use of placeholders? It looks like the major benefit is portability, where I can put info from different forms into the same queries. I am just getting started with PERL and programming but I have learned a lot with the help of many sources including Mr. Howard and this group. I am going to sit down and rethink everything in terms of variable declarations which will streamline everything I think. How can I point to values in another file to be used as values in certain scripts...for example, the user and password information to be used to connect to the database. This way if I change my password in the database I only have to change it in one place instead of in all my scripts. This would be a neat tool to streamline everything! Thanks again.
-----Original Message----- From: Steve Howard [mailto:[EMAIL PROTECTED]] Sent: Sunday, February 03, 2002 12:41 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: pop-up box JOIN() to mysql not populating dates Correction to a syntax error I left. The insert query should be: my $insert = qq{INSERT INTO News (ntitle, ndate, nurl, nblurb, nsource) VALUES(?, ?, ?, ?, ?)}; I pasted yours in, and edited, and forgot to delete the first double quote. Just wanted to clear that up since I was commenting on your code :) Steve H. -----Original Message----- From: Steve Howard [mailto:[EMAIL PROTECTED]] Sent: Saturday, February 02, 2002 11:00 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: pop-up box JOIN() to mysql not populating dates 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] -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
