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 4017) - 17 March 2024

Legacy Cumulus 1 release v1.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

Discussion and support for 3rd-party (non-Sandaysoft) tools for Cumulus
Post Reply
Mapantz
Posts: 1774
Joined: Sat 17 Dec 2011 11:55 am
Weather Station: Davis Vantage Pro2
Operating System: Windows 11 x64
Location: Dorset - UK
Contact:

phpMyAdmin question

Post by Mapantz »

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?
Image
User avatar
mcrossley
Posts: 12687
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

Post by mcrossley »

You could just do a "select" of the data required in phpMyAdmin, then "export" the resulting rows.
water01
Posts: 3215
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

Post by water01 »

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 :lol: :lol: :lol: :lol: :lol:
David
Image
Mapantz
Posts: 1774
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

Post by Mapantz »

mcrossley wrote: Thu 11 Aug 2022 11:01 am You could just do a "select" of the data required in phpMyAdmin, then "export" the resulting rows.
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.
Image
User avatar
HansR
Posts: 5870
Joined: Sat 20 Oct 2012 6:53 am
Weather Station: GW1100 (WS80/WH40)
Operating System: Raspberry OS/Bullseye
Location: Wagenborgen (NL)
Contact:

Re: phpMyAdmin question

Post by HansR »

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 a

Code: Select all

select [fieldname(s)] from [tablename]
dump that in a file and pass it on.
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
Mapantz
Posts: 1774
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

Post by Mapantz »

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 a

Code: Select all

select [fieldname(s)] from [tablename]
dump that in a file and pass it on.
If you can't do that automatically, whats the use of having SQL?
The gentleman who wants the data wants to have it in a PDF or CSV format.
Image
User avatar
mcrossley
Posts: 12687
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

Post by mcrossley »

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

Code: Select all

[client]
user=your_user
password=your_password
(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...

Code: Select all

mysql SELECT LogDateTime, temp FROM realtime INTO OUTFILE '/tmp/realtime.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
sfws
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

Post by sfws »

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.
User avatar
HansR
Posts: 5870
Joined: Sat 20 Oct 2012 6:53 am
Weather Station: GW1100 (WS80/WH40)
Operating System: Raspberry OS/Bullseye
Location: Wagenborgen (NL)
Contact:

Re: phpMyAdmin question

Post by HansR »

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 ;)
Mapantz wrote: Thu 11 Aug 2022 10:28 am 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.
So I think Mark's solution does well, you might even mail it automatically 8-)
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
freddie
Posts: 2433
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

Post by freddie »

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.
Freddie
Image
Mapantz
Posts: 1774
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

Post by Mapantz »

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?
Image
User avatar
mcrossley
Posts: 12687
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

Post by mcrossley »

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
Mapantz
Posts: 1774
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

Post by Mapantz »

Got it!

Thanks folks!
Image
Post Reply