Web-Based Library Management Systemwith PHP and MySQLBachelor’s ThesisMarch 2011Jin JiaweiDegree Programme in Information TechnologySpecialization in Network Communication Technology

BACHELORS’S THESIS ABSTRACTTurku University of Applied SciencesDegree Programme Information TechnologyDate 05.04.2011 Number of pages 40Instructor: Yngvar Wikström M. EngJin JiaweiWeb-Based Library Management System with PHP andMySQLThe objective of this Bachelor’s thesis was to develop a web-based librarymanagement system based on PHP and MySQL in order to reduce the cost ofmanagement and make it convenient for the user. The web-based librarymanagement system includes the most popular components a common librarymanagement system has, administration, book seeker, leasing and E-mail.Besides, it has more humanistic functions such as second-hand online book shopand top 10 ranking. The website was tested on some of the most popular browsers.The basic functions of Internet Information Services 7.5 and detail features of PHPand MySQL were selected for the purpose of this project. The structure of LibraryManagement System which included a website interacting with a database wasdetermined. The system and development tools were chosen based on theirspecific features that benefit the system. Furthermore, all necessary components inthe library management system were integrated and tested.The first result of this study was an understanding of the advantages of PHP andhow MySQL benefited a large project. The second result was building practicalPHP and MySQL projects based on a web server on Windows 7 ultimate.The understanding of the development tools directly affected the quality of thewebsite. The website which was built in this project offered a high performance,secure, stable and an easy-to-maintain environment. In addition, the websiteimproved the management of a university library. The site was built for testingpurposes only.Key words: library management system, PHP, IIS, MySQLBACHELOR’S THESIS OF TURKU UNIVERSITY OF APPLIED SCIENCES Jin Jiawei

ACKNOWLEDGEMENTThe thesis was carried out in Turku, Finland. The previous experience in Miccia Oyas a junior web developer supports the practical part of this thesis. I would like tothank my manager Maria Jokelainen for offering the opportunity and help, and mysupervisor Yngvar Wikström for guidance and comments on the text and for thevaluable advice about implementing the database.Author’s name: Jin JiaweiDate: 05.04.2011BACHELOR’S THESIS OF TURKU UNIVERSITY OF APPLIED SCIENCES Jin Jiawei

GLOSSARY AND ABBREVIATIONSAPIApplication Programming InterfaceASPActive Server PagesCMSContent Management SystemDNSDomain Name SystemERDEntity-Relationship DiagramFTPFile Transfer ProtocolGPLGeneral Public LicenseHTMLHyper Text Markup LanguageHTTPHypertext Transfer ProtocolIEWindows Internet ExplorerIISInternet Information ServicesIMAPInternet Message Access ProtocolIPInternet ProtocolIPNInstant Payment NotificationISBNInternational Standard Book NumberJSPJava Server PagesPDTPayment Data TransferPHPHypertext PreprocessorBACHELOR’S THESIS OF TURKU UNIVERSITY OF APPLIED SCIENCES Jin Jiawei

RDBMSRelational Database Management SystemSPARCScalable Processor ArchitectureSQLStructured Query LanguageTCPTransmission Control ProtocolUTF-8Universal Character Set Transformation Format-8 bitURLUniform Resource LocatorWAPWireless Application ProtocolWWWWorld Wide WebBACHELOR’S THESIS OF TURKU UNIVERSITY OF APPLIED SCIENCES Jin Jiawei


6.2Implementing the Database . 146.3Accessing Database . 166.4User Authentication. 176.5Book Searching . 206.6Online Shopping . 216.6.1 Implementing Shopping Cart . 226.6.2 Implementing Payment. 236.7E-mail Service . 256.8Top 10 Ranking . 276.9Further Functions . 277Discussion . 298Conclusion . 30BACHELOR’S THESIS OF TURKU UNIVERSITY OF APPLIED SCIENCES Jin Jiawei

LIST OF FIGURESFigure 1:A sample test file . 5Figure 2:Block diagram of system structure . 13Figure 3:ER-diagram of the database . 14Figure 4:The form according to search.php . 20Figure 5:The data flow of the user view in the library management system . 21Figure 6:Functionality of E-mail Service . 25Figure 7:Administrator’s login form . 27BACHELOR’S THESIS OF TURKU UNIVERSITY OF APPLIED SCIENCES Jin Jiawei

11 INTRODUCTIONWhile seeking for a book in the university’s library, students must check whatcategory the book belongs to and check the books within this category one by one.Students might keep the book for a long period; sometimes, remembering to renewthe book before a specific date is not very easy; this loan period might exceed thetime limit so that the students must pay for a fine and the fine could even be worthas much as to purchasing a second-hand book. Some university libraries havebeen trying to deal with those problems, Åbo Akademi library, for instance, allowsstudents to search the book from the website and shows the bookshelf location butnot the specific row and column and it will remind the student via E-mail, but itdoesn’t have its own E-mail system.This thesis was produced by initiating some theoretical ideas from a project in thesame field called Koha. Koha provided the method to reduce the cost ofmaintenance. [1]The goal of this thesis was to design a website with PHP and MySQL that supportthe additional functions listed below in addition to the basic functions which are to: Display specific location of books in search result Support second hand book shop Integrate E-mail service and Subscriptions Provide Top 10 rankingThis solution was chosen because PHP could be embedded into HTML and itsopen source itself so that the developer would not need to recode the originalsource code with any extra funds. Even though the installation phase is discussed,the focus is more on empirical work. Bank negotiation is a part of the shopping cart;however, it is beyond the scope of this thesis. The feature and installation ofdevelopment tools are introduced. Then the construction of this project ispresented and ultimately each component of website is introduced.BACHELOR’S THESIS OF TURKU UNIVERSITY OF APPLIED SCIENCES Jin Jiawei

22 INTERNET INFORMATION SERVICES 7.5 (IIS 7.5)Microsoft IIS was used as the web server in this project, even though there weremany other web servers, it is the most used web server after Apache and it doesnot need any complicated configurations before being using [2]. Furthermore, afterIIS 6.0 was released, Microsoft’s security was greatly improved and especially itcooperated with the .net environment, the cooperation makes the security almostperfect.2.1 FeaturesThe difficulty of the tool directly affects the amount of users, especially the webpublishing tool. After all, many companies want to have their own websites, but donot want to hire a high-paid network administrator to maintain them. Therefore, thecompanies must use tools which are relatively easy to build the website with.It is easier to use IIS as the web server, make it work and publish the website andits configuration is graphical.The language of establishing a website or the forum is varied, such as ASP, PHP,JSP and other languages. It is quite stable to run ASP on IIS, especially in the .netenvironment. However, PHP is supported on Windows 7 only after a complexconfiguration.The FTP Server, Web Management Tools, World Wide Web Services were turnedon before the IIS configuration. In order to support PHP, Module Mapping wasadded in IIS and the executable option associated with the location of php-cgi.exeafter installing PHP in the local server.BACHELOR’S THESIS OF TURKU UNIVERSITY OF APPLIED SCIENCES Jin Jiawei

32.2 Application poolApplication pool was proposed after IIS6.0 was released to support highperformance web application design. It associates one or several applications withone or several groups of process. Those groups can avoid the overflow effectivelywhen the website consumes too large memory. [3]The website was assigned a separate application pool in this project, anindependent process called w3wp.exe was created in the system once the sitestarted, therefore, even if the site hangs up for some reason, it will not influenceother sites which ran on the same web server.If each site used a separate application pool, there would be a problem that theserver would be overloaded. Meanwhile, the server would need to allocate morememory and CPU usage. However, for the high performance servers located in acompany, using a separate application pool is a best choice to provide the userswith the server’s strong performance regardless of system resource.2.3 SecurityTimely installation of patches for the IIS web server is especially important sincethe security patches relate to the system security. The Microsoft official websiteoften publishes the latest security patches. For this reason, the server used in thisproject was ensured to be updated once a week. And the FTP root directory wasassigned to the logical drive in order to prevent hackers’ attack. In addition, theFTP default port was modified to other than 21 and the log was enabled in case ofserver exception.BACHELOR’S THESIS OF TURKU UNIVERSITY OF APPLIED SCIENCES Jin Jiawei

43 HYPERTEXT PREPROCESSOR (PHP)3.1 What is PHPPHP (recursive acronym for PHP: Hypertext Preprocessor) is a widely-used opensource general-purpose scripting language that is especially suited for webdevelopment and HTML can be embedded into it. The developer could use PHPand HTML to generate the homepage. Once a visitor accesses the index page, theserver will execute the PHP command and send the outcomes of implementationto the visitor’s browser, however, the difference is that PHP is open source andsupports most of the popular platforms and it can be run on a Windows platformand multiple versions of UNIX. [4]PHP does not require any pre-processing before rapid feedback; it does not needmod perl to adjust the memory image of server. PHP consumes few resources, asa part of the IIS server, PHP does not need to call an external binary code and theserver does not bear any additional burden. In addition to operating the page, PHPcan cooperate with HTTP. The cookie and digital signatures management can bemodified in the settings, and it provides a good connectivity to the database.There is no need to have a very special development environment with PHP, theblock starts and ends with the tags ?php and ? .Certainly, PHP could beconfigured with tags and even in ASP format and it would deal with everythingamong those signs, but not in the same file.The PHP programming language is similar to Pascal. There is no need to defineany variables before using them, and it is very simple to establish the array and theHash. PHP also has some object-oriented features which are provided to supportand to organizing and packaging the source code.BACHELOR’S THESIS OF TURKU UNIVERSITY OF APPLIED SCIENCES Jin Jiawei

53.2 Installing PHP on WindowsSince PHP is open source, it is easy to download the Windows Binaries from thePHP official website. The PHP version used in this project is 5.2.0. Above all, thePHP 5.2.0 zip file was downloaded and decompressed to local hard disk. In orderto associate PHP with the local database, some extensions were enabled in a textfile called php.ini such as mbstring, gd2 and MySQL. In the next place, index.phpwas added into the default library of IIS so that the server could recognize the phpfile located in the root of the web server once the visitor accesses the IP address orthe DNS name.Figure 1. A sample test file.The successful of installation was tested by creating a file called info.php which isshown in Figure 1, the file was placed into default website folder and the browsershowed the result by typing http://localhost/info.php. Localhost is the DNS name oflocal IP address.BACHELOR’S THESIS OF TURKU UNIVERSITY OF APPLIED SCIENCES Jin Jiawei

64 MySQLMySQL was owned and sponsored by a Swedish company MySQL AB, nowowned by Oracle Corporation [5]. MySQL is free for open source and not-for profitprojects. For commercial use, developers have to pay a license fee and paideditions offer additional functionality [6].4.1 What is MySQLMySQL is a Relational Database Management System (RDBMS) that runs as aserver providing multi-user access to a number of databases. [7]MySQL was designed for three principles, which are performance, reliability andusability. A cheap, distinctive, fast and efficient RDBMS was created by followingthose principles. MySQL becomes a perfect tool for developers and administratorsto establish maintain and configure complex applications. It has the following mainfeatures:PerformanceIn the RDBMS, the speed of executing a query and returning the results to thesearchers is very important. MySQL is very fast, sometimes the implementation ofmajor orders is even faster than its competitors. The benchmark on the MySQLofficial website shows that it is superior to almost all other databases such asMicrosoft SQL Server 2000 and IBM DB2 [8].Open SourceThe developer of MySQL(MySQL AB) is a strong supporter of open source, andMySQL software could be used smoothly under General Public License (GPL).BACHELOR’S THESIS OF TURKU UNIVERSITY OF APPLIED SCIENCES Jin Jiawei

7Users can download and modify the source code to meet their needs of application,and are free to use it to enhance their applications. [9]ReliabilityIn most cases, the higher the performance of the database is, the more it willreduce the reliability. However, MySQL is not the case as it provides maximumreliability and uptime, and a large number of demanding applications has beentested and certified. MySQL’s huge user base will help to quickly find and resolvethe existent defects, and can test software in a variety of environments; thisapproach has created almost no defects in the software. In addition, each newversion of MySQL must be tested with internal testing and crash-me tool testing,whose main purpose is to reach its limit to access the ability of the system [10].PortabilityMySQL can be run on UNIX and non-UNIX operating systems, including Linux,Solaris, FreeBSD, OS/2, MacOS, and Windows, it can run on a range ofarchitecture, including Intel x86, Alpha, SPARC, PowerPC and IA64, it alsosupports the 386 series from low to high-end Pentium machines and IBM zSeriesmainframes.[11]4.2 Installing MySQL on WindowsWe simply downloaded MySQL windows installer and installed it into local harddisk. The following settings were configured in the MySQL Server: Server type Database usage Path of InnoDB tablespaceBACHELOR’S THESIS OF TURKU UNIVERSITY OF APPLIED SCIENCES Jin Jiawei

8 Number of concurrent connections TCP/IP Networking Server SQL mode Default character set Windows Service Security.After the configuration of the MySQL Server, the setting was executed into aconfiguration file. The service was started and the security settings were applied.The default character set used in the server was utf-8. It is widely usedtransformation format with encoding for the world-wide web and accounting formore than half of all Web pages. [12][13]BACHELOR’S THESIS OF TURKU UNIVERSITY OF APPLIED SCIENCES Jin Jiawei

95 SYSTEM AND DEVELOPMENT TOOLSThere were four components that needed to be added in the system when thedevelopment of the website was made. The computer needed the right kind ofoperating system to support Microsoft IIS 7.5, the PHP web pages needed to behosted by the IIS server. Secondly, a database engine and a program were chosento easily manipulate the construction of the database. The development processwas made by using a convenient text editor. At last, the distribution of the websitewas designed by a web development application.5.1 Operating SystemThe programming work was carried out on one computer which ran the Windows 7Ultimate system. The home version was not chosen since it did not support the IISweb server. The features of IIS 7.5 were introduced in Chapter 2. In the finalimplementation, the PHP website was tested on two computers which ranWindows 7 and Ubuntu, and it was tested on IE, Chrome, and Firefox.5.2 PhpMyAdminThe database management tool used in this project was phpMyAdmin. It is anopen source tool written in PHP. It realized the web database management insteadof the traditional system implementation.The following configuration was modified in in order to access thephpMyAdmin from the local host: cfgServers[1]["host"] "localhost"; // hostname of MySQL cfgServers[1]["port"] ""; // port of MySQL, default value is 3306 cfgServers[1]["adv auth"] true; // Whether use advanced functions or not cfgServers[1]["stduser"] "username"; // username of administrator cfgServers[1]["stdpass"] "password"; // password of administratorBACHELOR’S THESIS OF TURKU UNIVERSITY OF APPLIED SCIENCES Jin Jiawei

10The successful of installation was tested by typing EditPlusThe main code of PHP website was written on EditPlus. It is a functional 32-bitcompiler which can handle text, HTML and almost all programming languages. Thereason why EditPlus was chosen was that it supports HTML, CSS and PHP whichwere involved in this project.In addition, it has the following features so that it could make the projectdevelopment process even faster. It includes: fast booting support syntax highlighting support code completion good project management built-in Browser.5.4 DreamweaverAfter compiling the PHP website, Dreamweaver CS5 was used to design the layoutof website. After Dreamweaver CS5 was released, Content Management System(CMS) was supported and PHP received better support, not only the programmercould use code hints in the document to search a custom function, but could alsouse this feature to design better CMS templates. In addition to those features,Dreamweaver CS5 had some other new features, such as its integratedBrowserLab network service that allows the developer to link to a web browser inthe laboratory and to using a different browser to check the layout at the same time.In order to test the compatibility of PHP website with different browsers, theBACHELOR’S THESIS OF TURKU UNIVERSITY OF APPLIED SCIENCES Jin Jiawei

11computer needed installations of several browsers, however, CS5 had the build-inWebkit engine included so that it could simulate Safari and Chrome and previewthe layout. This did reduce the resources for testing the layout.RequirementsSince the Dreamweaver was implemented on the Windows system, by way ofmeeting the needs of stable running, there were some minimum requirements for itand these are: Intel Pentium 4 or AMD Athlon 64 processor Microsoft Windows 7 Ultimate or Enterprise 512MB of RAM 1GB of available hard-disk space for installation 1280x800 display with 16-bit video card DVD-ROM drive Broadband Internet connection required for online services[14].BACHELOR’S THESIS OF TURKU UNIVERSITY OF APPLIED SCIENCES Jin Jiawei

126 THE LIBRARY MANAGEMENT SYSTEMThe library management system is a web-based widespread informationmanagement system which realizes information storage and query. Through thesystem requires preliminary design, detailed design, coding and testing, thedeveloper could get a good experience of coordination and hands-on developmentcapabilities.The book search and lending management system which were developed are animportant part of the whole library management system, mainly for the retrieval,query and lending of the books in the library. Those are the epitome of the mainlibrary management system.6.1 OverviewThe website was used to implement search and book lending management,therefore, the system had the following features: The reader could search the books according to the information they have. The result is fuzzy-matched when searching. The reader could borrow the books based on the searching result they got. The reader could check the current lending books and lending history. The reader could return the book they are keeping.The system provided a simple interface for quick book searching, lending andreturning. The interface was designed to be mainly used for the common browsers,making the system migration and usage easier.BACHELOR’S THESIS OF TURKU UNIVERSITY OF APPLIED SCIENCES Jin Jiawei

13Structure of Library Management SystemFigure 2 shows the whole structure of Library Management System. After a useraccessed the IP address of Library Management System, the initialization interfacewould be shown in the broswer unless a server error was detected by the webserver. Here, the user could access the top 10 ranking and search modules withoutlogin. Once the user had logged in successfully, the user would be able to selectthe main functions of the Library Management System which were Book Status,Lending, Return Books, Lending Status, Online Shopping, E-mail Service andLogout. If the username and password the user entered did not tally with thevalues stored in the database server, then the webpage would notice the problemand be turned back to the index page. The web server cooperated with thedatabase server and all the informaiton used in this system was stored in theMySQL database server.Figure 2. Block diagram of system structure.BACHELOR’S THESIS OF TURKU UNIVERSITY OF APPLIED SCIENCES Jin Jiawei

146.2 Implementing the DatabaseIn the first phase of system design, we built an ER-diagram for the database. ERDwas used to describe information needs and the type of information stored in thedatabase during the requirements analysis phase. Figure 3 shows the ERD of thedatabase. Entity represents a discrete object, it can be considered as a noun, suchas Publication, Physical Copy, Location, Loan and Customer.Relationshipcaptures how two or more entities are associated with each other, it can beconsidered as a verb, such as Copy, Store, Lend and Borrow. Entity sets aredrawn as rectangles, relationship sets as diamonds. If an entity set participates in arelationship set, they are connected with a line. Entities and relationships haveattributes which are drawn as ovals.Figure 3. ER-diagram of the database.BACHELOR’S THESIS OF TURKU UNIVERSITY OF APPLIED SCIENCES Jin Jiawei

15CustomerFor the library management system to run, we need a customer to either borrow orpurchase books. Each customer has a unique number which is User ID; thisnumber is used to identify one customer from the other customers. After acustomer registers an account, the customer will hold one personal username andpassword to access the user authentication. An e-mail message will be created incase of losing the password.Publication, Physical copy and LocationPublication contains the detailed information of books; each book has a unique title,author and publication year. Other information of the book is needed for thecustomers to search books. Each publication may have many physical copies andeach copy has a copy number which distinguishes it from different copies from thesame publication. Shelf, Department and Name are used to define the specificlocation of a book.LoanLoan was created so that the customer may borrow the book he had borrowedagain. Once a customer borrows a book, the loan information will be written in thedatabase which contains Data, Return date and User ID. If the customer returnsthe book later than a specific return date, then a fine will be charged. The cost ofthe fine will be counted according to the return date and returned date.Global variables used in the system1. Logged Sign SESSION[“login”]2. Username SESSION[“username”]3. Password SESSION[“password”]4. Connect to SQL [email protected] pconnect()BACHELOR’S THESIS OF TURKU UNIVERSITY OF APPLIED SCIENCES Jin Jiawei

165. Select [email protected] select db()The variables above were used in sql config.php and any other files could usethose variables as long as they hold this file.The relationship between data structures and systemLogin module: The system will set the global variables from 1 to 5 after successfulloginLog out module: The system will re-set the global variables from 1 to 5 aftersuccessful log out.All other modules need to include the reference to the file sql config.php to confirmthe login information when they are being used. If the visitor is not logged in, thesystem will turn to the default login page automatically during borrowing andreturning books.6.3 Accessing DatabaseThis system was connected to the database through the specifications ofsql config.php. All modules were required to include sql config.php when in use,and the syntax of connecting database is as follows: ?php dbhost 'localhost';// Database server dbuser 'username';// SESSION["username"];// Database username dbpass "password";// SESSION["password"];// Database password dbname 'library';// Database [email protected] pconnect( dbhost, dbuser, dbpass) or die(“cannot connect to server!");//echo ("config");BACHELOR’S THESIS OF TURKU UNIVERSITY OF APPLIED SCIENCES Jin Jiawei

[email protected] select db( dbname) or die("cannot select database!");? The web server will check the hostname, username and password of databaseserver while connecting to it. After successfully connected, the server will check thename of selected database and search for the values of tables.6.4 User AuthenticationLogin and RegistrationThe first page which was built was called index.php because it provided users withthe opportunity to log into the system. In order to accept a user table informationand URL parameters, the global variables POST and GET were predefined.PHP regular expressions were used to determine whether the user’s input tallieswith the requirements or not. Session was used to keep the user’s login informationafter detecting user logs. The main function was divided into three parts: userregistration, user login and user logout.User registrationThe user registration has four main features which are to: fill out the registration information form and javascript detect the initialregistration information entered by the user check the registration information by registration processing module detect whether the username was already exist or not write the user’s information into the database, registration is successful.BACHELOR’S THESIS OF TURKU UNIVERSITY OF APPLIED SCIENCES Jin Jiawei

18User loginThe main features of user login are: Javascript detects the initial login information entered by the user in thelogin for

May 04, 2011 · PHP and MySQL projects based on a web server on Windows 7 ultimate. The understanding of the development tools directly affected the quality of the . Since PHP is open source, it is easy to download the Windows Binaries from the PHP official website. The PHP version used in this project is 5.2.0. Above all, the . library management system.