Vietnam and Japan Joint
ICT HRD Program
Objectives
To
understand the advantages of
using
i
d
databases
t b
to
t store
t
Web
W b data
d t
To learn how to prepare a MySQL
database for use with PHP
To learn how to store, retrieve, and
update data in a MySQL database
ICT 5 Web Development
Chapter 6.1. Using MySQL with PHP
Nguyen Thi Thu Trang
trangntt--fit@mail.hut.edu.vn
trangntt
2
Content
What is a database?
1. Database and MySQL Overview
2. Basic SQL commands
3. Creating a table
4. Inserting data to a table
5. Retrieving data from a table
6. Updating data for a table
A
set of data organized into one or
more computer
t files.
fil
Using files for product inventory is a
type of database
Generally the term is reserved for
more formal database systems like
access, Oracle or MySQL.
3
4
1
Advantages of Databases
Over Files
Relational Database?
Faster
access
Better concurrent access
Easier changes to data and scripts
Increased security
A database is a collection of tables with defined
relationships between them
Columns define attributes of the data
– All data in a column must have the same data type
A record is stored in a row
table name
row
First Name
Nadia
Madhu
Ajuma
Wade
Helen
column
5
Which Database System
Employees
Last Name Phone
Li
2687
Charu
7856
Kinsaka
4489
Randal
5257
Clark
2147
6
Using A Query Language
PHP
works with a variety of
databases
d t b
that
th t include:
i l d
When
using a database, use a
separate
t query language
l
to
t work
k
with database
Within MySQL, use Structured Query
Language (SQL), to access database
– Oracle
– Access
– Ingres
– SQL Server
– MySQL
PHP
Script
Will
use MySQL since simple to use,
free and very popular.
7
Send
SQL Query
Query
Results
MySQL
Database
8
2
2. Basic SQL commands
Content
- Connecting to MySQL from the Command Line
mysql -uusername -p
1. Database and MySQL Overview
2. Basic SQL commands
3. Creating a table
4. Inserting data to a table
5. Retrieving data from a table
6. Updating data for a table
E.g.:
>mysql -uroot
- To EXIT MySQL:
EXIT;
EXIT
;
9
2. Basic SQL Commands (2)
SQL statements end with a semicolon
View databases
SHOW DATABASES;
Creating a database
CREATE DATABASE trii;
2. Basic SQL Commands (2)
Importing a database:
Use database databasename
USE databasename
databasename;
;
Display all tables in a database
SHOW TABLES;
View column details for a table
DESC tablename
tablename;
;
mysql -uusername -ppassword
databasename < filename.sql
E.g.:
mysql -uroot trii < trii.sql
3
Content
Creating a Database Instance
Once
you have access to a server
with
ith M
MySQL
SQL iinstalled,
t ll d need
d tto gett a
database instance created for you.
– Usually created by a database
administrator
– Creates a database instance, userid and
password.
1. Database and MySQL Overview
2. Basic SQL commands
3. Creating a table
4. Inserting data to a table
5. Retrieving data from a table
6. Updating data for a table
13
14
MySQL Data Types
3. Creating a table
Once
database instance is created
need
d to
t create
t your tables.
t bl
TEXT
– hold a large amount of character data
– Use space inefficiently since it reserves space for up to
65,535 characters.
– Use SQL CREATE TABLE command
CHAR(N)
– hold a fixed length string of up to N characters (N must
be less than 256).
VARCHAR(N)
( )
– hold a variable length string of up to N characters
– removes any unused spaces on the end of the entry.
15
16
4
Some additional CREATE
TABLE Options
MySQL Data Types (2)
Can
specify some additional options
in
i CREATE TABLE
TABLE:
INT
– hold an integer with a value from about –2 billion to about 2
billion.
INT UNSIGNED
SMALLINT
SMALLINT UNSIGNED
DECIMAL(N,D)
– hold an integer with a value from 0 to about 4 billion.
– hold an integer with a value from –32,768 to 32,767.
– hold an integer with a value from 0 to 65,535.
– a number that supports N total digits, of which D digits are to
the right of the decimal point.
17
Issuing CREATE TABLE From PHP
Script Segment
1. $connect = mysql_connect($server, $user, $pass);
2. if ( !$connect ) {
3.
die ("Cannot connect to $server using $user");
4. } else {
Connect to
5.
mysql_select_db('MyDatabaseName');
MySQL
6.
$SQLcmd = 'CREATE TABLE Products(
ProductID INT UNSIGNED NOT NULL
AUTO_INCREMENT PRIMARY KEY,
Product desc VARCHAR(50),
Product_desc
VARCHAR(50) Cost INT
INT,
Weight INT, Numb INT)';
7.
mysql_query($SQLcmd, $connect);
Issue the SQL query
8.
mysql_close($connect);
to the database.
9. }
19
18
Full Script
1. Create Table
2. Created Table';
print "$table_name in database$mydb ";
>$mydb ";
16.
17.
print " SQLcmd=$SQLcmd";
18. } else {
19.
die ("Table Create Creation Failed SQLcmd=$SQLcmd");
20. }
21. mysql_close($connect);
22. }
23. ?>
?>