Problem exporting a MySql database table

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
rbhoumik
Forum Newbie
Posts: 13
Joined: Wed Nov 11, 2009 7:06 am

Problem exporting a MySql database table

Post by rbhoumik »

I am trying to export a Mysql table into a CSV file...The problem is that I can't get to calculate the total of the values in a column without click drag the colums and get the summation...Is it possible to get the total of the column values automatically while exporting?
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: Problem exporting a MySql database table

Post by McInfo »

I think there are some details missing from your description. How are you exporting the CSV -- phpMyAdmin? Where do you "click drag the colums and get the summation" -- in a spreadsheet program?

Assuming my guesses were close, here is an example. The example uses a table named "money" which holds the number of pennies, nickels, dimes, and quarters involved in some transactions.

Code: Select all

DESCRIBE `money`
# +-------------+------------------+------+-----+---------+----------------+
# | Field       | Type             | Null | Key | Default | Extra          |
# +-------------+------------------+------+-----+---------+----------------+
# | transaction | int(10) unsigned | NO   | PRI |         | auto_increment |
# | pennies     | int(10) unsigned | NO   |     |         |                |
# | nickels     | int(10) unsigned | NO   |     |         |                |
# | dimes       | int(10) unsigned | NO   |     |         |                |
# | quarters    | int(10) unsigned | NO   |     |         |                |
# +-------------+------------------+------+-----+---------+----------------+
 
SELECT * FROM `money`
# +-------------+---------+---------+-------+----------+
# | transaction | pennies | nickels | dimes | quarters |
# +-------------+---------+---------+-------+----------+
# |           1 |       4 |       9 |     5 |        1 |
# |           2 |      10 |      10 |     4 |        2 |
# |           3 |       6 |       1 |     6 |        5 |
# +-------------+---------+---------+-------+----------+
Using GROUP BY WITH ROLLUP, we can create a result set that includes totals for both columns and rows. If we left out the last two SUM()s, it would just be totals for columns.

Code: Select all

SELECT `transaction`          AS `tn` # transaction number
     , SUM(`pennies`)         AS `pc` # pennies count
     , SUM(`pennies`  * 0.01) AS `pv` # pennies value
     , SUM(`nickels`)         AS `nc` # nickels count
     , SUM(`nickels`  * 0.05) AS `nv` # nickels value
     , SUM(`dimes`)           AS `dc` # dimes count
     , SUM(`dimes`    * 0.10) AS `dv` # dimes value
     , SUM(`quarters`)        AS `qc` # quarters count
     , SUM(`quarters` * 0.25) AS `qv` # quarters value
     , SUM(`pennies`
         + `nickels`
         + `dimes`
         + `quarters`)        AS `cc` # coins count
     , SUM(`pennies`  * 0.01
         + `nickels`  * 0.05
         + `dimes`    * 0.10
         + `quarters` * 0.25) AS `cv` # coins value
FROM `money`
GROUP BY `transaction` WITH ROLLUP
# +----+----+------+----+------+----+------+----+------+----+------+
# | tn | pc | pv   | nc | nv   | dc | dv   | qc | qv   | cc | cv   |
# +----+----+------+----+------+----+------+----+------+----+------+
# |  1 |  4 | 0.04 |  9 | 0.45 |  5 | 0.50 |  1 | 0.25 | 19 | 1.24 |
# |  2 | 10 | 0.10 | 10 | 0.50 |  4 | 0.40 |  2 | 0.50 | 26 | 1.50 |
# |  3 |  6 | 0.06 |  1 | 0.05 |  6 | 0.60 |  5 | 1.25 | 18 | 1.96 |
# |    | 20 | 0.20 | 20 | 1.00 | 15 | 1.50 |  8 | 2.00 | 63 | 4.70 |
# +----+----+------+----+------+----+------+----+------+----+------+
In phpMyAdmin, there is an "Export" link in the "Query results operations" box below the results table on the "Browse" page. You can export the query results in CSV format.

The problem with this is that if you import the CSV into a spreadsheet program the totals will be treated as fixed data rather than the result of a summation function and will not change based on changes you make to other cells in the spreadsheet.

Related topic: (broken) 98448 (crosstab queries)

Edit: This post was recovered from search engine cache.
Post Reply