How to Build a Receipt in a Relational Database

by Sue Smith

Relational databases are often used for commercial applications such as Web stores and customer services. If a relational database is holding the data for a system through which customer orders are placed, creating receipts based on order data will be a necessary and useful feature. Building a receipt from a relational database is something you can achieve easily using free technologies, whether using a system on the Web or from a desktop application.

Compile a list of the information you want to appear on the receipt. This will vary depending on your database and what kind of receipt you need. A receipt can include quantities, names of purchased items, order totals and dates. Additional elements you may want to include are payment details and information about buyer and seller. All of the information will have to come from your database.

If it is not already built, design your relational database. Sketch a design for the database on paper or using a graphic design program, including entities and the relationships between them. Each entity will correspond to a table in the database, and will have a list of attributes that become the table columns. Relationships in the data can be implemented using foreign keys when you build the database.

Build the database. If your database is going to be used over the Web, you can use a system such as MySQL, and your Web host may provide access through the phpMyAdmin interface. Using this interface or SQL statements, create each of the tables in your database along with their columns, as in the following simple example SQL: CREATE TABLE customer ( customerID INT, customerName VARCHAR(20)); CREATE TABLE order ( orderID INT, orderDate DATE, orderPaid DECIMAL(5,2), custID INT); The field "custID" acts as a foreign key, holding the ID for the customer placing the order and linking the two tables.

Design and build an interface to access your database. If your system is going to operate on the Internet, you can use a server side language such as PHP or ASP, and if you're developing a desktop application you can use a language such as Java. Within your interface, whether on the desktop or Internet, you will need to connect to the database and facilitate the ordering process. The following PHP code outlines connecting to a MySQL database: <?php mysql_connect("database_host", "database_username", "database_password"); mysql_select_db("database_name"); ?>

Assemble the elements in your receipt dynamically when an order is placed, or when payment is made. Using your server side or desktop programming code, query the database to create a customer receipt. The following builds the elements in a simple receipt for display and printing from a Web browser: <?php $customer_id=123;//where the customer ID is known echo "<p>Receipt for customer: ".$customer_id."</p>"; $order_result = mysql_query("SELECT * FROM order WHERE custID=".$customer_id); while($order_row=mysql_fetch_array($order_result)) { echo "<p>Order ID: ".$order_row['orderID']."</p>"; echo "<p>Order date: ".$order_row['orderDate']."</p>"; echo "<p>Amount: ".$order_row['orderPaid']."</p>"; } ?> Depending on the structure of your database, you may also want to include details of items ordered.

Tip

  • check Take time to figure out what you need in a receipt before you start writing code, as this will minimize the likelihood of errors.

Warning

  • close Make sure your system responds well to errors that may occur when connecting to your database, as this is always a possibility.

About the Author

Sue Smith started writing in 2000. She has produced tutorials for companies including Apex Computer Training Software and articles on computing topics for various websites. Smith has a Master of Arts in English language and literature, as well as a Master of Science in information technology, both from the University of Glasgow.

More Articles

Photo Credits

  • photo_camera fattura 7 image by Rido from Fotolia.com