Page 1 of 1

MySQL "into outfile"

Posted: Wed Jun 20, 2007 1:30 am
by galbus
Hello,

Can anyone see a problem with this code?

Code: Select all

// delete current temp version
	@unlink(TMP_DIR."members.csv");

	// construct sql
	$sql = "SELECT   'Surname',
	                 'First Name',
	                 'Email',
	                 'Registration Date',
	                 'Opt-In'
	        UNION
	       (SELECT   user_surname,
	                 user_firstname,
	                 user_email,
	                 DATE_FORMAT(FROM_UNIXTIME(user_registrationdate), '%d/%m/%Y'),
	                 IF (user_optin>0, 'Yes', 'No')
	        INTO     OUTFILE '".TMP_DIR."members.csv'
 	        FIELDS   TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
 	        LINES    TERMINATED BY '\n'
 	        FROM     user 
	        WHERE    role_id=1 
	        ORDER BY user_surname ASC,
	                 user_firstname ASC,
	                 user_registrationdate DESC
	       ) ";
	
	// perform query
	$result = mysql_query($sql) or die(mysql_error());
I am running it on my local xampp server but when I upload onto a Windows server I get the following error:
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 ')' at line 20
the error is sounding like the closing bracket is unexpected but it looks like it should be there to me.

Cheers

Posted: Wed Jun 20, 2007 3:19 am
by onion2k
If you're running a version of MySQL less than 4.1 on your online server then you won't have access to subqueries.

Posted: Wed Jun 20, 2007 8:55 am
by feyd
Why is there a UNION?

Posted: Wed Jun 20, 2007 1:57 pm
by Weirdan
If you're running a version of MySQL less than 4.1 on your online server then you won't have access to subqueries.
It's not a subquery there, it's union
Why is there a UNION?
To add column headers to the csv file he's outputting.

galbus, try loosing both opening and closing parenthesis around your second select part.

Code: Select all

select something from ....
union
select somethingelse from ....