Web Database Based Inventory System

Web Database Based Inventory System Web database based inventory system is for management of items, orders, customers and payment. This system helps in Stock management, payment, and orders. This system allows conducting payment for customers and for ordered items in the purchase order. The main objective of this project is about the design of Web-based inventory system. Bakery feyasse use traditional paper based inventory system to manage his information concerning customer, ordered item, item and payment. There are several problems with this system such as time consuming, hard labour and sometimes lost of information. The sales growth and order misplacement cause a great deal of work for Bakery Feyasse. The manual system is prone 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 returned results which are also difficult to correct or verify. After careful investigation, discussion thorough analysis of existing method, it was agreed that a new effective system is required to solve Bakery Feyasse problems. After reviewing the possible design choices, it was decided that MySQL database and PHP script language 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 website via PHP script language. The online server will enable employee to enter, create, update and delete information online. 1.1. Project objectives 1. To create a system that will assist the Bakery employee at their different task 2. To explore the ideas involved in the development of a web database inventory site. This web database inventory system will include the main function as following: Select data from database Update information into the database Delete information from database And add information into database The objectives of this project are: Design a relational database Implement this database Testing the new system Evaluate the system 1.2. Personal objectives My 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 enthusiasm is that after completing this project. I will gain more knowledge in computer technology. To enable me to succeed this project, I used 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 new script language which is PHP. 1.2. 1. Career objectives Gain my BSc Honours Degree. Gain more skills in Programming language, Computing science and Information Systems Technologies. 1.2. 2. Academic objectives 1.Learn programming language such as PHP and MySQL Learn how to use PHP script language to create a form to retrieve information from website Learn how to connect MySQL Database with dynamic Website How to use PHP code to retrieve information from MySQL database How to create HTML form How to use HTML form to retrieve information from database How to create database table using MySQL application. 1.3. Project Limitations I 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 target. I am thinking of finish on time. Time management has been considered because; I also got one exam to prepare. 1.4. Gantt chart 1.5. Literature review At the start of my project, the researches have been made to gathering information because many activities go into the development of a new system. Understanding system requirements, choosing database-tier software, designing databases, and building the tier are the first steps in successful web database application development. The following issues have been explored: Information System Analysis Database Database management systems Database Server Database tier Information Systems Analysis System Analysis System Design This phase show how the system run. Prototype This phase will enable the employee to understand the functionality of the new system. Implementation Development of the new system Testing The new system is tested in order to verify its functions Database A 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 System Database 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 server Database 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 tier Database 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 review The 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 tools Dreamweaver CS3 Dreamweaver CS3 Dreamweaver is a full-featured Web application development tool. Its features not only assist with creation and editing Web pages, but also with managing and maintaining the entire Web site. MySQL MySQL 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. SQL SQL (Structured Query Language) SQL used to communicate with most databases. You can 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 performing 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 Fusion ColdFusion 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. ASP ASP 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 technologies PHP PHP is a Scripting Language that is interpreted on the web server before the webpage is sent to a web browser to be displayed. This can be seen in the expanded PHP recursive acronym  PHP-Hypertext  Pre-processor. 1.7. Technologies used 1.7.1. PHP and MySQL PHP and MySQL work in combination, where the database is MySQL and the script language is PHP. How PHP and MySQL 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 MySQL PHP 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 can collect information. 1. 8. Methodologies Approach 1.8.1. Prototyping Prototyping 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 capabilities that users wants in the system, such as the options on a menu used by the a system. 1.8.2. Rapid Application Development Its 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 Methodology Prototyping Methodology Development choice has been influenced by several factors: 1. Clarify the user requirements 2. Familiarity with the base technology 3. System complexity 4. Need for the system reliability 5. Time pressure 6. Need to see progress on the time schedule. 7. And when timelines are short because they best enable me to adjust the functionality in the system on the basis of a specific delivery date. CHAPTER 2 ANALYSIS 2.1. Bakery Feyasse profile Bakery 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 second Bakery has been created in 2008. The diagram below show the Bakery Structure Production manager Sale manager Manager Chairman Department manager Department manager Department manager Fig: Bakerys Feyasse structure 2.2. Existing system (appendices) The Bakery use traditional paper based where the employee checks the availability of the items by querying the items books, and fills the book by using paper and pen. Moreover the ordering process is made 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 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 book 2.2. Current Systems The proposed system is a web based inventory system. The staff 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 system 1. 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 list of items produced by the production department anytime 3. Customers details can be kept 4. Order can be kept for future monthly report. System Requirement Analysis At this stage I met the user to obtain a detailed understanding of 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 system Defining the problem Identify the problem causes Specifying the solution Identifying the users requirements, And identifying new opportunity for using new technology 2.2. Users Requirements Define the objectives of the new system and develop a detailed description of the function that the new system will perform The 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 salesperson 2.3. System Specification The system would enable end-users (employee) to create, update information All information about customer, item, order and payment will be stored in a database, which the web database inventory system will display through different interfaces Allows the deletion and appends information Facility to query the database Enables the manipulation of sales activities Tracking user activities using the system 2.4. Use Cases Select order/customer details Add order/customer details Delete order/customer details Update order/customer details Use cases description (appendices) CHAPTER 3DESIGN 3.1. Architectural Design Logical design Physical design 3.1.1. Entities and Attributes Bakery 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 Database In order to issue MySQL data manipulation statements, we must specify a username, a password 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 on  Creating 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 values will be used. Database Name vworksDB Valid MySQL Username vworks Password mypass In 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 MySQL MySQL 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 | Null | Key | Default | Extra | ++-++++ | item_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_description | varchar(20) | YES | | NULL | | amount | float(6,2) | YES | | NULL | ++-++++ 3.4. Database Normalisation (Appendices) 1NF 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) 2NF customer(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) 3NF customer(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 Relationship There are three kinds of relationship. One-to-one relationship means that one of each thing is used in the relationship One-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 QUERIES Talking to Databases Using SQL SQL 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 modify 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 data Inserting, Updating, Selecting and Deleting. Insert data into database table The INSERT INTO statement is used to add new record in a table. Syntax INSERT INTO table_name VALUES (value1, value2à ¢Ã¢â€š ¬Ã‚ ¦) [reference] mysql> INSERT INTO CUSTOMERS VALUES( -> 10,Gnahore Hortense,34 webster road,Bermondsey), -> (11,Kouakou Nkruma,10 salisbury court,London); mysql> SELECT * FROM CUSTOMERS; ++++ | CustID | NAME | ADDRESS | 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. Syntax SELECT column_name [reference] FROM table_name mysql> 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. Syntax UPDATE table_name SET column1=value, column2=value2à ¢Ã¢â€š ¬Ã‚ ¦ [Reference] WHERE some_column=some_value. mysql> UPDATE CUSTOMERS -> SET ADDRESS =45 clement road -> WHERE CustID =11; Delete data in a database The DELETE FROM statement is used to delete records from a database. Syntax DELETE FROM table_name WHERE some_column= some value [reference] 3.6. Web Database Structure Home page Description of home page: 3.6.1. Menu structure BAKERY FEYASSE MAIN MENU CUSTOMER ITEM ORDER PAYMENT Page description: 3.6.1.1. Order page structure BAKERY FEYASSE ORDER SELECT ORDER ADD ORDER UPDATE ORDER DELETE ORDER Page description: 3.6.1.2. Select Order page 3.6.1.3. Add Order Page 3.6.1.4. Update Order page 3.6.1.5. Delete Order page SQL 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 PHP Whether 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 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_close(), or automatically with the end of the script. Lets take a closer look at each step separately Things to Remember MySQL is a popular relational database product SQL 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 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 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_INCREMENT Primary ID column defined. All of PHPs MySQL handling functions return FALSE if there is an error in the query or the query fails. Using mysql_error() returns the exact error reported by MySQL. Connecting to MySQL Just 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 failed: if ($connect==FALSE) { print Database connection failed; exit; } It 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 handling functions (typically as the second parameter) to specify that  this  database connection should be used, as opposed to any other active ones within the script. Selecting a Database Once 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 database: mysql_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 function: mysql_select_db(database,$connect); Without the second parameter, the function uses the database connection that was last established within the script. Querying the Database The mysql_query() function is essentially a PHP wrapper 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. SELECT The 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 this: SELECT * FROM users Essentially, 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 sample database from the start of the article and the select statement above to see how you would output the data: while ($row = mysql_fetch_array($result)) {   Ãƒâ€šÃ‚  Ãƒâ€šÃ‚  Ãƒâ€šÃ‚   print $row[id].,.$row[name].,.$row[email].; } 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 first column in the table) can be accessed as $row[id] or $row[0]; INSERT INSERT is used to add a new record to a table. The syntax looks like this: mysql_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 match 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 out: mysql_query(INSERT INTO users VALUES(,Name, [emailprotected]'); MySQL will insert the values into the columns in the order that they appear. UPDATE So far weve covered sele