Web Database Based Inventory System

vane Database Based Inventory tran record bookionWeb breedingbase based blood ar come inment is for practicement of items, tacks, nodes and payment. This administration helps in Stock management, payment, and orders. This outline allows conducting payment for clients and for ordered items in the purchase order. The main heading of this project is about the design of Web-based instrument system.bakery feyasse use traditional paper based inventory system to manage his culture concerning customer, ordered item, item and payment. There argon some(prenominal) problems with this system such as time consuming, hard labour and sometimes lost of information. The sales g wranglingth and order misplacement cause a great smokestack of work for Bakery Feyasse.The manual system is pr angiotensin converting enzyme to errors resulting major complaints from customers.The volume of selective information and complexity of analysis to be conducted seems overwhelming use their manual sys tem which sometimes raises suspicions of refurbishmented results which argon also difficult to correct or verify. After c beful investigation, discussion thorough analysis of existing method, it was agreed that a overbold effective system is required to solve Bakery Feyasse problems.After reviewing the possible design choices, it was decided that MySQL database and PHP deal speech is the best economical fit for Bakery Feyasse system in terms of cost, ease of maintenance, interface design and upgradability using Microsoft windows platform. The database would be link to the companys web come in via PHP script language. The online master of ceremonies entrust enable employee to enter, create, update and set off information online.1.1. Project objectives1. To create a system that pull up stakes assist the Bakery employee at their different task2. To explore the ideas involved in the suppuration of a web database inventory site.This web database inventory system will include th e main function as followingSelect data from databaseupdate information into the databaseDelete information from databaseAnd add information into databaseThe objectives of this project atomic number 18Design a relational databaseImplement this databaseTesting the risingfangled system try the system1.2. Personal objectivesMy personal objective for this project is to complete the project on time. I set up a monthly time tabularize to carry out different task. My enthusiasm is that after completing this project. I will gain more knowledge in computer technology. To enable me to win this project, I utilize the skills I gain last year in MySQL, and SQL. I also learn how to create a dynamic web site which I did not know. I learn untried script language which is PHP.1.2. 1. Career objectivesGain my BSc Honours Degree.Gain more skills in Programming language, cypher science and Information Systems Technologies.1.2. 2. Academic objectives1.Learn programming language such as PHP and M ySQLLearn how to use PHP script language to create a form to conceive information from websiteLearn how to bear on MySQL Database with dynamic WebsiteHow to use PHP code to ascertain information from MySQL databaseHow to create HTML formHow to use HTML form to retrieve information from databaseHow to create database delay using MySQL natural covering.1.3. Project LimitationsI am entirely conscious 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 have got to accomplish this task. So the time is my tar modernize. I am idea 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 do to gathering information because galore(postnominal) activities go into the development of a new system. Understanding system requirements, choosing database-tier software, designing databases, and building t he tier are the first travel in successful web database application development. The following issues have been exploredInformation System analysisDatabaseDatabase management systemsDatabase ServerDatabase tierInformation Systems AnalysisSystem AnalysisSystem DesignThis phase hand over how the system run.PrototypeThis phase will enable the employee to understand the functionality of the new system.ImplementationDevelopment of the new systemTestingThe new system is tested in order to verify its functionsDatabaseA database is a collection of related data. The data stored is rows, which make up a simple customers, items, orders and payment details.Database Management SystemDatabase Management System enables the users to retrieve relevant information. It refers to information infrastructures that compose of relevant information. Database system creates and maintains a database and enables individual business application to extract the data in order to create report.Database serverDa tabase 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 by the Internet users via their web 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, access by more than one middle-tier process, 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 accessible anywhere that have access to Internet.1.6.1. Wed Based Database Systems development toolsDreamweaver CS3Dreamweaver CS3 Drea mweaver is a full-featured Web application development tool. Its features not only assist with creation and editing Web pageboys, but 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 describe as process that accesses the server in order to store, retrieve, update and alter the database schema.SQLSQL (Structured Query Language) SQL used to communicate with almost databases. You lot manage your database, retrieve data from it, or add data to it using SQL queries. You build and send an SQL query to the database. The database responds by executeing the action defined by the query or, if the database is unable to perform the requested operation, its returns error message with information about the problem.Cold FusionColdFusion i s 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 technology. It built 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 specify 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 fecal matter be seen in the expanded PHP recursive acronymPHP-HypertextPre-processor.1.7. Technologies used1.7.1. PHP and MySQLPHP and MySQL work in combination, where the database is MySQL and the script language is PHP.How PHP and MySQL work to compassher?PHP collect information and MySQL s tore 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 coalesce together make easy the development of a dynamic Web page. HTML creates useful web pages with the addition of PHP and MySQL where you house collect information.1. 8. Methodologies Approach1.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 chance on the capabilities 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. Chosen MethodologyPrototyping Methodology Development choice has been in fluenced by several factors1. Clarify the user requirements2. Familiarity with the base technology3. System complexity4. Need for the system reliability5. Time pressure6. Need to see progress 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 outline2.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 progress a sulphur Bakery has been created in 2008. The diagram below show the Bakery StructureProduction managerSale managerManagerChairman segment managerDepartment managerDepartment managerFig Bakerys Feyasse structure2.2. Existing system (appendices)The Bakery use traditional paper based where the employee checks the availableness of the items by querying the items books, and fills the book by using paper and pen. Moreover the ordering process is made b y pen and hand and it is time consuming to dawn 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 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 mental faculty of each department can 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 accessible 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 increment 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 list of items produced by the production department anytime3. Customers details can be kept4. identify can be kept for future monthly report.System Requirement AnalysisAt this stage I met the user to obtain a detailed understanding o f Bakery needs. A functional specification, process model, 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 put-on of the salesperson2.3. System SpecificationThe system would enable end-users (employee) to creat e, 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 activities tracking user activities using the system2.4. Use CasesSelect order/customer detailsAdd 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. Connecting To DatabaseIn order to issue MySQL data manipulation educations, we essential specify a username, a battle cry and a database to use. This information will have been provided by your systems adminsistrator. If you are running MySQL yourself, past 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 proceedword for that database and a password. For the purposes of these workshops the following example values will be used.Database NamevworksDBValid MySQL UsernamevworksPasswordmypassIn order to log into MySQL correctly we must 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++-++++-+ subject field Type Null Key Default Extra ++-++++ i tem_id int(11) NO PRI NULL 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 varchar(10) YES NULL payment_descr iption varchar(20) YES NULL amount float(6,2) YES NULL ++-++++3.4. Database standardisation (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 blood.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 another table.Many-to-many relationship means many rows in one table are linked to many rows in another table.3.5. SQL QUERIESTalking to Databases Using SQLSQL is an abbreviation for Structured Query Language. It is a affect natural language that allows us to talk to relational databases, such as MySQL, to request and specify data. It also allows us to get information about and modify the framework that contains the data, databases themselves, and tables in spite of appearance 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 program line is used to add new record in a table.SyntaxINSERT INTO table_nameVALUES (value1, value2) referencemysql INSERT INTO CUSTOMERS VALUES(- 10,Gnahore Hortense,34 webster road,Bermondsey),- (11,Kouakou Nkruma,10 salisbury court,London)mysql SELECT * FROM CUSTOMERS++++ CustID appellation ADD RESS CITY++++ 10 Gnahore Hortense 34 webster road Bermondsey 11 Kouakou Nkruma 10 salisbury court London++++Select data from a database table.The SELECT statement is used to select data from a database.SyntaxSELECT column_name referenceFROM table_namemysql SELECT * 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 pageverbal description of home page3.6.1. Menu structureBAKERY FEYASSEMAIN MENUCUSTOMER ITEM ORDER PAYMENTP age 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 established. This connection usually lasts for the lifetime of the running script. Additionally, as you will later see, there are instances where they can be contiguous crosswise 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 embark on 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_close(), or automatically with the end of the script.Lets take a closer look at each step separatelyThings to RememberMySQL 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 associable arrays. Each database can contain on e 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 sorted using the ORDER BY clause. By default, String 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 contract. 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_INCREMENT Primary ID column defined.A ll of PHPs MySQL handling functions return dour if there is an error in the query or the query fails. Using mysql_error() returns the exact error reported by MySQL.Connecting to MySQLJust like a multi-user computer system or an FTP 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 attempts to establish the connection$connect = mysql_connect(hostname,username,password)The function returns a value TRUE on success, and FALSE on failure, so it is useful (though not necessary) to assign the function to a variable. In the above example, $connect is used. It can be evaluated to avoid performing any additional queries if the connection attempt failedif ($connect==FALSE) print Database connection failedexitIt i s 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 handling functions (typically as the second parameter) to specify thatthisdatabase connection should be used, as opposed to any other active 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 DatabaseThe mysql_query() function is essentially a PHP wrapper for performing SQL comman ds. 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 wild 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 be 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 values 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 resource 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 while loop to itinerate through 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 prototype 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 fit out 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 first 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 going to perform, in this 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 retard the order of the fields within the query and the table for them to be inserted in the proper location.Like 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 empty 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