Welcome to the Cumulus Support forum.
Latest Cumulus MX V3 release 3.28.6 (build 3283) - 21 March 2024
Cumulus MX V4 beta test release 4.0.0 (build 4019) - 03 April 2024
Legacy Cumulus 1 release 1.9.4 (build 1099) - 28 November 2014
(a patch is available for 1.9.4 build 1099 that extends the date range of drop-down menus to 2030)
Download the Software (Cumulus MX / Cumulus 1 and other related items) from the Wiki
Latest Cumulus MX V3 release 3.28.6 (build 3283) - 21 March 2024
Cumulus MX V4 beta test release 4.0.0 (build 4019) - 03 April 2024
Legacy Cumulus 1 release 1.9.4 (build 1099) - 28 November 2014
(a patch is available for 1.9.4 build 1099 that extends the date range of drop-down menus to 2030)
Download the Software (Cumulus MX / Cumulus 1 and other related items) from the Wiki
phpMyAdmin question
-
- Posts: 1819
- Joined: Sat 17 Dec 2011 11:55 am
- Weather Station: Davis Vantage Pro2
- Operating System: Windows 11 x64
- Location: Dorset - UK
- Contact:
phpMyAdmin question
I've had a request from someone who works at the university of bath, who is studying the river Frome, they would like me to share certain weather value for each day.
I know how to dump my SQL data to PDF's or CSV's etc, but is there a way to select just the columns I need and dump those in to a file?
I know how to dump my SQL data to PDF's or CSV's etc, but is there a way to select just the columns I need and dump those in to a file?
- mcrossley
- Posts: 12765
- Joined: Thu 07 Jan 2010 9:44 pm
- Weather Station: Davis VP2/WLL
- Operating System: Bullseye Lite rPi
- Location: Wilmslow, Cheshire, UK
- Contact:
Re: phpMyAdmin question
You could just do a "select" of the data required in phpMyAdmin, then "export" the resulting rows.
-
- Posts: 3253
- Joined: Sat 13 Aug 2011 9:33 am
- Weather Station: Ecowitt HP2551
- Operating System: Windows 10 64bit
- Location: Burnham-on-Sea
- Contact:
Re: phpMyAdmin question
As far as I am aware phpMyAdmin even in Custom export will only export all columns in a given table.
Personally I would export it as a CSV file, load it into Excel and just remove the columns you do not want by deleting the column.
Or as mark says
![Laughing :lol:](./images/smilies/icon_lol.gif)
Personally I would export it as a CSV file, load it into Excel and just remove the columns you do not want by deleting the column.
Or as mark says
![Laughing :lol:](./images/smilies/icon_lol.gif)
![Laughing :lol:](./images/smilies/icon_lol.gif)
![Laughing :lol:](./images/smilies/icon_lol.gif)
![Laughing :lol:](./images/smilies/icon_lol.gif)
![Laughing :lol:](./images/smilies/icon_lol.gif)
-
- Posts: 1819
- Joined: Sat 17 Dec 2011 11:55 am
- Weather Station: Davis Vantage Pro2
- Operating System: Windows 11 x64
- Location: Dorset - UK
- Contact:
Re: phpMyAdmin question
It's not the rows that I need, I need to pick out temperature, wind, solar, pressure, rainfall
I guess I'll have to download excel and edit it that way, as David suggested.
- HansR
- Posts: 5964
- Joined: Sat 20 Oct 2012 6:53 am
- Weather Station: GW1100 (WS80/WH40)
- Operating System: Raspberry OS/Bookworm
- Location: Wagenborgen (NL)
- Contact:
Re: phpMyAdmin question
If this is required on regular basis you don't want to do that manually through excel isn't it.
I assume you can just do adump that in a file and pass it on.
If you can't do that automatically, whats the use of having SQL?
I assume you can just do a
Code: Select all
select [fieldname(s)] from [tablename]
If you can't do that automatically, whats the use of having SQL?
Hans
https://meteo-wagenborgen.nl
CMX build 4017+ ● RPi 3B+ ● Raspbian Linux 6.1.21-v7+ armv7l ● dotnet 8.0.3
https://meteo-wagenborgen.nl
CMX build 4017+ ● RPi 3B+ ● Raspbian Linux 6.1.21-v7+ armv7l ● dotnet 8.0.3
-
- Posts: 1819
- Joined: Sat 17 Dec 2011 11:55 am
- Weather Station: Davis Vantage Pro2
- Operating System: Windows 11 x64
- Location: Dorset - UK
- Contact:
Re: phpMyAdmin question
The gentleman who wants the data wants to have it in a PDF or CSV format.HansR wrote: ↑Thu 11 Aug 2022 11:54 am If this is required on regular basis you don't want to do that manually through excel isn't it.
I assume you can just do adump that in a file and pass it on.Code: Select all
select [fieldname(s)] from [tablename]
If you can't do that automatically, whats the use of having SQL?
- mcrossley
- Posts: 12765
- Joined: Thu 07 Jan 2010 9:44 pm
- Weather Station: Davis VP2/WLL
- Operating System: Bullseye Lite rPi
- Location: Wilmslow, Cheshire, UK
- Contact:
Re: phpMyAdmin question
The question was about phpMyAdmin, and my method works for that, you just get the columns you select in the file.
But if you want it automating then you will have to ditch the GUI and use the command line...
eg.
Create a config file on your path - .my.cnf
(You may want to secure that file)
EDIT: from MySQL 5.6 you can use the mysql_config_editor utility to create that file for you - and bonus, the password will be encrypted.
https://dev.mysql.com/doc/refman/8.0/en ... ditor.html
Then you can just run a command like...
But if you want it automating then you will have to ditch the GUI and use the command line...
eg.
Create a config file on your path - .my.cnf
Code: Select all
[client]
user=your_user
password=your_password
EDIT: from MySQL 5.6 you can use the mysql_config_editor utility to create that file for you - and bonus, the password will be encrypted.
https://dev.mysql.com/doc/refman/8.0/en ... ditor.html
Then you can just run a command like...
Code: Select all
mysql SELECT LogDateTime, temp FROM realtime INTO OUTFILE '/tmp/realtime.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
-
- Posts: 1183
- Joined: Fri 27 Jul 2012 11:29 am
- Weather Station: Chas O, Maplin N96FY, N25FR
- Operating System: rPi 3B+ with Buster (full)
Re: phpMyAdmin question
Create View is the standard way in SQL to define just once the ability to see just selected columns from a table, I'm sure some Forum users have experience of using views.
phpMyAdmin supports defining default settings for export, so only need to define once. Settings not only support export output as CSV or PDF (as Mark says) but also whether export lists data in table or does a create view.
I have never tried creating a view in phpMyAdmin, nor using that option in Export, so it might not work as required. If you have time for experimentation, maybe could be explored.
phpMyAdmin supports defining default settings for export, so only need to define once. Settings not only support export output as CSV or PDF (as Mark says) but also whether export lists data in table or does a create view.
I have never tried creating a view in phpMyAdmin, nor using that option in Export, so it might not work as required. If you have time for experimentation, maybe could be explored.
- HansR
- Posts: 5964
- Joined: Sat 20 Oct 2012 6:53 am
- Weather Station: GW1100 (WS80/WH40)
- Operating System: Raspberry OS/Bookworm
- Location: Wagenborgen (NL)
- Contact:
Re: phpMyAdmin question
I kicked in on Mapantz's original post which insinuates a daily action. That will be boring after a while, they invented computers for that
![Wink ;)](./images/smilies/icon_e_wink.gif)
So I think Mark's solution does well, you might even mail it automatically
![Cool 8-)](./images/smilies/icon_cool.gif)
mcrossley wrote: ↑Thu 11 Aug 2022 12:37 pm Then you can just run a command like...Code: Select all
mysql SELECT LogDateTime, temp FROM realtime INTO OUTFILE '/tmp/realtime.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
Hans
https://meteo-wagenborgen.nl
CMX build 4017+ ● RPi 3B+ ● Raspbian Linux 6.1.21-v7+ armv7l ● dotnet 8.0.3
https://meteo-wagenborgen.nl
CMX build 4017+ ● RPi 3B+ ● Raspbian Linux 6.1.21-v7+ armv7l ● dotnet 8.0.3
-
- Posts: 2477
- Joined: Wed 08 Jun 2011 11:19 am
- Weather Station: Davis Vantage Pro 2 + Ecowitt
- Operating System: GNU/Linux Ubuntu 22.04 LXC
- Location: Alcaston, Shropshire, UK
- Contact:
Re: phpMyAdmin question
I second using views. I use views to amalgamate my extra sensors data with standard MX data. But the reverse applies equally, if you need a cut-down view of a single table.
-
- Posts: 1819
- Joined: Sat 17 Dec 2011 11:55 am
- Weather Station: Davis Vantage Pro2
- Operating System: Windows 11 x64
- Location: Dorset - UK
- Contact:
Re: phpMyAdmin question
Thanks for the replies.
I don't need anything automated, the guy just wants the data up to this point in time.
I still see no option to be able to select certain columns in the dayfile to export, only rows?
Hold up; under the 'structure' bit?
I don't need anything automated, the guy just wants the data up to this point in time.
I still see no option to be able to select certain columns in the dayfile to export, only rows?
Hold up; under the 'structure' bit?
- mcrossley
- Posts: 12765
- Joined: Thu 07 Jan 2010 9:44 pm
- Weather Station: Davis VP2/WLL
- Operating System: Bullseye Lite rPi
- Location: Wilmslow, Cheshire, UK
- Contact:
Re: phpMyAdmin question
No, just browse the table and edit the SQL, or select the SQL tab. Both will have the default "SELECT * FROM Dayfile...", just change the select * to the columns you want