Retrieving the Insert ID

In Chapter 6, I cover the LAST_INSERT_ID() function, which is a MySQL-specific tool for retrieving the value inserted into an auto-incremented field. In Chapter 7, "MySQL and PHP," I demonstrate PHP's mysql_insert_id() function, which serves the same purpose. With Perl, you would use a construct like this:

$sth = $dbh->do ("INSERT INTO tablename (table_id, column) VALUES (NULL, 'value")
");
$insert_id = $dbh->{'mysql_insertid'}

With this in mind, I'll create a new script, similar to add_user.pl, that lets a user create an invoice and reports the new invoice number back to them.

To retrieve an insert ID:

Create a new Perl script in your text editor with the standard beginning lines of code (Script 8.5).

#!/usr/bin/perl -w
use strict;
use DBI;

Print some introductory text.

print "Use this program to add an invoice.\n\n";
print "Client Name (Client ID):\n";

Establish a connection to the database.

[View full width]my $dbh = DBI->connect("DBI:mysql: accounting:localhost", 'username', 'password',
 {RaiseError => 1});

Script 8.5. This script retrieves the last automatically incremented value from the `invoices` table.

1 #!/usr/bin/perl -w 2 3 # Script 8.5 - add_invoice.pl 4 # This script adds an invoice to the invoices table. 5 # It relies upon user input. 6 7 # Use what needs to be used. 8 use strict; 9 use DBI; 10 11 # Print a message. 12 print "Use this program to add an invoice.\n\n"; 13 print "Client Name (Client ID):\n"; 14 15 # Connect to the database. 16 my $dbh = DBI->connect("DBI:mysql: accounting:localhost", 'username', 'password', {RaiseError => 1}); 17 18 # Show the current clients with their IDs. 19 my $sql = "SELECT client_name, client_id FROM clients ORDER BY client_name ASC"; 20 21 # Query the database. 22 my $sth = $dbh->prepare($sql); 23 24 if (defined($sth)) { 25 $sth->execute(); 26 my @row; 27 while (@row = $sth->fetchrow_array()) { 28 print "$row[0] ($row[1])\n"; 29 } 30 } 31 $sth->finish(); 32 33 # Get the information from the user. 34 print "Enter the client ID: "; 35 my $client_id = <STDIN>; 36 print "Enter the invoice amount: "; 37 my $amount = <STDIN>; 38 print "Enter the invoice description: "; 39 my $desc = <STDIN>; 40 41 # Query the database. 42 $sql = "INSERT INTO invoices (client_id, invoice_amount, invoice_description) VALUES ($client_id, $amount, '$desc')"; 43 my $affected= $dbh->do($sql); 44 45 # Report on the success of the query attempt. 46 if ($affected== 1) { 47 print "Invoice #" . $dbh-> {'mysql_insertid'} . " has been created.\n"; 48 } else { 49 print "The invoice could not be created! \n"; 50 } 51 52 # Disconnect. 53 $dbh->disconnect;

List the current clients with their client IDs.

my $sql = "SELECT client_name, client_id FROM clients ORDER BY client_name ASC";
my $sth = $dbh->prepare($sql);
if (defined($sth)) {
    $sth->execute();
    my @row;
    while (@row = $sth-> fetchrow_array()) {
    print "$row[0] ($row[1])\n";
  }
}
$sth->finish();

The code to this point is exactly like that in add_user.pl.

Prompt for the client's ID, the invoice amount, and the invoice description.

print "Enter the client ID: ";
my $client_id = <STDIN>;
print "Enter the invoice amount: ";
my $amount = <STDIN>;
print "Enter the invoice description: ";
my $desc = <STDIN>;

This script will prompt for three things (Figure 8.24): the client's ID, the invoice amount, and the invoice description. These correspond to the fields in the invoices table. Along with these fields, the table also has an invoice ID, which will be automatically generated, an invoice date, which will be automatically set as the current date (because it's a TIMESTAMP type), and the date_invoice_paid date.

Figure 8.24. The script requests three pieces of information from the user.

Create the INSERT query and send it to the database.

[View full width]$sql = "INSERT INTO invoices (client_id, invoice_amount, invoice_description) VALUES 
($client_id, $amount, '$desc')";
my $affected= $dbh->do($sql);

Nothing new here. You could do some validation on the submitted data before using it in a query, though.

Print a message indicating the success of the query.

if ($affected== 1) {
    print "Invoice #" .$dbh->{'mysql_insertid'} . " has been created.\n";
} else {
    print "The invoice could not be created! \n";
}

Now, if the invoice is created in the database, the invoice ID is returned by calling

$dbh->{'mysql_insertid'}.

Close the database connection.

$dbh->disconnect;

Save the file as add_invoice.pl, change the permissions (if necessary), and run the script (Figure 8.25).

Figure 8.25. The invoice number is revealed to the user.

Tips

Another option for obtaining the last MySQL insert ID is to use the format $query->{'insertid'}. Or you could run a SELECT LAST_INSERT_ID() query and fetch the results.
You may want to add code that chops the extra spaces off of the user-submitted invoice description. Otherwise, the newline character that's appended to the end of each string will also be stored in the database.