MySQL query question

Report · Sep 22, 2006

MySQL version 4.0.25-standard

I've tried to run this query to write the contents of a table to a file:

SELECT * FROM `commingEvents` WHERE 1
select event, location, eventTime, eventDate
into outfile 'wmEvents.csv'
fields terminated by ',' optionally enclosed by '"'
lines terminated by '\n'

But get the following error:

SELECT *
FROM `commingEvents`
WHERE 1
SELECT event, location, eventTime, eventDate
INTO OUTFILE 'wmEvents.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'

MySQL said:

#1064 - You have an error in your SQL syntax. Check the manual that
corresponds to your MySQL server version for the right syntax to use near
'select event, location, eventTime, eventDate
into outfile 'wmE

Could someone please help me with the correct syntax?

Thank You
Jeff

Report · Sep 23, 2006

Jeff wrote:
> I've tried to run this query to write the contents of a table to a file:
>
> SELECT * FROM `commingEvents` WHERE 1
> select event, location, eventTime, eventDate
> into outfile 'wmEvents.csv'
> fields terminated by ',' optionally enclosed by '"'
> lines terminated by '\n'

SELECT event, location, eventTime, eventDate
INTO OUTFILE 'wmEvents.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM commingEvents

--
David Powers
Adobe Community Expert
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
http://foundationphp.com/

Report · Sep 23, 2006

Thank you for your help, I think I'm getting closer but still not working.
what I'm trying to do is get data from a MySQL table written to a file
"wmevents.csv" so I can make that file avilable for download.

when I run the query in PHPmyAdmin I get this error:
SQL query:
SELECT event, location, eventTime, eventDate
INTO OUTFILE 'wmEvents.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM commingEvents
MySQL said:
#1045 - Access denied for user: 'wmevents@localhost' (Using password: YES)

If I add it in the SQL box of the Recordest menu in DreamWeaver and the try
to preview the page I get this error:

Parse error: parse error, unexpected T_CONSTANT_ENCAPSED_STRING in
/home/wmevents/public_html/untitled.php on line 33

This is the php code that DreamWeaver wrote:

<?php require_once('Connections/events.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "",
$theNotDefinedValue = "")
{
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

$theValue = function_exists("mysql_real_escape_string") ?
mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" :
"NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue :
$theNotDefinedValue;
break;
}
return $theValue;
}
}

mysql_select_db($database_events, $events);
$query_getEvents = "SELECT event, location, eventTime, eventDate INTO
OUTFILE 'wmEvents.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES
TERMINATED BY '\n' FROM commingEvents";
$getEvents = mysql_query($query_getEvents, $events) or die(mysql_error());
$row_getEvents = mysql_fetch_assoc($getEvents);
$totalRows_getEvents = mysql_num_rows($getEvents);
?>

Thank You
Jeff

"David Powers" <david@example.com> wrote in message
news:ef3cqi$lms$1@forums.macromedia.com...
> Jeff wrote:
>> I've tried to run this query to write the contents of a table to a file:
>>
>> SELECT * FROM `commingEvents` WHERE 1
>> select event, location, eventTime, eventDate
>> into outfile 'wmEvents.csv'
>> fields terminated by ',' optionally enclosed by '"'
>> lines terminated by '\n'
>
> SELECT event, location, eventTime, eventDate
> INTO OUTFILE 'wmEvents.csv'
> FIELDS TERMINATED BY ','
> ENCLOSED BY '"'
> LINES TERMINATED BY '\n'
> FROM commingEvents
>
> --
> David Powers
> Adobe Community Expert
> Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
> http://foundationphp.com/

Report · Sep 23, 2006

Jeff wrote:
> when I run the query in PHPmyAdmin I get this error:
>
> MySQL said:
> #1045 - Access denied for user: 'wmevents@localhost' (Using password: YES)

That's a permissions error. I have just tested the same code (using my
own column and table names), and it works perfectly. One problem,
however, is that the CSV file is created in the MySQL data store, so you
need to set a full path to indicate where the file is to be stored. So
this line:

INTO OUTFILE 'wmEvents.csv'

needs to be something like:

INTO OUTFILE 'C:\wmEvents.csv'

or

INTO OUTFILE '/home/myfolder/wmEvents.csv'

> If I add it in the SQL box of the Recordest menu in DreamWeaver and the try
> to preview the page I get this error:
>
> Parse error: parse error, unexpected T_CONSTANT_ENCAPSED_STRING in
> /home/wmevents/public_html/untitled.php on line 33

> $query_getEvents = "SELECT event, location, eventTime, eventDate INTO
> OUTFILE 'wmEvents.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES
> TERMINATED BY '\n' FROM commingEvents";

The parse error is caused by the double quote in ENCLOSED BY '"'

You need to escape it like this:

ENCLOSED BY '\"'

--
David Powers
Adobe Community Expert
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
http://foundationphp.com/

Report · Sep 28, 2006

Sorry for not getting back to you David, your corrections to the SQL syntax
were perfect.

Unfortunately, the issues with user permission persists. I talked to tec
support at the hosting co but they were little help. Just asked if the DB
user permissions were set to "all" and if the permission of the target file
was set to at least 755.
I had already given "all" permissions to the user and the target file
permission is 777. This was the last I've heard from them for the past 3
days. Needless to say the "#1045 - Access denied for user:
'wmevents@localhost' (Using password: YES)"
has not been solved.

Jeff

"David Powers" <david@example.com> wrote in message
news:ef48ob$mah$1@forums.macromedia.com...
> Jeff wrote:
>> when I run the query in PHPmyAdmin I get this error:
>>
>> MySQL said:
>> #1045 - Access denied for user: 'wmevents@localhost' (Using password:
>> YES)
>
> That's a permissions error. I have just tested the same code (using my own
> column and table names), and it works perfectly. One problem, however, is
> that the CSV file is created in the MySQL data store, so you need to set a
> full path to indicate where the file is to be stored. So this line:
>
> INTO OUTFILE 'wmEvents.csv'
>
> needs to be something like:
>
> INTO OUTFILE 'C:\wmEvents.csv'
>
> or
>
> INTO OUTFILE '/home/myfolder/wmEvents.csv'
>
>> If I add it in the SQL box of the Recordest menu in DreamWeaver and the
>> try to preview the page I get this error:
>>
>> Parse error: parse error, unexpected T_CONSTANT_ENCAPSED_STRING in
>> /home/wmevents/public_html/untitled.php on line 33
>
>> $query_getEvents = "SELECT event, location, eventTime, eventDate INTO
>> OUTFILE 'wmEvents.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES
>> TERMINATED BY '\n' FROM commingEvents";
>
> The parse error is caused by the double quote in ENCLOSED BY '"'
>
> You need to escape it like this:
>
> ENCLOSED BY '\"'
>
> --
> David Powers
> Adobe Community Expert
> Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
> http://foundationphp.com/

Report · Sep 28, 2006

Jeff wrote:
> Needless to say the "#1045 - Access denied for user:
> 'wmevents@localhost' (Using password: YES)"
> has not been solved.

Answered in the thread titled "write MySQL table data to a file".

--
David Powers
Adobe Community Expert
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
http://foundationphp.com/