Web Database Based Inventory System

Web Database Based Inventory SystemWeb database found store variant is for management of occurrences, ordinances, guests and allowance. This musical arrangement helps in Stock management, payment, and coifs. This arranging lay offs conducting payment for nodes and for ordinationed items in the procure order. The master(prenominal) objective of this project is about the design of Web-based inventory organization. bakeshop feyasse mathematical mapping traditional theme based inventory dodging of rules to manage his culture concerning customer, ordered item, item and payment. there ar ruptureal(prenominal) problems with this system such as time consuming, hard labour and sometimes lost of instruction. The sales maturement and order mis piazzament cause a great deal of work for Bakery Feyasse.The manual system is pr integrity to errors resulting major complaints from customers.The volume of data and complexity of analysis to be conducted seems overwhelming using their manual system which sometimes raises suspicions of croaked results which are in any case difficult to correct or verify. After careful investigation, watchword thorough analysis of existing method, it was agreed that a new effective system is required to sort out Bakery Feyasse problems.After reviewing the possible design choices, it was decided that MySQL database and PHP script language is the best economical tog for Bakery Feyasse system in terms of cost, ease of maintenance, interface design and upgradability using Microsoft windows platform. The database would be touch to the companys bladesite via PHP script language. The online innkeeper pull up stakes enable employee to enter, create, up go steady and delete entropy online.1.1. Project objectives1. To create a system that get out suffice the Bakery employee at their different task2. To explore the ideas involved in the developing of a network database inventory site.This vane database inventory syst em forget include the main function as following carry data from database qualify tuition into the databaseDelete information from databaseAnd tack on information into databaseThe objectives of this project areDesign a relational databaseImplement this databaseTesting the new systemEvaluate the system1.2. Personal objectivesMy personal objective for this project is to complete the project on time. I set up a monthly time table to carry out different task. My fervency is that after completing this project. I will gain more knowledge in computer applied science. To enable me to succeed this project, I utilise the skills I gain last year in MySQL, and SQL. I in addition short-change how to create a participating web site which I did not know. I need new script language which is PHP.1.2. 1. Career objectivesGain my BSc Honours Degree.Gain more skills in computer programming language, Computing science and Information Systems Technologies.1.2. 2. Academic objectives1.Learn pr ogramming language such as PHP and MySQLLearn how to use PHP script language to create a form to retrieve information from websiteLearn how to connect MySQL Database with dynamic WebsiteHow to use PHP code to retrieve information from MySQL databaseHow to create HTML formHow to use HTML form to retrieve information from databaseHow to create database table using MySQL coating.1.3. Project LimitationsI am entirely assured that my assignment is to create a web database system which is not an easy task and I am also aware of the short I subscribe got to accomplish this task. So the time is my target. I am thinking of finish on time. Time management has been considered because I also got one exam to prepare.1.4. Gantt chart1.5. Literature reviewAt the start of my project, the researches have been make to gathering information because some activities go into the development of a new system. Understanding system requirements, choosing database-tier software, designing databases, and building the tier are the starting signal steps in successful web database application development. The following smothers have been exploredInformation System analysisDatabaseDatabase management systemsDatabase ServerDatabase tierInformation Systems AnalysisSystem AnalysisSystem DesignThis manikin salute how the system run.PrototypeThis phase will enable the employee to understand the functionality of the new system.Implementation tuition of the new systemTestingThe new system is tested in order to verify its functionsDatabaseA database is a postulateion of related data. The data stored is rows, which make up a simple customers, items, orders and payment details.Database solicitude SystemDatabase Management System enables the substance abusers to retrieve relevant information. It refers to information infrastructures that make up of relevant information. Database system creates and maintains a database and enables individual business application to extract the data in orde r to create report.Database serverDatabase server is a system designed to manipulate the database. Its function is to store, retrieve, and manage information. Its plays an important role in web services and provides the n-tier connection through the Internet users via their web browser.Database tierDatabase tier is the base of a web database application. In a three-tier architecture application, the database tier manages the data. The data management typically includes storage and retrieval of data, as well as managing updates, admission charge by more than one middle-tier address, providing security, ensuring the integrity of data, and providing support services such as data backup.1.6. Technical reviewThe web based database inventory system is creating using MySQL application and PHP script language. The interface of the system is developed with Hypertext Markup Language (HTML). The system is come-at-able anywhere that have access to Internet.1.6.1. Wed Based Database Systems d evelopment toolsDreamweaver CS3Dreamweaver CS3 Dreamweaver is a full-featured Web application development tool. Its features not only assist with creation and editing Web paginates, plainly also with managing and maintaining the entire Web site.MySQLMySQL implies the Client /Server architecture, which compose of a server process and client process. A server process manages the logical database management. A sever process takes of how the data is stored and organized.The client process would be described as process that accesses the server in order to store, retrieve, update and alter the database schema.SQLSQL (Structured Query Language) SQL apply to communicate with most databases. You groundwork manage your database, retrieve data from it, or add data to it using SQL queries. You build and send an SQL ask to the database. The database responds by execute the action defined by the query or, if the database is unable to perform the requested operation, its shines error messag e with information about the problem.Cold FusionColdFusion is a tagged language, developed by Macromedia, who is also the developer of Dreamweaver. A stand-alone Web server is also included as part of ColdFusion. The stand-alone server is fine development, but you should use a more powerful Web site.ASPASP Application Server Pages (ASP) is Microsoft engine room. It create into IIS (Internet Information Server).When you use ASP technology you have your choice of languages VBScript or JScript. When you set up your Dreamweaver site, you ready which language you want to use.1.6.2. Web Based Database development technologiesPHPPHP is a Scripting Language that is interpreted on the web server before the webpage is sent to a web browser to be displayed. This smoke be seen in the expanded PHP recursive acronymPHP-HypertextPre-processor.1.7. Technologies utilise1.7.1. PHP and MySQLPHP and MySQL work in combination, where the database is MySQL and the script language is PHP.How PHP and M ySQL work together?PHP collect information and MySQL store information. PHP will create retrieve information form for the system, but MySQL keep the information in a format PHP.1.7.2. Why use PHP and MySQLPHP and MySQL combine together make easy the development of a dynamic Web page. HTML creates useful web pages with the addition of PHP and MySQL where you john collect information.1. 8. Methodologies flak1.8.1. PrototypingPrototyping is most appropriate methodology for systems that include data querying, online data updating, and on-line data reporting functions. It is used to create appropriate data access interfaces for users and to help identify the cowlabilities that users wants in the system, such as the options on a menu used by the a system.1.8.2. Rapid Application DevelopmentIts basic process is to get systems personnel and users together in formal meetings, where they rapidly develop systems that provide users with their information needs.1.8.3. elect MethodologyPrototy ping Methodology Development choice has been influenced by several factors1. Clarify the user requirements2. Familiarity with the base technology3. System complexity4. Need for the system reliability5. Time pressure6. Need to see occur on the time schedule.7. And when timelines are short because they best enable me to adjust thefunctionality in the system on the basis of a specific delivery date.CHAPTER 2 ANALYSIS2.1. Bakery Feyasse profileBakery Feyasse is a Bakery that was created by Malan Kouao in 1988. The business started in 1988 with one Bakery and after continuous make a second Bakery has been created in 2008. The diagram below show the Bakery Structure outturn managerSale managerManagerChairman part managerDepartment managerDepartment managerFig Bakerys Feyasse structure2.2. Existing system (appendices)The Bakery use traditional paper based where the employee checks the approachability of the items by querying the items makes, and fills the book by using paper and pen. M oreover the ordering process is make by pen and hand and it is time consuming to contact the production and it causes slow moving for the customer order and there is risk of mistake caused by the employee.All employee access the same(p) data, share the same information .The salesperson check the availability of the items by querying the production department. The production department check the availability of the items from the book register manually. After knowing the availability, he fills up the availability book manually so that the salesperson will know which item is available. The account manager checks the stock items manually in order to re order the product. The information about the item, the customers, and the suppliers is kept in a temporary contact book2.2. Current SystemsThe proposed system is a web based inventory system. The staff of each department nookie work peacefully at their department. The system allows the staff to fill up the order payment, the order, the item order form. The objective of the new system is to create an online access database to store all data concerning the Bakery.The main activities performed by the system are.The system provides data processing and storage.The system display system functionality.The system functionality is tender by clicking selected task.The system will allow the staff to insert information through the selected page.The system will allow the staff to cancel information.The system will allow the staff to process a payment.The system will allow the staff to update information.2.2.1. Benefits with current system1. To increase the good organisation in item availability, customers details, orders, ordered items and payment data inputting by using database system.2. Salespersons will be able to view the argument of items produced by the production department anytime3. Customers details can be kept4. read can be kept for future monthly report.System Requirement AnalysisAt this stage I met the user to obtain a detailed understanding of Bakery needs. A functional specification, process poser, logical model are defined.Analysis of the problem where Bakery Feyasse try to find a solution with the new systemDefining the problemIdentify the problem causesSpecifying the solutionIdentifying the users requirements,And identifying new opportunity for using new technology2.2. Users RequirementsDefine the objectives of the new system and develop a detailed description of the function that the new system will performThe system provides data processing and storage.The system display system functionality.The system functionality is accessible by clicking selected task.The system allows the staff to insert information through the selected page.The system allows the staff to cancel information.The system allows the staff to process a payment.The system allows the staff to update information.The manager shall be able to do the job of the salesperson2.3. System SpecificationThe system would enable end-users (employee) to create, update informationAll information about customer, item, order and payment will be stored in a database, which the web database inventory system will display through different interfacesAllows the deletion and appends informationFacility to query the databaseEnables the manipulation of sales activitiesTracking user activities using the system2.4. Use CasesSelect order/customer details jibe order/customer detailsDelete order/customer detailsUpdate order/customer detailsUse cases description (appendices)CHAPTER 3DESIGN3.1. Architectural DesignLogical designPhysical design3.1.1. Entities and AttributesBakery Feyasse will have a relational database system with the following entities and attributes.customer (cust_id, cust_name, cust_address,cust_city)order(order_id,cust_id*,item_id*, quantity,item_price, total_price)payment(order_id*,payment_id, payment_type, payment_description, amount)item(item_id ,item_name, item_price, item_description)3.1.2. Connectin g To DatabaseIn order to issue MySQL data manipulation statements, we moldiness specify a username, a fleetword and a database to use. This information will have been provided by your systems adminsistrator. If you are running MySQL yourself, then skip ahead to the section onCreating a Database in Part 6 then come back to this point.Either way you should now have in your possession a database name, a valid password for that database and a password. For the purposes of these workshops the following example set will be used.Database NamevworksDBValid MySQL UsernamevworksPasswordmypassIn order to log into MySQL correctly we moldiness pass information to the MySQL client program when we start it. This is done with the following commands and syntax.$ mysql -u -p 3.1.2. Database Design using MySQLMySQL database is made up of database which contains tables. A table is a section of the database for storing information.mysql show columns from item++-++++-+ Field Type bootless nomin ate Default Extra ++-++++ item_id int(11) NO PRI profitless auto_increment item_name varchar(50) YES NULL item_price float(6,2) YES NULL item_description text YES NULL ++-++++-+mysql show columns from order a refaire+-+-++++-+ Field Type Null Key Default Extra ++-++++-+ order_id int(11) YES NULL item_id varchar(50) YES NULL quantity int(11) YES NULL item_price float(6,2) YES NULL total_price float(6,2) YES NULL +-+-++++-mysql show columns from customer++-++++-+ Field Type Null Key Default Extra ++-++++-+ cust_id int(11) NO PRI NULL auto_increment cust_name varchar(20) YES NULL cust_address varchar(20) YES NULL cust_city varchar(25) YES NULL cust_phone varchar(25) YES NULL ++-++++-+mysql show columns from payment++-++++ Field Type Null Key Default Extra++-++++ payment_id int(11) NO PRI NULL auto_increment order_id int(11) YES NULL order_date date YES NULL payment_type va rchar(10) YES NULL payment_description varchar(20) YES NULL amount float(6,2) YES NULL ++-++++3.4. Database Normalisation (Appendices)1NFcustomer(cust_id,cust_name,cust_address,cust_city)order (order_id, cust_id*,item_id*, quantity, item_price,total_price)payment(order_id*,payment_id,payment_type,payment_description,amount)2NFcustomer(cust_id,cust_name,cust_address,cust_city)order (order_id,cust_id*,quantity,total_price)payment(order_id*,payment_id,payment_type,payment_description,amount)item(item_id ,item_name, item_price,item_description)3NFcustomer(cust_id,cust_name,cust_address,cust_city)ordered_item(order_id,quantity,total_price)payment(order_id*,payment_id,amount)item(item_id ,item_name, item_price, item_description)payment_type(payment_type,payment_description)3.5. Entity RelationshipThere are three kinds of relationship.One-to-one relationship means that one of each thing is used in the relationshipOne-to-many relationship means one row in one table is linked to many row in other table.Many-to-many relationship means many rows in one table are linked to many rows in some other table.3.5. SQL QUERIESTalking to Databases Using SQLSQL is an abbreviation for Structured Query Language. It is a surprising natural language that allows us to talk to relational databases, such as MySQL, to request and deepen data. It also allows us to get information about and modify the framework that contains the data, databases themselves, and tables within them. The queries formed with SQL can range from the simple to the very complex, which makes it an extremely powerful tool for working with dataInserting, Updating, Selecting and Deleting.Insert data into database tableThe INSERT INTO statement is used to add new record in a table. sentence structureINSERT INTO table_nameVALUES ( tax1, value2) referencemysql INSERT INTO CUSTOMERS VALUES(- 10,Gnahore Hortense,34 webster road,Bermondsey),- (11,Kouakou Nkruma,10 salisbury court,London)mysql learn * FROM CUST OMERS++++ CustID NAME contend CITY++++ 10 Gnahore Hortense 34 webster road Bermondsey 11 Kouakou Nkruma 10 salisbury court London++++Select data from a database table.The acquire statement is used to select data from a database.Syntax hold column_name referenceFROM table_namemysql consume * FROM CUSTOMERS++++ CustID NAME ADDRESS CITY++++ 10 Gnahore Hortense 34 webster road Bermondsey 11 Kouakou Nkruma 10 salisbury court London++++Update data in a database existing records in a table.The UPDATE statement is used to modify data in a table.SyntaxUPDATE table_nameSET column1=value, column2=value2 ReferenceWHERE some_column=some_value.mysql UPDATE CUSTOMERS- SET ADDRESS =45 clement road- WHERE CustID =11Delete data in a databaseThe DELETE FROM statement is used to delete records from a database.SyntaxDELETE FROM table_nameWHERE some_column= some value reference3.6. Web Database StructureHome pageDescription of home page3.6.1. Menu structureBAKERY FEYASSEMAIN MENUCUST OMER keepsake ORDER PAYMENTPage description3.6.1.1. Order page structureBAKERY FEYASSEORDERSELECT ORDER ADD ORDER UPDATE ORDER DELETE ORDERPage description3.6.1.2. Select Order page3.6.1.3. Add Order Page3.6.1.4. Update Order page3.6.1.5. Delete Order pageSQL is not exclusive to MySQL, nor should it be mistaken as being a part of PHP. Its also not the only database query language out there. It is, however, the most popular. If you learn how to speak SQL, that knowledge translates to a range of other popular database products.Working with Existing Databases through PHPWhether you are using PHP and MySQL, or another combination, the same basic procedure for working with a database applies.First, a connection to the database server is schematic. This connection commonly lasts for the lifetime of the running script. Additionally, as you will later see, there are instances where they can be abutting across scripts.Next, a specific database is selected to work with. Only one database at a time can be selected per connection, but you can switch to different databases without terminating the connection. You can also have multiple connections established within the script, each with an independent selected database.Finally, once you have a connection established and a database selected, you can begin to work with the tables within them by using SQL to issue various commands. Data can be retrieved, added, modified, or deleted, and changes can be made to the selected database itself.When finished, the connection to the database server is terminated manually with mysql_ virtually(), or mechanically with the end of the script.Lets take a closer look at each step separatelyThings to commemorateMySQL is a popular relational database productSQL is a language used to communiate with MySQL (and other databases). It allows information to be retrevied, modified, and deleted. It also provides a means to modify databases and tables.Databases are similiar to associative arrays. Each database can contain one or more tables. Each table is composed of records divided into fields.Working with a database through a PHP script follows this basic process Connect to the server, select a database, perform any queries on the database, close the connection.SELECT, INSERT, UPDATE, and DELETE make up SQLs data manipulation commands.The data retreived from a table can be limited in different ways Specific columns can be returned by including them within the query, the WHERE clause can be used to to include or exclude specific records, and LIMIT can cap the number of entries that are returned.Results can be sieve using the ORDER BY clause. By default, strand Type fields are sorted alphabetically Numeric Type fields are sorted from smallest to largest.When creating a new table in a database, a unique table name must be specified. In addition, a name, field type, and length are defined for each column in the table.In almost every case, a table should have an AUTO_INCREME NT Primary ID column defined.All of PHPs MySQL discussion functions return wrong if there is an error in the query or the query fails. Using mysql_error() returns the select error reported by MySQL.Connecting to MySQLJust identical a multi-user computer system or an file transfer protocol server, MySQL allows different user accounts to be established with individual levels of access. To establish a connection to MySQL, three pieces of information are required the hostname of the database server, the username for the account, and the associated password.This information is passed to the mysql_connect() function, which crusades to establish the connection$connect = mysql_connect(hostname,username,password)The function returns a value authorized on success, and FALSE on failure, so it is useful (though not necessary) to assign the function to a varying. In the above example, $connect is used. It can be evaluated to avoid performing any additional queries if the connection attemp t failedif ($connect==FALSE) print Database connection failedexitIt is also useful to store the results in a variable because the function returns a unique connection identifier upon success. The variable then can be passed to any of PHPs MySQL discourse functions (typically as the second parametric quantity) to specify thatthisdatabase connection should be used, as opposed to any other wide awake ones within the script.Selecting a DatabaseOnce a connection is set up, the next step is to select a database. To do this, all that is needed is the name of an existing databasemysql_select_db(database)As with mysql_connect(), this function returns TRUE or FALSE depending on whether the database was successfully selected. To specify which connection the function should use, we can pass a connection identifier to the functionmysql_select_db(database,$connect)Without the second parameter, the function uses the database connection that was last established within the script.Querying the Da tabaseThe mysql_query() function is essentially a PHP housecoat for performing SQL commands. Any valid and complete SQL statement can be passed as a parameter to this function.Four types of queries make up SQLs data manipulation statements. They are as follows SELECT, UPDATE, INSERT, and DELETE.SELECTThe SELECT query is used to retrieve data from one or more tables within a database. As youll learn later, we can also select data from multiple tables using Table Joins, but the simplest type of SELECT statement works with only one table at a time. The syntax looks like thisSELECT * FROM usersEssentially, this statement says, Select all the fields in all the rows of the table called users. The asterisk is a unsubdued card character that tells MySQL that it should grab anything available in the specified tables.To request this data from a PHP script, we pass the query to the mysql_query () function. Because we are expecting records to be returned from the database, the function must b e assigned to a variable. The query is a string, so PHP requires it to be surrounded by quotes. (Variables are often passed as part of queries and SQL requires that string set in WHERE clauses and INSERT statements be surrounded with single quotes. Though single quotes can be used here, it is generally preferable to use double quotes for clarity.)$result = mysql_query (SELECT * FROM users)Just like mysql_connect () and mysql_select_db (), this function will return FALSE if there is an error in the SQL. If the function is successful, however, $result will hold a preference identifier.Because the variable just holds an identifier, we cannot get any of our records by outputting $result. To get the data, we need to pass the result to another PHP function, mysql_fetch_array (). This function returns an array of each record from a SELECT query, line by line. Unless you know for a fact that your query will only return a single record, it is advisable to use a era loop to itinerate throu gh each row of the result set. The function maintains an internal place mark of which record it is on, so the loop will terminate once all of the records have been returned. Lets return to our sample database from the start of the article and the select statement above to see how you would output the datawhile ($row = mysql_fetch_array($result)) print $rowid.,.$rowname.,.$rowemail.Notice that the array indexes of $row match the column names of the table. The array generated by mysql_fetch_array() can optionally be an associative or a numeric array. By default, records are accessible by both types of keys. So, the id column (the archetypal column in the table) can be accessed as $rowid or $row0INSERTINSERT is used to add a new record to a table. The syntax looks like thismysql_query(INSERT INTO users (id,name,email) VALUES(,Name, emailprotected)By now, the first part of this query should look fairly familiar to you. It begins with the SQL command we are qualifying to perform, in t his case INSERT. Because we are inserting a new record, INTO is included, followed by the name of the table we are adding it to.The first set of parentheses surrounds a list of the fields within the table. These must be listed in the same order that they occur in the actual table.The second portion defines the values that are going to be added. They must match the order of the fields within the query and the table for them to be inserted in the proper location. analogous the field names, SQL requires that each value be surrounded by quotes, because they are strings. If there is no value to be inserted, the position for the value must still be reserved by using modify quotes.If you are inserting values for all of the fields, the field listing may be left outmysql_query(INSERT INTO users VALUES(,Name, emailprotected)MySQL will insert the values into the columns in the order that they appear.UPDATESo far weve covered sele