PHP and MySQL by Example- P16

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

lượt xem

PHP and MySQL by Example- P16

Mô tả tài liệu
  Download Vui lòng tải xuống để xem tài liệu đầy đủ

Tham khảo tài liệu 'php and mysql by example- p16', công nghệ thông tin, kỹ thuật lập trình phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả

Chủ đề:

Nội dung Text: PHP and MySQL by Example- P16

  1. 4 After  extracting  the  records  with  the  mysql_fetch_assoc()  function,  a  table  is   displayed  with  links  to  each  artist,  his  or  her  phone  number  and  e-­‐mail  address,   and  a  Delete  hyperlink.  If  the  user  clicks  the  hyperlink  with  the  artist’s  name,  the   artist’s  id  will  be  passed  to  the  admin_artist_edit.php  script  via  a  URL.  (The   value  of  the  id  is  appended  to  the  ?  after  the  link.) 5 The  Delete  link,  when  clicked,  passes  the  action  value  and  the  id  of  the  artist  to   the  PHP  script  (line  1). Figure A.1. The admin_artlist.php page.     Updating an Artist in the Gallery (admin_artist_edit.php) To get to the admin_artist_edit.php page, the user clicked the hyperlink of an artist listed in the previous Figure A.1. Line 4 of Example A.1 shows that the hyperlink is sending the id for that artist via a URL. The admin_artist_edit.php page (Example A.2) uses the id to determine which artist to update. (You can see the value of the id in the URL because the GET method is being used). The id for the artist is stored in a hidden field (see Please purchase PDF Split-Merge on to remove this watermark.
  2. line 6 in Example A.2). If this is the first time the page is displayed, a SQL SELECT statement (see line 5) is executed to retrieve all the data for that artist from the database. The user is presented with a self-processing HTML form containing the artist information, such as the name, e-mail address, phone, and so on, for the particular artist he or she selected for update. Once the user has updated the information in the form, it is passed into the PHP portion of the page and extracted from the $_REQUEST superglobal array (see line 1), The variables resulting from the extract() function are used to “SET” the new values for the artist in the SQL UPDATE command on line 3. (Be sure when you test this script that you update line 2 and provide the correct server, username, and password to connect to your version of MySQL.) Example A.2. Code  View:        
  3.   Explanation 1 After  the  link  with  the  artist’s  name  (in  Figure  A.1)  is  clicked,  PHP  will  extract   the  user  input. 2 The  MySQL  connection  is  made  to  the  localhost  and  the  “test”  database  is   opened. 3 If  the  form  shown  in  Figure  A.2  has  been  submitted,  this  SQL  statement  will   update  the  “Artist”  table  for  an  artist  selected  by  his  or  her  id  passed  in  the  URL   (Example  A.1,  line  4). 4 If  the  update  was  successful,  the  $status  variable  is  assigned  to  the  string   "SUCCESSFULLY updated $name". 5 If  the  form  hasn’t  been  submitted,  all  rows  are  retrieved  from  the  MySQL  “Artist”   table  based  on  the  id  of  the  artist  selected  by  the  user  in  Figure  A.1.  The   extract()  function  will  create  named  variables  for  each  column  in  the  row  for   the  selected  artist,  such  as  $name, $email,  and  so  on,  used  as  values  for  the   form’s  input  devices.  The  hidden  field  assigns  the  value  of  the  artist’s  id  (passed   in  the  URL)  to  its  name  attribute,  also  called  id. Figure A.2. The admin_artlist_edit.php page updates the information on a particular artist.   Please purchase PDF Split-Merge on to remove this watermark.
  4. Adding a New Artist to the Gallery (admin_artist_insert.php) The page for adding a new artist to the database, admin_artist_insert.php, is basically the same as the admin_artist_edit.php page except that it performs a SQL INSERT statement (see line 1 in Example A.3) instead of an UPDATE and does not require an ID to be passed into the page via a URL. The user will enter the name of the new artist and all the requested information in a simple HTML form presented as a table (see line 5). An ID, primary key, will automatically be generated by the database for each new artist that is added. (When testing this page, make sure to enter the correct information when connecting to the database; that is, host, username, and password are supplied for the mysql_connect() function on line 2.) Example A.3. Code  View:        
  5. Explanation 1 This  SQL  statement  is  used  to  insert  a  new  artist  into  the  “Artist”  table. 2 Make  sure  the  host,  username,  and  password  are  supplied  for  the  mysql_connect()   function. 3 The  mysql_query()  function  sends  the  SQL  statement  to  the  database,  and  if  it  fails,   displays  the  MySQL  error  and  dies.  Otherwise,  the  new  record  has  been  inserted. 4 Figure  A.3  displays  the  admin_artist_insert.php  form.   Figure A.3. The admin_artist_insert.php page that will allow a new artist to be added to the Gallery. Please purchase PDF Split-Merge on to remove this watermark.
  6. Adding and/or Editing a Piece of Art (admin_art_edit.php) The page for adding a new art piece is slightly different from the pages previously shown where one page added a new artist and another page updated an existing artist. The process of inserting and updating a new piece of art is handled on a single page. A drop-down menu will present the user with a list of artists from which to choose. The art is linked to the artist who owns it by the artist’s ID, id in the “Artist” table and a foreign key in the “Art” table, artist_id, a one-to- many relationship in which one artist can have multiple pieces of art. The drop-down menu displays a list of artists by name and the value of the option is the artist’s ID (see Example A.4). Example A.4. (Section of the page source to view the drop-down menu after an artist, Laura Blair, was checked) Please enter the art title. Title Artist Please Select Todd Brown stuART Sheldon Elliott Easterling Laura Blair After selecting an artist from the drop-down list (line 5 in Example A.5), the page simply checks whether the art ID has been set. If it has, the matching art record is selected for update (line 1). Otherwise, the script inserts a new record (line 2) into the database and the database determines the new ID. Example A.5. Code  View:   (The admin_art_edit.php page)
  7. } mysql_query( $sql ) or die(mysql_error()); $status = "SUCCESSFULLY updated $title"; } } elseif ( isset($id) ) { // If id is set, but form has not // been submitted $sql = "SELECT Art.title, Art.description, Art.price, Art.image, Art.artist_id 3 FROM Art, Artist WHERE AND'$id'"; $resultset = mysql_query( $sql ) // Get all art for an artist or die(mysql_error()); $row = mysql_fetch_assoc( $resultset ); extract( $row ); } else { $id=""; $title=""; $description=""; $price=""; $image=""; $artist_id=0; } ?> Art Update Screen 4
  8. 8 Description
  9. Figure A.4. The user selected the Title Dragonfly from the admin_art_list.php file for update. (Note the id is passed in the URL.)   A.3.3. Public Pages The public pages are the pages a user can view without being required to log in. The public pages for the Art Gallery are are index.php, artist_detail.php, and contact.php. All these pages include header.php and footer.php at the beginning and at the end of the page. The Header Page (header.php) The header.php page is an HTML page included to provide the navigation menu, consisting of links to the other pages for the site (see Example A.6 and Figure A.5). Typically, the links to the administration section would not be included for security reasons, but we include them here for this demo Web site to make it easier to navigate between the private and public pages. Session data verifies whether or not the user has successfully logged in, and if not, redirects him or her to a login page called admin_login.php, discussed in detail in Chapter 16, “Cookies and Sessions.” Example A.6. Code  View:   The Canvas Gallery, San Francisco Art Gallery, Music Club, Bar, and Restaurant, Open Mic, Poetry, Film
  10. Figure A.6. The index.php page (default page).   Please purchase PDF Split-Merge on to remove this watermark.
  11.   If the user clicks the artist’s name, the hyperlink will pass along the id of the artist and send it to another page called by artist_detail.php (line 5). If the user clicks the link with the artist’s e-mail address, a “mailto” box will appear where an e-mail message can be composed and sent to the artist (line 6). Example A.7. Code  View:   Welcome To The Canvas Gallery Welcome to The Canvas Gallery. Please choose an artist from the list below to view the details. The Detail Page (artist_detail.php) The link to the atist’s detail page (see Figure A.7) contains the ID of the particular artist so that page can determine what artist was requested. The first SQL statement (see line 1 in Example A.8) selects all the information about the selected artist by his or her id number to be displayed on the page (line 2) with the artist’s name centered over the text area containing his or her bio information, and under that his or her e-mail address as a link, and his phone number. The next SQL statement (line 2) links the art to the artist and retrieves all the art information for the selected artist by joining the two tables based on the artist’s id. If the art has an image(s) associated with it, the image(s) (line 6) will be displayed below the other information about the art, such as the name or price (line 7). All of the artwork for the artist is displayed at the bottom of the page. Please purchase PDF Split-Merge on to remove this watermark.
  12. Figure A.7. The artist_detail.php page. (The ID of this artist is 12, as shown in the URL.)     Example A.8. Code  View:  
  13. $resultset = mysql_query( $sql )or die(mysql_error()); $row = mysql_fetch_assoc( $resultset ); extract( $row ); ?> 2 Email           The Contact Us Page (contact.php) Finally, the Contact Us page (see Figure A.8), contact.php, asks the user to enter the contact information in a form and submit it. After the user clicks the submit button, the data is formatted and e-mailed to the administrator of the Web site, manager@The (see line 1 in Example A.9), as well as a copy to the user to confirm that the e- mail was sent (line 2). This page also displays the status information confirming that the e-mail has been sent (line 3). Please purchase PDF Split-Merge on to remove this watermark.
  14. Figure A.8. The contact.php page.     Example A.9. Code  View:   Please purchase PDF Split-Merge on to remove this watermark.
  15. Contact Us Please fill out your contact information and we will contact you within 24 hours. Name Phone Email Message   A.3.4. Securing Pages with Login To secure the administration pages from unauthorized access, we require the user to log in. Because every administration page will include the admin_header.php, this is a good place to check if the user has already logged in. The following example script is taken from admin_header.php, which starts the session and gets the value of the variable 'authorized'. If this value is not 'yes', the script redirects the user to the admin_login.php page. Example A.10. The admin_login.php page (see Figure A.9) prompts the user to log in. If the username and password are correct, the script puts value 'yes' for the variable 'authorized' so that the user can access any of the administration pages for the duration of the session. This script is explained in Chapter 16, “Cookies and Sessions,” in detail. Please purchase PDF Split-Merge on to remove this watermark.
  16. Figure A.9. At the same time, the user is redirected to the page to administer the list of artists.     Example A.11. Code  View:   Please purchase PDF Split-Merge on to remove this watermark.
  17. Art Gallery Administration Art Gallery Administration Please Login (Use 'admin' and 'guess' for login and password) Login: Password:                 A.4. Installing the Art Gallery To install the art gallery on your computer, you must have successfully installed and configured PHP and MySQL. A.4.1. Where to Find the Files for the Canvas Gallery Site To install the PHP files on your computer, unzip and copy the entire art gallery directory into your Web root—the default directory that is opened when you go to http://localhost/ on your computer. The files can all be found on the CD. A.4.2. Installing the MySQL Database Next, you need to install the database. To do this, log into MySQL using either the mysql.exe command prompt (as shown in Figure A.10) or a GUI such as the MySQL Query Browser (see Figure A.11). We will use the “test” database. If you choose the gallerydb.sql script to populate the database (see Figure A.12), there will be no initial data, and you will be responsible for adding the artists and their art work. If you use the db.sql script, you will be provided with some demo art and artists. Figure A.10. Logging into the MySQL “test” database.         Please purchase PDF Split-Merge on to remove this watermark.
  18. Figure A.11. Using the Query Browser, rather than the mysql.exe command prompt.   Figure A.12. Once you log in, you could simply copy and paste the contents of the gallerydb.sql file into the SQL field and click the Execute button.     Example A.12. Code  View:     1 mysql> use test; Database changed mysql> \. db.sql Query OK, 0 rows affected, 1 warning (0.00 sec) Query OK, 0 rows affected (0.02 sec) Query OK, 0 rows affected, 1 warning (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 17 rows affected (0.03 sec) Records: 17 Duplicates: 0 Warnings: 0 Please purchase PDF Split-Merge on to remove this watermark.
  19. Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected, 1 warning (0.02 sec) Query OK, 0 rows affected, 1 warning (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected, 1 warning (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> show tables in test; +----------------+ | Tables_in_test | +----------------+ | art | | artist | | messages | | register | | te | +----------------+ 5 rows in set (0.00 sec) mysql> A.4.3. Editing the PHP Pages The art gallery application assumes that the data is stored in the MySQL database called “test”, that the username is root, and the password is password root running on the localhost. If this is not the case, you will have to update all the pages presented here and change the mysql_connect() parameters at the top of each page. If you do not have short_tabs set to “On” in the php.ini file, you will need to start each php script with:
Đồng bộ tài khoản