Inserting Data Into MySQL Database Using Perl

SET NAMES latin1;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE `address` (
  `name_first` varchar(30) NOT NULL,
  `name_last` varchar(30) NOT NULL,
  `address_01` varchar(40) NOT NULL,
  `address_02` varchar(40) NOT NULL,
  `address_city` varchar(30) NOT NULL,
  `address_state` varchar(20) NOT NULL,
  `address_postal_code` varchar(12) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

SET FOREIGN_KEY_CHECKS = 1;


We are only going to insert one address line with our data. You can modify this script to loop the process and insert multiple rows of data.





use DBI;
use DBD::mysql;

use warnings;

$database = "scripting_mysql";

$connection = ConnectToMySql($database);

$query = "insert into address (name_first, name_last, address_01, address_02, address_city, address_state, address_postal_code) 
			values (?, ?, ?, ?, ?, ?, ?) ";
$statement = $connection->prepare($query);

$statement->execute('John', 'Smith', '100 Main Street', 'Suite 500', 'Buffalo', 'NY', '14201');


$query2 = "select name_first, name_last, address_01, address_02, address_city, address_state, address_postal_code from address where name_last = 'Smith'";


$statement = $connection->prepare($query2);

$statement->execute();

# we will loop through the returned results that are in the @data array
# even though, for this example, we will only be returning one row of data

   while (@data = $statement->fetchrow_array()) {
      $name_first = $data[0];
      $name_last = $data[1];
      $address_01 = $data[2];
      $address_02 = $data[3];
      $address_city = $data[4];
      $address_state = $data[5];
      $address_postal_code = $data[6];

print "RESULTS - $name_first, $name_last, $address_01, $address_02, $address_city, $address_state, $address_postal_code\n";

}

exit;

sub ConnectToMySql {

my ($db) = @_;

open(ACCESS_INFO, "<..\/accessAdd") || die "Can't access login credentials";

my $database = <ACCESS_INFO>;
my $host = <ACCESS_INFO>;
my $userid = <ACCESS_INFO>;
my $passwd = <ACCESS_INFO>;

my $connectionInfo="dbi:mysql:$db;$host";

close(ACCESS_INFO);
chomp ($database, $host, $userid, $passwd);
my $l_connection = DBI->connect($connectionInfo,$userid,$passwd);
return $l_connection;

}