34,333
edits
Changes
New page: When is retrieved from a MySQL database it is not always in the form we need it. For example, we may need to display a customer name and address as a single test string, but in reality the...
When is retrieved from a MySQL database it is not always in the form we need it. For example, we may need to display a customer name and address as a single test string, but in reality the name and address are held in separate database tables. Similarly, a table might contain the price for a item and the shipping cost in two columns, when what we want is the result of the shipping cost and product cost added together. Fortunately MySQL allows fields retrieved from a table to be concatenated or used in calculations. In this chapter we will look at both approaches to manipulating data as it is extracted from a database.
== Performing Calculations on Retrieved Data ==
Suppose we have a database table called ''shipping'' which contains product names, prices and shipping costs. The data contained in such a table might appear as follows:
<pre>
SELECT * FROM shipping;
</pre>
Now lets assume that we need to extract the total cost of a product including the price and the shipping cost. To do so we can perform a calculation based on the product price and product_shipping columns and assign the result to an ''alias'' using the ''AS'' keyword:
<pre>mysql> select * from shipping;
+------------+--------------------------+---------------+------------------+
| product_id | product_name | product_price | product_shipping |
+------------+--------------------------+---------------+------------------+
| 2 | Microsoft 10-20 Keyboard | 25 | 7 |
| 3 | Apple iPhone 8Gb | 400 | 13 |
| 4 | Dell XPS 400 | 900 | 100 |
+------------+--------------------------+---------------+------------------+
3 rows in set (0.00 sec)
</pre>
== Performing Calculations on Retrieved Data ==
Suppose we have a database table called ''shipping'' which contains product names, prices and shipping costs. The data contained in such a table might appear as follows:
<pre>
SELECT * FROM shipping;
</pre>
Now lets assume that we need to extract the total cost of a product including the price and the shipping cost. To do so we can perform a calculation based on the product price and product_shipping columns and assign the result to an ''alias'' using the ''AS'' keyword:
<pre>mysql> select * from shipping;
+------------+--------------------------+---------------+------------------+
| product_id | product_name | product_price | product_shipping |
+------------+--------------------------+---------------+------------------+
| 2 | Microsoft 10-20 Keyboard | 25 | 7 |
| 3 | Apple iPhone 8Gb | 400 | 13 |
| 4 | Dell XPS 400 | 900 | 100 |
+------------+--------------------------+---------------+------------------+
3 rows in set (0.00 sec)
</pre>