PERL Tutorial #7

Finally, we need to insert some data. This is done like this:-

#! /usr/bin/perl

use DBI;
use strict;

my ($dsn)="DBI:mysql:fone:localhost";
my ($user_name)="linux";
my ($password)="linux";
my ($dbh, $sth);
my ($name) = "ethel";
my ($num) = "01234 12344";
	
$dbh=DBI->connect($dsn,$user_name, $password, { RaiseError => 0, PrintError => 0 })
	or die "Could not connect\n";

$sth=$dbh->prepare("INSERT INTO fone(name, number)
			VALUES ('$name', '$num')")
	or die "Unable to create\n";

$sth->execute();

$sth->finish();
$dbh->disconnect;
exit(0);

Again, all the work is done by an SQL statement:-

INSERT INTO fone(name, number)
		VALUES ('$name', '$num')

This is fairly straight forward. Note that we haven't entered a value for id as this is being generated automatically for us. Now we want to view the data we have just entered:-

#! /usr/bin/perl

use DBI;
use strict;

my ($dsn)="DBI:mysql:fone:localhost";
my ($user_name)="linux";
my ($password)="linux";
my ($dbh, $sth);
my (@ary);

$dbh=DBI->connect($dsn,$user_name, $password)
	or die "Could not connect";

$sth=$dbh->prepare("SELECT * FROM fone");
$sth->execute();

while(@ary=$sth->fetchrow_array())
{
	print join("\t",@ary),"\n";
}

$sth->finish();
$dbh->disconnect;
exit(0);

This uses the SQL statement to view all the entries. Note that the field names are enclosed in quotes " and square brackets [ ]. For completeness, we will now delete an entry:-

#! /usr/bin/perl

use DBI;
use strict;

my ($dsn)="DBI:mysql:fone:localhost";
my ($user_name)="linux";
my ($password)="linux";
my ($dbh, $sth);
my (@ary);

$dbh=DBI->connect($dsn,$user_name, $password)
	or die "Could not connect";

$sth=$dbh->prepare("DELETE FROM fone WHERE id = 1");
$sth->execute();

$sth->finish();
$dbh->disconnect;
exit(0);

Note this SQL statement:-

DELETE FROM fone where id = 1

This will only delete the entry that has an id that equals 1. To delete everything in the table use:-

DELETE FROM fone

That just about completes this tutorial. Have fun with PERL.

Back