I crave the benevolence of some wisdom as I'm trying to write a script which updates a database and creates a fake user address using (I hoped) random numbers and the auto-incremented db index at a faked subdomain (in case the same random number ever does appear twice). I've run into two problems.
Firstly, I'm trying to check if an address exists and if it does go onto the next email to check that but my code is currently stopping the entire process and exiting out of the loop if it comes across a duplicate at next.
Secondly, I've not been able to get the code to call the correct item from the index to insert it into string I'm creating. Is there a better way of coding some sort less random sub string (like a timestamp) which can at least be unique?

use strict;
use warnings;

use File::Find;
use DBI;

my $admin = 'admin';

my $dbh = DBI->connect("dbi:mysql:list:localhost", "user", "pword") or
+ die "Connection Error: $DBI::errstr\n";;

my $sthcheck;
my $sthcaluser;
my $sth;
my $sthuser;
my ($email, $password, $caluser, $opt_out, $founduser);


my $dir = "file";

find (\&wanted, $dir);

sub wanted {
   my @useroutput;
    
   open (IN, "$_") or die "Can't open $_";
   @useroutput = <IN>;
   close(IN);
   chomp @useroutput;
    
   foreach my $person (@useroutput) {
      my ($email, $password) = split(/:/, $person);
      #need to check if user email exists. If not then add
      
      if (!defined $sthcheck) {
         $sthcheck = $dbh->prepare_cached("SELECT name FROM user WHERE
+ name = ?") or die "Couldn't prepare statement: " . $dbh->errstr;
      }
      $sthcheck->execute($email);
      ($founduser) = $sthcheck->fetchrow_array();
      
      next $person unless !$founduser;
      
      if ($password eq "no_password" ) {
         $password =~ s/no_password//;
        }
      
      #get the last id 
      my $db_id = $sth->{'mysql_insertid'};
      if (!$db_id) {
          $db_id = 0;
      }
      
      my $caluser = create_random() . "-" . $db_id . "\@foo.bar.com";
      my $opt_out="F";
         
      if (!defined $sth) {
     $sth = $dbh->prepare_cached("INSERT INTO user(name, pass, cal_nam
+e, opt_out) VALUES (?,?,?,?)") or die "Couldn't prepare statement: " 
+. $dbh->errstr;
     }
      $sth->execute($email, $password, $caluser, $opt_out);
   
         #insert user role into db
      my $sthadmin = $dbh->prepare ("INSERT INTO user_role(name, role)
+ VALUES (?,\"admin\")") or die "Couldn't prepare statement: " . $dbh-
+>errstr;
      if (!defined $sthuser) {
     $sthuser = $dbh->prepare_cached("INSERT INTO user_role(name, role
+) VALUES (?,\"user\")") or die "Couldn't prepare statement: " . $dbh-
+>errstr;
     }
      #if section to place helpline as the site admin
      if (grep $email eq $_, $admin ) {
     $sthadmin->execute($email);
     } else {
     $sthuser->execute($email);
     }
     }
}

sub create_random {
   my @chars = ( "A" .. "Z", "a" .. "z", 0 .. 9, qw(! @ $ % ^ & *) );
   my $user_name = join("", @chars[ map { rand @chars } ( 1 .. 10 ) ])
+;
   return $user_name;
}

$sth->finish();
$dbh->disconnect();
[download]

I tried using use diagnostics but the line it gave was the $sth->execute which is referenced at find (\&wanted, $dir); but I'm fairly sure the error is in my trying to call $db_id as when I tried $db_id=$dbh->last_insert_id($catalog, $schema, $table, $field) it returned Mo&72sEbUI-0@foo.bar.com and the 0 was repeated after the - for all the emails. I'd be grateful for some advice on sorting this script out. Thanks.

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.