Write query to .txt file and update

[adzie]

Peeps,

 

Is there a way of outputting a query to a txt file and updating that every fifteen minutes via cron?

 

Thanks

[Wildbug]

Yes.

 

mysql your_database < query_in_a_file.sql > output_file.txt

[adzie]

thanks I'll try that maybe what i meant was to ouput certain fields from a table into the text file and auto update

[adzie]

ok let me try and make this clearer as i think my question was confusing.

 

What i want to do is extract certain fields of information from a DB. extract that into a txt file. every 24 hours create a cron which will delete the txt file and again extract the information adding any new entries this time

 

any help appreciated thanks.

[Wildbug]

So, put your query in a file and set cron to execute the command "mysql your_database < query_in_a_file.sql > output_file.txt" every 24h.

 

If you use ">" the output file will be overwritten; there's no need to delete it explicitly.

 

MySQL client

crontab

[adzie]

thanks wildbug I dont have a script at the moment everything I have tried has failed

[Wildbug]

You mean your query fails?  What is it?

[adzie]

No sorry thats misleading, my appologies I can get my query to display on the screen ok so I can see the fields

 

Its just getting the information of that query from the screen into a txt file

[Wildbug]

Well, from the OS shell prompt, you can use the -e switch and redirect the output to a file with ">".  From inside the mysql client, you can use ...INTO OUTFILE (see manual for usage), but that method is more limited because, for one, you can only write to files that don't already exist (security issue).

 

What OS?

 

On either Win or *nix, typically:

$ mysql test -e "SELECT * FROM test_table"
+------+----------+------+
| id   | order_no | name |
+------+----------+------+
|    1 |       20 | bxxx |
|    2 |     NULL | bbbb |
|    3 |     NULL | cccc |
+------+----------+------+
$ mysql test -e "SELECT * FROM test_table" > output.txt
$

 

Except, in output.txt, the columns will be tab delimited unless you specify "--table" on the command line.

[adzie]

thanks for the help

 

i really need it to update the file so having it exist is there a way around it?

 

I also need the information in the following format in the text file

 

level name password

level name password

level name password

level name password

 

thanks

[Wildbug]

What are you having trouble with?  You aren't really giving me any information about what you've tried and what was wrong with it.  You said that you're planning to use cron.  Then wtf is wrong with my original post?

[adzie]

sorry didnt mean to offend It linux by the way I'll try that code

you put there

 

$ mysql test -e "SELECT * FROM test_table"

+------+----------+------+

| id  | order_no | name |

+------+----------+------+

|    1 |      20 | bxxx |

|    2 |    NULL | bbbb |

|    3 |    NULL | cccc |

+------+----------+------+

$ mysql test -e "SELECT * FROM test_table" > output.txt

 

[Wildbug]

Like I said at first, the following how to query and capture output from the prompt (the dollar sign, $, represents the Linux prompt):

 

$ mysql your_database < query_in_a_file.sql > output_file.txt

 

You can put a command to that effect in your crontab or wherever will accept a shell command.  You will need to use any user/password authentication that is normally required, just like using the mysql client interactively.

 

$ mysql your_database -u username -pxxxxxx < query.sql > output.txt

 

If your query is short and simple, maybe you don't want to keep it in a seperate file.  You can use the -e option to specify the query directly at the prompt:

 

$ mysql your_database -u username -pxxxxx -e "SELECT level,name,password FROM your_table" > output.txt

 

Btw, this is all in the man page for the mysql client.

 

$ man mysql

[adzie]

should have mentioned before that I dont have root access to the host

[Wildbug]

You don't need root access.  You can perform these tasks as a regular user.

 

Or do you mean you don't have shell access?

[adzie]

yes thats it I have a normal host account anything mysql or script has to be in a PHP run file or via the cron panel or phpmyadmin

[Wildbug]

So, doesn't cron run commands?