ericlee has asked for the wisdom of the Perl Monks concerning the following question:

I've created a new MySQL database, with a new table in it, and an HTML form to add records - and then I copied code I've used before to insert the records (using DBI) and it doesn't work. No records are added. And I'm getting no error message about connection problems. And I've filled in every field in the test. Here's the relevant Perl code:

use DBI;
$dbh = DBI->connect('dbi:mysql:***:db371055412.db.1and1.com','***','**
+*') or die("Cannot connect to database.");
$sth = $dbh->prepare('insert into istanbul2011 (name, country, languag
+e, union, title, email, cellphone, landlinephone, facebook, skype, tw
+itter, gender, age, needshelp) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)')
+;
$sth->execute($name, $country, $language, $union, $title, $email, $cel
+lphone, $landlinephone, $facebook, $skype, $twitter, $gender, $age, $
+needshelp);
$dbh->disconnect();

[download]

Help!

Thanks.

Replies are listed 'Best First'.
Re: DBI Insert not working
by moritz (Cardinal) on Jun 09, 2011 at 11:24 UTC
[reply]
[d/l]
[select]
      This made no difference.
[reply]

        This is really unusal. If an insert fails, and you don't get an error in the presence of RaiseError => 1, it means that either one of the components on your system is seriously busted (your installation of mysql, DBD::mysql, DBI or perl), or that you're not actually running the code you're showing.

        It could be that parts of the code you haven't shown either prevent the insert statement from being executed, or something is catching exceptions or something like that.

        So please provide a short, stand-alone script that exhibts your problem that we can actually run (and enter our own mysql account data, of course).

        Perl 6 - second systems done right
[reply]
Re: DBI Insert not working
by roboticus (Chancellor) on Jun 09, 2011 at 11:46 UTC

    ericlee:

    You're talking about HTML forms, but nothing like that is in your code. Did you try the stripped down version you've shown, or did you just extract it from a CGI program. If the latter, then try making a standalone version work first.

    ...roboticus

    When your only tool is a hammer, all problems look like your thumb.

[reply]
Re: DBI Insert not working
by Anonymous Monk on Jun 09, 2011 at 12:46 UTC

    Are you sure you haven't turned off AutoCommit (although I believe your disconnect would issue a warning). Are you using warnings?

    Are you sure you are looking in the right schema for the inserted row.

    If the row is unique, what happens if you try and select it back after the insert with the primary key?

[reply]
Re: DBI Insert not working
by wind (Priest) on Jun 10, 2011 at 02:00 UTC

    This does not directly relate to your issue. However, whenever I have a query like that, I often prefer to build the sql statement instead of having a chain of placeholders that might or might not be balanced with the correct field.

    To ensure better readability and quality assurance, I simply create an intermediate data structure that is transformed into the fields and values like so:

    my @pairs = (
    name          => $name,
    country       => $country,
    language      => $language,
    union         => $union,
    title         => $title,
    email         => $email,
    cellphone     => $cellphone,
    landlinephone => $landlinephone,
    facebook      => $facebook,
    skype         => $skype,
    twitter       => $twitter,
    gender        => $gender,
    age           => $age,
    needshelp     => $needshelp,
);

my @fields = do {my $b = 0; grep {$b ^= 1} @pairs};
my @values = do {my $b = 1; grep {$b ^= 1} @pairs};

$sth = $dbh->prepare('INSERT INTO istanbul2011 SET ' . join ',', map {
+"$_=?"} @fields));
$sth->execute(@values) or die $dbh->errstr

    [download]
[reply]
[d/l]
Some progress - and some more information
by ericlee (Novice) on Jun 13, 2011 at 14:23 UTC

    OK, I've gotten the script to add the first 3 fields out of 14. But it will not add the 4th field, or any others. I created a simple HTML page to display the form, here: http://www.labourstart.org/2011/register.html.

    The first 3 fields can be added to the database. But not the fourth (union). If you fill in the form, you'll see a screen showing what you've put into the fields. I receive an email with all the data you've input. So I think that's all fine. But the MySQL database won't update beyond the first 3 fields. Here's the structure of the database (from MyPHPadmin):

          Field     Type    Collation    Attributes    Null    Default    
+Extra    Action
     name     varchar(128)    latin1_german2_ci         No            
+                             
     country     varchar(128)    latin1_german2_ci         No         
+                                
     language     varchar(128)    latin1_german2_ci         No        
+                                 
     union     varchar(128)    latin1_german2_ci         No           
+                              
     title     varchar(128)    latin1_german2_ci         Yes     NULL 
+                                   
     email     varchar(128)    latin1_german2_ci         No           
+                              
     cellphone     varchar(128)    latin1_german2_ci         Yes     N
+ULL                                    
     landlinephone     varchar(128)    latin1_german2_ci         Yes  
+   NULL                                    
     facebook     varchar(128)    latin1_german2_ci         Yes     NU
+LL                                    
     skype     varchar(128)    latin1_german2_ci         Yes     NULL 
+                                   
     twitter     varchar(128)    latin1_german2_ci         Yes     NUL
+L                                    
     gender     varchar(16)    latin1_german2_ci         No           
+                              
     age     varchar(64)    latin1_german2_ci         No              
+                           
     needshelp     varchar(16)    latin1_german2_ci         No

    [download]
    And here's the entire CGI script: 
    #!/usr/bin/perl

print "Content-type: text/html\n\n";

use CGI qw(param);

$name = param("pname");
$country = param("pcountry");
$language = param("planguage");
$union = param("punion");
$title = param("ptitle");
$email = param("pemail");
$cellphone = param("pcellphone");
$landlinephone = param("plandlinephone");
$facebook = param("pfacebook");
$skype = param("pskype");
$twitter = param("ptwitter");
$gender = param("pgender");
$age = param("page");
$needshelp = param("pneedshelp");

# 1 Adds record to database of registrants

use DBI;
$db = DBI->connect('******','**********','******');

my $sth = $db->prepare("insert into istanbul2011(name, country, langua
+ge, union, title, email, cellphone, landlinephone, facebook, skype, t
+witter, gender, age, needshelp) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)")
+;

$sth->execute($name, $country, $language, $union, $title, $email, $cel
+lphone, $landlinephone, $facebook, $skype, $twitter, $gender, $age, $
+needshelp);

$db->disconnect();

# 2 Sends an email confirmation message to us

$to = "ericlee\@labourstart.org";
$from = "ericlee\@labourstart.org";
$content1 = "Name: $name\nCountry: $country\nEmail: $email\nUnion: $un
+ion\nTitle: $title\nLanguage: $language\nCell phone: $cellphone\nLand
+line phone: $landlinephone\nFacebook: $facebook\nSkype: $skype\nTwitt
+er: $twitter\nGender: $gender\nAge: $age\nNeeds help: $needshelp";
   
open(MAIL, "|/usr/sbin/sendmail -t");    
print MAIL "To: $to\nFrom: $email\n";
print MAIL "Subject: $name from $country has registered to attend the 
+LabourStart conference\n";
print MAIL "The following application form has been submitted:\n\n$con
+tent1\n";
close (MAIL);   
  
# 3 Sends an email confirmation message to the registrant
   
$content2 = "Dear $name \n\nThank you for registering to attend the La
+bourStart conference this year.\n\nFor full information about the con
+ference, please make sure to visit http://www.labourstart.org/2011.\n
+\n\We'll be writing to you again closer to the date of the event.\n\n
+Looking forward to seeing you there.\n\nEric Lee\n";
   
open(MAIL, "|/usr/sbin/sendmail -t");    
print MAIL "To: $email \nFrom: $from\n";
print MAIL "Subject: Thank you for registering to attend the LabourSta
+rt conference\n";
print MAIL "$content2\n";

close (MAIL);

# 4 Posts a thank you message on the screen, also showing details subm
+itted

print qq|
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"> 
<title>Thank you!</title> 
</head>
<body>
<h1>The following information has been added to our database of confer
+ence registrants:</h1>
<p>Name: $name</p>
<p>Country: $country</p>
<p>Language: $language</p>
<p>Union: $union</p>
<p>Title: $title</p>
<p>Email: $email</p>
<p>Cellphone: $cellphone</p>
<p>Landlinephone: $landlinephone</p>
<p>Facebook: $facebook</p>
<p>Skype: $skype</p>
<p>Twitter: $twitter</p>
<p>Gender: $gender</p>
<p>Age: $age</p>
<p>Needshelp: $needshelp</p>
<hr />
<h2>You have been sent a confirmation email message.<br /><a href="htt
+p://www.labourstart.org/2011">Click here to return to our conference 
+website</a></h2>
</body> 
</html>
|;

    [download]

    Any ideas? Help!

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom