コンテンツ

  1. HOME
  2. Services
  3. Hosting services
  4. Manuals ・ Guidebooks
  5. Contents creation guidebook
  6. PHP-Database cooperation

PHP-Database cooperation

Back to the TOP of contents creation guidebook

1.Database and SQL

We will explain the mechanism of the database and take the example of MySQL as a sample for the relational database. We don't mention the practical management of database server because it's beyond our purpose.Please check the reference books detailed later.

About database

Database is the system to deal with the data specially independent from the application for business. Database stores and search the data accepting the request from the other application.

In using Database, it enables you to unify management of the data created separately in each application and to share them, which improve the convenience of use and makes it easy to keep the integrity of the information to be treated. Therefore the database (including the Web application by PHP programming to be mentioned later)is used in the various situation.

データベースの利用

Main database service is as follows.

  • MySQL
  • PostgreSQL
  • ORACLE
  • MS Access

Role of Database Management System(DBMS)

Database is just the group of the stored data by the above explanation. In using it as database system, it requires the mediate system to deal with the data understanding the request from client such as application and return the result.

Then the Database Management System(DBMS) to operate and manage the database is here. Database plays a role as database server incorporated with the DBMS to the system together. DBMS has 3 functions mainly.

Operation function of the data
The function to operate the data un database and to return the result to the request for creating and acquiring the data from the application. DBMS communicates with the application in database language.
Management function of the data
The function to manage the maintenance needed for keeping the database such as backup, lock, unlock the data.
Management function of user authority
The function to manage the access right to renew and browse the data needed for the use of data with group of users(application).

DBMSの役割

Relational database

Database will be divided into 4 categories depending on the structure of data.

  • hierarchical database
  • network-type database
  • Relational database
  • object oriented database

Most of the database available these days in information society is relational database. The relational database process the data with the concept of table. The table means the two-dimensional chart with vertical and cross line familiar in our daily life. Data structure in relational database is as follows.

リレーショナルデータベースの構造

The vertical axis of the table is called field and the cross axle is called record. The field is the items of data and the record is the substance of the each data here. The primary key(main key) , the field which the parameter never overlap between records, has to be specified in the table. The specification of the registered record will be guaranteed by specifying the primary key. And the ID will be specified as primary key as usual.

There are lots of elements to be defined such as the items of the field, primary key, access rights of each users etc in database. Therefore, in building up the database, users are required to define the purpose of the use and develop the ideas in which database to create in advance.

Incorporating the unnecessary elements in database causes waste the capacity, and break down the integrity of data and security. In building up the database of good quality, we are required to design logically fixing our eyes on a purpose and avoid storing unnecessary data and giving access right.

VM hosting service and webpage service prepare for the option with relational database such as MySQL to be mentioned later.

SQL and Query

SQL is the database language to order the operation of the data with relational database. The order of the operation to the database is called Query here. The operation and management of the database will be performed by sending Query, mentioned in SQL, to DBMS in relational database.

Here are the typical order of SQL.

  • Search(SELECT)
  • Add(INSERT)
  • Renew(UPDATE)
  • Delete(DELETE)
  • Create the table(CREATE TABLE)
  • See the information(SHOW)

Query will be created with the order from SQL. Sentence of the Query consists of one SQL order and plural options and we should add";" at the end. It will be recognized as one query until entering ";", which means " end" and users can start their new line in the halfway of the SQL sentence.

Let's create the table of kyoto-u as an example of SQL and search in adding the record. The creation of the table is "CREATE TABLE" sentence.
CREATE TABLE kyoto-u(id int not null, name varchar(20) not null, primary key(id)); In performing this query, the table of kyoto-u with the primary key of id field, which consists of int(integer) type field id and varchar(character string)type field name, will be created.

Next, let's add the record in this table using INSERT sentence. The parameter has to be entered in both of this field(id and name) of this table in inserting because the option with "not null" specification is added on them.
INSERT INTO kyoto-u(id,name) VALUES(1,"Kyodai Taro");
INSERT INTO kyoto-u(id,name) VALUES(2,"Kyodai Hanako");
In performing this query, Kyodai Taro and Kyodai Hanako will be registered in the table of kyoto-u.

Then, let's use the SELECT sentence to search the objective information from the created table. SELECT id,name FROM kyoto-u; In performingthis query, the list of corresponding id field and name field from the stored data in the table of kyoto-u will be output.

Let's add the option in this query and change it as follows. SELECT id,name FROM kyoto-u WHERE name="Kyodai Hanako"; In performing this query, the list of corresponding id field and name field of Kyodai Hanako from the stored data in the table of kyoto-u will be output.

You can operate the database as above in entering query in SQL language. Please refer to the reference books for the details of SQL language.

The use of database "MySQL"

Let's use the relational database as an example of MySQL. We will explain assuming that MySQL server is already built up and operated.

First of all, let's access to the database of MySQL. Please start your screen of the terminal and enter as follows. mysql -u root -p
enter the password
When the message displayed "mysql>", you had completed your log in.
(以下(Herein after, we write" mysql>" concerning the input in mysql.)

-p option is unnecessary because the password is not set in root user when you log in for the first time. Please set your password for securing your security. In setting your password, you should enter as follows.
mysql>set password for root=password('password to be set');
mysql>flush privileges;

In registering the MySQL users, you should enter as follows. mysql>grant all on *.* to user's name identified by "user's password";
mysql>flush privileges;
The part "all on *.*" indicate the user's authorities in database. And "all specification" indicate that this user has the whole authorities excluding the grant. After "on" is the specification of the database name and the table name of authorities. The authorities extend to the whole database and table in MySQL because it has "*". We can restrict the authority for the use of database in changing the statement option of the parameter.

Let's create the database in the server then.mysql>CREATE DATABASE univ;In entering as above, the database "univ" will be created.

The creation of the database and MySQL user is over now. Let's cut the access to the database with root and acess to the univ database with the user created new. In getting out of the database, you should use "exit" command. mysql>exit; Then you can go back to the usual terminal screen.

You will log in to MySQL again with your terminal screen. You are required to use the created user's name, not root, this time.mysql -u user's name -p univ
Entering password
The different things from your last log in is that you logged in specifying the database directly in connecting. If you were to log in without specifying the database like last time, please specify the database with USE command after logging in. mysql>USE univ;

Also, you can use the remote connection via network to the MySQL server with -h option. You should enter as follows from the command prompt of Windows or the terminal screen. mysql -h address -u user's name -p univ
Entering password
Please be cautious of the security such as managing password in your use even though remote connection of entering password is very convenient function. Also, you are required to open the port of MySQL with the setting of firewall in advance.

Let's create the query with SQL and operate the database after connecting to the database. Please refer to the reference books for the details of SQL

Introduction of the reference books

  • MySQL徹底攻略ガイド
    著者:志村伸弘 発行:技術評論社
  • 現場で使えるMySQL
    著者:松信嘉範 発行:翔泳社
  • ORACLE SQL入門
    著者:David Lockman 翻訳:MbCD 発行:ASCII
  • SQL92/99 標準リファレンスブック
    著者:マーチン・グルーバ 翻訳:株式会社ドキュメントシステム 発行:ピアソン・エデュケーション

Back to the TOP of contents creation guidebook

2. Use of Database to Web application

We will explain creating the Web application with database taking an example of the cooperation with PHP and MySQL. We don't mention the practical creation of Web application because it's beyond our purpose.Please refer to the reference books after mentioned.

Cooperation with Database and Web

We explained that database provides the process result of searching and stored data to the process request from the application dealing with just data.

Web application is not exceptional as an object for the use of database. In cooperating the database to Web application, it enables to perform the process through the Web page screen. You can display the search result in your keyword search and renew or store the information to the database based on the sent contents in using form for instance.

Therefore, you can perform your process from the various places with network connection and create your Web page with dynamic business performance because you can process the data on Web by coordinating the database and Web application.

データベースと連携したWebアプリケーション

PHP and Web application made from database

PHP language has many functions needed to create Web application with database and is very simple to coordinate with database. In using PHP particularly, it will be processed as the one in Apache, the Web server and you can expect it for the rapid operation.

The basic operation of the application is to process to the input from users and return the result. In PHP Web application, Web server receive the contents input in client, process the PHP program, and display the result in the browser of the client. Therefore the coordination of PHP and database is a communication between Web server and database server.

データベースとPHPを連携

Let's think of building up the Web application in coordinating with PHP and Database concretely.

At first we should think of receiving the information from Web application users and store them in database. In Web application, we can get the input from the users from the entry form in Web page displayed in the browser. For example, we will receive the input from the users by the text area, radio button or the check box using the <form> element in HTML and sent the information to the PHP file which process with POST or GET.

PHP file script which received the information perform their process to send the INSERT query which stores the information as a data to the database server. In getting the process result from the database, PHP reports their process result to the users in displaying the Web screen.

レコードを追加する処理

Next, we should think of inquiring the database and searching the stored information. We will get the search keyword from users by entry form and send the information to the PHP file which process by using POST or GET this time.

PHP file script which received the information performs the process to send the SELECT query which search the information by keyword. In getting the result from the database, PHP report the search result to the users in displaying them on Web screen.

レコードを検索する処理

We should think of performing the operation of the data such as renewing and deleting the information in the database at the end. In this case, we will get the instruction such as renewing or deleting after showing users which record are there in database currently.

Therefore, PHP script sends SELECT query to the database requesting the list of whole records and display their result. The form to input how to process the data based on the result will be prepared and send the information to another PHP files to process the command from the users input in the form with POST or GET.

The PHP files which received the information will perform the process to send the query of UPDATE or DELETE to the database and report users showing the process result from the database.

レコードを更新・削除する処理

In combining the above three, You can realize performing your business with basic Web application by coordinating PHP and database. We will show the concrete performing examples of Web application coordinating PHP and database as follows.

  • BBS
  • Chat system
  • Reservation accepting such as seminars
  • A questionnaire survey
  • Searching a collection of books at the library
  • Authentication of the pages for users

Importance of the security measure in server script

The server side process based on the input of the client at the server side script operating in the server such as PHP.

The program which coordinates with the database has access rights to the database. If the program of the Web application were to be open to attack, the unexpected process would be performed because the vulnerability will be attacked by the attack method such as SQL injection and it will be connected to the attack such as database alteration or the unauthorized process result.

Database is the treasure house of the information and users should be cautious not to be damaged such as data alteration or information leakage by these attacks. Also users should confirm well if there are security hole or not to the implemented PHP program.

We introduce the security measure at various place inside the contents creation guidebook as well but new attack method will be conceived everyday and the security measure changes in conjunction so we can't support perfect in this contents. Please refer to the professional books(the latest one as possible) and perform the security measure of the program.

In using MySQL with PHP

We will explain the use of database with PHP taking some examples of MySQL here.

PHP language enables users to perform the query in the program. At first you will perform the remote connection from PHP program to MySQL. In connection, PHP mysql_connect function will be used. You are required to input your password, user's name, and connection address of MySQL as parameter in this function.
The next command is the choice of database in being successful in your connection. You will be required to specify the database using with mysql_select_db function. IN the process with MySQL, I will recommend you to set to display in the echo when you fail to your connection. I will give you an example of the PHP program from connection to the choice of database as follows.

データベース接続の実行

We will use the mysql_query function for the process of sending query to MySQL. You should prepare for the variable for query storage in advance and use the function from the variable after storing, not use this function directly for query. I will give you an example of the PHP program which shows the result in searching data from MySQL as follows.

サンプルプログラム

For the practical creation of PHP program and the reference of function. please refer to the reference books.

Introduction of the reference books

  • 独習テキスト完全理解 SQL Webデータベース
    著者:豊崎直也 発行:株式会社すばる舎
  • MySQL×PHPによる本格Web-DBシステム入門
    著者:館岡佐到士 監修:(株)ソフトエイジェンシー 発行:技術評論社
  • Webアプリセキュリティ対策入門 ?あなたのサイトは大丈夫?
    著者:大垣靖男 発行:技術評論社

Back to the TOP of contents creation guidebook

 

Copyright © Institute for Information Management and Communication, Kyoto University, all rights reserved.