MySQL & Perl

ByGeorgesTarbouriechWhataboutthisnicepair? MySQL is a relational database system (RDBMS). its homepage is www.mysql.com. It is released by GNUGPL for free. please read the copyright notice. It can work on many platforms, both as a server and a client. In addition to MySQL, there are also some other free soft Perl

By Georges Tarbouriech
　　
What about this nice pair?
MySQL is a relational database system (RDBMS). its homepage is http://www.mysql.com /. It is released by gnu gpl for free. please read the copyright notice. It can work on many platforms, both as a server and a client. In addition to MySQL, there are some other free-software RDBMS, which are not compared here. this article only discusses MYSQL. We do not compare it with those large commercial databases such as Informix, Oracle, and Sybase. we have enough reason to believe that MySQL is one of the most widely used database systems on the Internet. MySQL version 3.23.36 is used in this article. The current stable release version is 3.23.46, and the awaiting version 4.0 is still being tested. People can download their source code or package files from the Internet.
To use MySQL and Perl in combination, you need to use the Perl DBI module. At least you should download DBI, Msql-Mysql-modules, Data-Dumper, and Data-ShowTable.
This article does not introduce their installation process, because it is very simple, and the instructions in the package have provided all the content you need to know.
Perl stands for the Practical Extraction and Report Language ). Initially it was used for file processing (analysis, summary...), and soon its functionality was extended. You can use it to do almost anything: system management, CGI scripts, and database interface programs.
Perl contains many (if not all) Unix release versions, some of which are free and some are not. When writing this article, the stable version is 5.6.1, and version 5.7.2 is under test. This article uses 5.005_03, a very good version, although the old version. If your machine has not installed Perl, you can download it from http://www.perl.com. Perl provides many modules that can be used to accomplish almost everything. you can find them in the CPAN section of this website.
Finally, you need a web server to make the two software really start to work. Apache should be a good choice. it is integrated into a variety of Unix systems. If you do not have one, download it at http://www.apache.org.
　　
　　　
Example
You may have noticed that LinuxFocus magazine is available in multiple languages. This means that you need to manage the new article and its translation version at the same time as editing. Generally, we can see about 200 articles. on average, each article has five versions in five languages. in this way, about 1000 articles are generated and continue to grow! These articles need to be archived, standardized, summarized, and summarized... how should they be done? Of course, use Perl!
Many perl programs compiled by Guido Socher make our work much easier. He wrote a three-part Perl Tutorial and a book commenting on Perl. Refer to the references at the end of this article.
Javi, our Spanish editor, compiled a program using Perl to manage the translation progress.
Atif is our star author from the perl kingdom, so his mother tongue is Perl. He also wrote an article on MySQL, devoted to improving a WEB management tool. You can also find him in the references section.
In short, if you are looking for a Perl world, join LinuxFocus.
I was one of the editors of LinuxFocus, and I was lazy. so I created my own LinuxFocus database and guessed what to use: MySQL and Perl!
　　
　　　
Create a database
First, you should have installed MySQL correctly and configured the user password. Installation is not discussed in this article. a large number of MySQL documents have described all the details.
Start the mysql server with MySQL. server. this command also calls the safe_mysqld daemon, so you can pass parameters to it.
Use
　　
Mysql-h host-u user-p
　　
Connect to the server. if the server is installed on your local machine, you do not need to add the-h host parameter.
After you enter the correct password, you will be connected to the server. Now you can create your own database.
Enter
Create database lf;
　　
This is our demo database (lf stands for LinuxFocus). you can name it another one based on your needs. The next step is to authorize the user. of course, you must first have sufficient permissions (you need to connect to the user with administrator permissions ). If you want a user to manage the database
Grant all on lf. * TO username;
　　
Authorize him. Input
USE lf
　　
Select the database you just created and create a table. The table we created here is trissue, and the command format is:
Create table trissue (num integer unsigned, category VARCHAR (25), title VARCHAR (40), author VARCHAR (20), en VARCHAR (20), es VARCHAR (20 ), fr VARCHAR (20), de VARCHAR (20), nl VARCHAR (20), ru VARCHAR (20), tk VARCHAR (20), issue VARCHAR (20 ));
　　
Run the following command to check whether the table we just created is correct.
USE lf
Show tables;
DESCRIBE trissue;
　　
　　
Below we need to fill in data in the table. The simplest way to import data to an empty table is to use a text file with a TAB separator. If the text file is ready, enter
Load data local infile "maindb.txt" into table trissue;
　　
If your text file is correct, you can enter the following command to check the data in this table:
SELECT * FROM trissue;
　　
This will result in a long list. Now, you can query to obtain any type of data.
Okay. so far, we have only used MySQL to do anything. so, what should we do with Perl?
　　
　　　
Perl's work
Perl can help us perform queries automatically and display the results on a WEB browser. Repeat it. first install the correct module for Perl to work with MYSQL.
Now we use Perl to write a CGI script. It integrates Perl and HTML technology to query databases and format the results.
We use a simple script to query all the articles of an author, display the document numbers, categories, titles, the names of translators of different language versions, and the Journal Number of the published articles.
You can use this script as a module, but note that this routine is not a very reliable program. You can download a version with detailed notes from the following link. => Here <=.
　　
　　
　　
#! /Usr/bin/perl-Tw
# First, we say this is a "Tainted" Perl script.
#
# This is a comment
# Db consult
#
# We use the Perl DBI module
Use DBI;
　　
# As cgi:
Use CGI qw (param ());
　　
Print < 　　
Content-type: text/html
　　
　　
　　 LFAuthors main db
　　
　　



　　 　　 　　 　　
　　
Run the script to query the database.
Search by author
　　
　　
　　
　　
END_of_start
　　
　　
If (param ("author") ne ''){
$ Author = param ("author ");
　　
$ Autsrch. = '"';
$ Autsrch. = $ author;
$ Autsrch. = '"';
　　
# We connect to the database named lf as user doe
　　
$ Dbh = DBI-> connect ("DBI: mysql: lf", "doe ",'');
　　
$…… = $ Dbh-> prepare ("
Select *
From trissue
Where
Author = $ autsrch
");
　　
$ Something-> execute;
　　
Then, use a script to display the query results. If we do not limit the query conditions, all contents of the database will be displayed. if we provide an author name, all articles related to the author will be displayed. When your database has thousands of records, it is not recommended to display all the content!
Print < 　　
　　
　　























　　 　　 　　 　　 　　 　　 　　 　　 　　 　　 　　 　　 　　 　　 　　END_suite　　While ($ num, $ category, $ title, $ author, $ en, $ es, $ fr, $ de, $ nl, $ ru, $ tk, $ issue) = $ Something-> fetchrow ()){Print" ";Print" ";Print" ";Print" ";Print" ";Print"

Num	Category	Title	Author	En	Es	Fr	De	Nl	Ru	Tk	Issue
$ Num	$ Category	$ Title	$ Author	$ E