Performing mail merge from a mySQL database

[erp23]

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?

[erp23]

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:

ssh -l user -L 3660:localhost:3660 remoteserver
 

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?

[pbmods]

Heya, Erp.

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

SELECT
        *
    FROM
        `table`
    INTO
        OUTFILE
            '/path/to/file.csv'
            FIELDS TERMINATED BY ','
            OPTIONALLY ENCLOSED BY '"'
            LINES TERMINATED BY '\n'
 

[erp23]

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...)

[pbmods]

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?

[mwasif]

MySQL Manual says The file is created on the server host, so you must have the FILE privilege to use this syntax.

[erp23]

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]

[pbmods]

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 :)