PHP with MySQL

1
PHP with MySQL

• PHP ?MySQL ??
• ?????????
• ?????
• ?????-??SQL ??

2

• PHP ??? MySQL ???????,?????????
• ????
• (??????????)
• ?????
• ?????
• ????
• ??????
• ????

3
??????

• PHP ??? MySQL ??????,??????????????????
• ?????????,??????????,?????????
• mysql_connect()??????
• ? PHP ????? mysql_connect() ?????
  MySQL?????,???????

mysql_connect(MySQL?????, ??, ??)
4
??????

• ??????????
• link mysql_connect("127.0.0.1", "root",
  "pwd999")
• // link ???????(link identifier)
• ???????????(??????????????????),????????????????

• link mysql_connect('localhost', 'root',
  'pwd999')
• or die("Could not connect " .
  mysql_error())
• ??
• link _at_mysql_connect('localhost', 'root',
  'pwd999')
• if (!link)
• die("Could not connect " . mysql_error())
  

5
??????????

• ?? MySQL ????????????????? big5 ????,? PHP
  ??????? utf-8 ???????,??????????????!
• ??????? utf-8 ???????????????????,????????????????
  ??,????????? PHP ?????????????????????
• ???mysql_query()?????,???SQL ?????

mysql_query("SET NAMES 'utf8'")
6
???????

• ? PHP ??????? MySQL ??????????,???????????,???????
  ??????????
• ????????????????????????,?????????????????????????
  ,???????????????????????????????,??????????????

7
???????
connMysql.php
lt?php //??????? db_host "localhost" db_usern
ame "studdb" db_password "www2013" //?????
db_link _at_mysql_connect(db_host, db_username,
db_password) if (!db_link) die("??????!") //??
???????? mysql_query("SET NAMES 'utf8'") ?gt
lt?php header("Content-Type text/html
charsetutf-8") include("connMysql.php") seldb
_at_mysql_select_db("studdb") if (!seldb)
die("???????!") sql_query "SELECT FROM
students" result mysql_query(sql_query)
8
?????

• ????????????,? PHP ????? mysql_select_db()
  ??????????,???????
• ????mysql_query????????
• ?????????
• mysql_select_db("dbname") ?
• mysql_query("use dbname")

mysql_select_db(????? ,?????)
mysql_query("use ????? ")
9
???????

• ?????????????????,???????????????????????????
• ?????? mysql_query() ??? MySQL ???? SQL
  ???,???????
• ?????????
• result mysql_query("select from students")
• result mysql_query("select from students",
  db_link)

mysql_query(SQL ???? , ????? )
10
??????????

• ???????????????????????????
• mysql_select_db("studdb")
• resultmysql_query("select from
  students",db_link)
• ??PHP???????????????
• ?? mysql_db_query() ? MySQL??? SQL
  ?????????????,????? FALSE ??
• ?
• result mysql_db_query("studdb", "select from
  students", db_link)

mysql_db_query(?????, SQL ???? ,?????)
11
????

• ???????????????,
• ????,?????????????,
• ????,?????????????
• ?PHP ????mysql_fetch_field()???????,???????
• ??SQL???????,????????????,?????while??????

mysql_fetch_field(?????)
12
??????(??)
lt?php include("connMysql.php") seldb
_at_mysql_select_db("studdb") if (!seldb)
die("???????!") sql_query "SELECT FROM
students" result mysql_query(sql_query)
echo "lttablegtlttrgt" while (field
mysql_fetch_field(result)) echo
"ltthgtfield-gtnamelt/thgt" echo
"lt/trgtlt/tablegt" ?gt
13
mysql_num_fields( ), mysql_field_name( )
lt?php include("connMysql.php") seldb
_at_mysql_select_db("studdb") if (!seldb)
die("???????!") sql_query "SELECT FROM
students" result mysql_query(sql_query)
echo "lttablegtlttrgt" cnt mysql_num_fields(resul
t) for (i0iltcnti) fName
mysql_field_name(result, i) echo
"ltthgtfNamelt/thgt" echo "lt/trgtlt/tablegt" ?gt
14
????(??????)

• ?????????????,??student????,???????cID???cName????
  ?????
• ??????????,??????
• mysql_fetch_row( ) ?"??"???
• mysql_fetch_assoc( )?"????"???
• mysql_fetch_array( ) ?"??"?"????"?????

15
????-??????

• ?????????,????mysql_fetch_row( )????
• ????????????,????????,???????????? false
• ?
• row_result mysql_fetch_row(result)

mysql_fetch_row(?????)
16
????-????????
lt?php header("Content-Type text/html
charsetutf-8") include("connMysql.php") seldb
_at_mysql_select_db("studdb") if (!seldb)
die("???????!") sql_query "SELECT FROM
students" result mysql_query(sql_query) w
hile (row_resultmysql_fetch_row(result)) forea
ch(row_result as itemgtvalue) echo
item."".value."ltbr /gt" echo "lthr /gt" ?gt
17
????-????????
lt?php header("Content-Type text/html
charsetutf-8") include("connMysql.inc") seldb
_at_mysql_select_db("studdb") if (!seldb)
die("???????!") sql_query "SELECT FROM
students" result mysql_query(sql_query) w
hile(row_resultmysql_fetch_assoc(result))
foreach(row_result as itemgtvalue)
echo item."".value."ltbr /gt" echo
"lthr /gt" ?gt
18
????-?????????????
lt?php header("Content-Type text/html
charsetutf-8") include("connMysql.inc") seldb
_at_mysql_select_db("studdb") if (!seldb)
die("???????!") sql_query "SELECT FROM
students" result mysql_query(sql_query)
while(row_resultmysql_fetch_array(result
)) foreach(row_result as
itemgtvalue) echo item."".value."ltbr
/gt" echo "lthr /gt" ?gt
19
??????

• ???? mysql_fetch_row()?mysql_fetch_assoc() ?
  mysql_fetch_array() ??,???????????????????????????
  ??????????????????????,???? mysql_data_seek()
  ??,???????

mysql_data_seek(?????, ??????)
20
????????
"connMysql.php"
"studdb"
21
????????
22
database class, table students
23
??? connMysql.php
lt?php //??????? db_host "127.0.0.1" db_usern
ame "studdb" db_password "www2013" //?????
db_link _at_mysql_connect(db_host, db_username,
db_password) if (!db_link) die("??????!") //??
???????? mysql_query("SET NAMES 'utf8'") ?gt
24
data.php (1/3)
lt?php //header("Content-Type text/html
charsetutf-8") include("connMysql.php") seldb
_at_mysql_select_db("studdb") if (!seldb)
die("???????!") sql_query "SELECT FROM
students" result mysql_query(sql_query) t
otal_records mysql_num_rows(result) ?gt
25
data.php (2/3)
lth1 align"center"gt????????lt/h1gt ltp
align"center"gt??????lt?php echo
total_records?gt,lta href"add.php"gt??????lt/agt?lt/p
gt lttable border"1" align"center"gt lt!-- ????
--gt lttrgt ltthgt??lt/thgtltthgt??lt/thgtltthgt??lt/thgtltth
gt??lt/thgt ltthgt????lt/thgtltthgt??lt/thgtltthgt??lt/thgtltt
hgt??lt/thgt lt/trgt
26
data.php (3/3)
lt?php while(row_resultmysql_fetch_assoc(result)
) echo "lttrgt" echo "lttdgt".row_result"cID"."
lt/tdgt" echo "lttdgt".row_result"cName"."lt/tdgt"
echo "lttdgt".row_result"cSex"."lt/tdgt" echo
"lttdgt".row_result"cBirthday"."lt/tdgt" echo
"lttdgt".row_result"cEmail"."lt/tdgt" echo
"lttdgt".row_result"cPhone"."lt/tdgt" echo
"lttdgt".row_result"cAddr"."lt/tdgt" echo
"lttdgtlta href'update.php?id".row_result"cID"."
'gt??lt/agt " echo "lta href'delete.php?id".row_r
esult"cID"."'gt??lt/agtlt/tdgt" echo "lt/trgt" ?gt
27
?? (add.php)
28
add.php (1/2)
ltform action"" method"post" name"formAdd"
id"formAdd"gt lttable border"1" align"center"
cellpadding"4"gt lttrgtltthgt??lt/thgtltthgt??lt/thgtlt/t
rgt lttrgtlttdgt??lt/tdgtlttdgtltinput type"text"
name"cName" id"cName"gtlt/tdgtlt/trgt
lttrgtlttdgt??lt/tdgtlttdgt ltinput type"radio"
name"cSex" id"radio" value"M" checkedgt?
ltinput type"radio" name"cSex" id"radio"
value"F"gt? lt/tdgtlt/trgt lttrgtlttdgt??lt/tdgtlttdgt
ltinput type"text" name"cBirthday"
id"cBirthday"gtlt/tdgtlt/trgt lttrgtlttdgt????lt/tdgtlttd
gtltinput type"text" name"cEmail"
id"cEmail"gtlt/tdgt lt/trgt
lttrgtlttdgt??lt/tdgtlttdgtltinput type"text"
name"cPhone" id"cPhone"gtlt/tdgtlt/trgt
lttrgtlttdgt??lt/tdgtlttdgtltinput name"cAddr"
type"text" id"cAddr" size"40"gtlt/tdgtlt/trgt
lttrgt lttd colspan"2" align"center"gt
ltinput name"action" type"hidden" value"add"gt
ltinput type"submit" name"button"
id"button" value"????"gt ltinput
type"reset" name"button2" id"button2"
value"????"gt lt/tdgt lt/trgt
lt/tablegt lt/formgt
29
add.php (2/2)
lt?php if (isset(_POST"action")(_POST"actio
n""add")) include("connMysql.php") if
(!_at_mysql_select_db("studdb")) die("???????!") s
ql_query "INSERT INTO students (cName
,cSex ,cBirthday ,cEmail ,cPhone
,cAddr) VALUES (" sql_query .
"'"._POST"cName"."'," sql_query .
"'"._POST"cSex"."'," sql_query .
"'"._POST"cBirthday"."'," sql_query .
"'"._POST"cEmail"."'," sql_query .
"'"._POST"cPhone"."'," sql_query .
"'"._POST"cAddr"."')" mysql_query(sql_query)
mysql_close(db_link) //????????? header("L
ocation data.php") ?gt
30
?? (update.php)
31
update.php (1/2)
lt?php include("connMysql.php") if
(!_at_mysql_select_db("studdb")) die("???????!") if
(isset(_POST"action")(_POST"action""upda
te")) sql_query "UPDATE students SET
" sql_query . "cName'"._POST"cName"."',"
sql_query . "cSex'"._POST"cSex"."',"
sql_query . "cBirthday'"._POST"cBirthday".
"'," sql_query . "cEmail'"._POST"cEmail"
."'," sql_query . "cPhone'"._POST"cPhone"
."'," sql_query . "cAddr'"._POST"cAddr"
."' " sql_query . "WHERE cID"._POST"cID"
mysql_query(sql_query) mysql_close(db_lin
k) //????????? header("Location
data.php") sql_db "SELECT FROM students
WHERE cID"._GET"id" result
mysql_query(sql_db) row_resultmysql_fetch_asso
c(result) ?gt
32
update.php (2/2)
ltform action"" method"post" name"formFix"
id"formFix"gt lttable border"1" align"center"
cellpadding"4"gt lttrgtltthgt??lt/thgtltthgt??lt/thgtlt/trgt lt
trgtlttdgt??lt/tdgtlttdgt ltinput type"text"
name"cName" id"cName" value"lt?php echo
row_result"cName"?gt"gt lt/tdgtlt/trgt lttrgtlttdgt??lt/t
dgtlttdgt ltinput type"radio" name"cSex" id"radio"
value"M" lt?php if(row_result"cSex""M") echo
"checked"?gtgt? ltinput type"radio" name"cSex"
id"radio" value"F" lt?php if(row_result"cSex"
"F") echo "checked"?gtgt? lt/tdgtlt/trgt lttrgtlttdgt??lt/t
dgtlttdgt ltinput type"text" name"cBirthday"
id"cBirthday" value"lt?php echo
row_result"cBirthday"?gt"gt lt/tdgtlt/trgt lttrgtlttdgt?
???lt/tdgtlttdgt ltinput type"text" name"cEmail"
id"cEmail" value"lt?php echo row_result"cEmail"
?gt"gt lt/tdgtlt/trgt lttrgtlttdgt??lt/tdgtlttdgt ltinput
type"text" name"cPhone" id"cPhone"
value"lt?php echo row_result"cPhone"?gt"gt lt/tdgt
lt/trgt lttrgtlttdgt??lt/tdgtlttdgt ltinput name"cAddr"
type"text" id"cAddr" size"40" value"lt?php
echo row_result"cAddr"?gt"gt lt/tdgtlt/trgt lttrgtlttd
colspan"2" align"center"gt ltinput name"cID"
type"hidden" value"lt?php echo
row_result"cID"?gt"gt ltinput name"action"
type"hidden" value"update"gt ltinput
type"submit" name"button" id"button"
value"????"gt ltinput type"reset" name"button2"
id"button2" value"????"gt lt/tdgtlt/trgt lt/tablegt lt/f
ormgt
33
?? (delete.php)
34
delete.php (1/2)
lt?php include("connMysql.php") if
(!_at_mysql_select_db("studdb")) die("???????!") if
(isset(_POST"action")(_POST"action""dele
te")) sql_query "DELETE FROM students
WHERE cID"._POST"cID" mysql_query(sql_que
ry) mysql_close(db_link) //????????? header
("Location data.php") sql_db "SELECT
FROM students WHERE cID"._GET"id" result
mysql_query(sql_db) row_resultmysql_fetch_a
ssoc(result) ?gt
35
delete.php (2/2)
ltform action"" method"post" name"formDel"
id"formDel"gt lttable border"1" align"center"
cellpadding"4"gt lttrgtltthgt??lt/thgtltthgt??lt/thgtlt/trgt lt
trgtlttdgt??lt/tdgtlttdgtlt?php echo row_result"cName"
?gtlt/tdgtlt/trgt lttrgtlttdgt??lt/tdgtlttdgt lt?php if
(row_result"cSex""M") echo
"?" else echo "?" ?gt lt/tdgtlt/trgt lttrgtlttdgt??lt/t
dgtlttdgtlt?php echo row_result"cBirthday"?gtlt/tdgtlt
/trgt lttrgtlttdgt????lt/tdgtlttdgtlt?php echo
row_result"cEmail"?gtlt/tdgtlt/trgt lttrgtlttdgt??lt/tdgt
lttdgtlt?php echo row_result"cPhone"?gtlt/tdgtlt/trgt
lttrgtlttdgt??lt/tdgtlttdgtlt?php echo row_result"cAddr"
?gtlt/tdgtlt/trgt lttrgt lttd colspan"2"
align"center"gt ltinput name"cID" type"hidden"
value"lt?php echo row_result"cID"?gt"gt
ltinput name"action" type"hidden"
value"delete"gt ltinput type"submit"
name"button" id"button" value"??????????"gt
lt/tdgt lt/trgt lt/tablegt lt/formgt
36
???? (data_page.php)
37
lt?php include("connMysql.php") seldb
_at_mysql_select_db("studdb") if (!seldb)
die("???????!") pageRow_records
5 num_pages 1 //??????,????? if
(isset(_GET'page')) num_pages
_GET'page' //???????? (??-1)?????? start
Row_records (num_pages -1)
pageRow_records //?????????SQL??? sql_query
"SELECT FROM students" //?????????SQL???,?
??????????,???????? sql_query_limit
sql_query." LIMIT " . startRow_records. ", " .
pageRow_records //??????????SQL????????
result ? result mysql_query(sql_query_limit)
//???????????SQL???????? all_result
? all_result mysql_query(sql_query) //?????
total_records mysql_num_rows(all_result) /
/?????(???/????)??????? total_pages
ceil(total_records/pageRow_records) ?gt
data_page.php (1/2)
38
data_page.php (2/2)
lttable border"0" align"center"gt lttrgt lt?php if
(num_pages gt 1) // ????????? ?gt lttdgtlta
href"data_page.php?page1"gt???lt/agtlt/tdgt
lttdgtlta href"data_page.php?pagelt?php echo
num_pages-1?gt"gt???lt/agtlt/tdgt lt?php ?gt lt?php
if (num_pages lt total_pages) // ??????????
?gt lttdgtlta href"data_page.php?pagelt?php echo
num_pages1?gt"gt???lt/agtlt/tdgt lttdgtlta
href"data_page.php?pagelt?php echo
total_pages?gt"gt???lt/agtlt/tdgt lt?php ?gt
lt/trgt lt/tablegt lttable border"0" align"center"gt
lttrgtlttdgt?? lt?php for (i1 ilttotal_pages
i) if (inum_pages) echo i."
" else echo "lta href\"data_page.php?pa
gei\"gtilt/agt " ?gt lt/tdgtlt/trgt lt/tablegt
39
Case study (????)

• ??????????
• ????

40
Homework -????

• ??????
• index.php
• ????????????????,????????????????
• ?form ????????????????????????????????
• recommand.htm
• ????????????????????????????
• result.php
• ????????????????????????

41
Homework - ????(????)

• ?????vote
• ?????candidate
• ????
• Id ????,?????
• Name ????????
• Introduction ????????
• Score?????
• Picture ?????

42
index.php
43
recommand.htm
44
result.php