Accessing MySQL from PHP

Fundamental PHP & Web Development

Transcript

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