Web-Sites-Design-and-Programming-10--计算机科学与技术---网站设计与编程--双语教学课件

1、Web Sites Design and Programming,Lecturer: Lijie Guo Room: XNA508 Phone: 23678517 Email: guo_,2020/9/15,School of Computer Science and Technology,10-2,More PHP and MySQL,P3W CN 第12章, 355389 第14章, 419435,2020/9/15,School of Computer Science and Technology,10-3,Overview,Other function Form Handling Fi

2、les Architectures for Database Access Introduction to SQL The MySQL Database System,2020/9/15,School of Computer Science and Technology,10-4,PHP Information,The phpinfo() function is used to output PHP information., ,INFO_GENERALThe configuration line, php.ini location, build date, Web Server, Syste

3、m and more INFO_CREDITSPHP 4 credits INFO_CONFIGURATIONLocal and master values for php directives INFO_MODULESLoaded modules INFO_ENVIRONMENTEnvironment variable information INFO_VARIABLESAll predefined variables from EGPCS INFO_LICENSEPHP license information INFO_ALLShows all of the above (default)

4、,view the output page,2020/9/15,School of Computer Science and Technology,10-5,Form Handling,Forms could be handled by the same document that creates the form, but that may be confusing A separate document to handle the form can be specified as the value of the action attribute It does not matter wh

5、ether GET or POST method is used to transmit the form data PHP builds an array of the form values $_GET for the GET method $_POST for the POST method,2020/9/15,School of Computer Science and Technology,10-6, Enter your name: Enter your age: , Welcome You are years old! ,2020/9/15,School of Computer

6、Science and Technology,10-7,Files (self-study),PHP is able to create, read and write files on the server system Opening a file Prepares file for use and associates a variable with the file for future reference $fptr = fopen(filename, use_indicator) Every open file has an internal pointer (where the

7、next file operations should take place) Because fopen could fail, use it with die $file_var = fopen (“test.dat”, “r”) or die (“Error test.dat cant be opened”);,2020/9/15,School of Computer Science and Technology,10-8,File Use Indicators (self-study),2020/9/15,School of Computer Science and Technolog

8、y,10-9,Files (self-study),Use file_exists(filename) to determine whether file exists before trying to open it Use fclose(file_var) to close a file Reading from a file 1. Read all or part of the file into a string variable $str = fread($file_var, #bytes) To read the whole file, use filesize(file_name

9、) as the second parameter $file_string = fread ($file_var, filesize(“test.dat”); 2. Read the lines of the file into an array $file_lines = file(file_name); Need not open or close the file,2020/9/15,School of Computer Science and Technology,10-10,Files (self-study),Reading from a file (continued) 3.

10、Read one line from the file $line = fgets(file_var, #bytes) Reads characters until eoln, eof, or #bytes characters have been read 4. Read one character at a time $ch = fgetc(file_var) Control reading lines or characters with eof detection using feof (TRUE for eof; FALSE otherwise) while(!feof($file_

11、var) $ch = fgetc($file_var); ,2020/9/15,School of Computer Science and Technology,10-11,Files (self-study),Writing to a file $bytes_written = fwrite($file_var, $out_data); fwrite returns the number of bytes it wrote Files can be locked (to avoid interference from concurrent accesses) with flock Take

12、s 2 parameters file variable and intreger that specifies particular operation 1 file can be read by others 2 no other access 3 unlocks file,2020/9/15,School of Computer Science and Technology,10-12,File Workings (self-study), $line) echo Line #$l_num:“ .$line.”; ?,view the output page,view the outpu

13、t page,view the output page,view the output page,2020/9/15,School of Computer Science and Technology,10-13,Architectures for Database Access,A two-tier system has clients that are connected directly to the database server Client tasks: Provide a way for users to submit queries Run applications that

14、use the results of queries Display results of queries Database server tasks: Implement a data manipulation language, which can directly access and update the database However, because the relative power of clients has grown considerably, we could shift processing to the client, but then keeping all

15、clients current with application updates is difficult,2020/9/15,School of Computer Science and Technology,10-14,Architectures for Database Access (con.),A solution to the problems of two-tier systems is to add a component in the middle create a three-tier system For Web-based database access, the mi

16、ddle tier can run applications (client just gets results),2020/9/15,School of Computer Science and Technology,10-15,Architectures for Database Access (con.),PHP ,2020/9/15,School of Computer Science and Technology,10-47,The MySQL Database System,Tables created with CREATE TABLE command CREATE TABLE

17、Equipment (Equip_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, Equip CHAR(10) ); To see the tables of a database: SHOW TABLES; To see the description of a table (columns): DESCRIBE Equipment; Other commands INSERT, SELECT, DROP, UPDATE, DELETE same as SQL commands,2020/9/15,School of Computer

18、 Science and Technology,10-48,Database Tables,A database most often contains one or more tables. Each table has a name (e.g. Customers or Orders). Each table contains records (rows) with data. Below is an example ofa table called Persons: SELECT LastName FROM Persons,2020/9/15,School of Computer Sci

19、ence and Technology,10-49,PHP MySQL Connect to a Database,The free MySQL Database is very often used with PHP. Before you can access and work with data in a database, you must create a connection to the database. In PHP, this is done with the mysql_connect() function. Syntax mysql_connect(servername

20、,username,password);,2020/9/15,School of Computer Science and Technology,10-50,PHP MySQL Connect to a Database,2020/9/15,School of Computer Science and Technology,10-51,Example,In the following example we store the connection in a variable ($con) for later use in the script. The die part will be exe

21、cuted if the connection fails: ,2020/9/15,School of Computer Science and Technology,10-52,Closing a Connection,The connection will be closed as soon as the script ends. To close the connection before, use the mysql_close() function. ,2020/9/15,School of Computer Science and Technology,10-53,PHP MySQ

22、L Create Database and Tables,A database holds one or multiple tables. The CREATE DATABASE statement is used to create a database in MySQL. Syntax CREATE DATABASE database_name To get PHP to execute the statement above we must use the mysql_query() function. This function is used to send a query or c

23、ommand to a MySQL connection,2020/9/15,School of Computer Science and Technology,10-54,Example,In the following example we create a database called my_db: ,my_db.php,2020/9/15,School of Computer Science and Technology,10-55,Create a Table,The CREATE TABLE statement is used to create a database table

24、 in MySQL. Syntax CREATE TABLE table_name ( column_name1 data_type, column_name2 data_type, column_name3 data_type, . ) We must add the CREATE TABLE statement to the mysql_query() function to execute the command. The following example shows how you can create a table named person, with three columns

25、. The column names will be FirstName, LastName and Age:,2020/9/15,School of Computer Science and Technology,10-56,person.php,2020/9/15,School of Computer Science and Technology,10-57,Important: A database must be selected before a table can be created. The database is selected with the mysql_select_

26、db() function. Note: When you create a database field of type varchar, you must specify the maximum length of the field, e.g. varchar(15).,2020/9/15,School of Computer Science and Technology,10-58,MySQL Data Types,Below is the different MySQL data types that can be used,2020/9/15,School of Computer

27、Science and Technology,10-59,2020/9/15,School of Computer Science and Technology,10-60,2020/9/15,School of Computer Science and Technology,10-61,2020/9/15,School of Computer Science and Technology,10-62,Primary Keys,Each table should have a primary key field. A primary key is used to uniquely identi

28、fy the rows in a table. Each primary key value must be unique within the table. Furthermore, the primary key field cannot be null because the database engine requires a value to locate the record. The primary key field is always indexed. There is no exception to this rule! You must index the primary

29、 key field so the database engine can quickly locate rows based on the keys value.,2020/9/15,School of Computer Science and Technology,10-63,AUTO_INCREMENT,The following example sets the personID field as the primary key field. The primary key field is often an ID number, and is often used with the

30、AUTO_INCREMENT setting. AUTO_INCREMENT automatically increases the value of the field by 1 each time a new record is added. To ensure that the primary key field cannot be null, we must add the NOT NULL setting to the field,2020/9/15,School of Computer Science and Technology,10-64,Example,$sql = CREA

31、TE TABLE person ( personID int NOT NULL AUTO_INCREMENT, PRIMARY KEY(personID), FirstName varchar(15), LastName varchar(15), Age int ); mysql_query($sql,$con);,2020/9/15,School of Computer Science and Technology,10-65,PHP MySQL Insert Into,Example In the previous , we created a table named Person, wi

32、th three columns; Firstname, Lastname and Age. We will use the same table in this example. The following example adds two new records to the Person table:,2020/9/15,School of Computer Science and Technology,10-66,Person,Insert Into.php,2020/9/15,School of Computer Science and Technology,10-67,Insert

33、 Data From a Form Into a Database,Now we will create an HTML form that can be used to add new records to the Person table. Here is the HTML form: Firstname: Lastname: Age: ,2020/9/15,School of Computer Science and Technology,10-68,Insert Data From a Form Into a Database,When a user clicks the submit

34、 button in the HTML form in the example above, the form data is sent to insert.php. The insert.php file connects to a database, and retrieves the values from the form with the PHP $_POST variables. Then, the mysql_query() function executes the INSERT INTO statement, and a new record will be added to

35、 the database table. Below is the code in the insert.php page:,2020/9/15,School of Computer Science and Technology,10-69,Insert Data From a Form Into a Database,Insert Data From a Form.html,2020/9/15,School of Computer Science and Technology,10-70,PHP MySQL Select,The SELECT statement is used to sel

36、ect data from a database. Syntax SELECT column_name(s) FROM table_nameNote:,2020/9/15,School of Computer Science and Technology,10-71,Example,The following example selects all the data stored in the Person table (The * character selects all of the data in the table): ; mysql_close($con); ?,Select.ph

37、p,2020/9/15,School of Computer Science and Technology,10-72,Select,The example above stores the data returned by the mysql_query() function in the $result variable. Next, we use the mysql_fetch_array() function to return the first row from the record set as an array. Each subsequent call to mysql_fe

38、tch_array() returns the next row in the recordset. The while loop loops through all the records in the record set. To print the value of each row, we use the PHP $row variable ($rowFirstName and $rowLastName). The output of the code above will be: Peter Griffin Glenn Quagmire Lijie Guo,2020/9/15,Sch

39、ool of Computer Science and Technology,10-73,Display the Result in an HTML Table,The following example selects the same data as the example above, but will display the data in an HTML table:,2020/9/15,School of Computer Science and Technology,10-74,Display Example, Firstname Lastname ; while($row =

40、mysql_fetch_array($result) echo ; echo . $rowFirstName . ; echo . $rowLastName . ; echo ; echo ; mysql_close($con); ?,Display Example.php,2020/9/15,School of Computer Science and Technology,10-75,The output of the code above will be,2020/9/15,School of Computer Science and Technology,10-76,PHP MySQL The Where Clause,To select only data that matches a specified criteria, add a WHERE clause to the SELECT statement. Example The following example will select all rows from the Person table, where FirstName=Peter:,2020/9/15,School of Computer Science and