PHP with MySQL

1
PHP with MySQL

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

2

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

3
??????

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

mysqli_connect(MySQL?????, ??, ??, ?????)
4
??????

• ??????????
• db_link mysqli_connect('127.0.0.1', 'root',
  'pwd999', 'studdb')
• // db_link ???????(link identifier)
• ???????????(??????????????????),????????????????

• db_link mysqli_connect('localhost', 'root',
  'pwd999', 'studdb')
• or die("Could not connect " .
  mysqli_error())
• ??
• db_link _at_mysqli_connect('localhost', 'root',
  'pwd999', 'studdb')
• if (!db_link)
• die("Could not connect " .
  mysqli_error())

5
??????????

• ?? MySQL ???????????????? big5 ????,? PHP ???????
  utf-8 ???????,?????????
• ? ???? utf-8 ?????????
• ????????????????????????
• ???mysqli_set_charset () ? mysqli_query()??????,??
  ??

mysqli_set_charset(db_link,"utf8")
?
mysqli_query(db_link, "SET NAMES 'utf8'")
6
???????

• ? PHP ??????? MySQL ??????????,???????????,???????
  ??????????
• ????????????????????????????(?? connMysql.php)
• ?????????????????????????,??????????????????
• include 'connMysql.php'
• ??????????,?????????

7
???????
connMysql.php
lt?php //??????? db_host "localhost" db_usern
ame "studdb" db_password "www2015" //?????
db_link _at_mysqli_connect(db_host,
db_username, db_password, 'studdb') if
(!db_link) die("??????!") //?????????? mysqli_se
t_charset(db_link, 'utf8') ?gt
lt?php header("Content-Type text/html
charsetutf-8") Include "connMysql.php" sql_que
ry "SELECT FROM students" result
mysqli_query(db_link, sql_query)
8
?????

• ????????????,? PHP ????? mysqli_select_db()
  ??????????,???????
• ????mysql_query????????
• ?????????
• mysqli_select_db(db_link, "dbname")
• ?
• mysqli_query(db_link, "use dbname")

mysqli_select_db(?????, ?????)
mysqli_query(?????, "use ????? ")
9
???????

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

mysqli_query(?????, SQL ????)
10
???? (??????)

• ???????????????,
• ????,?????????????,
• ????,?????????????
• ??????,???????,???????????
• mysqli_fetch_row( ) ?"??"???
• mysqli_fetch_assoc( ) ?"????"???
• mysqli_fetch_array( ) ?"??"?"????"?????

11
mysqli_fetch_row( ) -??????

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

mysqli_fetch_row(?????)
12
????-????????
lt?php include "connMysql.php" sql_query
"SELECT FROM students" result
mysqli_query(db_link, sql_query) while
(row_resultmysqli_fetch_row(result))
foreach(row_result as item gt value)
echo item value ltbr /gt" echo "lthr
/gt" ?gt
13
????-????????
lt?php include "connMysql.php" sql_query
"SELECT FROM students" result
mysqli_query(db_link, sql_query) while(row_re
sultmysqli_fetch_assoc(result))
foreach(row_result as item gt value)
echo item valueltbr /gt" echo "lthr
/gt" ?gt
14
????-?????????????
lt?php include "connMysql.php" sql_query
"SELECT FROM students" result
mysqli_query(db_link, sql_query) while(row_re
sultmysqli_fetch_array(result))
foreach(row_result as item gt value)
echo item value ltbr /gt" echo "lthr
/gt" ?gt
15
??????

• ???? mysqli_fetch_row()?mysqli_fetch_assoc() ?
  mysqli_fetch_array() ??,??????????????????????????
  ???????????????????????,???? mysqli_data_seek()
  ??,???????

mysqli_data_seek(?????, ??????)
16
????

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

mysqli_fetch_field(?????)
17
??????(??)
lt?php include("connMysql.php") sql_query
"SELECT FROM students" result
mysqli_query(db_link, sql_query) echo
"lttablegtlttrgt" while (field mysqli_fetch_field(
result)) echo "ltthgtfield-gtnamelt/thgt"
echo "lt/trgtlt/tablegt" ?gt
18
?????? mysql_fetch_fields( )
lt?php include("connMysql.php") sql_query
"SELECT FROM students" result
mysqli_query(db_link, sql_query) echo
"lttablegtlttrgt" fieldInfo mysqli_fetch_fields(r
esult) foreach (fieldInfo as fi)
fName fi-gtname echo "ltthgtfNamelt/thgt"
echo "lt/trgtlt/tablegt" ?gt
19
????????
20
database studdb, table students
21
??? connMysql.php
lt?php //??????? db_host "127.0.0.1" db_usern
ame "studdb" db_password "www2015" db_name
db_username //????? db_link
_at_mysqli_connect(db_host, db_username,
db_password, db_name) if (!db_link)
die("??????!") //?????????? mysqli_set_charset(d
b_link, 'utf8') ?gt
22
data.php (1/3)
lt?php include "connMysql.php" sql_query
"SELECT FROM students" result
mysqli_query(db_link, sql_query) total_record
s mysqli_num_rows(result) ?gt
23
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
24
data.php (3/3)
lt?php while(row_resultmysqli_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
25
?? (add.php)
26
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
27
add.php (2/2)
lt?php if (isset(_POST"action")(_POST"actio
n""add")) include("connMysql.php") sql_qu
ery "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"."')" mysqli_query(db_link,
sql_query) mysqli_close(db_link) //????????
? header("Location data.php") ?gt
28
?? (update.php)
29
update.php (1/2)
lt?php include("connMysql.php") 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"
mysqli_query(db_link, sql_query) mysqli_c
lose(db_link) //????????? header("Location
data.php") sql_db "SELECT FROM students
WHERE cID"._GET"id" result
mysqli_query(db_link, sql_db) row_resultmysql
i_fetch_assoc(result) ?gt
30
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
31
?? (delete.php)
32
delete.php (1/2)
lt?php include("connMysql.php") if
(isset(_POST"action")(_POST"action""dele
te")) sql_query "DELETE FROM students
WHERE cID"._POST"cID" mysqli_query(db_lin
k, sql_query) mysqli_close(db_link) //?????
???? header("Location data.php") sql_db
"SELECT FROM students WHERE
cID"._GET"id" result mysqli_query(db_li
nk, sql_db) row_resultmysqli_fetch_assoc(resu
lt) ?gt
33
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
34
???? (data_page.php)
35
lt?php include "connMysql.php" 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 mysqli_query(db_link,
sql_query_limit) //???????????SQL????????
all_result ? all_result mysqli_query(db_link
, sql_query) //????? total_records
mysqli_num_rows(all_result) //?????(???/????)?
?????? total_pages ceil(total_records/pageRo
w_records) ?gt
data_page.php (1/2)
36
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