PHP and MySQL
Introduction
HTML (Hypertext Markup Language), as described in the course HTML, makes it
possible to create documents independent of any platform, and thus well
adapted for exchanging informations in a heterogeneous environment like
the Web. It is a well tested method of creating Web pages. Why is it necessary
to program in PHP? Why would one need "dynamic" Web pages?
Limits of HTML
However internet and the intranets are used more and more for applications,
that include databases. The sites
and their applications are dynamic, because the contents are modified
according to the implied data and actions from the user. You can create
powerful applications acting in an interactive way with a database, and
generating dynamic contents by executing PHP on a server.
- facilitates the modification of the contents of a web page, intervening
on the data of a database and not directly in the HTML code.
- creates personalized pages in order to only display what interests a particular
user.
- displays and refreshs database included in the web page, can handle the
data, for example by carrying out a sorting or by displaying part of the database.
- creates pages which carry out a cycle among various images
- obtains an answer from the user, then returns the information according
to this answer.
- can still do well other things …
This course is not exaustif. It gives basics concepts to any programmer who
wish to take his first steps in PHP and MySql. That being, a clear understanding,
at least intuitive, of the basic data-processing concepts (what a computer
network, a file, a text editor, a programming language, a compilation
etc.) is preferable. We suppose moreover that you have at least one of
the following environments:
•a computer, connected to internet throught
an ISP proposing the MySql/PHP environnement.
•a computer under Linux or Windows, laying out in room an Apache/MySql/PHP
complete environment.
As in C, the instruction
separator is the semicolon "; " .
Else, if the comment
have to be written on several lines, it should be placed between the signs
"/*" and "*/".
Of course, one
can mix the two styles of comments
in the same script.
c. variables and data
PHP distinguishes
the uppercases letters from the lowercase letters in the name of the variables;
thus "$mavariable" and "maVariable" indicate two different
variables; on the other hand the names of functions are insensitive to
the case.
• Data
< ?php
echo « text generated
by PHP » ;
?>
</HTML>
<FORM>
Please enter your name here: <BR>
<INPUT TYPE = TEXT NAME = user>
<BR><BR>
<INPUT TYPE = SUBMIT VALUE = « submit »>
</FORM>
Your entered :
< ?php
echo ($user) ;
?>
</HTML>
« + » : addition
« - » : substraction
« * » : multiplication
« / » : division
« % » : modulo
« < » : lower
than
« > » : greater
than
« <= » : lower or equal to
« >= » : greater of equal to
« != » : different from
« <> » : different from
c. logical operators
« || » : or
« and » : and
« or » : or
« xor » : exclusive or
« ! » : no
• if instruction
{instruction1;}
[else
{instruction2;}]
case value1 ;
instruction1 ;
break ;
case value2 ;
instruction2 ;
break ;
case value_n ;
instruction_n ;
break ;
default :
instruction ;
}
for (expr1 ;
expr2 ; expr3) :
// instructions
endfor ;
• while loop
// instructions
}
instructions;
} while (condition);
The functions
are declared with the function instruction.
function body
}
E. g.
return $ number * $ number * $ number ; // return $number exponent
3
}
To take advantages
of a dynamic and powerful site, the data must be stored, and grouped if
necessary in tables, which will constitute a database. There are several
stable and tested systems comprising powerful techniques allowing programmers
to create applications meeting their needs: Databases Management Systems
(DBMS), with the number of which Access, Oracle, SQL Server, Sybase, InterBase,
MySQL. We will be interested in DBMS MySQL this paragraph, because the
solution provided by the couple
(MySQL, PHP) is of an increasing popularity for the management of dynamic
Web sites; then because these two tools constitute a complete solution
to the problems mentioned above, while remaining very simple to use, and
finally because the tandem formed by MySQL and PHP can easily associate
to the Apache server, and this, as well as under Linux and Windows.
2. Instructions of data definition
Also called requests,
these instructions of the MySQL language make it possible to modify the
diagram of the database by creating or modifying the objects in this one.
• CREATE
•
•
Creation of a table
fieldName type
[NOT NULL | NULL] [DEFAULT literal] [AUTO_INCREMENT]
[PRIMARY KEY] [definitionReference]
or PRIMARY KEY (fieldName, …)
or KEY [nomIndex] (fieldName, …)
or INDEX [nomIndex] (fieldName, …)
or UNIQUE [INDEX] [nomIndex] (fieldName, …)
or [CONSTRAINT constraint] FOREIGN KEY nomIndex
(fieldName, …) [reference]
or CHECK (expression)
optionsTable :
TYPE
= {ISAM | MYISAM | HEAP}
or AUTO_INCREMENT = #
or AVG_ROW_LENGTH = #
or CHECKSUM = {0 | 1}
or COMMENT = « comments »
or MAX_ROWS = #
or MIN_ROWS = #
or PACK_KEYS = {0 | 1}
or PASSWORD = « passWord »
or DELAY_KEY_WRITE = {0 | 1}
optionSelect:
[IGNORE | REPLACE]
SELECT … (request SQL)
reference:
REFERENCES TableName
[(fieldName, …)]
[MATCH FULL | MATCH PARTIAL]
[ON DELETE optionRef]
[ON UPDATE optionRef]
optionRef
RESTRICT | CASCADE
| SET NULL | NO ACTION | SET DEFAULT
The instruction
CREATE table creates a table
in the current database. The option TEMPORARY
indicates that the table is created for the current connection only. The
option optionTable makes it
possible to choose the table type. The index name is optional.
example of CREATE instruction in the creation of database and table:
CREATE DATABASE university
Let us create inside university database, the student
table:
CREATE TABLE student
(IDnumb VARCHAR (40) NOT NULL,
lastname
VARCHAR (30) NOT NULL,
firstname
VARCHAR (30) NOT NULL,
sex
CHAR (1),
birthDate
INTEGER,
school
VARCHAR (30),
option
VARCHAR (30),
remark
TEXT,
PRIMARY
KEY (IDnumb));
• DELETE
syntax :
DELETE
[LOW_PRIORITY] FROM TableName
[WHERE clauseWhere] [LIMIT nbLines]
This instruction deletes all lines verifying the WHERE clause. The option LOW_PRIORITY shows to MySQL that the deletions
are lower priorities than the other current request accessing to the table.
E. g. of use DELETE instruction:
DELETE FROM TABLE student ;
This request delete all the student table records
DELETE FROM TABLE student WHERE school = ‘health
sciences ’ AND
option = ‘medicine’ ;
This request deletes from the student table all students registered in the option « medicine » of the « health
science » school.
• SELECT
SELECT [STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT]
[HIGH_PRIORITY]
[DISTINCT | DISTINCTROW | ALL]
fieldsList
[INTO {OUTFILE
| DUMPFILE} ‘fileName’ optionExport]
[FROM clauseFROM
[WHERE clauseWHERE]
[GROUP BY nomAttribut,
…]
[HAVING clauseWHERE]
[ORDER BY {entire
| fieldName | formule} [ASC | DESC], …]
[LIMIT [debut,]
nbLines]
[PROCEDURES]]
clauseFROM:
TableName, TableName
or TableName [CROSS] JOIN TableName
or TableName INNER JOIN TableName
or TableName STRAIGHT_ JOIN TableName
or TableName LEFT [OUTER] JOIN TableName
ON expression
or TableName LEFT [OUTER] JOIN TableName
USING (fieldsList)
or TableName NATURAL LEFT [OUTER] JOIN TableName
or TableName LEFT OUTER JOIN
This instruction
extracts from one or several tables the lines which satisfy the WHERE
clause. FieldList is a list of fields coming from the FROM tables, or
from expressions implying functions. We can refer to an attribute by its
name, the name of its table and its name, or by the name of its database,
the name of its table and his name: university.student.lastname indicates
the field lastname from the table student
of the database university.
The option:
- STRAIGHT_JOIN
indicates that the joint must reach the tables in the specified order.
- SQL_SMALL_RESULT
warns MySQL that the result will contain just a few lines, which allows
to speed up the request.
- SQL_BIG_RESULT
indicates the inverse.
- HIGH_PRIORITY
requires the priority of execution for the request compared to those which
carry out modifications.
Caution: these options
are reserved to the experimented users and must be used with full knowledge
of the facts.
• INSERT
INSERT [LOW_PRIORITY
| DELAYED] [IGNORE]
[INTO] TableName
[(fieldName, …)] VALUES (expression, …), (…), …
or INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
[INTO] TableName
[(fieldName, …)] SELECT…
or INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
[INTO] TableName
SET fieldName = expression, …
E. g. use the instruction INSERT
INSERT INTO student
(IDnumb, lastname, firstname, sex, birthYear, school, option, remark)
VALUES (‘20194’, ‘GUINKO’, ‘Tonguim Ferdinand’, ‘M’, ‘1979’, ‘computer
sciences ’, ‘software engineering’, ‘none’) ;
• USE
syntax :
E. g. use of USE
instruction: USE university
• ABS (number): returns the absolute value of a number.
• ASCII (char): reurns the ASCII code of the character.
• CONCAT (chaîne1, [ chaîne2? ]) retuns the concatenation of all the
arguments.
• CONV (a number, base1, base2): return the conversion of a number,
base1 in base2; the base is a
• BIN (decimal): returns the binary value of a decimal number.
• CURDATE (): returns the current date into format AAAAMMJJ or Aaaa-mm-jj
according to whether
• CURTIME (): returns current time into format HHMMSS or HH:MM:SS according
to whether the
• DATABASE (): returns the name of the current data base.
• DAYNAME (date): returns the name of the day in English.
• DAYOFMONTH (date): returns the number of the month.
• DAYOFWEEK (date): returns the number of the day in the week.
• DAYOFYEAR (date): returns the number of the day in a year.
• LTRIM (chain): withdraws all the blanks spaces at the beginning of
string.
• MONTHNAME (date): returns the name of the month of date in English.
• NOW (): returns the current date and hour.
• PASSWORD (string): encoding of string with the function used for the
MySQL.
• UCASE (string): return string in uppercase letter.
•
•Mysql_pconnect: Idem, but with a persistent connection.
This second version is more powerful when the PHP interpretor is included
in Apache.
• Mysql_select_db: allows to go into a
database. It is the equivalent of the USES databse instruction under mysql.
•
•
•
•
To reach a MySQL
data base, a script PHP must:
• be connected to the MySQL database server’s.
• send a SQL request to the MySQL database server’s, then recover the result.
• extract the data of the result with the help of functions.
• generate HTML page to display the datas.
<HEAD>
<TITLE> connection to MySQL </TITLE>
</HEAD>
<BODY>
<H1>interrogation of the student
table</H1>
< ?php
// connection
to the MySQL database server’s
require (‘‘connect.php’’) ;
$connexion =
mysql_pconnect (server, loginName, passWord) ;
if ( !$connexion)
{
echo ‘‘Sorry, connection to ’’ . server . ‘‘ failed\n’’;
exit ;
}
{
echo “Sorry, acces to table ” . tableName . ‘‘ failed\n’’;
exit ;
}
/* send a SQL request to
the MySQL database server’s, then recover the result */
if ($result)
{
while ($Student = mysql_fetch_object ($result))
{
echo “$Student ->IDnumb, of the student ’’
. ‘‘ $Student->lastname $Student->firstname’’
<BR>\n’’ ;
}
}
else
{
echo ‘‘<B>Errors detected in the request</B><BR>’’ ;
echo ‘‘<B>Message from MySQL : </B>’’ . mysql_error
($connection) ;
}
?>
</BODY>
</HTML>
The instructions
require allows to include the
contents of a file in a script. Some informations are common to many scripts,
and to repeat them systematically is at the same time a waste of time
and a large source of matters the day when it is necessary to carry out
a modification in n versions
duplicated. Here we placed in the file connect.php
some basic information on the site: the name of the server, the name of
the database and the acess account to the database.
< ?php
// connect.php
$server =
‘‘www.hosting_server.com’’; //
host of MySQL database
execution
$loginName
= ‘‘php’’
// database user name
$passWord = ‘‘php’’ // user
password
$databaseName = ‘‘university’’ // database name
?>
Conclusion
This work is on one hand the fruit of long research on the Net and in several works,
and on the other hand, of our experience in this area. Certainly It is
not exhaustive, but allows the programmer who wishes to take his first
steps in PHP and/or MySQL to understand the basic elements of these languages,
in simplified terms.
PHP represents
in fact one technology among several others which can be used to create
more dynamic and more interactive Web pages. Among other technologies
allowing to carry out dynamic Web sites we can quote:
- Active Server Pages (ASP): it is similar to PHP;
the fundamental inconvenient of this technique is that it cannot be used
with an other server than a Microsoft Web
server (IIS, PWS), or with an Microsoft operating system.
- Javascript side waiter (SSJS): it combines
with HTML like the two precedents; the inconvenient of this technique
is that it requires the compilation of SSJS applications before being
able to carry out them; moreover, SSJS is only taken charges by Netscape
Enterprise Server.
Concerning the
DBMS, they are numerous: Access, Oracle, SQL Server, Sybase, InterBase,
MySQL… The MySQL/PHP solution presents the advantage to be powerful, and
portable on the linux and Windows operating systems.
To go further
Bibliography
J. Castagnetto, H. Rawat, S. Schumann, C. Scollo, D.
Veliath, “PHP professionel”,
Eyrolles, 2001
Philippe Riguaux,
« MySQL et PHP », O’Reilly, édition
originale, 2001
Webography