Work with MySQL: Create Tables - Web Hosting Reviews

Server Applications :: Work with MySQL: Create Tables.

Other Articles:

24/7 Solutions - Work with MySQL: Create Tables

If I am not mistaken, language Perl borrows predominating position in the field of Web-appendices. Great bulk CGI - scripts and various information/operating systems on Web the interface at present it is written on Perl. In this case, we considering interaction of language Perl with a server of databases MySQL, shall use library DBD:: mysql.

For use of library DBD presence of library DBI is necessary, therefore they should be established on a computer where the script is used. Besides presence of server MySQL is necessary.

1. Testing of connection to MySQL to a server

#!/usr/bin/perl
use DBI;
my $dbh = DBI-> connect ("DBI:mysql:database=your_base;host=server_adress_mysql", "Login", "password") || die $DBI:: errstr;
@tables = $dbh-> func (' _ListTables');
foreach $line (@tables) {
print $line, " \n";
}
$dbh-> disconnect ();

Thus if all is normal, you on an output of it scripts should receive anything something of type:

table1
table2
table3

Where is table1 - the name of tables in your database. If connection to create it has not turned out, then something is wrong, or not that host, the password, a login or not such base - for example if there is no database there will be something of type:

Unknown database 'your_base' at try.pl line N.

If there is no access to the user under "login" either a wrong login or the password the message will be given out

Access denied for user: ' login@yourhost.domain ' (Using password: YES) at try.pl line N.

The address of a server can a life as initial a kind dbserver.domain.com (if correctly works DNS) or absolute IP the address.

So, if it was possible to be connected successfully you receive the list of tables in your DB. If is not present - check up a login/password, swear with the administrator that it to you has resolved access from your machine to server MySQL. (only on the system administrator strongly do not run - something can bad make to you, itself I know, the system administrator).

2. Creation of tables in MySQL

Now we shall try to create the table in your database. We shall be defined at once - we shall make the small catalogue of references to your favourite resources. We shall create the table, it is proud called "links". In the table there will be following fields:

Naming	Type	Leinch	Comments
id	TINYINT	4	Auto identificator
name	VARCHAR>	64	Link name
url	VARCHAR	128	URL
category	TINYINT	4	Category number

When with structure of the table clearly, we shall make SQL inquiry for its creation:

CREATE TABLE links (
id TINYINT, name VARCHAR (64), url VARCHAR (128), category TINYINT
}

In Perl it is necessary to execute it as follows:

#!/usr/bin/perl
use DBI;
my $dbh = DBI-> connect ("DBI:mysql:database=your_base;host=mysql_server_adress",
"Login", "password") || die $DBI:: errstr;
$dbh-> do (" CREATE TABLE links
(id TINYINT, name VARCHAR (64), url VARCHAR (128), category TINYINT) ");
$dbh-> disconnect ();

Here the code for creation of the table in a database is those.

The method of the index of a database do prepares and executes single instruction SQL. Returns quantity of the chosen lines or undef in case of a mistake. If return =-1 the quantity of lines is not known or is absent. In our case the quantity of lines is irrelevant, therefore we have lowered the operator of giving.

This method is equivalent to a following set of a code:

sub do {
my ($dbh, $statement, $attr, @bind_values) = _;
my $sth = $dbh-> prepare ($statement, $attr) or return undef;
$sth-> execute (@bind_values) or return undef;
my $rows = $sth-> rows;
($rows == 0)? "0E0": $rows;
}

The field id should be auto increment. Now there are some remarks:

Field AUTO_INCREMENT should be key (a primary index) - key;
The index key cannot be created on a field which can accept value null;

Means to us it is necessary to change all over again properties of a field id so that the field could not accept value null, was a key index, and was auto increment. Here a code for this purpose, three SQL the expressions executed by a method do:

...
$dbh-> do (" ALTER TABLE links CHANGE id id TINYINT (4) not null ");
$dbh-> do (" ALTER TABLE links ADD PRIMARY KEY (id) ");
$dbh-> do (" ALTER TABLE links CHANGE id id TINYINT (4) not null AUTO_INCREMENT ");
...

SQL instruction ALTER TABLE allows to change properties of the table.

Now the table links corresponds to our requirements. At addition of new record value of a field id in this automatically will increase on 1.

Further it is necessary to create the tablet of categories. Its structure:

Naming	Type	Leinght	Comment
id	TINYINT	4	Category ID
name	VARCHAR	64	Category naming

This time we shall create the table with all attributes of fields necessary for us, - the field id will be key, auto increment. Here SQL inquiry about its creation:

CREATE TABLE category (id TINYINT not null AUTO_INCREMENT,
name VARCHAR (64) not null, PRIMARY KEY (id))

We execute its method already known to us do:

$dbh-> do (" CREATE TABLE category (id TINYINT not null
AUTO_INCREMENT, name VARCHAR (64) not null, PRIMARY KEY (id)) ");