Tập bài giảng Lập trình mã nguồn mở (Phần 2) - Nguyễn Văn Trung

99 trang Gia Huy 17/05/2022 2560

Download

Bạn đang xem 20 trang mẫu của tài liệu "Tập bài giảng Lập trình mã nguồn mở (Phần 2) - Nguyễn Văn Trung", để tải tài liệu gốc về máy bạn click vào nút DOWNLOAD ở trên

Tài liệu đính kèm:

tap_bai_giang_lap_trinh_ma_nguon_mo_phan_2_nguyen_van_trung.pdf

Nội dung text: Tập bài giảng Lập trình mã nguồn mở (Phần 2) - Nguyễn Văn Trung

CHƢƠNG 3: SỬ DỤNG PHP VỚI MYSQL 3.1. Cấu trúc và cú pháp của MySQL MySQL là hệ thống cơ sở dữ liệu quan hệ. Ý nghĩa cơ bản của MySQL là nó có thể lƣu trữ thông tin ở những vùng khác nhau và liên kết chúng lại với nhau. Có thể chứa bất cứ thứ gì trong một cơ sở dữ liệu. Ví dụ nhƣ những thông tin liên quan đến một ngƣời: chẳng hạn nhƣ first name, last name, address, phone . MySQL cho phép tạo những thông tin riêng lẻ trên bảng hoặc những khu vực chứa thông tin thích hợp. Trong MySQL mỗi bảng bao gồm những trƣờng dữ liệu. MySQL là hệ quản lý dữ liệu quan hệ, nó cho phép chúng ta tạo những bảng thông tin riêng, hoặc những vùng thông tin thích hợp. Trong hệ thống cơ sở dữ liệu không quan hệ, tất cả những thông tin đƣợc lƣu trữ trong một bảng lớn tạo nên những khó khăn trong việc sắp xếp và chỉ có thể chép dữ liệu. Trong SQL, mỗi bảng bao gồm những phần riêng biệt, biễu diễn mỗi thông tin. Ví dụ: Cho rằng bảng bao gồm tên khách hàng, địa chỉ và số ID, bảng khác bao gồm số ID, nơi ở, .Vùng chung là số ID, thông tin đƣợc lƣu trữ trong hai bảng riêng biệt sẽ liên kết với nhau nơi mà số ID là nhƣ nhau. 3.1.1. Các kiểu dữ liệu MySQL Trƣớc khi thiết kế cơ sở dữ liệu trên MySQL, cần phải tham khảo một số kiểu dữ liệu thƣờng dùng, chúng bao gồm các nhóm nhƣ: numeric, date and time và string. Trong khi thiết kế cơ sở dữ liệu, cần phải xem xét kiểu dữ liệu cho một cột trong Table sao cho phù hợp với dữ liệu của thế giới thực. Điều này có nghĩa là khi chọn dữ liệu cho cột trong Table, phải xem xét đến loại dữ liệu cần lƣu trữ thuộc nhóm kiểu dữ liệu nào, chiều dài cũng nhƣ các ràng buộc khác, nhằm khai báo cho phù hợp. 1) Loại dữ liệu numeric Kiểu dữ liệu numeric bao gồm kiểu số nguyên và kiểu số chấm động, trong trƣờng hợp dữ liệu kiểu dấu chấm động cần phải chỉ rõ bao nhiều số sau đấu phần lẻ. Kiểu dữ liệu số nguyên Loại Range Bytes Diễn giải tinyint -127 ->128 hay 1 Số nguyên rất nhỏ 0 255 smallint -32768 ->32767 hay 2 Số nguyên nhỏ 0 65535 mediumint -8388608 -> 838860 hay 3 Số nguyên vừa 0 16777215 158
int -231 -> 231 - 1 hay 4 Số nguyên 0 232-1 bigint -263 -> 263-1 hay 8 Số nguyên lớn 0 264-1 Kiểu dữ liệu số chấm động Loại Range Bytes Diễn giải float Phụ thuộc số thập phân Số thập dạng Single hay Double. Float(M,D) ±1.175494351E-38 4 Số thập phân dạng ±3.40282346638 Single. Double(M,D) ±1.7976931348623157308 8 Số thập phân dạng ±2.2250738585072014E-308 Double. Float(M[,D]) Số chấm động lƣu dƣới dạng char. 2) Loại dữ liệu Date and Time Kiểu dữ liệu Date and Time cho PHP nhập liệu dƣới dạng chuỗi hay dạng số. Loại Range Diễn giải Date 1000-01-01 Date trình by dƣới dạng yyyy-mm-dd. Time -838:59:59 Time trình by dƣới dạng hh:mm:ss. 838:59:59 DateTime 1000-01-01 Date v Time trình by dƣới dạng yyyy-mm-dd 00:00:00 hh:mm:ss. 9999-12-31 23:59:59 TimeStamp[(M)] 1970-01-01 TimeStamp trình by dƣới dạng yyyy-mm-dd 00:00:00 hh:mm:ss. Year[(2|4)] 1970-2069 Year trình by dƣới dạng 2 số hay 4 số. 1901-2155 Đối với kiểu dữ liệu TimeStamp, có thể định dạng nhiều cách nhƣ trình bày trong bảng sau: Loại Hiển thị TimeStamp YYYYMMDDHHMMSS TimeStamp(14) YYYYMMDDHHMMSS TimeStamp(12) YYMMDDHHMMSS TimeStamp(10) YYMMDDHHMM TimeStamp(8) YYYYMMDD 159
TimeStamp(6) YYMMDD TimeStamp(4) YYMM TimeStamp(2) YY 3) Loại dữ liệu String Kiểu dữ liệu String chia làm ba loại, loại thứ nhất char (chiều dài cố định) và varchar (chiều dài biến thiên). Char cho phép nhập liệu dƣới dạng chuỗi với chiếu dài lớn nhất bằng chiều dài đã định nghĩa, nhƣng khi truy cập dữ liệu trên Field có khai báo dạng này, cần phải xử lý khoảng trắng. Điều này có nghĩa là nếu khai báo chiều dài là 10, nhƣng chỉ nhập chuỗi 4 ký tự, MySQL lƣu trữ trong bộ nhớ chiều dài 10. Ngƣợc lại với kiểu dữ liệu Char là Varchar, chiều dài lớn hất ngƣời dùng có thể nhập vào bằng chiều dài đã định nghĩa cho Field này, bộ nhớ chỉ lƣu trữ chiều dài đúng với chiều dài của chuỗi đã nhập. Nhƣ vậy, có nghĩa là nếu khai báo kiểu varchar 10 ký tự, nhƣng chỉ nhập 5 ký tự, MySQL chỉ lƣu trữ chiều dài 5 ký tự, ngoài ra, khi truy cập đến Field có kiểu dữ liệu này, không cần phải giải quyết khoảng trắng. Loại thứ hai là Text hay Blob, Text cho phép lƣu chuỗi rất lớn, Blob cho phép lƣu đối tƣợng nhị phân. Loại thứ 3 là Enum và Set. Có thể tham khảo cả ba loại trên trong bảng 3.5. Loại Range Diễn giải char 1-255 characters Chiều dài của chuỗi lớn nhất 255 ký tự. varchar 1-255 characters Chiều dài của chuỗi lớn nhất 255 ký tự (characters). tinyblob 28-1 characters Khai báo cho Field chứa kiểu đối tƣợng nhị phân cở 255 tinytext 28-1 characters Khai báo cho Field chứa kiểu chuỗi cở 255. blob 216-1 characters Khai báo cho Field chứa kiểu blob cở 65,535 text 216-1 characters Khai báo cho Field chứa kiểu chuỗi dạng văn bản cở 65,535 Mediumblob 224-1 characters Khai báo cho Field chứa kiểu blob vừa khoảng 16,777,215 Mediumtext 224-1 characters Khai báo cho Field chứa kiểu chuỗi dạng văn bản vừa khoảng 16,777,215 Longblob 232-1 characters Khai báo cho Field chứa kiểu blob lớn khoảng 4,294,967,295 Longtext 232-1 characters Khai báo cho Field chứa kiểu chuỗi dạng văn bản lớn khoảng 4,294,967,295 160
3.1.2. NULL/NOT NULL Đây là trạng thái của một cột trong bảng cho phép chấp nhận giá trị NULL hay không. Nếu chỉ ra ràng buộc giá trị NOT NULL thì bắt buộc phải có giá trị trong cột này mỗi khi bản ghi đƣợc nhập vào. Đối với một số kiểu dữ liệu không cho phép NULL nên thiết lập giá trị mặc định cho cột đó, ví dụ nhƣ kiểu dữ liệu bit không cho phép NULL. Trong phát biểu SQL tạo bảng, chỉ cần khai báo NULL hay NOT NULL sau kiểu dữ liệu của cột. Trong giao diện đồ họa chỉ cần đánh dấu chọn vào tuỳ chọn Not NULL. 3.1.3. INDEXES MySQl sử dụng INDEXES để giải quyết việc tìm kiếm thông tin. Nếu lƣợng thông tin đƣợc lƣu trữ trong bảng lớn, bằng cách sử dụng hệ thống chọn lọc bên trong MySQl sẽ giúp tìm nhanh và chính xác, nó làm đƣợc điều này nhờ sử dụng INDEXES. MySQL yêu cầu INDEX trong mỗi bảng. Thông thƣờng, sử dụng khóa chính, hoặc tạo ra sự duy nhất để giữ dữ liệu riêng lẻ. Trƣờng này phải “not null” và “unique” 3.1.4. UNIQUE Sử dụng UNIQUE để thể hiện tính duy nhất, không thể chèn thêm dữ liệu, khi thêm vào chƣơng trình sẽ báo lỗi. 3.1.5. Tăng tự động (auto Increment) auto_increment là khái niệm cực kỳ quan trọng trong MySQL (tƣơng đƣơng với Identity trong SQL Server, Autonumber trong MS Access). Khi muốn một cột có giá trị tăng tự động nhƣ AutoNumber/Identity, nên định nghĩa cột đó nhƣ auto_increment. Khi sử dụng auto_increment làm số tăng tự động thì kiểu dữ liệu là số nguyên hoặc số nguyên lớn. Trong trƣờng hợp, khai báo số tự động trong MySQL Server, cần phải khai báo thêm các thông số nhƣ seed. Seed là giá trị khởi đầu khi MySQL Server tự động tăng giá trị, Increament là bƣớc tăng, nó cho biết mỗi lần tăng cần bao nhiêu giá trị. Ví dụ khi tạo auto_increment cho cột ItemID [Int] auto_increment, nghĩa là bắt đầu số 1 và mỗi lần tăng 1 số. Kết quả sẽ có là 1,2,3,4, n. Trong phát biểu SQL của MySQL, để tạo bảng có gá trị tăng tự động chỉ cần khai báo tên cột, kiểu dữ liệu Int (Integer) và auto_increment nhƣ sau: IDNO Int auto_increment NOT NULL 3.1.6. Các kiểu bảng của MySQL và kỹ thuật lƣu trữ MySQL hỗ trợ nhiều kiểu bảng dữ liệu hoặc các máy lƣu trữ khác nhau để giúp chúng ta tối ƣu hóa CSDL của mình. Các kiểu bảng dữ liệu trong MySQL gồm: 1. ISAM 2. MyISAM 161
3. InnoDB 4. BDB 5. MERGE 6. HEAP Đặc điểm quan trọng nhất để phân biệt các kiểu bảng dữ liệu ở trên là tính có an toàn giao tác hoặc không. Chỉ các bảng dữ liệu kiểu InnoDB và BDB là có tính an toàn giao tác (transaction) và chỉ những bảng dữ liệu kiểu MyISAM hỗ trợ chỉ mục toàn văn bản (full text index) và các đặc tính tìm kiếm. MyISAM cũng là kiểu bảng dữ liệu mặc định khi tạo bảng dữ liệu mới mà không khai báo kiểu bảng dữ liệu cụ thể. Dƣới đây là những đặc điểm chính của từng kiểu bảng dữ liệu: ISAM: ISAM bị loại khỏi các phiên bản từ 5.x trở đi. Nó đƣợc thay thế bởi MyISAM. Một bảng dữ liệu kiểu ISAM có dung lƣợng tối đa 4GB và không thể di chuyển. MyISAM: Kiểu MyISAM là mặc định khi tạo ra một bảng dữ liệu mới. Các thao tác trên bảng dữ liệu kiểu này diễn ra rất nhanh, tuy nhiên nó lại không hỗ trợ đặc tính an toàn giao tác. Dung lƣợng của một bảng dữ liệu kiểu MyISAM phụ thuộc và hệ điều hành. Bảng dữ liệu kiểu MyISAM có thể chuyển từ hệ thống này sang hệ thống khác. Với bảng dữ liệu kiểu MyISAM có thể có tới 64 khóa và chiều dài tối đa của khóa là 1024byte. InnoDB: Khác với bảng dữ liệu kiểu MyISAM, bảng dữ liệu kiểu InnoDB có đặc tính an toàn giao tác và hỗ trợ khóa dòng (row level locking). Các khóa ngoại đƣợc hỗ trợ trong kiểu InnoDB. Tập tin dữ liệu của bảng dữ liệu kiểu InnoDB có thể lƣu trữ ở nhiều file khác nhau. Vì thế dung lƣợng của bảng InnoDB phụ thuộc vào dung lƣợng của ổ đĩa. Giống nhƣ bảng dữ liệu kiểu MyISAM, tập tin dữ liệu của InnoDB có thể chuyển từ hệ thống này sang hệ thống khác. Điểm bất lợi của InnoDB so với MyISAM là nó cần nhiều không gian lƣu trữ. BD: BDB tƣơng tự nhƣ InnoDB ở tính an toàn. Nó hỗ trợ khóa trang (page level locking). Tuy nhiên tập tin dữ liệu và DB không thể chuyển đổi giữa các hệ thống. MERGE: Bảng dữ liệu kiểu Merge dduwwocj thêm vào để giải quyết vấn đề hạn chế của MyISAM. Nó biến nhiều bảng MyISAM thành một bảng dữ liệu vì thế những hạn chế về dung lƣợng của MyISAM không còn là trở ngại kĩ thuật. HEAP: Bảng dữ liệu kiểu Heap đƣợc lƣu trữ trong bộ nhớ. Do đó, nó là kiểu bảng đƣợc thao tác nhanh nhất. Do bởi cơ chế lƣu trữ, dữ liệu sẽ bị mất đi khi máy tính không còn nguồn điện và đối khi nó còn có thể gây ra tình trạng tràn bộ nhớ đối với máy chủ cơ sở dữ liệu. Các bảng Heap không hỗ trợ những trƣờng có kiểu AUTO_INCREMENT, BLOB và TEXT. 162
3.1.7. Lệnh và cú pháp trong MySQL MySQL là một hệ thống quản lý cơ sở dữ liệu quan hệ (RDBMS) hay còn đƣợc gọi là Relational Database Management System. RDBMS là một trong những mô hình cơ sở dữ liệu quan hệ thông dụng hiện nay, hầu hết sản phẩm cơ sở dữ liệu quan hệ hiện nay đều dựa trên chuẩn của SQL và ANSI-SQL, chẳng hạn nhƣ SQL Server, Oracle, PostgreSQL và MySQL. Điều này có nghĩa là tất cả những cơ sở dữ liệu quan hệ đều phải có những tiêu chuẩn theo cú pháp SQL =và MySQL cũng không phải là ngoại lệ. Ngôn ngữ SQL chia làm 4 loại sau: . DDL (Data Definition Language): Ngôn ngữ định nghĩa dữ liệu, dùng để tạo cơ sở dữ liệu, định nghĩa các đối tƣợng cơ sở dữ liệu nhƣ Table, Query, Views hay các đối tƣợng khác. . DML (Data Manipulation Language): Ngôn ngữ thao tác dữ liệu, dùng để thao tác dữ liệu, chẳng hạn nhƣ các phát biểu: Select, Inert, Delete, Update, . DCL: (Data Control Language): Ngôn ngữ sử dụng truy cập đối tƣợng cơ sở dữ liệu, dùng để thay đổi cấu trúc, tạo ngƣời dùng, gán quyền chẳng hạn nhƣ: Alter, Grant, Revoke, . TCL: (Transaction Control Language): Ngôn sử dụng để khai báo chuyển tác chẳng hạn nhƣ: Begin Tran, Rollback, Commit, 1) Phát biểu SQL dạng CREATE Phát biểu SQL dạng CREATE dùng để tạo cơ sở dữ liệu và những đối tƣợng của cơ sở dữ liệu trong MySQL, SQL Server, Oracle, , chúng cú pháp nhƣ sau: CREATE Database CREATE . OBJECT TYPE: Loại đối tƣợng cơ sở dữ liệu nhƣ Procedure, Table, View, . OBJECT NAME: Tên đối tƣợng trong cơ sở dữ liệu SQL nhƣ sp_IC, tblEmployer, 2) Tạo cơ sở dữ liệu - Create database Để tạo cơ sở dữ liệu trên MySQL hay SQL Server sử dụng cú pháp sau: CREATE DATABASE Cú pháp đầy đủ của phát biểu tạo cơ sở dữ liệu nhƣ sau CREATE DATABASE [ ON [PRIMARY] ( [Name= ,] FileName= [, SIZE= ] [, MAXSIZE= ][, FILEGROWTH = ] 163
)] [ LOG ON ( [Name= ,] FileName= [, SIZE= ] [, MAXSIZE= ][, FILEGROWTH = ] )] [COLLATE ] [For Load | For Attach] ON: Dùng để định nghĩa nơi chứa cơ sở dữ liệu và không gian chứa tập tin log. . NAME: Dùng định nghĩa tên của cơ sở dữ liệu. Tên này dùng tham chiếu khi gọi đến cơ sở dữ liệu, tên đƣợc dùng cho quá trình backup, export, Import, Shrink cơ sở dữ liệu đó. . FILENAME: Tên tập tin cơ sở dữ liệu lƣu trong đĩa cứng, thông thƣờng khi cài SQL Server lên ổ đĩa nào thì giá trị mặc định cho phép lƣu tập tin đến thƣ mục đó. Tuy nhiên, nếu muốn cũng có thể thay đổi vị trí các file này. Khi tạo cơ sở dữ liệu, đã định nghĩa vị trí đặt tập tin ở thƣ mục nào thì không thể di chuyển một cách thủ công (nhƣ dùng Explorer của Windows), vì làm điều đó thật nguy hiểm nhất là khi dữ liệu trong cơ sở dữ liệu đang có giá trị kinh tế. . SIZE: Dung lƣợng của cơ sở dữ liệu khi khởi tạo chúng. Thông thƣờng giá trị mặc định là 1 MB. . Dung lƣợng phải là số nguyên, có thể tăng thêm bằng cách sử dụng thủ tục Shrink trong SQL Server. . MAXSIZE: Dung lƣợng lớn nhất, khi dung lƣợng cơ sở dữ liệu tăng lên đến mức MaxSize thì dừng lại. Nếu khi dung lƣợng bằng MaxSize, các chuyển tác có thể bị huỷ bỏ hay trả về lỗi không thể thực hiện đƣợc, và có thể làm cho cơ sở dữ liệu bị treo. Để tránh điều này xảy ra, thì ngƣời quản trị cơ sở dữ liệu phải thƣờng xuyên theo giỏi quá trình tăng dung lƣợng cơ sở dữ liệu theo thời gian, để có biện pháp tránh mọi rủi ro có thể xảy ra. . FILEGROWTH: Dung lƣợng khởi tạo cùng dung lƣợng tối đa cho phép tăng trong quá trình thêm dữ liệu vào cơ sở dữ liệu. Nhằm tự động hóa, chúng ta phải thiết lập quá trình tăng tự động theo chỉ số KB cho trƣớc hay tỷ lệ phần trăm theo dung lƣợng đang có. . LOG ON: Log on cho phép quản lý những chuyển tác xảy ra trong quá trình sử dụng cơ sở dữ liệu của SQL Server. 164
Xây dựng cơ sở dữ liệu Test Nhƣ đã trình bày ở trên, sau đây ví dụ tạo cơ sở dữ liệu Test có cú pháp nhƣ sau. USE master GO CREATE DATABASE Test ON ( NAME = Test, FILENAME = 'c:\mssql7\data\Testdat.mdf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ) LOG ON ( NAME = 'Testlog', FILENAME = 'c:\mssql7\data\Testlog.ldf', SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB ) GO Để đơn giản hoá các đối tƣợng Table trong cơ sở dữ liệu Test, chúng ta chỉ trình bày một vài phát biểu SQL dạng Create Table Ví dụ: Tạo một số bảng trong Test /* Tạo bảng danh sách khách hàng thường xuyên */ CREATE TABLE tblcustomers ( CustID int(3) unsigned NOT NULL auto_increment, Username varchar(20) NOT NULL DEFAULT '' , Password varchar(10) NOT NULL DEFAULT '' , CustName varchar(50) , Address varchar(100) , Tel varchar(20) , FaxNo varchar(10) , Email varchar(50) , Contact varchar(50) , CountryCode char(3) , ProvinceCode char(3) , PRIMARY KEY (CustID), INDEX CustID (CustID) ); 165
/* Tạo bảng hợp đồng mua hàng qua mạng */ CREATE TABLE tblorders ( OrderID int(3) NOT NULL auto_increment, OrderDate date , CustID int(11) , Description varchar(100) DEFAULT '0' , TranID tinyint(3) DEFAULT '0' , PaymentID tinyint(3) DEFAULT '0' , Amount float DEFAULT '0' , ShipCost float DEFAULT '0' , TotalAmount float DEFAULT '0' , PRIMARY KEY (OrderID), INDEX OrderID (OrderID) ); /* Tạo bảng hợp đồng chi tiết mua hàng qua mạng */ CREATE TABLE tblorderdetails ( ItemID int(3) unsigned DEFAULT '0' , OrderID int(3) unsigned DEFAULT '0' , No tinyint(3) unsigned DEFAULT '0' , Qtty int(3) unsigned DEFAULT '0' , Price int(3) unsigned DEFAULT '0' , Discount int(3) unsigned DEFAULT '0' , Amount bigint(3) unsigned DEFAULT '0' ); 3) Một số quy định khi thiết kế Table Tên cột - Column Name Đặt tên cột cũng giống nhƣ đặt tên bảng, có rất nhiều quy tắc đặt tên (nhƣ đã trình bày ở trên phần table), nhƣng khuyến khích nên theo một số quy tắc cơ bản sau: . Tên cột bắt đầu chữ hoa, còn lại bằng chữ thƣờng. . Tên ngắn gọn và đầy đủ ý nghĩa. . Không nên đặt tên cột có khoảng trắng, sau này sẽ gặp những phiền toái khi tham chiếu đến cột đó. . Không đặt tên cột trùng với những từ khoá, từ dành riêng, và những ký tự đặc biệt nhƣ những phép toán hay toán tử khác. . Nên đặt tên cột cùng tên những cột có quan hệ với những bảng khác trong cùng cơ sở dữ liệu, giúp dễ hiểu và tránh bị nhầm lẫn. Kiểu dữ liệu - Data type 166
Nhƣ đã trình bày các lại dữ liệu trong phần trên, khi xây dựng cơ sở dữ liệu, tất cả những trƣờng trong bảng cần phải có kiểu dữ liệu cụ thể. Vấn đề quan trọng là chọn kiểu dữ liệu nào cho phù hợp với dữ liệu mà ngƣời dùng sẽ nhập vào. Để thiết kế dữ liệu phù hợp với thực tế, ngoài tính ứng dụng hợp với ngữ cảnh cũng cần quan tâm đến kiểu dữ liệu tƣơng thích và chiều dài của từng cột. [CustID] [varchar] (10) /* hay */ [CustID] int Giá trị mặc định - Default Thông thƣờng khi tạo ra một cột trong bảng đôi khi chúng ta cần áp dụng giá trị mặc định, không chỉ cho trƣờng hợp số liệu không nhập từ bên ngoài mà còn cho các cột tự động có giá trị tự sinh. Với những lý do nhƣ vậy, chúng ta cần có một số giá trị mặc định cho những cột cần thiết. . Nếu cột đó là số chúng ta có giá trị mặc định là 0 . Nếu cột đó là ngày tháng chúng ta có giá trị mặc định là ngày nào đó (nhƣ 0000-00-00 là CurDate()) . Nếu cột đó có giá trị là 0 hoặc 1, có thể khai báo giá trị mặc định là 0 hoặc 1 . Nếu cột đó là chuỗi chúng ta có giá trị mặc định nhƣ là 'A' 4) Thay cấu trúc đối tƣợng bằng ALTER Khi chúng ta cần thiết phải sửa đổi một phần cấu trúc của các đối tƣợng nhƣ table (view, hay SP trong SQL Server) vì mục đích nào đó, thì sử dụng phát biểu ALTER để thay đổi cấu trúc của đối tƣợng hiện có: ALTER Khi một bảng tồn tại trong cơ sở dữ liệu, do nhu cầu cần thiết phải thay đổi cấu trúc bảng, sử dụng phát biểu ALTER TABLE cùng các tham số của chúng nhƣ cú pháp sau: ALTER TABLE table alteration [,alteration] Chẳng hạn, có thể sử dụng phát biểu ALTER TABLE để thêm một cột tên Activate với kiểu dữ liệu TinyInt có giá trị mặc định là 1. Ví dụ: Thêm một cột tên Activate vào bảng tblOrders ALTER TABLE tblorders ADD Activate TINYINT DEFAULT "1" Khi thay đổi thiết lập giá trị mặc định cho cột nên quan tâm đến giá trị mặc định đó có phù hợp cho những bản ghi đang tồn tại hay không. Muốn thay đổi giá trị mặc định của cột cho những bản ghi đang tồn tại, sử dụng đến mệnh đề phụ nhƣ trong ví dụ sau: 167
Ví dụ: Thiết lập giá trị mặc định trong bảng tblOrders ALTER TABLE tblorders CHANGE OrderDate OrderDate DATETIME DEFAULT "0000-00-00" Thay đổi kiểu dữ liệu từ Date dang DateTime, có thể khai báo nhƣ ví dụ sau: Ví dụ: Thay đổi kiểu dữ liệu ALTER TABLE tblorders CHANGE OrderDate OrderDate DATE DEFAULT "0000-00-00 00:00:00" 5) Phát biểu SQL dạng DRO Drop là phát biểu thực hiện phép xoá. DROP dùng để xoá đối tƣợng của cơ sở dữ liệu nhƣ bảng, cơ sở dữ liệu, Cú pháp của phát biểu DROP: DROP [, n] Có thể xoá cơ sở dữ liệu, bằng cách khai báo nhƣ sau: Drop Database Test /* Phát biểu DROP TABLE chỉ rõ bảng nào cần xoá, nếu xoá nhiều bảng thì bạn cần dùng dấu phẩy (,) */ DROP TABLE tblCustomers, tblSuppliers 6) Phát biểu SQL dạng SELECT a) Khái niệm cơ bản về Select Phát biểu Select dùng để truy vấn dữ liệu từ một hay nhiều bảng khác nhau, kết quả trả về là một tập bản ghi thoả các điều kiện cho trƣớc nếu có, cú pháp của phát biểu SQL dạng SELECT: SELECT [FROM ] [WHERE ] [GROUP BY ] [HAVING ] [ORDER BY ] [LIMIT FromNumber | ToNumber] Danh sách các cột: Khai báo các tên cột, biểu thức kết hợp giữa các cột của Table cần truy vấn. Trong trƣờng hợp có hai cột cùng tên của hai Table trong phát biểu, cần phải chỉ định tên Table đi trƣớc. Chẳng hạn, nhƣ ví dụ sau. Ví dụ: Phát biểu SELECT Select ItemID,ItemName From tblItems Where Cost>100; 168
Select tblOrders.OrderID,OrderDate,ItemID,Qtty From tblOrders,tblOrderDetails Where tblOrders.OrderID = _ tblOrderDetail.OrderID; b) Phát biểu SELECT với mệnh đề FROM Phát biểu SQL dạng SELECT là một trong những phát biểu yêu cầu MySQL truy lục dữ liệu trên cơ sở dữ liệu chỉ định. SELECT dùng để đọc thông tin từ cơ sở dữ liệu theo những trƣờng quy định, hay những biểu thức cho trƣờng đó. Mệnh đề FROM chỉ ra tên một bảng hay những bảng có quan hệ cần truy vấn. Sau khi thực thi phát biểu SQL, kết quả trả về số bản ghi và tổng số bản ghi đƣợc lấy ra từ bảng. Dấu * cho phép lọc bản ghi với tất cả các trƣờng trong bảng, nếu muốn chỉ rõ những trƣờng nào cần lọc cần nêu tên cụ thể những trƣờng đó. Chúng ta sử dụng một phần cơ sở dữ liệu có sẵn của MySQL, đồng thời bổ sung thêm cơ sở dữ liệu dành cho ứng dụng bán hàng qua mạng có tên là Test, và bao gồm nhiều bảng. Bằng phát biểu SELECT chúng ta có thể biết số bảng hay đối tƣợng khác đang có trong cơ sở dữ liệu Test Ví dụ: Thực thi phát biểu SQL SELECT hệ thống show tables from Test /* Hiển thị tất cả tên bảng của cơ sở dữ liệu hiện hành */ Ghi chú: Có thể sử dụng phát biểu SQL trên để hiển thị những đối tƣợng trong cơ sở dữ liệu, bằng cách thay thế các tham số và điều kiện. Cú pháp: Select * From tablename /* Lọc tất cả số liệu của tất cả các cột (field) của tablename*/ Select field1,field2 From tablename /* Lọc tất cả số liệu của 2 field: field1, field2 của tablename*/ Select * From tablename Limit 0,10 /* Lọc top 10 bản ghi đầu tiên của tất cả các field của tablename*/ Select field1, field2 From tablename Limit 0,10 /* Lọc top 10 bản ghi đầu tiên của 2 fields field1, field2 của tablename*/ Ví dụ: Phát biểu phát biểu SQL dạng Select Select * From tblCountries /* Liệt kê tất cả các quốc gia trong bảng tblCountries hoặc bạn có thể liệt kê tên như phát biểu sau */ Select CountryName From tblCountries 169
c) Phát biểu SQL dạng SELECT với mệnh đề Where Khi dùng mệnh đề WHERE để tạo nên tiêu chuẩn cần lọc bản ghi theo tiêu chuẩn đƣợc định nghĩa, thông thƣờng WHERE dùng cột (trƣờng) để so sánh với giá trị, cột khác, hay biểu thức chứa cột (trƣờng) bất kỳ có trong bảng. Phát biểu SQL dạng Select với mệnh đề Where cú pháp có dạng nhƣ sau: Select * from tablename where conditions Select field1, field2, field3 from tablename where conditions Với conditions trong cả hai phát biểu trên đƣợc định nghĩa điều kiện truy vấn nhƣ khai báo sau: Select * From tablename where field1>10 select * from tblCountries where CountryCode in('VNA','CHN') Các phép toán so sánh trong conditions bao gồm: > : lớn hơn where Amount > 100000; = : lớn hơn hoặc bằng where Amount >= 100000; >= : nhỏ hơn hoặc bằng where Amount : Khác where CustID 100000 And CustID='12'; Or : Phép toán "or" SELECT * FROM tblOrderDetails Where Amount!>100000 Or CustID=„12‟; Not : Phép toán phủ định (not) SELECT * FROM tblOrders where OrderDate is not null; Not in : Phép toán phủ định (not in) 170
SELECT * FROM tblOrders where OrderID not in („12‟,‟15‟); Between: Kết quả thuộc trong miền giá trị SELECT * FROM tblOrders Where Amount between 10 And 500; Like : Phép toán so sánh gần giống, sử dụng dấu % để thể hiện thay thế bằng ký tự đại diện SELECT * FROM tblCustomers where CustName like '%A'; Not Like : Phép toán phủ định so sánh gần giống, sử dụng dấu % để thể hiện thay thế bằng ký tự đại diện SELECT * FROM tblCustomers where CustName not like '%A'; IN : Phép toán so sánh trong một tập hợp SELECT * FROM tblOrders Where OrderID in ('100','200','300'); Ví dụ: SQL dạng SELECT và Where /* > : lớn hơn */ Select * From tblOrders Where Amount > 100000; /* = : lớn hơn hoặc bằng */ Select * From tblOrders Where Amount >= 100000; /* >= : nhỏ hơn hoặc bằng */ Select * From tblOrders Where Amount : Khác */ 171
Select * From tblOrders Where CustID : Không lớn hơn */ Select * From tblOrders Where Amount !> 100000; /* ! 100000 And CustID=„12‟; /* Or : Phép toán hoặc */ Select * From tblOrders Where Amount !>100000 Or CustID=„12‟; /* Not : Phép toán phủ định */ Select * From tblOrders Where OrderDate is NOT NULL; /* Between: giá trị nằm trong miền */ Select * From tblOrders Where Amount Between 10 and 500; /* Like : Phép toán so sánh gần giống, sử dụng % để thay thế bất kỳ ký tự */ Select * From tblOrders Where Descriion like '%A' Or CustID ='152'; /* Not Like : Phép toán phủ định so sánh gần giống, sử dụng dấu % để thể hiện thay thế bất kỳ ký tự */ Select * From tblOrders Where Descriion not like '%A' Or CustID ='152'; /* IN : Phép toán so sánh trong một tập hợp */ Select * From tblOrders Where OrderID in ('134','244','433'); /* Not IN : Phép toán phủ định so sánh trong một tập hợp */ Select * From tblOrders Where OrderID not in ('134','244','433'); d) Mệnh đề Order by 172
Thông thƣờng, trong khi truy vấn bản ghi từ bảng dữ liệu, kết quả hiển thị cần sắp xếp theo chiều tăng hay giảm dựa trên ký tự ALPHABET. Nhƣng cũng có thể sắp xếp theo một tiêu chuẩn bất kỳ, chẳng hạn nhƣ biểu thức. Khi sắp xếp dữ liệu trình bày trong kết quả, cần phải chọn trƣờng hay biểu thức theo trật tự tăng dần hoặc giảm dần. Cú pháp cho mệnh đề ORDER BY cùng với trạng thái tăng hay giảm, ứng với ASC sắp xếp tăng dần, DESC giảm dần. Cú pháp có dạng nhƣ sau: Order by columnname DESC Order by columnname1 + columnname2 DESC Order by columnname ASC Order by columnname1 ASC, columnname2 DESC Ví d: SELECT với mệnh đề Order by DESC /* Giảm dần theo thời gian */ Select OrderID , OrderDate, CustID, Amount From tblOrders Where Amount >1000 Order by OrderDate DESC Ví dụ: SQL dạng SELECT với mệnh đề Order by và ASC /* Tăng dần theo thời gian */ Select OrderID , OrderDate, CustID, Amount From tblOrders Where Amount >1000 Order by OrderDate ASC Nếu muốn sắp xếp theo nhiều cột (trƣờng), chỉ cần sử dụng dấu phẩy (,) để phân cách các cột. Ví dụ: SELECT với mệnh đề Order by với 2 cột dữ liệu Select OrderID , OrderDate, CustID, Amount From tblOrders Where Amount >1000 Order by OrderID,CustID DESC Nếu muốn sắp xếp theo nhiều trƣờng kết hợp, chỉ cần dùng thứ tự từng cột cách nhau bằng dấu +. Ví dụ: SELECT với mệnh đề Order by hợp 2 cột /* Giảm dần theo số OrderID và CustID */ Select OrderID , OrderDate, CustID, Amount From tblOrders Where Amount >1000 Order by OrderID + CustID DESC 173
Nếu trong phát biểu SQL dạng SELECT có nhiều bảng kết hợp lại với nhau, có thể dùng thêm tên bảng ứng với cột của bảng đó. Phần này sẽ đƣợc diễn giải cụ thể hơn trong phần kế tiếp (JOIN -Phép hợp). e) SQL dạng SELECT với mệnh đề GROUP BY Khi truy vấn bản ghi trên một hay nhiều bảng dữ liệu, thông thƣờng có những nghiệp vụ thuộc trƣờng nào đó có cùng giá trị, ví dụ khi hiển thị hợp đồng phát sinh trong tháng, kết quả sẽ có nhiều hợp đồng của khách hàng lặp đi lặp lại. Ví dụ: SQL dạng SELECT với mệnh đề Order by Select CustID, Amount from tblOrders Trong báo cáo chúng ta lại cần phải biết mỗi khách hàng có bao nhiêu lần trả tiền, tổng số tiền của mỗi khách hàng đã trả là bao nhiêu? Để làm điều này, chúng ta sử dụng mệnh đề GROUP BY trong phát biểu SQL dạng SELECT cùng với một số hàm trong MySQL, nhƣng nhóm bản ghi bằng mệnh đề Group By. Ví dụ: SQL dạng SELECT với mệnh đề Group By Select CustID, count (CustID), Sum(Amount) From tblOrders Group by CustID Order by CustID f) Phát biểu SQL dạng Select với AS Khi cần thiết phải thay đổi tên trƣờng trong câu truy vấn, chỉ cần dùng phát biểu AS. AS cho phép ánh xạ tên cũ, hay giá trị chƣa có tên thành tên mới (header). Ví dụ, khi sử dụng GROUP BY, những cột tạo ra từ các phép toán count, sum, max, min, cho ra kết quả không có header, nghĩa là không có tên cột để tham chiếu trong khi gọi đến chúng. Chúng ta phải cần phát biểu AS cho những trƣờng hợp này. Ví dụ: SQL dạng SELECT với AS và các hàm Select CustID, Count (CustID) as No, Sum(Amount) as TIENHD, Max(Amount) as HDLONNHAT, Min(Amount) as HDNHONHAT, Avg(Amount) as TRUNGBINH From tblOrders Group by CustID Order by CustID g) Phát biểu SQL dạng Select với Limit N , M Phát biểu SQL dạng SELECT cho phép truy lục chỉ một số bản ghi tính từ vị trí thứ n đến vị trí thứ m trong Table (theo một tiêu chuẩn hay sắp xếp nào đó). Để làm 174
điều này, trong phát biểu SQL dạng SELECT dùng chỉ định từ khoá LIMIT với số lƣợng bản ghi cần lấy từ vị trí thứ n đến m. Chẳng hạn, trong trƣờng hợp khai báo Select * from tblOrders limit 0,10. Kết quả sẽ trả về 10 bản ghi đầu tiên trong bảng tblOrders. Cũng có thể sử dụng kết hợp LIMIT với các mệnh đề nhƣ WHERE, ORDER BY nhằm tạo ra kết quả nhƣ ý muốn. Do yêu cầu khác nhau thông qua phát biểu SQL dạng SELECT có sử dụng LIMIT, nghĩa là kết quả trả về số lƣợng 10 bản ghi đầu tiên với tất cả các cột trong bảng tblOrders Ví dụ: Phát biểu SQL dạng SELECT với Limit N,M Select * From tblOrders Limit 0,10 Nếu muốn lọc ra 10 hợp đồng có số tiền nhiều nhất, chỉ cần sử dụng sắp xếp theo cột TotalAmount hay Amount trong bảng tblOrders. Ví dụ: Phát biểu SQL dạng SELECT với Limit N,M Select OrderID,OrderDate,CustID,Amount From tblOrders Order by Amount Desc Limit 0,10 Nếu muốn lọc ra 10 sản phẩm có số lƣợng bán nhiều nhất, chỉ cần sử dụng sắp xếp theo cột số lƣợng Qtty. Ví dụ: Phát biểu SQL dạng Select với Limit N,M Select ItemID,Qtty,Price,Amount from tblOrderDetails Where Amount>10 order by Qtty Limit 0,10 h) Phát biểu SQL dạng SELECT với DISTINCT Nếu có một hay nhiều bảng kết nối với nhau, sẽ xảy ra trùng lặp nhiều bản ghi. Nhƣng trong trƣờng hợp này chỉ cần lấy ra một bản ghi trong tập bản ghi trùng lặp, sử dụng phát biểu SQL dạng SELECT với chỉ định DISTINCT. Ví dụ: Phát biểu SQL dạng SELECT Select ItemID,Qtty,Price,Amount from tblOrderDetails order by Qtty Ví dụ: Phát biểu SQL dạng SELECT với DISTINCT Select Distinct ItemID,Qtty,Price,Amount From tblOrderDetails 175
Order by Qtty 7) Nhập dữ liệu bằng phát biểu SQL dạng Insert Khi thêm bản ghi vào bảng trong cơ sở dữ liệu MySQL, có nhiều cách để thực hiện công việc này. Tuy nhiên, để sử dụng các phát biểu SQL mang tính chuyên nghiệp trong MySQL, cần sử dụng phát biểu INSERT. Có thể sử dụng phát biểu Insert ngay trên ứng dụng kết nối với MySQL. Khi thêm dữ liệu, cần chú ý kiểu dữ liệu giống hoặc tƣơng ứng kiểu dữ liệu đã khai báo của cột đó, nếu không phù hợp thì lỗi sẽ phát sinh. Ngoài ra cần quan tâm đến quyền của User đang truy cập cơ sở dữ liệu. User phải đƣợc cấp quyền Insert dữ liệu vào từng bảng cụ thể (quyền này do nhà quản trị cơ sở dữ liệu phân quyền cho User đó). Trong phát biểu INSERT INTO chúng ta thực hiện trên bảng tblOrderDetails và bảng tblOrderDetailsHist, hai bảng này có cấu trúc nhƣ sau: /* Bảng tblOrderDetails*/ CREATE TABLE tblorderdetails ( ItemID int(3) unsigned DEFAULT '0' , OrderID int(3) unsigned DEFAULT '0' , No tinyint(3) unsigned DEFAULT '0' , Qtty int(3) unsigned DEFAULT '0' , Price int(3) unsigned DEFAULT '0' , Discount int(3) unsigned DEFAULT '0' , Amount bigint(3) unsigned DEFAULT '0' ); /* Bảng tblOrderDetailsHist, dùng để chứa các thông tin hợp đồng chi tiết khi hợp đồng của khách hàng này kết thúc, chương trình tự động xoá trong tblOrderDetails và lư trữ lại trong bảng tblOrderDetailsHist.*/ CREATE TABLE tblorderdetailshist ( ItemID int(3) unsigned DEFAULT '0' , OrderID int(3) unsigned DEFAULT '0' , No tinyint(3) unsigned DEFAULT '0' , Qtty int(3) unsigned DEFAULT '0' , Price int(3) unsigned DEFAULT '0' , Discount int(3) unsigned DEFAULT '0' , Amount bigint(3) unsigned DEFAULT '0' ); 176
Khi Insert dữ liệu vào bảng, có 3 trƣờng hợp xảy ra: insert dữ liệu vào bảng từ các giá trị cụ thể, insert vào bảng lấy giá trị từ một hay nhiều bảng khác, và cuối cùng là kết hợp cả hai trƣờng hợp trên. Insert vào bảng lấy giá trị cụ thể: INSERT INTO [ ] Values (data_value) Ví dụ: INSERT dữ liệu vào bảng từ giá trị cụ thể /* Thêm bản ghi với một số cột */ INSERT INTO TBLCUSTOMERS (CustName,Username,Password, Address,Tel,FaxNo,Email,Contact, CountryCode,ProvinceCode) Values ('Khach San CENTURY', „century‟, ‟1111‟,‟5 Le Loi‟,‟8676767‟,‟8767676‟, „century@yahoo.com‟,‟Hoang Anh‟, „VNA‟,‟HCM‟) /* Thêm bản ghi với một số cột */ INSERT INTO TBLORDERS (OrderID,OrderDate, CustID,Description,Amount) Values ('11',curdate(),‟1', 'Dat hang qua mang', 20000) Insert vào bảng lấy giá trị từ bảng khác: INSERT INTO [ ] Select [columnname list] From Where Ví dụ: INSERT vào bảng từ giá trị của bảng khác /* Thêm bản ghi với các cột cụ thể */ /* Chuyển tất cả những hợp đồng chi tiết từ bảng tblOrderDetails vào bảng tblOrderDetailsHist */ INSERT INTO TBLORDERDETAILSHIST( ItemID, OrderID, No, Qtty, Price, Discount, Amount) SELECT ItemID, OrderID, No, Qtty, Price, Discount, Amount From tblOrderDetails ORDER BY OrderID ASC /* Có thể viết lại thêm bản ghi với tất cả các cột như sau 177
Chuyển tất cả những hợp đồng chi tiết từ bảng tblOrderDetails vào bảng tblOrderDetailsHist với điều kiện số cột tương ứng trong bảng tblOrderDetails bằng với số cột trong bảng tblOrderDetailsHist, bạn có thể viết lại như sau */ INSERT INTO TBLORDERDETAILSHIST SELECT * from tblOrderDetails ORDER BY OrderID ASC Insert vào bảng lấy giá trị cụ thể, bảng khác: INSERT INTO [ ] Select [columnname list], valueslist From Where ORDER BY ASC/DESC Ví dụ: INSERT vào bảng từ giá trị cụ thể, bảng khác /* Thêm bản ghi với các cột cụ thể */ /* Chuyển tất cả những hợp đồng chi tiết từ bảng tblOrderDetails vào bảng tblOrderDetailsHist. Giả sử rằng, ngoài những cột giống như tblOrderDetails, bảng tblOrderDetailsHist còn có thêm cột Tranferdate. */ INSERT INTO TBLORDERSHIST( OrderID, OrderDate, ReceiveFolio, CustID, Descriion, Amount, Historydate) SELECT OrderID, OrderDate, ReceiveFolio, CustID, Descriion, Amount, getdate() as Historydate From tblOrders where Month(OrderDate)=12 Order by OrderDate,CustID /* Có thể viết lại thêm bản ghi với tất cả các cột như sau */ /* Chuyển tất cả những phiếu thu trong tháng 12 từ bảng tblOrders vào bảng tblOrdersHist với điều kiện số cột tương ứng trong bảng tblOrders bằng với số cột trong bảng tblOrdersHist, bạn có thể viết lại như sau */ INSERT INTO TBLORDERDETAILSHIST( ItemID, OrderID, No, Qtty, Price, Discount, Amount,TranferDate) SELECT ItemID,OrderID, No, Qtty, Price, Discount, Amount,CurDate() From tblOrderDetails ORDER BY OrderID ASC 8) Phát biểu SQL dạng UPDATE 178
Phát biểu SQL dạng UPDATE dùng cập nhật lại dữ liệu đã tồn tại trong bảng. Khi UPDATE dùng cập nhật dữ liệu cho một bản ghi chỉ định nào đó thƣờng UPDATE sử dụng chung với mệnh đề WHERE. Nếu cần cập nhật tất cả các bản ghi trong bảng có thể bỏ mệnh đề WHERE. Phát biểu này có cấu trúc nhƣ sau: /* nếu cập nhất giá trị cụ thể */ Update Set = ,[ = ] [where ] /* nếu cập nhất giá trị là kết quả trả về từ phát biểu select trên một hay nhiều bảng khác */ Update Set = [where ] UPDATE có thể ảnh hƣởng đến nhiều bảng, nhƣng cập nhất giá trị chỉ có hiệu lực trên bảng đó. Ví dụ: UPDATE trên các cột dữ liệu từ giá trị cụ the /* cập nhật cột với giá trị cụ thể */ Update tblCustomers Set CustName='Cong ty TNHH Coca cola Vietnam' Where CustID=„12‟ /* cập nhật một cột với giá trị cột khác trong bảng tblOrderDetails*/ Update tblOrders Set Amount= Amount*.01, TotalAmount=Amount*0.1 Where Month(OrderDate)=12 /* cập nhật một cột với giá trị từ bảng khác*/ /* cập nhật cột Price với giá trị từ cột Cost của bảng tblItems, khai báo sau chỉ đúng trong MySQL 4.1 trở về sau*/ Update tblOrderDetails Set Price= (select distinct Cost] from tblItems where ItemID=tblOrderDetails.ItemID) Where Price<1000 /* cập nhật một cột với giá trị cụ thể với điều kiện từ bảng khác, , khai báo sau chỉ đúng trong MySQL 4.1 trở về sau */ Update tblOrderDetails Set Price= Price*10, Amount= Qtty*(Price+1) 179
Where ItemID in (select distinct ItemID from tblOrderDetails where Price>1000) 9) Phát biểu SQL dạng DELETE Với phát biểu SQL dạng DELETE thì đơn giản hơn. Khi thực hiện lệnh xoá bản ghi trong bảng chúng ta chỉ cần quan tâm đến tên bảng, và mệnh đề WHERE để xoá với những bản ghi đã chọn lọc nếu có. Cú pháp của Delete: Delete from Where Với mệnh đề WHERE giống nhƣ bất kỳ mệnh đề WHERE nào trong phát biểu SELECT hay UPDATE và INSERT của bất kỳ ứng dụng cơ sở dữ liệu nào có sử dụng SQL. Conditions có thể là phép toán giữa các cột và giá trị, nhƣng cũng có thể giá trị là kết quả trả về từ một phát biểu SELECT khác. Ghi chú: Không có khái niệm xóa giá trị trong một cột, vì xóa giá trị một cột đồng nghĩa với cập nhật cột đó bằng giá trị rỗng. Ví dụ: Xóa bản ghi với phát biểu SQL dạng DELETE /* Xoá bản ghi từ bảng với điều kiện */ Delete from tblCustomers Where CustName is null Trong trƣờng hợp có ràng buộc về quan hệ của dữ liệu, thì xóa bản ghi phải tuân thủ theo quy tắc: Xoá bản ghi con trƣớc rồi mới xoá bản ghi cha. Chẳng hạn, trong trƣờng hợp ta có 2 bảng: hợp đồng bán hàng (tblOrders) và hợp đồng bán hàng chi tiết (tblOrderDetails). Để xoá một hợp đồng cần xóa bản ghi trong bảng tblOrders trƣớc rồi mới đến các bản ghi trong bảng tblOrderDetails. Ví dụ: Xoá bản ghi với Delete /* Xoá bản ghi từ bảng con */ Delete from tblOrderDetails where OrderID=123 /* Xoá bản ghi từ bảng cha */ Delete from tblOrders where OrderID=123 Có thể thực hiện một phát biểu SQL dạng DELETE với điều kiện trong mệnh đề WHERE lấy giá trị trả về từ phát biểu SELECT từ bảng khác, khai báo nhƣ vậy chỉ có hiệu lực trong cơ sở dữ liệu MySQL phiên bản 8.1 trở về sau hay trong cơ sở dữ liệu SQL Server và Oracle. 180
Ví dụ: Xoá bản ghi theo quy tắc có ràng buộc quan hệ /* Xoá bản ghi từ bảng với điều kiện lấy giá trị từ bảng khác */ Delete from tblOrderDetails where ItemID in (select ItemID from tblItems where ItemName like 'IT%') 3.1.8. Các hàm cơ bản trong MySQL a) Các hàm trong phát biểu GROUB BY . Hàm AVG: Hàm trả về giá trị bình quân của cột hay trƣờng trong câu truy vấn, ví dụ nhƣ phát biểu sau: Select AVG(Amount) From tblOrders . Hàm MIN: Hàm trả về giá trị nhỏ nhất của cột hay trƣờng trong câu truy vấn, ví dụ nhƣ phát biểu sau: Select Min(Amount) From tblOrders . Hàm MAX: Hàm trả về giá trị lớn nhất của cột hay trƣờng trong câu truy vấn, ví dụ nhƣ các phát biểu sau: Select Max(Amount) From tblOrders . Hàm Count: Hàm trả về số lƣợng bản ghi trong câu truy vấn trên bảng, ví dụ nhƣ các phát biểu sau: Select count(*) From tblOrders Select count(CustID) From tblOrders Select count(*) From tblOrderDetails . Hàm Sum: Hàm trả về tổng các giá trị của trƣờng, cột trong câu truy vấn, ví dụ nhƣ các phát biểu sau: Select sum(Amount) From tblOrders Chẳng hạn, có thể tham khảo diễn giải toàn bộ các hàm dùng trong mệnh đề GROUP BY. Ví dụ: SQL dạng SELECT với Group By và các hàm Select CustID, Count (CustID),Sum(Amount), Max(Amount), Min(Amount), Avg(Amount) From tblOrders Group by CustID Order by CustID b) Các hàm xử lý chuỗi . Hàm ASCII: Hàm trả về giá trị mã ASCII của ký tự bên trái của chuỗi, ví dụ nhƣ khai báo: Select ASCII('TOI') Kết quả trả về: 84 181
. Hàm Char: Hàm này chuyển đổi kiểu mã ASCII từ số nguyên sang chuỗi. Select char(35) Kết quả trả về: # . Hàm UPPER: Hàm này chuyển đổi chuỗi sang kiểu chữ hoạ: Select UPPER('Khang') Kết quả trả về: KHANG . Hàm LOWER: Hàm này chuyển đổi chuỗi sang kiểu chữ thƣờng: Select LOWER('Khang') Kết quả trả về: khang . Hàm Len: Hàm này trả về chiều dài của chuỗi: Select len('I Love You') Kết quả trả về : 10 . Thủ tục LTRIM: Thủ tục loại bỏ khoảng trắng bên trái của chuỗi: Select ltrim(' Khang') Kết quả trả về : 'khang' . Thủ tục RTRIM: Thủ tục loại bỏ khoảng trắng bên phải của chuỗi: Select ltrim('Khang ') Kết quả trả về : 'khang' . Hàm Left: Hàm trả về chuỗi bên trái tính từ đầu cho đến vị trí thứ n: Select left('Khang',3) Kết quả trả về : 'Kha' . Hàm Right: Hàm trả về chuỗi bên phải tính từ cuối cho đến vị trí thứ n: Select Right('KHang',4) Kết quả trả về : 'Hang' . Hàm Instr: Hàm trả về vị trí chuỗi bắt đầu của chuỗi con trong chuỗi xét: Select INSTR ('Khang','Pham Huu Khang') Kết quả trả về : 11 (11 là tƣơng đƣơng vị trí thứ 11 của chữ Khang trong chuỗi "Pham Huu Khang") c) Các hàm về xử lý thời gian . Hàm CurDate(): Hàm trả về ngày, tháng và năm hiện hành của hệ thống: Select curdate() as 'Today is‟ . Hàm CurTime(): Hàm trả về giờ, phút và giây hiện hành của hệ thống: Select curtime() as 'Time is‟ . Hàm Period_Diff: Hàm trả về số ngày trong khoảng thời gian giữa 2 ngày: Select Period_diff (OrderDate, getdate()) as 'So ngay giua ngay thu tien đen hom nay:' 182
from tblOrders . Hàm dayofmonth: Hàm dayofmonth trả về ngày thứ mấy trong tháng: Select dayofmonth(curdate()) as 'hom nay ngay d) Các hàm về toán học . Hàm sqrt: Hàm trả về là căn bật hai của một biểu thức: Select sqrt (4) . Hàm Round: Hàm trả về là số làm tròn của một biểu thức: Select round (748.58,-1) 3.2. Làm việc với MySQL và PHP 3.2.1. Các hàm cơ bản làm việc giữa PHP và MySQL 1) Các hàm kết nối đến MySQL Server . mysql_connect () : hàm này sẽ tạo ra một liên kết tới máy chủ MySQL. Cú pháp : int mysql_connect (string [hostname [:port] [:/path_to_socket]], string [username], string [password]); - hostname : Tên máy chủ cơ sở dữ liệu, nơi trang web sẽ chứa cơ sở dữ liệu. Giá trị ngầm định là “localhost‟” - :port : Địa chỉ cổng, nơi bộ máy cơ sở dữ liệu lắng nghe yêu cầu. Giá trị ngầm định là “:3306” . - :/path_to_socket : Cũng giống nhƣ :port nhƣng chỉ cho hệ điều hành UNIX. Giá trị ngầm định là “:/tmp/mysql.sock” . - username : Tên của ngƣời sử dụng đƣợc phép kết nối vào cơ sở dữ liệu. - password : Mật khẩu của ngƣời sử dụng để kết nối vào bộ máy cơ sở dữ liệu. Hàm này trả về mã số nhận dạng nếu kết nối thành công, giá trị 0 (false) nếu việc kết nối có lỗi. Mã số nhận dạng này sẽ đƣợc sử dụng cho tất cả các yêu cầu tới bộ máy cơ sở dữ liệu sau này. Kết nối sẽ đóng lại khi gọi hàm mysql_close() hoặc kết thúc đoạn PHP script. . mysql_pconnect() : Hàm này tạo một liên kết bền vững với máy chủ MySQL. Cú pháp : int mysql_pconnect (string [hostname [:port] [:/path_to_socket]], string [username], string [password]); Tham số và giá trị trả về của hàm này cũng giống hàm mysql_connect(). Sự khác biết giữa hai hàm này là liên kết tới máy chủ MySQL không bị đóng lại kể cả khi kết thúc kịch bản (script) PHP hay gọi hàm mysql_close(). Mục đích của hàm này là luôn luôn duy trì liên kết tới máy chủ MySQL do luôn có sự yêu cầu tới máy chủ, tránh cho máy chủ phải tìm kiếm mã số nhận dạng mới từ đó giảm thời gian truy cập . 183
. mysql_close() : Hàm này huỷ bỏ sự kết nối tới máy chủ MySQL . Cú pháp : int mysql_close(int [link_identifier]); Tham số link_identifier là mã số nhận dạng tạo ra bởi hàm mysql_connect(). Hàm trả về là True nếu thành công, ngƣợc lại là False . 2) Các hàm thao tác trên cơ sở dữ liệu . mysql_create_db() : Hàm tạo cơ sở dữ liệu Cú pháp : int mysql_create_db(string name, int [link_identifier]) ; Trong đó : - string name : Tên của cơ sở dữ liệu cần tạo. - int link_identifier : Mã số nhận dạng đƣợc cấp bởi hàm mysql_connect() . . mysql_drop_db() : Hàm xoá cơ sở dữ liệu Cú pháp : int mysql_drop_db(string name, int [link_identifier]); Trong đó : - string name : Tên của cơ sở dữ liệu cần xoá . - int link_identifier : Mã số nhận dạng đƣợc cấp bởi hàm mysql_connect() . . mysql_select_db() : Hàm cho cơ sở dữ liệu hoạt động . Cú pháp : int mysql_select_db(string database_name, int [link_identifier]); Trong đó: - database_name : Tên của cơ sở dữ liệu mà sau này các hàm API khác của PHP sẽ thực hiện trên đó. - int link_identifier : Mã nhận dạng đƣợc cấp bởi hàm mysql_connect(). 3) Các hàm thao tác trên dữ liệu . mysql_query() : Hàm gửi câu lệnh SQL tới máy chủ MySQL . Cú pháp : int mysql_query(string query, [int link_identifier]) ; Trong đó : - string query : Câu lệnh SQL cần gửi tới máy chủ MySQL . - int link_identifier : Mã số nhận dạng, nó phải đƣợc thực hiện trong hàm mysql_select_db() trƣớc đó . . mysql_db_query() : Hàm gửi câu lệnh SQL tới máy chủ MySQL . Cú pháp : int mysql_db_query(string database, string query, int [link_identifier]); Trong đó : 184
- string database : Tên cơ sở dữ liệu câu lệnh SQL sẽ thực hiện trên đó. - string query : Câu lệnh SQL cần thực hiện . - link_identifier : Mã số nhận dạng đƣợc cấp bởi hàm mysql_connect() Hàm này chỉ rõ câu lệnh đƣợc thực hiện trên cơ sở dữ liệu nào nên trƣớc đó không cần thực hiện hàm mysql_select_db(); . mysql_insert_id() : Hàm lấy giá trị đƣợc sinh ra từ câu truy vấn INSERT trƣớc Cú pháp : int mysql_insert_id([link_identifier]) ; Trong đó: - int link_identifier : Mã số nhận dạng đƣợc cấp bởi hàm mysql_connect() . Hàm này trả về giá trị id đƣợc sinh ra trong cột AUTO_INCREMENT bởi câu truy vấn trƣớc đó. Điều này chỉ có tác dụng trên link_identifier đƣợc chỉ ra trong hàm, nếu gọi hàm trên mà không chỉ định tham số link_identifier thì liên kết đƣợc mở cuối cùng sẽ đƣợc chỉ định. Hàm mysql_insert_id() trả về giá trị 0 nếu câu truy vấn trƣớc đó không sinh ra một giá trị AUTO_INCREMENT. Nếu ta muốn giữ lại giá trị cho lần sau, thì phải gọi hàm này ngay sau câu truy vấn sinh ra giá trị . . mysql_fetch_row() Hàm trả về một mảng là giá trị của một bản ghi hiện tại với chỉ số là số thứ tự của các trƣờng (chỉ số bắt đầu từ 0). Sau đó hàm sẽ trỏ tới bản ghi tiếp theo cho tới khi gặp bản ghi cuối cùng hàm trả về giá trị false. Để truy xuất tới các giá trị của cột ta viết : tên_mảng[số thứ tự] array mysql_fetch_row( int result_identifier); Trong đó: result_identifier là mã số trả về của hàm mysql_query() hoặc mysql_db_query() . Ví dụ : . mysql_fetch_array() 185
Hàm trả về một mảng là giá trị của một bản ghi hiện tại, sau đó hàm sẽ trỏ tới bản ghi tiếp theo cho tới khi gặp bản ghi cuối cùng hàm trả về giá trị false. Cú pháp : array mysql_fetch_array( int result_identifier [, int result_type] ); Trong đó : result_identifier là mã số trả về của hàm mysql_query() hoặc mysql_db_query() . Để truy xuất đến các thành phần của cột. tên_biến_mảng[“tên_trƣờng”]; - result_type là một hằng số có thể nhận các giá trị sau: - MYSQL_NUM : chỉ trả lại một mảng chứa các chỉ số là số (giống nhƣ hàm mysql_fetch_row() ) - MYSQL_ASSOC: chỉ trả lại một mảng liên kết - MYSQL_BOTH : trả lại mảng chứa đựng các chỉ số gồm cả các con số và chỉ số liên kết . Hàm này là sự mở rộng của hàm mysql_fetch_row(). Nó cho phép truy cập trƣờng dữ liệu của mảng kết quả không chỉ thông qua các chỉ số là các số mà chúng có thể là tên của các trƣờng dữ liệu. Ví dụ: \n”; echo “user_id: “. $row[0] .“ \n”; echo “user_name: “. $row[“name”] .“ \n”; echo “user_name: “. $row[1] .“ \n”; } mysql_free_result ($result); ?> . mysql_fetch_object() Hàm trả về một đối tƣợng là giá trị của một bản ghi hiện thời. Sau đó hàm sẽ trỏ tới bản ghi tiếp theo cho tới khi gặp bản ghi cuối cùng hàm trả về giá trị false. Để truy xuất tới các giá trị của cột ta viết tên_object->tên_cột . Cú pháp : object mysql_fetch_object(int result_identifier); 186
Trong đó : result_identifier là mã số trả về của hàm mysql_query() hoặc mysql_db_query() . Ví dụ : id ; echo $row->name; } ?> . mysql_fetch_assoc() : Lấy về một dòng kết quả nhƣ là một mảng liên kết . Cú pháp: array mysql_fetch_assoc(int result_identifier) Trong đó : result_identifier là mã số trả về của hàm mysql_query() hoặc mysql_db_query() . Hàm trả về một mảng tƣơng ứng với một bản ghi đƣợc lấy về và trả lại FALSE nếu không có bản ghi nào. Hàm này tƣơng đƣơng với hàm array mysql_fetch_array() với tham số result_type là : MYSQL_ASSOC Ví dụ : . mysql_data_seek() Di chuyển con trỏ bên trong “tập kết quả” (có đƣợc sau khi câu truy vấn SELECT) Cú pháp: bool mysql_data_seek(int result_identifier, int row_number); - result_identifier là mã số trả về của hàm mysql_query(), mysql_db_query(), mysql_list_tables(), mysql_list_dbs() . - row_number là chỉ số của bản ghi mà cần đặt con trỏ vào . Hàm trả về true nếu thành công, false nếu lỗi . 187
Hàm này sẽ di chuyển con trỏ bên trong “tập kết quả” (đƣợc chỉ rõ bởi tham đối result_identifier) đến dòng có mã bằng tham đối row_number. Ví dụ: =0; $i ) { if (! Mysql_data_seek ($result, $i)) { printf ("Cannot seek to row %d\n", $i); continue; } if(!($row = mysql_fetch_object ($result))) continue; printf ("%s %s \n", $row->last_name, $row->first_name); } mysql_free_result ($result); ?> . mysql_num_rows() Trả lại số dòng trong result_identifier (nơi chứa kết quả của câu lệnh SQL đã đƣợc thực hiện) Cú pháp: mysql_num_rows(int result_identifier) ; Trong đó : result_identifier là mã số trả về của hàm mysql_query(), mysql_db_query(), mysql_list_tables(), mysql_list_dbs() . . mysql_affected_rows() Cú pháp : int mysql_affected_rows(int [link_identifier]) ; Trong đó :int link_identifier là mã số nhận dạng, nó phải đƣợc thực hiện trong hàm mysql_select_db() trƣớc đó . Hàm trả về số dòng đã bị tác động bởi một câu truy vấn SQL :INSERT, UPDATE, DELETE trƣớc đó theo tham số link_identifier. Nếu link_identifier không đƣợc chỉ định thì mã kết nối trƣớc đó sẽ đƣợc chỉ định. 188
- Nếu câu lệnh SQL trƣớc đó là DELETE không có mệnh đề WHERE thì toàn bộ các bản ghi trong bảng đã bị xoá nhƣng hàm mysql_affected_rows() sẽ trả về gián trị 0. - Hàm này không có tác dụng đối với câu lệnh truy vấn SELECT. Để lấy đƣợc số dòng trả về (số dòng đã bị tác động) bởi câu lệnh SELECT ta dùng hàm mysql_num_rows(). . mysql_result() : Lấy dữ liệu từ result_identifier Cú pháp : mixed mysql_result(int result_identifier, int row, mixed [field]); Trong đó : - result_identifier là mã số trả về của hàm mysql_query(), mysql_db_query(), mysql_list_tables(), mysql_list_dbs() . - row là bản ghi mà ta sẽ lấy dữ liệu - field là trƣờng trong dòng row mà ta sẽ lấy dữ liệu . Các tham số result_identifier và row phải có, còn tham field là tùy chọn. Hàm sẽ trả lại các nội dung của dòng row và cột field từ tập kết quả đƣợc chỉ định bởi biến result_identifier. Nếu đối số field không đƣợc chỉ định rõ thì trƣờng tiếp theo của bản ghi sẽ đƣợc trả về . Ví dụ: \n”; echo “ mysql_result($result, 0, “name”) \n”; ?> . mysql_free_result() Hàm giải phóng vùng bộ nhớ đƣợc liên kết với result_identifier . Cú pháp: mysql_free_result(int result_identifier) ; Trong đó : result_identifier là mã số trả về của hàm mysql_query(), mysql_db_query(), mysql_list_tables(), mysql_list_dbs() . Hàm này chỉ đƣợc dùng nếu nhƣ đánh giá thấy rằng kịch bản sử dụng quá nhiều bộ nhớ khi đang chạy. Gọi hàm này trên một trình xử lý kết quả sẽ giải phóng toàn bộ dữ liệu liên kết trong bộ nhớ . string mysql_tablename (int result_identifier, int i): Hàm trả lại tên của bảng/cơ sở dữ liệu tại chỉ số i trong result_identifier. 189
string mysql_field_name (int result_identifier, int field_index): Hàm trả lại tên của trƣờng tại vị trí field_index trong mã result_identifier int mysql_list_dbs ([int link_identifier]): Hàm trả lại một result_identifier là danh sách biến CSDL trên MySQL Server nếu thành công, lỗi trả về false . int mysql_list_tables (string database [, int link_identifier]): Hàm trả về danh sách tất cả câc bảng trong một CSDL MySQL, thành công trả về một result identifier, giá trị false nếu có lỗi int mysql_list_fields (string database_name, string table_name [, int link_identifier]): Hàm trả về thông tin liên quan đến một bảng dữ liệu. int mysql_num_fields (int result_identifier): Trả về số trƣờng trong tập kết quả . int mysql_num_rows (int result_identifier): Trả về số bản ghi trong tập kết quả, hàm này chỉ có giá đối với các câu lệnh SELECT ,để lấy lại số bản ghi đƣợc trả lại từ các lệnh :INSERT, UPDATE hoặc DELETE, dùng mysql_affected_rows(). string mysql_field_type (int result_identifier, int field_index): Hàm trả về kiểu dữ liệu của trƣờng tại vị trí field_index trong mã result_identifier . int mysql_field_len (int result_identifier, int field_offset): Hàm trả về độ dài của trƣờng đƣợc chỉ định thông qua tham số field_offset . array mysql_fetch_lengths (int result_identifier): Trả về một mảng tƣơng ứng với các độ dài của mỗi trƣờng trong bản ghi đƣợc lấy về bởi hàm mysql_fetch_row() hoặc false nếu có lỗi. int mysql_errno ([int link_identifier]): Hàm trả về mã lỗi từ hàm thao tác CSDL MySQL trƣớc ,trả về giá trị 0 nếu không có lỗi . string mysql_error ([int link_identifier]): Hàm trả về xâu thông báo lỗi từ hàm thao tác CSDL MySQL trƣớc, trả về xâu rỗng nếu không có lỗi . object mysql_fetch_field (int result_identifier [, int field_offset]): Lấy thông tin về trờng từ tập kết quả rồi trả lại nh một đối tƣợng. 3.2.2. Kết nối MySQL Server Để kết nối cơ sở dữ liệu mySQL sử dụng khai báp nhƣ sau: Trong đó khai báo sau là kết nối cơ sở dữ liệu mySQL với tên server/ip cùng với username và password: mysql_connect ("localhost", "root", "") 190
Và mysql_select_db("TestDB", $link); để chọn tên cơ sở dữ liệu sau khi mở kết nối cơ sở dữ liệu, nếu biến $link có giá trị là false thì kết nối cơ sở dữ liệu không thành công. Sau khi mở kết nối cơ sở dữ liệu mà không sử dụng thì có thể đóng kết nối cơ sở dữ liệu với cú pháp nhƣ sau: mysql_close($link); Chẳng hạn, khai báo trang connection.php để kết nối cơ sở dữ liệu và đóng kết nối ngay sau khi mở thành công. ::Welcome to PHP and mySQL Mo va dong ket noi CSDL MySQL 3.2.3. Các thao tác với cơ sở dữ liệu 1) Thêm bản ghi Để thêm bản ghi, sử dụng hàm mysql_query (chuỗi Insert). Chẳng hạn, chúng ta khai báo trang insert.php để thêm bản ghi vào bảng tblships có hai cột dữ liệu là ShipID và ShipName nhƣ ví dụ trong trang insert.php. ::Welcome to PHP and mySQL Them mau tin <?php require("dbcon.php"); $sql="insert into tblships values('A01','Testing')"; $result = mysql_query($sql,$link); $affectrow=0; 191
if($result) $affectrow=mysql_affected_rows(); mysql_close($link); ?> So mau tin them vao Trong đó, sử dụng hàm mysql_query với hai tham số $sql và $link. Kết quả trả về là số bản ghi thực thi. Ta sử dụng kết nối cơ sở dữ liệu trong tập tin dbcon.php nhƣ sau: Trong trƣờng hợp cho phép ngƣời sử dụng thêm bản ghi thì thiết kế form yêu cầu ngƣời sử dụng nhập hai giá trị sau đó submit đến trang kế tiếp để thực thi việc thêm giá trị vừa nhập vào cơ sở dữ liệu nhƣ hình sau. Hình 3.1. Thêm bản ghi Để làm điều này, trƣớc tiên khai báo trang them.php, trong đó khai báo đoạn javascript để kiểm tra dữ liệu nhập nhƣ sau: function checkInput() { if (document.frmPHP.txtID.value=="") { alert("Invalid ID, Please enter ID"); document.frmPHP.txtID.focus(); 192
return false; } if (document.frmPHP.txtName.value=="") { alert("Please enter Name"); document.frmPHP.txtName.focus(); return false; } return true; } Kế đến khai báo thể form và hai thẻ input lại text yêu cầu ngƣời sử dụng nnập ID và Name nhƣ sau: Please enter ID and Name ID: Name: 193
Lƣu ý rằng, khai báo số ký tự lớn nhất cho phép nhập bằng với kích thƣớc đã khai báo trong cơ sở dữ liệu ứng với thuộc tính maxlength. Khi ngƣời sử dụng nhập hai giá trị và nhấn nút submit, trang kế tiếp đựơc triệu gọi. Trang này lấy giá trị nhập bằng cách sử dụng biến form hay $HTTP_POST_VARS. Đối với trƣờng hợp này chúng ta sử dụng biến form nhƣ trang doinsert.php. ::Welcome to PHP and mySQL Them mau tin So mau tin them vao 2) Cập nhật bản ghi Trƣờng hợp cập nhật bản ghi, cũng sử dụng hàm mysql_query với phát biểu Update thay vì Insert nhƣ trên, ví dụ chúng ta khai báo trang update.php để cập nhật bản ghi trong bảng tblShips với tên là UpdateTesting khi mã có giá trị là A01. ::Welcome to PHP and mySQL Cap nhat mau tin <?php require("dbcon.php"); 194
$sql="Update tblships set ShipName='UpdateTesting' "; $sql.=" where ShipID='A01'"; $result = mysql_query($sql,$link); $affectrow=0; if($result) $affectrow=mysql_affected_rows(); mysql_close($link); ?> So mau tin cap nhat Lƣu ý rằng, để biết số bản ghi đã thực thi bởi phát biểu SQL sử dụng hàm mysql_affected_rows. if($result) $affectrow=mysql_affected_rows(); Tƣơng tự nhƣ trên, có thể thiết kế form cho phép ngƣời sử dụng cập nhật dữ liệu bằng cách thiết kế form yêu cầu ngƣời sử dụng nhập mã và tên. Hình 3.2. Thực thi trang cập nhật dữ liệu Sau khi ngƣời sử dụng nhấn nút submit, trang doupdate.php sẽ triệu gọi, kết quả trả về 1 hay 0 bản ghi. ::Welcome to PHP and mySQL Cap nhat mau tin <?php $affectrow=0; require("dbcon.php"); $sql="update tblships set ShipName='"; 195
$sql .=$txtName."' where ShipID='".$txtID."'"; $result = mysql_query($sql,$link); if($result) $affectrow=mysql_affected_rows(); mysql_close($link); ?> So mau tin cap nhat 3) Xoá bản ghi Tƣơng tự nhƣ vậy khi xoá bản ghi, chỉ thay đổi phát biểu SQL dạng Delete nhƣ ví dụ trong tập tin delete.php. ::Welcome to PHP and mySQL Xoa mau tin So mau tin da xoa Đối với trƣờng hợp xoá thì đơn giản hơn, chỉ cần biết đƣợc mã cần xoá, vì vậy trong trƣờng hợp này chúng ta chỉ cần thiết kế trang cho phép nhập mã nhƣ hình. 196
Hình 3.3. Thực thi xoá một bản ghi Sau khi nhập mã cần xoá, nếu ngƣời sử dụng nhấn nút Delete lập tức trang dodelete.php sẽ triệu gọi và xoá bản ghi tƣơng ứng. ::Welcome to PHP and mySQL Xoa mau tin So mau tin xoa 3.2.4. Truy vấn cơ sở dữ liệu Để truy vấn dữ liệu sử dụng hàm mysql_num_rows để biết số bản ghi trả về, hàm mysql_fetch_array đọc từng bản ghi vào mảng sau đó trình bày giá trị từ mảng. Chẳng hạn, chúng ta tạo một tập tin lietke.php dùng để liệt kê danh sách bản ghi trong bảng tblShips nhƣ hình sau. 197
Hình 3.4. Liệt kê bản ghi Để làm điều này, khai báo đoạn chƣơng trình đọc bảng dữ liệu nhƣ ví dụ sau: Sau đó, dùng hàm mysql_fetch_array để đọc từng bản ghi và in ra nhƣ sau: 0) { $i=0; while ($row = mysql_fetch_array ($result)) { $i+=1; ?> Trong trƣờng hợp số bản ghi trả về là 0 thì in ra câu thông báo không tìm thấy. 198
Oop! Ship not found! Xử lí dữ liệu dạng mảng Để xoá nhiều bản ghi cùng một lúc, trƣớc tiên ta khai báo trang PHP để liệt kê danh sách bản ghi trong mảng dữ liệu chằng hạn, mỗi bản ghi xuất hiện một checkbox tƣơng ứng. Checkbox này có giá trị là mã nhận dạng của bản ghi đó. Trong trƣờng hợp này chúng ta dùng cột khoá của mã chuyển hàng (SubCateID) trong bảng tblSubCategories định nghĩa trong trang lietke.php. Hình 3.5. Liệt kê danh sách lại sản phẩm Để cho phép lấy đƣợc nhiều giá trị chọn của sản phẩm nhƣ hình trên, khai báo các checkbox này cùng tên (giả sử tên là chkid) và giá trị trị là SubCateID của mỗi sản phẩm nhƣ ví dụ trong trang lietke.php sau: 0) { $i=0; while ($row = mysql_fetch_array ($result)) { $i+=1; 199
?> "> Tong so mau tin Oop! Ship not found! Trong đó, hai khai báo sau: 200
Cho ta biết submit từ trang nào và loại xoá nhiều bản ghi hay một bản ghi đối với bảng tƣơng ứng. Mục đích của vấn đề này là trang delete sử dụng chung cho nhiều bảng khác nhau và từ trang liệt kê (xoá nhiều) hoặc từ trang edit (1 bản ghi cụ thể). Ngoài ra, chúng ta khai báo để nhận giá trị chọn trên cách checkbox bằng cách khai báo đoạn javascript nhƣ sau: function calculatechon(){ var strchon=""; var alen=document.frmList.elements.length; var buttons=1; alen=(alen>buttons)?document.frmList.chkid.length:0; if (alen>0) { for(var i=0;i Tuy nhiên, do nhiều loại sản phẩm thuộc các nhóm sản phẩm khác nhau, chính vì vậy khai báo danh sách nhóm sản phẩm trên thẻ select cho phép ngƣời sử dụng liệt kê sách theo nhóm sản phẩm nhƣ hình. 201
Hình 3.6. Liệt kê danh sách loại sách Để liệt kê danh sách nhóm trong bảng tblCategories, bằng cách khai báo phƣơng thức nhận chuỗi SQL dạng Select và giá trị mặc định trả về nhiều phần tử thẻ option trong tập tin database.php nhƣ ví dụ sau: function optionselected($stSQL,$item,$links) { $results = mysql_query($stSQL, $links); $totalRows=mysql_num_rows($results); $strOption=" "; $strOption .=" Select "; if($totalRows>0) { while ($row = mysql_fetch_array ($results)) { $strOption .=" ".$row["Name"]; $strOption .=" "; } } return $strOption; } Sau đó, gọi phương thức này trong trang lietketheonhom.php như ví dụ 10-3. <?php 202
require("dbcon.php"); require("database.php"); $id=""; if (isset($cateid)) $id=$cateid; $stSQL ="select CateID As ID, CateName as Name from tblCategories "; $result = mysql_query($stSQL, $link); $totalRows=mysql_num_rows($result); $strOption=optionselected($stSQL,$id,$link); ?> Category: Lần đầu tiên có thể chọn mặc định một nhóm hoặc liệt kê tất cả, khi ngƣời sử dụng chọn nhóm sản phẩm nào đó thì trang lietketheonhom.php sẽ liệt kê danh sách loại sách của nhóm sách đó. Để làm điều này ta khai báo thẻ form với thẻ select nhƣ ví dụ sau: Category: Khi ngƣời sử dụng chọn các bản ghi và nhấn nút Delete, dựa vào giá trị của nút có tên action (trong trƣờng hợp này là Delete), có thể khai báo biến để lấy giá trị chọn bằng cách khai báo nhƣ sau: $strid=$chon $strid=str_replace(",","','",$strid); Dựa vào thẻ hidden khai báo trong các trang trình bày danh sách (chẳng ạn lietketheonhom.php) bản ghi nhƣ sau: 203
Có thể biết từ trang nào gọi đến trang dodelete.php để quay trở về khi thực hiện xong tác vụ xử lý. Ngoài ra, dựa vào giá trị của nút action để thực hiện phát biểu SQL. Chẳng hạn, trong trƣờng hợp này nếu ngƣời sử dụng nhấn hút Delete thì khai báo nhƣ ví dụ sau: switch($strfrom) { case "subcategories": $stSQL ="delete from tblsubcategories where SubCateID in('".$strid."')"; $strlocation="Location:lietketheonhom.php"; break; case "categories": $stSQL ="delete from tblcategories where CateID in('".$strid."')"; $strlocation="Location:nhom.php"; break; } Sau đó, có thể thực thi phát biểu SQL vừa khai báo ở trên nhƣ sau: if($stSQL!="") { $result = mysql_query($stSQL, $link); } Lƣu ý rằng, cũng nên khai báo try catch trong khi làm việc với cơ sở dữ liệu. Ngoài ra, cũng phải xác nhận trƣớc khi thực thi hành động xoá bản ghi chọn bằng cách khai báo đoạn Javascript nhƣ sau: function isok() { return confirm('Are you sure to delete?'); } Sau đó gọi trong biến cố onsubmit của form như sau: Tƣơng tự nhƣ trƣờng hợp Delete, khi duyệt một số bản ghi theo một cột dữ liệu nào đó, chẳng hạn, trong trƣờng hợp này cho phép sử dụng những sản phẩm đã qua sự đồng ý của nhà quản lý thì cột dữ liệu Activate của bảng tbltems có giá trị là 1. Để làm điều này, trƣớc tiên ta liệt kê danh sách sản phẩm nhƣ hình. 204
Hình 3.7. Liệt kê danh sách sản phẩm duyệt hay chưa Tƣơng tự nhƣ trong trƣờng hợp delete, khai báo trang doUpdate nhƣ sau: ::Welcome to PHP and mySQL Cap nhat mau tin "") { switch($strfrom) { case "items": $stSQL ="update tblItems set Activate=1 where ItemID in('".$strid."')"; break; } if($stSQL!="") { 205
$result = mysql_query($stSQL, $link); if($result) $affectrow=mysql_affected_rows(); mysql_close($link); } } ?> So mau tin cap nhat 3.3. Xây dựng một số trang web kết hợp PHP và MySQL 3.3.1. Xây dựng trang đăng nhập Trƣớc hết chúng ta phải thiết kế và xây dựng mô hình cơ sở dữ liệu ứng với từng lệnh bên dƣới sau đó mới tiến hành triển khai lập trình. user( id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, username VARCHAR(50) NOT NULL, password CHAR(50) NOT NULL, level CHAR(1) NOT NULL, PRIMARY KEY(id)); Chèn một dòng dữ liệu vào trong bảng user: insert into user(username,password,level) values ("admin","12345","2") ("abc","12345","1"); Vậy là chúng ta đã có cơ sở dữ liệu nhƣ mô hình trên. Tiếp đến chúng ta thiết kế Form HTML để có màn hình đăng nhập khi ngƣời dùng truy cập. Hình 3.8. Giao diện trang đăng nhập Username: Password: Tiếp đến chúng ta tiến hành kiểm tra dữ liệu từ form "; 206
} else{ $u=$_POST['username']; } if($_POST['password'] == NULL){ echo "Please enter your password "; } else { $p=$_POST['password']; } } ?> Đoạn code ở trên sẽ kiểm tra xem ngƣời dùng có tiến hành nhấn nút đăng nhập hay không. Và nếu có thì chúng ta sẽ xét tiếp tình trạng ngƣời dùng có để trống các ô username và password hay không. Nếu có chúng ta sẽ thông báo lỗi ở bên trên form, để ngƣời sử dụng tiến hành nhập liệu. Kế đến chúng ta kiểm tra xem có tồn tại hai biến $u và $p (chỉ khi ngƣời dùng đăng nhập thành công thì mới có thể tạo ra 2 biến đó). Tiếp đến chúng ta tiến hành kết nối cơ sở dữ liệu. Kiểm tra username và password ngƣời sử dụng vừa nhập có trùng khớp với thông tin có trong cơ sở dữ liệu hay không?. Nếu không thì chúng ta sẽ báo lỗi ngay. Ngƣợc lại sẽ tiến hành lấy dữ liệu từ bảng và gán vào session. Để có thể quản lý phiên làm việc một cách hiệu quả trên mọi trang của khu vực admin. <?php if($u && $p){ $conn=mysql_connect("localhost","root","root") or die("can't connect this database"); mysql_select_db("project",$conn); $sql="select * from user where username='".$u."' and password='".$p."'"; $query=mysql_query($sql); if(mysql_num_rows($query) == 0){ 207
echo "Username or password is not correct, please try again"; } else{ $row=mysql_fetch_array($query); session_start(); session_register("userid"); session_register("level"); $_SESSION['userid'] = $row[id]; $_SESSION['level'] = $row[level]; } } ?> Nhƣ vậy code hoàn chỉnh cho ứng dụng này là nhƣ sau: "; } else{ $u=$_POST['username']; } if($_POST['password'] == NULL){ echo "Please enter your password "; } else{ $p=$_POST['password']; } if($u && $p){ $conn=mysql_connect("localhost","root","root") or die("can't connect this database"); mysql_select_db("project",$conn); $sql="select * from user where username='".$u."' and password='".$p."'"; $query=mysql_query($sql); if(mysql_num_rows($query) == 0){ echo "Username or password is not correct, please try again"; } 208
else{ $row=mysql_fetch_array($query); session_start(); session_register("userid"); session_register("level"); $_SESSION['userid'] = $row[id]; $_SESSION['level'] = $row[level]; }}} ?> Username: Password: 3.3.2. Xây dựng trang thêm thành viên Sau khi đăng nhập vào trang admin ngƣời dùng sẽ đƣợc đẩy sang trang quản lý admin, tại đây chỉ những ai đã đăng nhập đúng với username và password trong cơ sở dữ liệu và có quyền hạn level là 2 thì mới có thể truy xuất vào trang này. Vì vậy chúng ta cần thao tác kiểm tra session để quản lý phiên làm việc của ngƣời sử dụng. Đặt đoạn code sau ở những trang muốn kiểm tra quyền truy cập của ngƣời sử dụng. Nhƣ vậy tại trang add_user.php chúng ta cũng đặt đoạn code trên và viết thêm 1 form thực thi công việc thêm thành viên. 209
Hình 3.9. Giao diện trang thêm thành viên Level: Member Admin Username: Password: Re-Password: Tƣơng tự nhƣ những bài trƣớc , chúng ta cũng lần lƣợt kiểm tra từng đối tƣợng . Trƣớc hết cần xem thử ngƣời dùng đã nhấn nút Add New user chƣa , sau đó lại kiểm tra xem ngƣời dùng đã nhập đầy đủ thông tin chƣa. Cuối cùng là so sánh giữa password và re-password có giống nhau hay không. Tiếp theo chúng ta gán biến $l sẽ bằng với giá trị mà khi ngƣời dụng chọn level cho user đó. "; }else{ $u=$_POST['username']; } if($_POST['password'] != $_POST['re-password']){ echo "Password va re-password khong chinh xac "; } else{ if($_POST['password'] == "" ){ echo "Vui long nhap password "; } else{ 210
$p=$_POST['password']; } } $l=$_POST['level']; } ?> Kế đến chúng ta lại tiếp tục kiểm tra xem có tồn tại biến $u, $p và $l hay không (chỉ khi ngƣời dùng nhập liệu thì mới tồn tại cùng lúc 3 biến này). sau khi đã kiểm tra sự tồn tại của 3 biến này chúng ta tiếp tục dùng PHP kết nối cơ sở dữ liệu để kiểm tra xem username mà ngƣời dùng vừa chọn có trùng với username khác trong cơ sở dữ liệu chƣa. nếu trùng thì phải xuất thông báo lỗi yêu cầu họ chọn username khác còn nếu không thì ta lại tiến hành thêm thành viên mới này vào CSDL. "; } else{ $sql2="insert into user(username,password,level) values('".$u."','".$p."','".$l."')"; $query2=mysql_query($sql2); echo "Da them thanh vien moi thanh cong"; }} ?> Tới đây ta đã kết thúc thao tác thêm một thành viên mới vào cơ sở dữ liệu. Sau đây là toàn bộ code trong ứng dụng này. "; 211
} else{ $u=$_POST['username']; } if($_POST['password'] != $_POST['re-password']){ echo "Password va re-password khong chinh xac "; } else{ if($_POST['password'] == "" ){ echo "Vui long nhap password "; } else{ $p=$_POST['password']; } } $l=$_POST['level']; if($u & $p & $l){ $conn=mysql_connect("localhost","root","root") or die("can't connect this database"); mysql_select_db("project",$conn); $sql="select * from user where username='".$u."'"; $query=mysql_query($sql); if(mysql_num_rows($query) != "" ){ echo "Username nay da ton tai roi "; } else{ $sql2="insert into user(username,password,level) values('".$u."','".$p."','".$l."')"; $query2=mysql_query($sql2); echo "Da them thanh vien moi thanh cong"; }}} } ?> Level: Member 212
Admin Username: Password: Re-Password: 3.3.3. Xây dựng trang quản lý thành viên Sau khi đăng nhập vào trang admin ngƣời dùng sẽ đƣợc đẩy sang trang quản lý admin, tại đây chỉ những ai đã đăng nhập đúng với username và password trong cơ sở dữ liệu và có quyền hạn level là 2 thì mới có thể truy xuất vào trang này. Vì vậy chúng ta cần thao tác kiểm tra session để quản lý phiên làm việc của ngƣời sử dụng. Đặt đoạn code sau ở những trang muốn kiểm tra quyền truy cập của ngƣời sử dụng. Nhƣ vậy, chúng ta sẽ đặt đoạn code này ở trang quản lý user. Cụ thể đặt trang này có tên file là manage_user.php. Vì dữ liệu sẽ lặp lại toàn bộ user và ứng với từng user sẽ là 1 dòng dữ liệu đƣợc lặp lại. Chúng ta sẽ xây dựng 1 bảng gồm có STT là số thứ tự của từng user đƣợc đếm trên mỗi user khi lặp, username là tên truy cập của họ, level là cấp bậc của user (1 là member và 2 là admin), edit là cột chứa các link chỉnh sửa user, del là cột xóa các user. 213
STT Username Level Edit Del Tiếp theo, chúng ta sẽ kết nối CSDL để tiến hành lặp các user. Chua co username nao "; } ?> Đoạn code ở trên tiến hành lựa chọn tất cả user có trong database. Đồng thời kiểm tra xem trong database có tồn tại user nào không. Nếu không sẽ xuất ra thông báo "chƣa có username nào". Ngƣợc lại khi có tồn tại user chúng ta sẽ xử lý tiếp nhƣ sau: "; echo " $stt "; echo " $row[username] "; if($row[level] == "1"){ echo " Member "; } else{ echo " Admin "; } echo " "; echo " "; echo " "; 214
?> Ở đây ta dùng biến $stt để làm bộ đếm cho số lƣợng user. Nhƣ vậy, cứ mỗi lần lặp 1 user thì bộ đếm này sẽ tự động tăng lên 1 đơn vị. Sau đó, chúng ta đƣa dữ liệu vào mảng và truyền các giá trị trên vào các cột dữ liệu trong bảng. Tới đây ta đã kết thúc thao tác quản lý các user đang tồn tại trong cơ sở dữ liệu. Sau đây là toàn bộ code trong ứng dụng này. STT Username Level Edit Del Chua co username nao "; } else{ $stt=0; while($row=mysql_fetch_array($query)){ $stt++; echo " "; 215
echo " $stt "; echo " $row[username] "; if($row[level] == "1"){ echo " Member "; } else{ echo " Admin "; } echo " "; echo " "; echo " "; }} ?> 3.3.4. Xây dựng trang xoá sửa thành viên Hệ thống sửa và xóa thành viên này cũng chỉ có thể thực hiện đƣợc khi ngƣời sử dụng đăng nhập thành công với quyền hạn của 1 administrator. Do vậy, phải kiểm tra session ở đầu khi bắt đầu viết ứng dụng. Trang sửa thành viên Vì là trang chỉnh sửa thành viên, nên nội dung của chúng có phần sẽ giống với phần thêm thành viên, chỉ khác là các ô nhập liệu giờ đây đã có dữ liệu. Dữ liệu này chúng ta tiến hành lấy từ cơ sở dữ liệu thông qua biến truyền mà ở trang quản lý đã gửi edit_user.php?userid=$row[id] 216
Hình 3.10. Giao diện trang sửa thanh viên Nhƣ vậy để lấy đƣợc giá trị từ liên kết này chúng ta sẽ sử dụng biến $_GET['userid']. Sau khi đã có đƣợc giá trị này, việc còn lại là lấy thông tin của id này từ cơ sở dữ liệu và đƣa vào form để ngƣời dùng có thể chỉnh sửa. " method="post"> Level: ) >Member >Administrator Username: " /> Password: Re-password: Theo nhƣ đoạn code ở trên phần value chúng ta muốn đƣa giá trị vào thì buộc phải sử dụng cú pháp gọn hoặc cũng có thể sử dụng bằng cách Ở đây ta chỉ đƣa ra giá trị trên text box username, còn lại mật khẩu do có thể bị mã hóa nên không nên hiển thị ra. Phần level do lƣu trong cơ sở dữ liệu là những con số 1,2 nên khi đƣa ra chúng ta phải so sánh, nếu trong bảng lƣu là 1 thì ở chỗ có giá trị bằng 1 sẽ thêm chữ selected. Để mặc định lựa chọn khi ngƣời dùng nhấn vào nút chỉnh sửa. 217
Tiếp tới ngƣời dùng sẽ nhấn nút edit. Việc còn lại của chúng ta là kiểm tra dữ liệu khi họ tiến hành chỉnh sửa dữ liệu. Điểm khác biệt ở đây là chúng ta phải cân nhắc khi viết trang chỉnh sửa. Bởi không phải ai vào chỉnh sửa cũng đều sửa cả thông tin username, password, level. Có khi chỉ sửa mỗi level hoặc sửa mỗi password. Vì thế ta phải xét các trƣờng hợp để giải quyết. Trƣờng hợp 1: Ngƣời dùng chỉ chỉnh sửa username, level mà không chỉnh sửa password. Khi đó username, level bắt buộc đều đã có dữ liệu nên ta không cần kiểm tra chúng rỗng hay không. Nhƣng với password chúng ta không thể bắt ngƣời lập trình nhập liệu. Và họ hoàn toàn có thể để rỗng. Trƣờng hợp 2: Ngƣời dùng chỉnh sửa username, level ,password. Khi đó ta xem password có dữ liệu và ta tiến hành cập nhật. Để giải quyết bài toán trên thỏa 2 trƣờng hợp ta sẽ xét phƣơng pháp sau: Ta kiểm tra password và repassword có giống nhau hay không?. Nếu chúng khác nhau nghĩa là ngƣời dùng nhập liệu nhƣng nhập sai. Vậy phải cảnh báo lỗi cho họ biết là họ đã nhập sai. Còn ngƣợc lại nếu password và repassword giống nhau thì sẽ nảy sinh 2 trƣờng hợp con. + Một là password và repassword đều có dữ liệu + Hai là password và repassword không có dữ liệu. Khi đó ở trƣờng hợp con 1 chúng ta sẽ khởi tạo biến $p và ghi nhận thông tin họ nhập liệu. Và ở trƣờng hợp 2 chúng ta không ghi nhận thông tin ngƣời nhập liệu vì họ không có ý định chỉnh sửa mật khẩu. Có thể viết đoạn code đơn giản nhƣ sau: Tiếp tới chúng ta sẽ phân loại dữ liệu cập nhật. Khi có username, password, level thì ta cập nhật tất cả thông tin của họ bằng cú pháp SQL. Ngƣợc lại khi không tồn tại password thì ta chỉ cập nhật username và level. Một điều nữa chúng ta cần quan tâm đó là đối với lệnh update và delete, ta phải truyền 1 id cụ thể để tránh việc chúng xóa hoặc sửa dữ liệu toàn bộ trong bảng user. Với id truyền nhận từ bên ngoài ta sẽ có. <?php 218
if($u && $p && $l ){ $sql="update user set username='".$u."', password='".$p."', level='".$l."' where id='".$id."'"; mysql_query($sql); header("location:mana_user.php"); exit(); } else{ if($u && $l){ $sql="update user set username='".$u."', level='".$l."' where id='".$id."'"; mysql_query($sql); header("location:mana_user.php"); exit(); }} ?> Sau khi sửa thành công một thành viên chúng ta sẽ đƣa ngƣời dùng trở về với trang quản lý. Nội dung của toàn bộ code ở trên nhƣ sau: <?php $conn=mysql_connect("localhost","root","root") or die("can't connect this database"); mysql_select_db("project",$conn); $id=$_GET['userid']; if(isset($_POST['ok'])){ if($_POST['user'] == NULL){ echo "Please enter your username"; } else{ $u=$_POST['user']; } if($_POST['pass'] != $_POST['repass']){ echo "Password and re-password is not correct"; } else{ if($_POST['pass'] != NULL){ $p=$_POST['pass']; } } 219
$l = $_POST['level']; if($u && $p && $l ) $sql="update user set username='".$u."', password='".$p."', level='".$l."' where id='".$id."'"; mysql_query($sql); header("location:mana_user.php"); exit(); } else{ if($u && $l){ $sql="update user set username='".$u."', level='".$l."' where id='".$id."'"; mysql_query($sql); header("location:mana_user.php"); exit(); }}} $sql="select * from user where id='".$id."'"; $query=mysql_query($sql); $row=mysql_fetch_array($query); ?> " method="post"> Level: ) >Member >Administrator Username: " /> Password: Re-password: Trang xoá thành viên Đối với trang xóa dữ liệu, chúng ta cũng không cần phải xử lý quá nhiều. Bởi nhiệm vụcủa chúng chỉ đơn giản là xóa đi những dòng trong bảng. 220
Nhƣ vậy cũng nhƣ trang edit chúng ta nhận giá trị từ nội dung liên kết ở trang quản lý đã gửi là del_user.php?userid=$row[id]. Và thực thi lệnh kết nối cơ sở dữ liệu để xử lý nội dung này. Sau khi xóa xong thành viên thì hệ thống sẽ đƣa trở về trang quản lý user. 3.3.5. Xây dựng trang tạo mã xác nhận Trong bài này, chúng ta tìm hiểu phƣơng pháp tạo dãy số ngẫu nhiên để chống tấn công flood dữ liệu. Hoặc ai đó cố tình spam khiến cơ sở dữ liệu của chúng ta không thể xử lý tiếp đƣợc. Trƣớc tiên, chúng ta tiến hành tạo form HTML đơn giản để thực thi thao tác nhập liệu nhƣ sau: Captcha Chúng ta chú ý phần hình ảnh, tại đây truyền đƣờng dẫn chính là liên kết tới trang random_image.php. Trang này sẽ thực thi công việc tạo ra những bức hình có dãy số ngẫu nhiên để phần nhập liệu của ngƣời sử dụng tham chiếu tới. Tiếp theo, chúng ta khởi tạo file random_image.php để lấy ra dãy số ngẫu nhiên và phát sinh chúng ngay trên tấm hình cho ngƣời truy cập nhập liệu. 221
Để làm đƣợc điều đó. Chúng ta sẽ khởi tạo session và lƣu dãy số nhập liệu đó vào session của mình, sau đó so khớp với trang form bên kia. Hình 3.11. Giao diện trang tạo mã xác nhận Đầu tiên chúng ta sử dụng hàm md5 và ran để mã hóa các ký tự bao gồm số và chữ cái. Khi sử dụng hàm md5() ký tự phát sinh sẽ lên tới 32 ký tự. Và chúng ta chỉ lấy duy nhất 5 ký tự từ chuỗi mã hóa đó bằng hàm substr. Tiếp tục ta lƣu đoạn mã hóa này trong session cụ thể$_SESSION['security_code'], để tại trang form ta sẽ sử dụng so sánh với phần nhập liệu của ngƣời sử dụng. Vậy nên đoạn code dƣới sẽ giải quyết các tình huống này. Kế đến ta khởi tạo tấm hình với chiều rộng và chiều cao mà ta thiết lập thông qua hàm ImageCreate() . Và tiếp tục khai báo 2 màu chính là trắng và đen bằng hàm ImageColorAllocate($image, red, green, blue ). Hàm này sẽ tạo ra một màu sắc từ hệ màu RGB trên tấm hình mà chúng ta vừa khởi tạo. Tiếp tục ta đổ background của tấm hình sẽ là màu đen và chữ xuất hiện trên tấm hình sẽlà màu trắng bằng hàm ImageFill() và hàm ImageString(). Hàm ImageString có một số đối số. cơ bản nhƣ sau: ImageString($image, 5, 30, 6, $security_code, $white); Trong đó: + $image là hình mà chúng ta khởi tạo. + 5: là font-size mà chúng ta quy ƣớc cho ký tự xuất hiện trên hình. + 30: là khoảng cách bên trái của tấm hình. + 6 : là khoảng cách từ trên của tấm hình. + $security_code: là đoạn code sau khi chúng ta cắt ra ở trên. + $white: là màu sắc mà chúng ta đã sử dụng hàm ImageColorAllocate() ở trên để khởi tạo ra màu trắng. <?php $width = 100; 222
$height = 30; $image = ImageCreate($width, $height); $white = ImageColorAllocate($image, 255, 255, 255); $black = ImageColorAllocate($image, 0, 0, 0); ImageFill($image, 0, 0, $black); ImageString($image, 5, 30, 6, $security_code, $white); ?> Sử dụng lệnh header để trả nội dung này trở về dữ liệu dạng hình. Và tiến hành khởi tạo định dạng cho file hình mà chúng ta vừa tạo là JPG đồng thời giải phóng hình tạm đƣợc sử dụng để khởi tạo ra tấm hình này bằng đoạn code sau: Để dễ quản lý chúng ta sẽ đặt tất cả đoạn code trên vào trong 1 hàm để dễ sử dụng và quản lý. Vậy toàn bộ đoạn code trong trang random_image.php này nhƣ sau. 223
Trong trang form chúng ta khởi tạo session để nhận đƣợc các giá trị mà ta đã khởi tạo và sử dụng ở trang random_image, tiếp theo kiểm tra xem ngƣời dùng có nhấn submit chƣa . Nếu có ta sẽ tiếp tục kiểm tra xem ngƣời dùng nhập vào textbox có đúng là dãy số hiển thị trên hình ảnh hay không. Và xuất ra thông báo tƣơng ứng với nội dung của ngƣời nhập liệu. Sau đây là toàn bộ code của trang form.php. Captcha 3.3.6. Xây dựng trang đếm số ngƣời online Trong bài này, ta tìm hiều cách thức xây dựng tính năng thống kê số ngƣời đang viếng thăm website. 224
Trƣớc hết, chúng ta khởi tạo cở sở dữ liệu nhƣ sau. mysql> create table useronline(tgtmp INT(15) DEFAULT "0" NOT NULL , ip VARCHAR(50) NOT NULL , local VARCHAR(100) NOT NULL, PRIMARY KEY(tgtmp), KEY ip(ip), KEY local(local)); tgtmp là thời gian mà họ truy cập đƣợc tính khi họ truy vào trang web đó. IP là dãy số lƣu thông tin IP của họ khi viếng thăm website của chúng ta.local là nơi lƣu đƣờng dẫn mà họ đang truy cập. Khi ngƣời dùng truy cập vào trang web, chúng ta sẽ tiến hành insert thông tin của họ vào cơ sở dữ liệu với các tham số cơ bản nhƣ ngày trong database.tgtmp đƣợc tính ra bằng hàm time(). Hàm này sẽ lấy ra thời gian hiện tại của ngƣời truy cập. Tiếp tục ta lại tính thời gian mới của họ, đƣợc tính bằng việc quy ƣớc thời gian quy định. Cụ thể ở đây là 900 giây tƣơng đƣơng với 15 phút truy cập. nhƣ vậy nếu thời gian lƣu trong database mà nhỏ hơn thời gian new này thì chúng ta có thể hiểu rằng vị khách ấy đã rời khỏi website của chúng ta. Ví dụ: ngƣời khách viếng thăm website đó là 7h, nhƣ vậy hệ thống sẽ ghi thông tin lúc đó là 7h. Nếu sau một thời gian ngƣời không làm gì, hoặc không truy cập website đó nữa thì hệ thống sẽ không ghi nhận thông tin mới. Nhƣ thế nếu bây giờ 8h và trừ đi 15 phút chúng ta quy ƣớc , rõ ràng là thời gian mới hiện tại là 7h45, Thời gian này vẫn lớn hơn thời điểm lƣu thông tin 7h (7h45 > 7h). Do vậy, nếu chúng thỏa điều kiện đó thì chúng ta chỉ việc xóa đi các record trong cơ sở dữ liệu. Vậy ta có code kết nối cơ sở dữ liệu nhƣ sau: Sau đó ta tiến hành ghi nhận thông tin ngƣời dùng vào CSDL. <?php 225
$sql="insert into useronline(tgtmp,ip,local) values('$tg','$REMOTE_ADDR','$PHP_SELF')"; $query=mysql_query($sql); ?> $REMOTE_ADDR là biến môi trƣờng dùng để lấy ra IP của ngƣời truy cập. $PHP_SELF là biến môi trƣờng để lấy đƣờng dẫn mà ngƣời dùng đang truy cập. Tiếp đến ta tiến hành xóa record khi thời gian thực lớn hơn thời gian trong cơ sở dữ liệu. Tiếp tục là công việc hiển thị thông tin ra bên ngoài bằng cách liệt kê các record có trong database. DISTINCT là cú pháp cho phép liệt kê các dòng record mà không cho phép chúng có dữ liệu trùng lặp nhƣ cú pháp select bình thƣờng . Vậy ta sẽ liệt kê tất cả những ip của những ai đang truy cập trên trang useronline.php. Phần việc cuối cùng là chúng ta sẽ xuất thông tin ấy ra trình duyệt. Sau đây là toàn bộ nội dung code của trang useronline.php <?php $tg=time(); $tgout=900; $tgnew=$tg - $tgout; $conn=mysql_connect("localhost","root","root") or die("can't connect"); mysql_select_db("online",$conn); $sql="insert into useronline(tgtmp,ip,local) values('$tg','$REMOTE_ADDR','$PHP_SELF')"; $query=mysql_query($sql); $sql="delete from useronline where tgtmp < $tgnew"; $query=mysql_query($sql); $sql="SELECT DISTINCT ip FROM useronline WHERE file='$PHP_SELF'"; 226
$query=mysql_query($sql); $user = mysql_num_rows($query); echo "user online :$user"; ?> 3.3.7. Xây dựng trang bình chọn Phân tích & thiết kế CSDL Trong tình huống này chúng ta cần 1 bảng câu hỏi và 1 bảng câu trả lời. Phân tích quan hệ giữa chúng ta có: 1 Câu hỏi có nhiều câu trả lời. 1 Câu trả lời chỉ dành cho 1 câu hỏi. Vậy giữa 2 bảng này sẽ phát sinh một khóa ngoại, là khóa dùng để liên kết giữa hai bảng này. Bảng câu hỏi ( question) mysql> create table question(qid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, -> qtitle VARCHAR(255) NOT NULL, -> qdate DATE NOT NULL DEFAULT '0000-00-00', -> PRIMARY KEY(qid)); 1 Bảng câu trả lời (anwser) mysql> create table answer(aid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, -> qid INT(10) UNSIGNED NOT NULL, -> atitle VARCHAR(255) NOT NULL, -> acount INT(10) NOT NULL DEFAULT '0', -> PRIMARY KEY(aid)); Nhập liệu bằng CSDL ta có: mysql> Insert into question(qtitle,qdate) values("Ban thay website Sao Viet the nao ?","2009-10-10"); 227
mysql> Insert into answer(qid,atitle,acount) values("1","Nhin rat dep",0); mysql> Insert into answer(qid,atitle,acount) values("1","Nhin Dep",0); mysql> Insert into answer(qid,atitle,acount) values("1","Nhin Cung duoc",0); mysql> Insert into answer(qid,atitle,acount) values("1","Nhin qua xau",0); Xây dựng trang bình chọn Kết nối cơ sở dữ liệu: Lựa chọn câu hỏi có trong cơ sở dữ liệu để liệt kê ra trên website, sau đó ta lại tiếp tục liệt kê các câu trả lời của câu hỏi đó bằng cách sử dụng dấu chọn lựa (radio). Nhƣ vậy chúng ta sẽ phải chạy cùng lúc 2 câu truy vấn lồng nhau trong suốt quá trình truy xuất. 0){ $row=mysql_fetch_array($query); $qid=$row[qid]; echo " "; echo " $row[qtitle] "; $sql2="select * from answer where qid='".$qid."' order by aid"; $query2=mysql_query($sql2); if(mysql_num_rows($query2) > 0){ while($row2=mysql_fetch_array($query2)){ echo " $row2[atitle] "; } } echo " "; echo " "; echo " "; } ?> Và màn hình sẽ xuất ra nhƣ hình sau: 228
Hình 3.12. Giao diện trang bình chọn Và khi ngƣời dùng nhấn nút Bình chọn, chúng ta sẽ gọi lại chính trang đó để xử lý dữ liệu mà ngƣời dùng vừa lựa chọn. Vậy ta phải sử dụng lệnh isset() để kiểm tra xem ngƣời dùng có nhấn nút bình chọn không, tiếp đến ta lấy ra id mà ngƣời dùng vừa tiến hành chọn ở form bên dƣới . Cuối cùng ta cập nhật dữ liệu bằng cách lấy số trong cơ sở dữ liệu cộng tiếp cho 1 đơn vị nữa. Và đƣa ngƣời dùng sang trang kết quả. Vì trong đoạn code này có lệnh header nên chúng ta phải đặt quá trình xử lý này ở trên form. Nếu không sẽ bị lỗi dữ liệu khi gửi. Vậy toàn bộ code của trang poll.php này như sau. <?php $conn=mysql_connect("localhost","root","root") or die("can not connect database"); mysql_select_db("poll_exam",$conn); if(isset($_POST['ok'])){ $id=$_POST['answer']; $qid=$_GET['questionid']; $sql3="update answer set acount=acount + 1 where aid='".$id."'"; mysql_query($sql3); header("location: result.php?questionid=$qid"); 229