狠狠撸

L?p trình và Thi?t k? Web 1
? 2007 Khoa C?ng ngh? th?ng tin
KhoaKhoa CNTTCNTT –– ?H?H.KHTN.KHTN
Bài 9
ThaoThao ttáácc CSDLCSDL vv??ii PHPPHP

L?p trình và Thi?t k? Web 1 – Bài 9: Thao tác CSDL v?i PHP
? 2007 Khoa CNTT - ?HKHTN
N?i dung
Các b??c thao tác v?i CSDL
M?t s? v?n ?? khi thao tác v?i CSDL
M?t s? ví d? minh h?a:
– Tìm ki?m và s?p x?p
– T?ng h?p Thêm, Xóa, S?a
– Ph?n trang d? li?u
Thao tác v?i các H? Qu?n tr? CSDL khác

Các b??c thao tác CSDL trong PHP
1. K?t n?i v?i CSDL
2. X?y d?ng c?u truy v?n d? li?u
3. Th?c thi c?u truy v?n
4. X? lí k?t qu? tr? v? t? c?u truy v?n SELECT
5. Ng?t k?t n?i v?i CSDL

1. T?o K?t n?i CSDL
K?t n?i MySQL và Ch?n CSDL
– resource mysql_connect([string host], [string username],
[string password])
– int mysql_select_db (string database, [resource connection])
<?php
$connection = mysql_connect(“localhost",“root”,“root");
mysql_select_db(“TestDB", $connection);
?>
DatabaseApplication
Connection

www.example.com
Webserver
2. X?y d?ng c?u truy v?n
Yêu c?u trang xlDangnhap.php
Textbox : txtDangnhap
Password Box: txtMatkhau
txtMatkhau = 123
txtDangnhap = admin
Database
Server
strSQL = “SELECT *
FROM Users
WHERE UserName =
‘admin’ and Password =
‘123’ “

2. X?y d?ng c?u truy v?n (tt)
SQL Ví d?
INSERT strSQL = "INSERT INTO Users (UserName, Password) VALUES (‘admin',‘123')“
DELETE strSQL = "DELETE FROM Users WHERE UserName = ‘minh'“
UPDATE strSQL = "UPDATE Users SET Password = ‘abc' WHERE UserName = ‘admin' “
SELECT strSQL = “SELECT * FROM Users WHERE UserName = ‘admin' “

3. Th?c thi c?u truy v?n
resource mysql_query(string SQL_command, [resource connection])
<?php
$strSQL = "INSERT INTO Users (UserName, Password) VALUES (‘admin',‘123')“;
mysql_query($strSQL);
$strSQL = “SELECT * FROM Users“;
$result = mysql_query($strSQL);
?>
Application
Connection 011010011011010011
query
Database

4. X? l? k?t qu? tr? v?
Hi?n th? d? li?u tr? v?
– S? d?ng hàm
$row = mysql_fetch_array($result)
– S? l??ng FIELD
$num = mysql_num_fields($result);
– Truy c?p ??n t?ng FIELD
echo $row[“UserName”];
Ho?c
echo $row[0];
UserName Password
A AA
B BB
C CC
D DD
… …
X XX
Y YY
Z ZZ
cursor
Ví d?: Xu?t records t? CSDL ra trang HTML

5. Ng?t k?t n?i d? li?u
int mysql_close([resource connection])
T? ??ng th?c thi khi k?t thúc m? l?nh

T?ng h?p các b??c thao tác CSDL MySQL
<?php
// 1. Ket noi CSDL
$connection = mysql_connect(“hostname",“username",“password");
mysql_select_db(“database_name", $connection);
// 2. Chuan bi cau truy van & 3. Thuc thi cau truy van
$strSQL = "SELECT * FROM TableName”;
$result = mysql_query($strSQL);
// 4.Xu ly du lieu tra ve
while ($row = mysql_fetch_array($result))
{
for ($i=0; $i<mysql_num_fields($result); $i++)
echo $row[$i] . " ";
}
// 5. Dong ket noi
mysql_close($connection);
?>

M?t s? hàm thao tác v?i CSDL MySQL
int mysql_data_seek(resource result_set, int row)
array mysql_fetch_row(resource result_set)
object mysql_fetch_object(resource result_set, [int result_type])
int mysql_free_result(resource result_set)
int mysql_num_rows(resource result_set)
resource mysql_pconnect([string host:port], [string user],
[string password])
resource mysql_unbuffered_query(string query,
[resource connection])
object mysql_fetch_field(resource result_set, [int attribute_number])
int mysql_affected_rows([resource connection])
int mysql_insert_id([resource connection])

M?t s? v?n ?? - MySQL & Font Unicode
Font UNICODE
<?php
mysql_query(“set names ‘utf-8’”);
?>
Encode
Decode

M?t s? v?n ?? - X? l? l?i
int mysql_errno(resource connection)
string mysql_error(resource connection)
<?php
function showerror( ) {
die("Error " . mysql_errno( ) . " : " . mysql_error( ));
}
if (!($connection = @ mysql_connect("localhost", "fred","shhh")))
die("Could not connect");
If (!(mysql_select_db("winestor", $connection)))
showerror( );
?>

M?t s? v?n ?? - Include File
<?php
$hostName = "localhost";
$databaseName = "bookstoreDB";
$username = "root";
$password = "root";
?>
<?php
function showError( )
{
die("Error " . mysql_errno( ) . " : " . mysql_error( ));
}
?>
<?php
include ‘db.inc’;
include ‘error.inc’;
?>
db.inc
error.inc

M?t s? ví d?:
Trình bày d? li?u ra trang Web – Tìm ki?m & S?p x?p
Tìm ki?m
Xu?t d? li?u tho? ?i?u ki?n
S?p x?p records theo tên tr??ng
Cho phép ng??i dùng chon c?t ?? s?p x?p

M?t s? ví d?:
T?ng h?p Thêm, Xóa và S?a d? li?u
Add
Delete
Edit

M?t s? ví d?:
Ph?n trang d? li?u
Tr??ng h?p d? li?u l?y v? có quá nhi?u m?u tin (hi?n th? b?ng
quá dài)
Chia d? li?u hi?n th? trên nhi?u trang
– S? m?u tin hi?n th? trên 1 trang: $rowsPerPage
– S? th? t? trang hi?n th?: $pageNum=1,2…
– Ch? s? m?u tin b?t ??u trong trang th? $pageNum:
$offset = ($pageNum - 1) * $rowsPerPage;
– T?ng s? m?u tin tr? v?: $numRows
– T?ng s? trang hi?n th?:
$maxPage = ceil($numRows/$rowsPerPage);

Ph?n trang d? li?u (tt)
Các b??c th?c hi?n ph?n trang
–Tính các th?ng s? ph?n trang
–L?y th?ng tin c?n hi?n th?
–Hi?n th? th?ng tin c?a trang hi?n t?i
–T?o liên k?t ch? ??n các trang
– Xem Ví d?

Thao tác v?i Microsoft SQL Server
<?php
// 1. Ket noi CSDL
$connection = mssql_connect(“hostname",“username",“password");
mysql_select_db(“database_name", $connection);
$result = mssql_query($strSQL);
while ($row = mssql_fetch_array($result))
{
for ($i=0; $i<mssql_num_fields($result); $i++)
echo $row[$i] . " ";
}
// 5. Dong ket noi
mssql_close($connection);
?>

Thao tác v?i Microsoft Access – S? d?ng ODBC
<?php
// 1. Ket noi CSDL
$connection = odbc_connect("mobileDB","","");
$result = odbc_exec ($connection, $strSQL);
while (odbc_fetch_row($result))
{
for ($i=1; $i<=odbc_num_fields($result); $i++)
echo odbc_result($result, $i) . " ";
}
// 5. Dong ket noi
odbc_close($connection);
?>

Thao tác v?i Microsoft Access – S? d?ng ADO
<?php
// 1. Ket noi CSDL
$db = "db/bookStoreDB.mdb";
$conn = new COM("ADODB.Connection") ;
$sql = "DRIVER={Microsoft Access Driver (*.mdb)} ;
DBQ=". realpath($db) ." ; uid=; pwd=;" ;
$conn->open($sql);
$rs = $conn->execute($strSQL);
while (!$rs->EOF) {
echo $rs->Fields[‘FieldName']->Value . " ";
$rs->MoveNext() ;
}
// 5. Dong ket noi
$rs->Close() ;
$conn->Close() ;
?>

狠狠撸

Web course php & csdl

More Related Content

Web course php & csdl