469,344 Members | 6,124 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,344 developers. It's quick & easy.

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 3220
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
801 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 suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.