1 / 111

CHAPTER 9

CHAPTER 9. PHP Database Connectivity. Topics. Developing a simple web-based application Install, run wamp and create folder Configuration Creating the first page View webpage from localhost Create database and table Putting it all together

waldo
Download Presentation

CHAPTER 9

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. CHAPTER 9 PHP Database Connectivity created by Mr Azlan Yusof, edited by F.Hani

  2. Topics • Developing a simple web-based application • Install, run wamp and create folder • Configuration • Creating the first page • View webpage from localhost • Create database and table • Putting it all together index.php, login.php, menu.php, add.php, view.php, delete.php, logout.php created by Mr Azlan Yusof, edited by F.Hani

  3. Developing a simple web-based application • Create a website called “Property Management Website”. • The purpose of the web site is to keep office properties (desk, computer etc.) records in a simple web-based application system. • In order to add, edit and delete property, one need to log in as administrator. • There is only one administrator. created by Mr Azlan Yusof, edited by F.Hani

  4. Developing a simple web-based application • After log in, an administrator is able to perform the following functions: • Insert a new property • Edit the existing property • Delete obsolete property • Log out • Normal user is able to view the property data only. created by Mr Azlan Yusof, edited by F.Hani

  5. Developing a simple web-based application • Module • For normal user • view property (index.php) • For administrator • login (login.php) • add new property information (add.php) • edit existing property information (edit.php) • delete existing property information (delete.php) • logout (logout.php) created by Mr Azlan Yusof, edited by F.Hani

  6. Developing a simple web-based application • Database: propertydb • Inside propertydb database, create 2 tables • admin table – keep the login and administrator information • property table – keep the property information created by Mr Azlan Yusof, edited by F.Hani

  7. Developing a simple web-based application Remember: • HTML table is not database table • Database table is not HTML table • To view record saved in database on web page, we need PHP to: • connect to database, • retrieve the record, and • view or display the record in HTML table. created by Mr Azlan Yusof, edited by F.Hani

  8. Creating PHP page • You have learnt how to create and view PHP file in Chapter 6. • Follow the same steps when to create PHP files in subsequent slides. • Now, let us learn how to create the database. created by Mr Azlan Yusof, edited by F.Hani

  9. Create Database and Table • In order to store the data in the database, we need to create a database (propertydb) with 2 tables: admin and property • There are two ways to create the database using MySQL. You may choose either to use command or GUI. • Before that you have to know the database structure. created by Mr Azlan Yusof, edited by F.Hani

  10. Create Database and Table The structure of the database propertydb is presented below: Tips: Use varchar when your strings do not have a fixed length (e.g. names, cities, etc.) Use char when your strings are always going to be the same length (e.g. phone numbers, zip codes, etc). created by Mr Azlan Yusof, edited by F.Hani

  11. Create Database and Table (using command) Creating database using command/ MySQL console. • How to open MySQL console: • Single left-click at the speedometer • Select MySQL > MySQL console • Prompt for password, just press Enter created by Mr Azlan Yusof, edited by F.Hani

  12. Create Database and Table (using command) • Type these commands • Create database – create database propertydb; • Use database – use propertydb; • Create table admin – create table admin (userid varchar(6) primary key,password varchar (6)); • Create table property – create table property (propertyid varchar(5) primary key, property varchar(100), addedby varchar(6), date date); • Display admin table structure – describe admin; • Display property table structure – describe property; • Exit mysql – exit; created by Mr Azlan Yusof, edited by F.Hani

  13. created by Mr Azlan Yusof, edited by F.Hani

  14. Create Database and Table (using GUI) Creating database using GUI. • Click once on the fully white speedometer (icon of Wamp server). • Choose phpMyAdmin when you see menu as shown on the right side of this slide. created by Mr Azlan Yusof, edited by F.Hani

  15. Create Database and Table (using GUI) • You will get the front page of phpMyAdmin as shown below. created by Mr Azlan Yusof, edited by F.Hani

  16. Create Database and Table (using GUI) • Enter the database name (propertydb) in the text field for Create New Database. • Then, click Create. • You can now enter the table name (admin) in text field for Create new table on database propertydb. • Since there are 2 fields for table admin, enter 2 in the text field for Number of fields. created by Mr Azlan Yusof, edited by F.Hani

  17. Create Database and Table (using GUI) • For the time being, concentrate on filling up the following fields: • Field • Type • Length/ values • Index (primary key or not?) • Then, click Save button. created by Mr Azlan Yusof, edited by F.Hani

  18. Create Database and Table (using GUI) • Here is the screen capture of place where you can create the fields. created by Mr Azlan Yusof, edited by F.Hani

  19. Create Database and Table (using GUI) • Here is the screen capture where you can see the created table and fields. Now the database is completed and you can do many things on it. created by Mr Azlan Yusof, edited by F.Hani

  20. Creating the First Page • Our first page is index.php. Why do we need to create a file named index? • In web development, index is always referred as the first page of all web pages. created by Mr Azlan Yusof, edited by F.Hani

  21. Index page • index.php • We need to add PHP functions to: • Connect to database – mysql_connect (host name, username, password) • Select which database to use – mysql_select_db (database name ) • Send the query to manipulate records in table property – mysql_query(query) • Fetch the records in an array variable using while loop. • View each record using HTML <tr> and <td> - mysql_fetch_array(query result, column) created by Mr Azlan Yusof, edited by F.Hani

  22. Index page index.php created by Mr Azlan Yusof, edited by F.Hani

  23. Index page $row[0] $row[1] $row[2] $row[3] index.php created by Mr Azlan Yusof, edited by F.Hani

  24. Index page index.php created by Mr Azlan Yusof, edited by F.Hani

  25. Index page • The mysql_fetch_array() function returns a row from a record set as an associative array and/or a numeric array. • This function gets a row from the mysql_query() function and returns an array on success, or FALSE on failure or when there are no more rows. created by Mr Azlan Yusof, edited by F.Hani

  26. Index page • Syntax: mysql_fetch_array(data,array_type) created by Mr Azlan Yusof, edited by F.Hani

  27. Index page • Associative array: • An associative array, each ID key is associated with a value. • When storing data about specific named values, a numerical array is not always the best way to do it. • With associative arrays we can use the values as keys and assign values to them. • Example: $ages = array("Peter"=>32, "Quagmire"=>30, "Joe"=>34); created by Mr Azlan Yusof, edited by F.Hani

  28. Index page • Why there is a word die? • die() is a function. • Equivalent to exit() • Output a message and terminate the current script. created by Mr Azlan Yusof, edited by F.Hani

  29. Index page • Why do we need die() function? • To handle: • Connection error • Database not found error • Query error • Any possible error created by Mr Azlan Yusof, edited by F.Hani

  30. Index page • How to test the error handling functions and see the effect of die() function? • Simply change the code to wrong statement/parameter • Example: Connection error $conn = mysql_connect("localhost",“azlany",""); • Run index.php again created by Mr Azlan Yusof, edited by F.Hani

  31. Index page • Database not found error $db = mysql_select_db(“azlanydb"); • Run index.php again created by Mr Azlan Yusof, edited by F.Hani

  32. Index page • Query error $query = "select * from azlanytable"; • Run index.php again created by Mr Azlan Yusof, edited by F.Hani

  33. Index page • Why there is $row[0], $row[1], in slide 23? • The number represent column number (numeric array) • The first column start with 0 • The column number is based on the query • If you type the query select * from property, it means that field propertyid is 0, property is 1, addedby is 2, and date is 3. • But if you type the query select date from property, it means that field date is 0. created by Mr Azlan Yusof, edited by F.Hani

  34. Index page • So far, there is no record exists in the database yet. • Thus, we need to allow admin to add record into the database. • Before that, let us create the login page for the admin to add, edit or delete record in database. • Without log in page, anybody can add the property record into the database. created by Mr Azlan Yusof, edited by F.Hani

  35. Something to Think About • Why does the website has no registration page? • You may consider to have registration page if you have more than one administrator. • Since there is only one admin in our application, thus no need to have a registration page. created by Mr Azlan Yusof, edited by F.Hani

  36. Something to Think About • If there is no registration page and the admin data is not exist in database, how can the admin log in? • We can add admin data manually using MySQL console or phpMyAdmin (GUI). created by Mr Azlan Yusof, edited by F.Hani

  37. Login Page • login.php • In this file, there are two sections: • Section a – a form for admin to log in • Section b – validate admin log in, and create a session. • login.php will check whether the userid and password entered by admin are same with the record in the database. • If same, login.php will create a session for the userid, using the session, other webpages (add.php, edit.php, delete.php, logout.php) will recognize that the admin had logged in and still active in the website. • To differentiate the section, PHP will check whether the form has been submitted or not. • If form is not submitted yet, login.php will view section a, else login.php will go to section b. created by Mr Azlan Yusof, edited by F.Hani

  38. Login Page (login.php) login.php created by Mr Azlan Yusof, edited by F.Hani

  39. Login Page (login.php) login.php Text field name created by Mr Azlan Yusof, edited by F.Hani

  40. Login Page (login.php) Column or field name Text field name login.php created by Mr Azlan Yusof, edited by F.Hani

  41. Login Page (login.php) • Try to enter something in user ID and password fields and see what happen. created by Mr Azlan Yusof, edited by F.Hani

  42. Login page • The mysql_num_rows( ) function returns the number of rows in a recordset. • Syntax: mysql_num_rows(data) created by Mr Azlan Yusof, edited by F.Hani

  43. Login Page (login.php) • Why does the browser displays UserID does not exist after user press Login button? • Answer: There is no admin record exists in the database yet, so we need to create a new record in the database. • This time, we will try to use phpMyAdmin to add admin record. created by Mr Azlan Yusof, edited by F.Hani

  44. Login Page (login.php) • How to add? • Single left-click speedometer • Select phpMyAdmin • Select propertydb database in drop-down • Select property table link • Select Insert tab • Type admin in userid field value, and admin in password field value • Make sure Ignore checkbox is checked • Click Go button • Select Browse tab to view all records in Property table created by Mr Azlan Yusof, edited by F.Hani

  45. Something to Think About • Do we need to retype again all PHP functions required to connect to database in every PHP page which get record from database? • Not necessary. You may use include file to save your time. • For the time being, let us assume that we will not use include file, but conventional way. created by Mr Azlan Yusof, edited by F.Hani

  46. Something to Think About • How? • Rewrite all the codes that you learnt before in every pages that require database. created by Mr Azlan Yusof, edited by F.Hani

  47. Something to Think About • In index.php, while loop is used to view all records, but why in login.php,while loop is not used? • In index.php, we want to view all records. Thus, we can use while loop to iterate to each record in the table. • In login.php, our focus is on one particular record only, which the user id (in database) match with user id entered by admin. created by Mr Azlan Yusof, edited by F.Hani

  48. Something to Think About • Next example shows you how to include file. • config.php (example) contains reusable info like database name, table name, server name, database user ID and many more. • You will have to write include("config.php"); at the beginning of file that refers to config.php. created by Mr Azlan Yusof, edited by F.Hani

  49. Something to Think About config.php created by Mr Azlan Yusof, edited by F.Hani

  50. Something to Think About *.php (this is not a complete code) include("config.php"); created by Mr Azlan Yusof, edited by F.Hani

More Related