ࡱ > P R = > ? @ A B C D E F G H I J K L M N O q` 0 bjbjqPqP 0T : : w w w w \ w v x x x x x { v k 4 $ h z { ^ { x x ˲ v v v x x v v v $ x x w F Z d 0 | i i 0 $ $ i e 0 v l D? >F D1 >F AN-NAJAH NATIONAL UNIVERSITY FACULTY OF ENGINEERING COMPUTER ENGINEERING DEPARTMENT Graduation Project Hospital System Prepared By: Ayman Wahbeh Supervisors: Dr. Luai Malhis Dr. Raed al-Qadi Eng: Ashraf Armoush Eng: Samer Arandi To build the online hospital system we need two component: First : The language to write the web pages like asp, HTML, xml and php The last choice will be good as we take the basic in the Advance Network with Dr:Raed Al-Qadi in the previous semester, The next factor that lead me to the PHP is the available of Tutorials over the internet; Second: I will need a database to store the patient information, and the staff information so their was Oracle. As I will learn the PHP why not to learn new database MySql is the alternative for Oracle in my project it is free to use where you can download it from HYPERLINK "http://www.mysql.com/downloads" www.mysql.com/downloads Three-Tier Architectures This project describes web database applications built around a three-tier architecture model, shown bellow At the base of an application is the database tier, consisting of the database management system that manages the database containing the data users create, delete, modify, and query. Built on top of the database tier is the complex middle tier, which contains most of the application logic and communicates data between the other tiers. On top is the client tier, usually web browser software that interacts with the application. The three-tier architecture model of a web database application When we use the term "the Web," we mean three major, distinct standards and the tools based on these standards: the Hypertext Markup Language (HTML), the Hypertext Transfer Protocol (HTTP), and the TCP/IP networking protocol suite. HTML works well for structuring and presenting information using a web browser application. TCP/IP is an effective networking protocol that transfers data between applications over the Internet and has little impact on web database application developers. The problem in building web database applications is interfacing traditional database applications to the Web using HTTP. This is where the complex application logic is needed. 1.2 The Client Tier The client tier in the three-tier architecture model is usually a web browser. Web browser software processes and displays HTML resources, issues HTTP requests for resources, and processes HTTP responses. As discussed earlier, there are significant advantages to using a web browser as the thin-client layer, including easy deployment and support on a wide range of platforms. There are many browser products available, and each browser product has different features. The two most popular windowing-based browsers are Netscape and Internet Explorer. they have a common basic set: All web browsers are HTTP clients that send requests and display responses from web servers (usually in a graphical environment). All browsers interpret pages marked up with HTML when rendering a page; that is, they present the headings, images, hypertext links, and so on to the user. Some browsers display images, play movies and sounds, and render other types of objects. Many browsers can run JavaScript that is embedded in HTML pages. JavaScript is used, for example, to validate a < form > or change how a page is presented based on user actions. Selected web browsers can run components developed in the Java or ActiveX programming languages. These components often provide additional animation, tools that can't be implemented in HTML, or other, more complex features. Several browsers can apply Cascading Style Sheets (CSS) to HTML pages to control the presentation of HTML elements. There are subtle "and sometimes not so subtle" differences between the capabilities different browsers have in rendering an HTML page. Lynx, for example, is a text-only browser and doesn't display images or run JavaScript. MultiWeb is a browser that renders the text on a page as sound (the spoken word) providing web access for the vision-impaired. Many subtle but annoying differences are in the support for CSS and the features of the latest HTML standard, HTML 4. Web browsers are the most obvious example of a user agent, a software client that requests resources from a web server. Other user agents include web spidersautomated software that crawls the Web and retrieves web pagesand proxy caches, software systems that retrieve and locally store web pages on behalf of many other user agents. The Middle Tier In most three-tier web database systems, the majority of the application logic is in the middle tier. The client tier presents data to and collects data from the user; the database tier stores and retrieves the data. The middle tier serves most of the remaining roles that bring together the other tiers: it drives the structure and content of the data displayed to the user, and it processes input from the user as it is formed into queries on the database to read or write data. It also adds state management to the HTTP protocol. The middle-tier application logic integrates the Web with the database management system. In the application framework used in this book, the components of the middle tier are a web server, a web scripting language, and the scripting language engine. A web server processes HTTP requests and formulates responses. In the case of web database applications, these requests are often for programs that interact with an underlying database management system. The web server I use throughout this project is the Internet Information Server IIS I use the PHP scripting language as middle-tier scripting language. PHP is particularly suited to web database applications because of its integration tools for the Web and database environments. In particular, the flexibility of embedding scripts in HTML pages permits easy integration with the client tier. The database-tier integration support is also excellent, with more than 15 libraries available to interact with almost all popular database management systems. Web Scripting with PHP PHP has emerged as a component of many medium- and large-scale web database applications. This isn't to say that other scripting languages don't have excellent features. However, there are many reasons that make PHP a good choice, including: PHP is open source, meaning it is entirely free. As such, community efforts to maintain and improve it are unconstrained by commercial imperatives. One or more PHP scripts can be embedded into static HTML files and this makes client-tier integration easy. On the down side, this can blend the scripts with the presentation There are over 15 libraries for native, fast access to the database tier. Fast execution of scripts. With the new innovations in the Zend engine for script processing, execution is fast, and all components run within the main memory space of PHP (in contrast to other scripting frameworks, in which components are in distinct modules). Empirical evidence suggests that for tasks of at least moderate complexity, PHP is faster than other popular scripting tools. Platform and operating-system flexibility PHP runs integrated with web servers such as IIS. PHP is suited to complex systems development. It is a fully featured programming language, with more than 50 function libraries. The Database Tier The database tier is the base of a web database application. Understanding system requirements, choosing database-tier software, designing databases, and building the tier are the first steps in successful web database application development. MySQL and SQL In this Document, I will introduce the MySQL database management system (DBMS) and the SQL database query language for defining and manipulating databases. MySQL Command Interpreter The MySQL command interpreter is commonly used to create databases and tables in web database applications and to test queries. Throughout the remainder of this Documentation I discuss the SQL statements for managing a database. All these statements can be directly entered into the command interpreter and executed. The statements can also be included in server-side PHP scripts, as discussed in later. Once the MySQL DBMS server is running, the command interpreter can be used. The command interpreter can be run using the following command from the command window: C:\mysql\bin>mysql -u root -p Enter password: **** Running the command interpreter displays the output: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 359 to server version: 3.23.57-nt Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> The command interpreter displays a mysql> prompt and, after executing any command or statement, it redisplays the prompt. For example, you might issue the statement: mysql> SELECT NOW( ); This statement reports the time and date by producing the following output: +---------------------+ | NOW( ) | +---------------------+ | 2002-01-01 13:48:07 | +---------------------+ 1 row in set (0.00 sec) mysql> As with all other SQL statements, the SELECT statement ends in a semicolon. Almost all SQL command interpreters permit any amount of whitespacespaces, tabs, or carriage returnsin SQL statements, and they check syntax and execute statements only after encountering a semicolon that is followed by a press of the Enter key. We have used uppercase for the SQL statements throughout this Documentation. However, any mix of upper- and lowercase is equivalent. On startup, the command interpreter encourages the use of the help command. Typing help produces a list of commands that are native to the MySQL interpreter and that aren't part of SQL. All non-SQL commands can be entered without the terminating semicolon, but the semicolon can be included without causing an error. 3.4.1 Creating Databases The CREATE DATABASE statement can create a new, empty database without any tables or data. The following statement creates a database called test: mysql> CREATE DATABASE test; To work with a database, the command interpreter requires the user to be using a database before SQL statements can be issued. Different command interpreters have different methods for using a database and these aren't part of the SQL standard. In the MySQL interpreter, you can issue the command: mysql> use test 3.4.2 Creating Tables After issuing the use test command, you then usually issue commands to create the tables in the database, Let's look at creation of one table, the customer table. The statement that created this table is shown bellow: Creating the customer table with SQL CREATE TABLE customer ( cust_id int(5) DEFAULT '0' NOT NULL auto_increment, surname varchar(50) NOT NULL, firstname varchar(50) NOT NULL, initial char(1), title varchar(10), addressline1 varchar(50) NOT NULL, addressline2 varchar(50), addressline3 varchar(50), city varchar(20) NOT NULL, state varchar(20), zipcode varchar(5), country varchar(20) DEFAULT 'palestine', phone varchar(15), fax varchar(15), email varchar(30) NOT NULL, salary int(7), birth_date date( ), PRIMARY KEY (cust_id), KEY names (surname,firstname) ); The CREATE TABLE statement has three parts: Following the CREATE TABLE statement is a free-form table namein this case customer. Following an opening bracket is a list of attribute names, types, and modifiers. After the attribute list is a list of keys; that is, information defining what attributes satisfy the uniqueness constraints of a primary key and what attributes are to be indexed for fast access. 3.4.3 Altering Tables and Indexes Indexes can be added or removed from a table after creation. For example, to add an index to the customer table, you can issue the following statement: ALTER TABLE customer ADD INDEX cities (city); To remove an index from the customer table, use the following statement: ALTER TABLE customer DROP INDEX names; The ALTER TABLE statement can also be used to add, remove, and alter all other aspects of the table, such as attributes and the primary index. 3.4.4 SHOW command Details of databases, tables, and indexes can be displayed with the SHOW command. The SHOW command isn't part of the SQL standard and is MySQL-specific. It can be used in several ways: SHOW DATABASES Lists the databases that are accessible by the MySQL DBMS. SHOW TABLES Shows the tables in the database once a database has been selected with the use command. SHOW COLUMNS FROM tablename Shows the attributes, types of attributes, key information, whether NULL is permitted, defaults, and other information for a table. For example: SHOW COLUMNS FROM customer shows the attribute information for the customer table. DESCRIBE table produces the same output. SHOW INDEX FROM tablename Presents the details of all indexes on the table, including the PRIMARY KEY. For example: SHOW INDEX FROM customer shows that there are two indexes, the primary index and the names index. SHOW STATUS Reports details of the MySQL DBMS performance and statistics. Inserting Data Having created a database and the accompanying tables and indexes, the next step is to insert data. Inserting a row of data into a table can follow two different approaches. We illustrate both approaches by inserting the same data for a new customer, new customer. Consider an example of the first approach using the customer table: INSERT INTO customer VALUES (NULL,'new','customer', 'M','Mr', '171 Nablus l0','','','Nablus','Nablus', '7608','Palestine','(059)63576028','', 'email@hotmail.com','1969-11-08',35000); The Second approach is like the following: INSERT INTO customer SET surname = 'new', firstname = 'customer', initial='M', title='Mr', addressline1='171 Nablus 10', city='Nablus', state='nablus', zipcode='7608', country='palestine', phone='(059)63576028', email='email@hotmail.com', birthdate='1969-11-08', salary=35000; Deleting Data There is an important distinction between dropping and deleting in SQL. DROP is used to remove tables or databases; DELETE is used to remove data. The statement: DELETE FROM customer; deletes all data in the customer table but doesn't remove the table. In contrast, dropping the table removes the data and the table. A DELETE statement with a WHERE clause can remove specific rows; WHERE clauses are frequently used in querying. Consider a simple example: DELETE FROM customer WHERE cust_id = 1; This deletes the customer with cust_id=1. Consider another example: DELETE FROM customer WHERE surname = 'Ayman'; This removes all rows for customers with the surname Ayman. 3.5.3 Updating Data Data can be updated using a similar syntax to that of the INSERT statement. Consider an example: UPDATE customer SET email = lower(email); This replaces the string values of all email attributes with the same string in lowercase. The UPDATE statement is also often used with the WHERE clause. For example: UPDATE customer SET title = 'Dr' WHERE cust_id = 7; This updates the title attribute of customer #7. Consider a second example: UPDATE customer SET zipcode = '960' WHERE city = 'Amman'; This updates the zipcode of all rows with a city value Amman Opening and Using a Database Connection We introduced the MySQL command interpreter. In PHP, there is no consolidated interface. Instead, a set of library functions are provided for executing SQL statements, as well as for managing result sets returned from queries, error handling, and setting efficiency options. We overview these functions here and show how they can be combined to access the MySQL DBMS. Connecting to and querying a MySQL DBMS with PHP is a five-step process. next code shows a script that connects to the MySQL DBMS, uses the test database, issues a query to select all the records from the customer table, and reports the results as preformatted HTML text. The example illustrates six of the key functions for connecting to and querying a MySQL database with PHP. Each function is prefixed with the string mysql_. We explain the function of this script in detail in this section.
The five steps of querying a database are numbered in the comments in the previous example, and they are as follows: Connect to the DBMS and use a database. Open a connection to the MySQL DBMS using mysql_connect( ). There are three parameters: the hostname of the DBMS server to use, a username, and a password. Once you connect, you can select a database to use through the connection with the mysql_select_db( ) function. In this example, we select the test database. Let's assume here that MySQL is installed on the same server as the scripting engine and therefore, we can use localhost as the hostname. The function mysql_connect( ) returns a connection handle. A handle is a value that can be used to access the information associated with the connection. As discussed in Step 2, running a query also returns a handle that can access results. Run the query. Let's run the query on the test database using mysql_query(). The function takes two parameters: the SQL query itself and the DBMS connection to use. The connection parameter is the value returned from the connection in the first step. The function mysql_query( ) returns a result set handle resource; that is, a value that can retrieve the outputthe result setof the query in Step 3. Retrieve a row of results. The function mysql_fetch_row( ) retrieves one row of the result set, taking only the result set handle from the second step as the parameter. Each row is stored in an array $row, and the attribute values in the array are extracted in Step 4. A while loop is used to retrieve rows until there are no more rows to fetch. The function mysql_fetch_row( ) returns false when no more data is available. Process the attribute values. For each retrieved row, a for loop is used to print with an echo statement each of the attributes in the current row. Use mysql_num_fields( ) is used to return the number of attributes in the row; that is, the number of elements in the array. For the customer table, there are six attributes in each row: wine_id, wine_name, type, year, winery_id, and description. The function mysql_num_fields( ) takes as a parameter the result handle from Step 2 and, in this example, returns 6 each time it is called. The data itself is stored as elements of the array $row returned in Step 3. The element $row[0] is the value of the first attribute , $row[1] is the value of the second attribute, and so on. The script prints each row on a line, separating each attribute with a single space character. Each line is terminated with a carriage return using echo "\n" and Steps 3 and 4 are repeated. Close the DBMS connection using mysql_close( ), with the connection to be closed as the parameter. Passing Data with URLs The first technique that passes data from a web browser to a web server is manual entry of a URL in a web browser. Consider an example user request with a parameter. In this example, the user types the following URL directly into the Location box in the Location toolbar of a explorer browser: http://localhost/main.php?no=150 The URL specifies that the resource to be retrieved is main.php with a query string parameter of no=150 appended to the resource name. The user then presses the Enter key to issue an HTTP request for the resource and to use the GET method that passes the parameter to the resource. The query string parameter consists of two parts: a parameter name no and a value for that parameter of 150. The script resource main.php is shown bellow. Before the script is processed by the PHP scripting engine, variables associated with any parameters to the resource are initialized and assigned values. In this code, a variable $no, which has the same name as the URL parameter name, is automatically initialized by the PHP engine and assigned the value 150 that was passed in the URL. This variable and its value are then accessible from within the script, making the data passed by the user available in the middle tier. Printing the value of a parameter passed to the script with an HTTP request