472,107 Members | 1,403 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,107 software developers and data experts.

Performing mail merge from a mySQL database

26
I run a php site which contains a number of user details onto a mySQL database.

I would like to be able to use the user database stored on my site's remote server as a source for mail merge fields in word:mac on my computer - the server connection is through ssl.

So far, I have figured out that I need to do download the tables to Excel first, using a third party ODBC driver (e.g. Actual Technologies). I have got the system to work with my machine as the host, but I am not able to connect to the remote database using ODBC - I suspect this is because I haven't found a way to log onto the server before logging onto the database itself.

Is this task possible, and if so, am I using the right approach?
Sep 1 '07 #1
7 3398
erp23
26
Ok, I've been trying to set up ssh tunneling in order to connect to the remote host through the terminal, but I haven't managed to get the syntax right. It is worth noting that I am running mySQL on my local comp, so will need a different port for this.

I've been using:
Expand|Select|Wrap|Line Numbers
  1. ssh -l user -L 3660:localhost:3660 remoteserver
  2.  
I'm not an expert on port settings, so in desperate need for some advice?

Alternatively, is there a good GUI driven SSH client for Mac?
Sep 1 '07 #2
pbmods
5,821 Expert 4TB
Heya, Erp.

To load your data into Excel, consider exporting your data as a CSV:

Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.         *
  3.     FROM
  4.         `table`
  5.     INTO
  6.         OUTFILE
  7.             '/path/to/file.csv'
  8.             FIELDS TERMINATED BY ','
  9.             OPTIONALLY ENCLOSED BY '"'
  10.             LINES TERMINATED BY '\n'
  11.  
Sep 1 '07 #3
erp23
26
when i run the query i am getting:

error 1045: Access denied for user...


How do I get around this? (since it is a remote server, I don't have root access...)
Sep 2 '07 #4
pbmods
5,821 Expert 4TB
Heya, erp.

Check your login credentials and make sure you are sending the correct Username / Password.

You should not need special permissions to save data into a dumpfile. Can you perform a regular SELECT statement against that table with the same login information?
Sep 2 '07 #5
mwasif
802 Expert 512MB
MySQL Manual says The file is created on the server host, so you must have the FILE privilege to use this syntax.
Sep 2 '07 #6
erp23
26
I've by-passed the problem by creating the output and then creating the page with the appropriate headers....

[PHP] $output = "Username,Title,First Name,Surname,Email,Address 1,Address 2,Address 3,City,Region,Country,Postcode,Telephone,Fax";
$output .= "\n";
$result = mysql_query("SELECT * FROM tbl_usrlogin INNER JOIN tbl_userdetails ON tbl_userdetails.userID=tbl_usrlogin.Username");

while($row = mysql_fetch_array($result)) {

$row[position] = str_replace(",","" , $row[position]);

$output .= "$row[Username], $row[repeat for all values] \n";
}

header("Content-type: application/vnd.ms-excel");
header("Content-disposition: attachment; filename =contacts " .
date("Y-m-d").".csv"); ;
print $output;
mysql_free_result($result);
exit; [/PHP]
Sep 3 '07 #7
pbmods
5,821 Expert 4TB
Heya, Erp.

That'll do it.

Glad to hear you got it working! Good luck with your project, and if you ever need anything, post back anytime :)
Sep 3 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by Pierre-Luc Soucy | last post: by
3 posts views Thread by cdelarte | last post: by
6 posts views Thread by crealesmith | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.