×
Copy
Open
Link
Embed
Share
Beginning
This slide
Copy link URL
Copy link URL
Copy iframe embed code
Copy iframe embed code
Copy javascript embed code
Copy javascript embed code
Share
Tweet
Share
Tweet
Slide 1
Slide 1 text
Accessing MySQL from PHP Fundamental PHP & Web Development lecturer : Lucien Lee 李柏緯 Lecture 5
Slide 2
Slide 2 text
2
Slide 3
Slide 3 text
flow •Connect MySQL Server •Open database •execute SQL •close database 3
Slide 4
Slide 4 text
connect server 4 Before we can access data in a database, we must open a connection to the MySQL server.
Slide 5
Slide 5 text
5
Slide 6
Slide 6 text
6 mysql_connect mysql_connect(host,username,password); mysql_connect(localhost,‘root’,‘123456’);
Slide 7
Slide 7 text
SELECT DB choose the db you would like to query 7
Slide 8
Slide 8 text
8
Slide 9
Slide 9 text
9 mysql_select_db $link = mysql_connect(localhost,‘root’,‘123456’); mysql_select_db(‘user’, $link);
Slide 10
Slide 10 text
query execute SQL query BY PHP 10
Slide 11
Slide 11 text
11
Slide 12
Slide 12 text
12 mysql_query $sql = “SELECT * FROM table”; $result = mysql_query($sql,$link);
Slide 13
Slide 13 text
PHP 5 and older //before query mysql_query('SET NAMES utf8'); 13
Slide 14
Slide 14 text
fetch result after query, we only get pointer to resource 14
Slide 15
Slide 15 text
mysql_fetch_array while($row = mysql_fetch_array($result)){ //do something echo $row[‘fieldName’]; echo $row[0]; } 15
Slide 16
Slide 16 text
mysql_fetch_array while($row = mysql_fetch_array($result,MYSQL_ASSOC)) { //do something echo $row[‘fieldName’]; //echo $row[0] is wrong } 16
Slide 17
Slide 17 text
mysql_fetch_assoc while($row = mysql_fetch_assoc($result)) { //do something echo $row[‘fieldName’]; } 17
Slide 18
Slide 18 text
mysql_fetch_array while($row = mysql_fetch_array($result, MYSQL_NUM)) { //do something //echo $row[‘fieldName’] is wrong echo $row[0]; } 18
Slide 19
Slide 19 text
array type •MYSQL_ASSOC •MYSQL_NUM •MYSQL_BOTH 19
Slide 20
Slide 20 text
remove your pointer we may directly get a specific record 20
Slide 21
Slide 21 text
mysql_data_seek mysql_data_seek(data,row_num) $row = mysql_fetch_array($result); mysql_data_seek($result,8) $row = mysql_fetch_array($result); //get 9th record 21
Slide 22
Slide 22 text
mysql_close close Database connect 22
Slide 23
Slide 23 text
mysql_close mysql_close(resource); 23
Slide 24
Slide 24 text
more something else 24
Slide 25
Slide 25 text
mysql_num_rows 25 KNOW HOw many rows selected
Slide 26
Slide 26 text
mysql_num_rows mysql_num_rows($result); 26
Slide 27
Slide 27 text
mysql_affected_rows know how many record affected 27
Slide 28
Slide 28 text
mysql_affected_rows //after update mysql_affected_rows($link_identifier); 28
Slide 29
Slide 29 text
mysql_fetch_field get field information 29
Slide 30
Slide 30 text
mysql_fetch_field mysql_fetch_field(data,offset) $meta= mysql_fetch_field($result,1); echo $meta->name; echo $meta->type; 30
Slide 31
Slide 31 text
? Use SQL to manipulate record 31
Slide 32
Slide 32 text
INSERT •Insert a new record • INSERT INTO `tableName` (field1,field2,...) Values (data1,data2...) 32
Slide 33
Slide 33 text
UPdate •update an exist record •UPDATE `tableName` SET field = data, ... WHERE condition 33
Slide 34
Slide 34 text
DELETE •delete an exist record •DELETE FROM `table` WHERE condition 34
Slide 35
Slide 35 text
You can use better way 35
Slide 36
Slide 36 text
mysqli 36 mysql improvement
Slide 37
Slide 37 text
reference 37 http://www.w3schools.com/php/php_ref_mysqli.asp
Slide 38
Slide 38 text
practice 38 • Make a message board that users can leave a message containing name(necessary), message and timestamp. • users can view all message and delete them.