[PHP-DB] Re: Exporting Data From MySQL Using PHP
Hello Ron, If I understand correctly, you're asking what query to execute (and how to put the result in a textfile) to get the results you want? The first part is described thorougly in the mysql-manual (chapter 14.1.7 for example, search for the SELECT syntax or examples. You'll find it at http://dev.mysql.com/doc/mysql/en/ ). You probably need a query that looks like this: SELECT e_mail FROM your_table_name WHERE discipleship_mailing_list_e_mail_subscription = on; This will return all the requested e-mail adresses. Having this information printed to a file could be reached by using the INTO OUTFILE form of SELECT. Again, check the mysql-manual. for the exact syntax, but you'll probably end up with somthing like: SELECT e_mail INTO OUTFILE '/some/filename.text' FROM your_table_name WHERE discipleship_mailing_list_e_mail_subscription = on; There are a bunch of other options and solutions, though this seems the easiest one to me. Mind that if you try to do this in PHP (and trough a webserver), the webserver should have write-access to the file you specify with 'OUTFILE' - that might be a problem. Personally, I'd run this as some sort of batch script (using the mysql command line interface with the -e option, for example). Hope this helps you along a bit. Regards, Guus der Kinderen Ron Piggott wrote: I have created a MySQL database. The table I am creating is a subscriptions database. I want to be able to export all e-mail addresses stored in the e_mail column into a plain text file on the web server (1 e-mail address per row) where the discipleship_mailing_list_e_mail_subscription equals on. I am new at PHP and I am not sure how to do this yet. My idea is that the user would click an UPDATE button and this action would be performed. Are any of you able to help me with this? Thanks. Ron -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Exporting Data From MySQL Using PHP
It is a problem having write access to the file. This is what the online mySQL manual says ... The SELECT ... INTO OUTFILE 'file_name' form of SELECT writes the selected rows to a file. The file is created on the server host, so you must have the FILE privilege to use this syntax. The file cannot already exist, which among other things prevents files such as `/etc/passwd' and database tables from being destroyed. The SELECT ... INTO OUTFILE statement is intended primarily to let you very quickly dump a table on the server machine. If you want to create the resulting file on some client host other than the server host, you can't use SELECT ... INTO OUTFILE. In that case, you should instead use some command like mysql -e SELECT ... file_name on the client host to generate the file. I need to go with another option. Another person wrote me and suggested using the fopen(), fwrite() and fclose() commands. I haven't investigated this option yet. You know when you first learn to walk you do so by example ... I am still needing to know which commands to look up and try to apply to my situation. I have got some PHP code to work ... I am far from done the project I am working on. I was able to get the command: SELECT e_mail FROM subscriptionsdatabase WHERE discipleship_mailing_list_e_mail_subscription LIKE 'on' to work in the mySQL command prompt but when I put it into a .PHP file I get a parse error. I get that parse error by simply coping and pasting the command into the PHP file. In more detail what I am trying to do and why I e-mailed out the question is to wipe clean a mailing list data file and re-create the e-mail subscriptions data file based on which people joined the list during the week. I am still learning mySQL ... The things you wrote helped me play ... I didn't get too far in creating code, but I am learning at the same time. Thanks for the advice. Ron - Original Message - From: Guus der Kinderen [EMAIL PROTECTED] Newsgroups: php.db To: Ron Piggott [EMAIL PROTECTED] Sent: Monday, August 09, 2004 7:22 PM Subject: Re: Exporting Data From MySQL Using PHP Hello Ron, If I understand correctly, you're asking what query to execute (and how to put the result in a textfile) to get the results you want? The first part is described thorougly in the mysql-manual (chapter 14.1.7 for example, search for the SELECT syntax or examples. You'll find it at http://dev.mysql.com/doc/mysql/en/ ). You probably need a query that looks like this: SELECT e_mail FROM your_table_name WHERE discipleship_mailing_list_e_mail_subscription = on; This will return all the requested e-mail adresses. Having this information printed to a file could be reached by using the INTO OUTFILE form of SELECT. Again, check the mysql-manual. for the exact syntax, but you'll probably end up with somthing like: SELECT e_mail INTO OUTFILE '/some/filename.text' FROM your_table_name WHERE discipleship_mailing_list_e_mail_subscription = on; There are a bunch of other options and solutions, though this seems the easiest one to me. Mind that if you try to do this in PHP (and trough a webserver), the webserver should have write-access to the file you specify with 'OUTFILE' - that might be a problem. Personally, I'd run this as some sort of batch script (using the mysql command line interface with the -e option, for example). Hope this helps you along a bit. Regards, Guus der Kinderen Ron Piggott wrote: I have created a MySQL database. The table I am creating is a subscriptions database. I want to be able to export all e-mail addresses stored in the e_mail column into a plain text file on the web server (1 e-mail address per row) where the discipleship_mailing_list_e_mail_subscription equals on. I am new at PHP and I am not sure how to do this yet. My idea is that the user would click an UPDATE button and this action would be performed. Are any of you able to help me with this? Thanks. Ron -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Exporting Data From MySQL Using PHP
It has been my experience that you sometimes need to do backquotes (`)
in MySQL queries when cutting/pasting into PHP -- it's the same key as
the tilde (~) on my keyboard. Try:
$result = mysql_query(SELECT e_mail FROM subscriptionsdatabase WHERE
`discipleship_mailing_list_e_mail_subscription` LIKE 'on');
That should work. I've always used the equal sign instead of LIKE, but
that seems to be a matter of preference.
Regarding the second part, with the fopen() and fwrite() commands,
that's certainly doable. For example, you could do something like:
$handle = fopen(/home/blah/blah.txt, w);
$result = mysql_query(...);
if($result) {
while($somevar = do mysql fetch from $result) {
fwrite($handle, $somevar);
}
...
}
fclose($result);
Now, I realize that code is sort of a hack, but since I'm doing it
mostly on the fly, you'll forgive me my coding errors. Take a look at
the Filesystem section of the PHP manual for information on the
fopen/fwrite() commands, and the MySQL section for more information on
MySQL commands in PHP.
Hope this helps a little more in solving the puzzle.
--
Peter Ellis - [EMAIL PROTECTED]
Web Design and Development Consultant
naturalaxis | http://www.naturalaxis.com/
On Mon, 2004-08-09 at 22:44 -0400, Ron Piggott wrote:
It is a problem having write access to the file. This is what the online
mySQL manual says ...
The SELECT ... INTO OUTFILE 'file_name' form of SELECT writes the selected
rows to a file. The file is created on the server host, so you must have the
FILE privilege to use this syntax. The file cannot already exist, which
among other things prevents files such as `/etc/passwd' and database tables
from being destroyed. The SELECT ... INTO OUTFILE statement is intended
primarily to let you very quickly dump a table on the server machine. If you
want to create the resulting file on some client host other than the server
host, you can't use SELECT ... INTO OUTFILE. In that case, you should
instead use some command like mysql -e SELECT ... file_name on the
client host to generate the file.
I need to go with another option.
Another person wrote me and suggested using the fopen(), fwrite() and
fclose() commands. I haven't investigated this option yet. You know when
you first learn to walk you do so by example ... I am still needing to know
which commands to look up and try to apply to my situation. I have got some
PHP code to work ... I am far from done the project I am working on.
I was able to get the command:
SELECT e_mail
FROM subscriptionsdatabase
WHERE discipleship_mailing_list_e_mail_subscription
LIKE 'on'
to work in the mySQL command prompt but when I put it into a .PHP file I get
a parse error. I get that parse error by simply coping and pasting the
command into the PHP file.
In more detail what I am trying to do and why I e-mailed out the question is
to wipe clean a mailing list data file and re-create the e-mail
subscriptions data file based on which people joined the list during the
week.
I am still learning mySQL ... The things you wrote helped me play ... I
didn't get too far in creating code, but I am learning at the same time.
Thanks for the advice.
Ron
- Original Message -
From: Guus der Kinderen [EMAIL PROTECTED]
Newsgroups: php.db
To: Ron Piggott [EMAIL PROTECTED]
Sent: Monday, August 09, 2004 7:22 PM
Subject: Re: Exporting Data From MySQL Using PHP
Hello Ron,
If I understand correctly, you're asking what query to execute (and how
to put the result in a textfile) to get the results you want?
The first part is described thorougly in the mysql-manual (chapter
14.1.7 for example, search for the SELECT syntax or examples. You'll
find it at http://dev.mysql.com/doc/mysql/en/ ). You probably need a
query that looks like this:
SELECT e_mail FROM your_table_name
WHERE discipleship_mailing_list_e_mail_subscription = on;
This will return all the requested e-mail adresses.
Having this information printed to a file could be reached by using the
INTO OUTFILE form of SELECT. Again, check the mysql-manual. for the
exact syntax, but you'll probably end up with somthing like:
SELECT e_mail INTO OUTFILE '/some/filename.text' FROM your_table_name
WHERE discipleship_mailing_list_e_mail_subscription = on;
There are a bunch of other options and solutions, though this seems the
easiest one to me. Mind that if you try to do this in PHP (and trough a
webserver), the webserver should have write-access to the file you
specify with 'OUTFILE' - that might be a problem. Personally, I'd run
this as some sort of batch script (using the mysql command line
interface with the -e option, for example).
Hope this helps you along a bit.
Regards,
Guus der Kinderen
Ron Piggott wrote:
I have created a MySQL database. The table I am creating is a
subscriptions
database. I want to be able to export all e-mail addresses stored
Re: [PHP-DB] Re: Exporting Data From MySQL Using PHP
Whoops -- the fclose() statement refers to $result when it should refer
to $handle. My bad for not at least spot checking the message before
hitting send!
--
Peter Ellis - [EMAIL PROTECTED]
Web Design and Development Consultant
naturalaxis | http://www.naturalaxis.com/
On Mon, 2004-08-09 at 20:34 -0700, Peter Ellis wrote:
It has been my experience that you sometimes need to do backquotes (`)
in MySQL queries when cutting/pasting into PHP -- it's the same key as
the tilde (~) on my keyboard. Try:
$result = mysql_query(SELECT e_mail FROM subscriptionsdatabase WHERE
`discipleship_mailing_list_e_mail_subscription` LIKE 'on');
That should work. I've always used the equal sign instead of LIKE, but
that seems to be a matter of preference.
Regarding the second part, with the fopen() and fwrite() commands,
that's certainly doable. For example, you could do something like:
$handle = fopen(/home/blah/blah.txt, w);
$result = mysql_query(...);
if($result) {
while($somevar = do mysql fetch from $result) {
fwrite($handle, $somevar);
}
...
}
fclose($result);
Now, I realize that code is sort of a hack, but since I'm doing it
mostly on the fly, you'll forgive me my coding errors. Take a look at
the Filesystem section of the PHP manual for information on the
fopen/fwrite() commands, and the MySQL section for more information on
MySQL commands in PHP.
Hope this helps a little more in solving the puzzle.
--
Peter Ellis - [EMAIL PROTECTED]
Web Design and Development Consultant
naturalaxis | http://www.naturalaxis.com/
On Mon, 2004-08-09 at 22:44 -0400, Ron Piggott wrote:
It is a problem having write access to the file. This is what the online
mySQL manual says ...
The SELECT ... INTO OUTFILE 'file_name' form of SELECT writes the selected
rows to a file. The file is created on the server host, so you must have the
FILE privilege to use this syntax. The file cannot already exist, which
among other things prevents files such as `/etc/passwd' and database tables
from being destroyed. The SELECT ... INTO OUTFILE statement is intended
primarily to let you very quickly dump a table on the server machine. If you
want to create the resulting file on some client host other than the server
host, you can't use SELECT ... INTO OUTFILE. In that case, you should
instead use some command like mysql -e SELECT ... file_name on the
client host to generate the file.
I need to go with another option.
Another person wrote me and suggested using the fopen(), fwrite() and
fclose() commands. I haven't investigated this option yet. You know when
you first learn to walk you do so by example ... I am still needing to know
which commands to look up and try to apply to my situation. I have got some
PHP code to work ... I am far from done the project I am working on.
I was able to get the command:
SELECT e_mail
FROM subscriptionsdatabase
WHERE discipleship_mailing_list_e_mail_subscription
LIKE 'on'
to work in the mySQL command prompt but when I put it into a .PHP file I get
a parse error. I get that parse error by simply coping and pasting the
command into the PHP file.
In more detail what I am trying to do and why I e-mailed out the question is
to wipe clean a mailing list data file and re-create the e-mail
subscriptions data file based on which people joined the list during the
week.
I am still learning mySQL ... The things you wrote helped me play ... I
didn't get too far in creating code, but I am learning at the same time.
Thanks for the advice.
Ron
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Exporting Data From MySQL Using PHP
On Tuesday 10 August 2004 11:34, Peter Ellis wrote: It has been my experience that you sometimes need to do backquotes (`) in MySQL queries when cutting/pasting into PHP -- it's the same key as the tilde (~) on my keyboard. Try: $result = mysql_query(SELECT e_mail FROM subscriptionsdatabase WHERE `discipleship_mailing_list_e_mail_subscription` LIKE 'on'); The backticks (`) are only needed if: (a) your column names uses (MySQL) reserved words (eg names of the builtin functions etc) (b) you have spaces in your column names Both (a) and (b) are rather bad and shouldn't really be used. Just use 'friendly' column names and backticks will not be necessary. That should work. I've always used the equal sign instead of LIKE, but that seems to be a matter of preference. It is not a matter of preference, = and LIKE have different purposes. Refer to manual and/or some SQL tutorial for details. -- Jason Wong - Gremlins Associates - www.gremlins.biz Open Source Software Systems Integrators * Web Design Hosting * Internet Intranet Applications Development * -- Search the list archives before you post http://marc.theaimsgroup.com/?l=php-db -- /* Why won't you let me kiss you goodnight? Is it something I said? -- Tom Ryan */ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Exporting Data From MySQL Using PHP
Ron Piggott wrote: I was able to get the command: SELECT e_mail FROM subscriptionsdatabase WHERE discipleship_mailing_list_e_mail_subscription LIKE 'on' to work in the mySQL command prompt but when I put it into a .PHP file I get a parse error. I get that parse error by simply coping and pasting the command into the PHP file. The reason that this didn't work is that you have to tell PHP first to create a link with your database, then execute a query and get back the results by issuing a few specific commands. Robby's example shows you how, but it's more thorougly explained in the php documentation. Have a look at http://www.php.net/manual/en/ref.mysql.php and its subpages. It has examples and explanations you'll find very useful in setting up PHP to work with a MySQL database. Regards, Guus der Kinderen -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Exporting Data From MySQL Using PHP
It seems to me that, at least in terms of consistency, using backticks isn't a bad idea. After all, if you look at any current version of phpMyAdmin that generates PHP selection code, it uses backticks constantly. I take your point, but it seems to me like it's a good habit no matter what. Perhaps that's more a matter of semantics than PHP programming itself :) As for the difference between = and LIKE, you are correct -- I just don't typically use LIKE for any of my database work, so I forget there's a difference every once in a while :) Thank you for the reminder. -- Peter Ellis - [EMAIL PROTECTED] Web Design and Development Consultant naturalaxis | http://www.naturalaxis.com/ On Tue, 2004-08-10 at 12:33 +0800, Jason Wong wrote: On Tuesday 10 August 2004 11:34, Peter Ellis wrote: It has been my experience that you sometimes need to do backquotes (`) in MySQL queries when cutting/pasting into PHP -- it's the same key as the tilde (~) on my keyboard. Try: $result = mysql_query(SELECT e_mail FROM subscriptionsdatabase WHERE `discipleship_mailing_list_e_mail_subscription` LIKE 'on'); The backticks (`) are only needed if: (a) your column names uses (MySQL) reserved words (eg names of the builtin functions etc) (b) you have spaces in your column names Both (a) and (b) are rather bad and shouldn't really be used. Just use 'friendly' column names and backticks will not be necessary. That should work. I've always used the equal sign instead of LIKE, but that seems to be a matter of preference. It is not a matter of preference, = and LIKE have different purposes. Refer to manual and/or some SQL tutorial for details. -- Jason Wong - Gremlins Associates - www.gremlins.biz Open Source Software Systems Integrators * Web Design Hosting * Internet Intranet Applications Development * -- Search the list archives before you post http://marc.theaimsgroup.com/?l=php-db -- /* Why won't you let me kiss you goodnight? Is it something I said? -- Tom Ryan */ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
