Greetings, Monks. I'm trying to create a script to insert new customer information into a mySQL database and at the same time generate a new unique and random customer number. My difficulty is in trying to figure out how to: 1) Check if the customer number really is unique and 2) Try a different number (the original number + 1 is what I've tried) and keep on trying until I hit upon one. Is there a good tutorial out there that anyone's seen on how to do this? I've been all over Google and not come up with a shoe that fits. I'll also post what I've tried so far in case I'm just making a simple mistake that can be fixed. The relevant areas are marked with a lot of ##s. I'm still learning, be gentle :-) Cheers, K. 
#!C:\Perl\bin\perl.exe -wT
##
use strict;
use warnings;
use CGI;
use CGI::Carp qw/fatalsToBrowser/;
use File::Basename;
use DBI;

my $driver = 'mysql';
my $database = 'mydatabase';
my $dsn = "DBI:$driver:database=$database";
my $userid = 'foo';
my $password = 'bar';
my $dbh = DBI->connect($dsn, $userid, $password )||die $DBI::errstr;

my $query = CGI->new;


######################################################
######################################################
#########This is where I generate the start number##########
my $basisNumber = int(rand(999));
my $customerNumber = $basisNumber + 2000;


########################################################
########################################################
#########Here I get ready to check if it's in the db########
my $customerCheck = $dbh->prepare("SELECT CustomerNumber FROM Customer
+List");
$customerCheck->execute();

my(@customerMatrix)=();
while(my @customerArray = $customerCheck->fetchrow_array())
  {
  push(@customerMatrix, [@customerArray]);
  }
$customerCheck->finish();


#####################################################
#####################################################
###Here I get ready to do the check in a subroutine#######
my $checkResult = checkValue(@customerMatrix, $customerNumber);
####Here I check it, and increment it if it's not unique, then check a
+gain#########
while($checkResult==1)
{
$customerNumber ++;
return $checkResult;
}

my $companyName = $query->param('cName');
my $billingStreetAddress = $query->param('bStreetAddress');
my $billingCity = $query->param('bCity');
my $billingState = $query->param('bState');
my $billingZip = $query->param('bZip');
my $billingPhone = $query->param('bPhone');
my $billingFax = $query->param('bFax');
my $shippingStreetAddress = $query->param('sStreetAddress');
my $shippingCity = $query->param('sCity');
my $shippingState = $query->param('sState');
my $shippingZip = $query->param('sZip');
my $shippingPhone = $query->param('sPhone');
my $shippingFax = $query->param('sFax');
my $email = $query->param('email');
my $contactPerson = $query->param('cPerson');


my $newCustomer = $dbh->prepare("INSERT INTO CustomerList (CustomerNum
+ber, CompanyName, BillingStreetAddress, BillingCity, BillingState, Bi
+llingZip, BillingPhone, BillingFax, ShippingStreetAddress, ShippingCi
+ty, ShippingState, ShippingZip, ShippingPhone, ShippingFax, Email, Co
+ntactPerson) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
+);
$newCustomer->execute($customerNumber, $companyName, $billingStreetAdd
+ress, $billingCity, $billingState, $billingZip, $billingPhone, $billi
+ngFax, $shippingStreetAddress, $shippingCity, $shippingState, $shippi
+ngZip, $shippingPhone, $shippingFax, $email, $contactPerson);
$newCustomer->finish();
$dbh->commit;

print $query->redirect('http://localhost/mysite.com/thankYou.html');


####################################################
####################################################
########This is the subroutine that does the check######
 sub checkValue
  {
  for(@customerMatrix)
    {
    return 1 if $_ eq $customerNumber;
    }
  return 0;
  }

[download]

In reply to DBI Insert Unique Customer ID by kalchas

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • 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! —
  • 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:
    & &amp;
    < &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.