Export a table to a file on the server as backup

[MacDknife]

Hi!
I would like to export a table to a text file on the server as backup, preferably formatted (e.g. .csv). I have solved this on my local WAMP server, but cannot get this to function on my remote website.
The website support sent me a statement:
“/usr/bin/mysqldump -u User -pPassword -h [Host](Host as URL) Database_Name user > /var/www/skglimma.se/user.sql”

This seems to be a command line statement, but I would like to execute this programmatically as a query in PHP. Is this at all possible? I have checked the Internet and some solution use mysql or mysqld in their statements, but that has just generated errors each time, probably because it is used in a command line statement.

I would appreciate an example if anyone knows the solution.
Thanks in advance,
MacD

[astonecipher]

That is a command line statement, and would typically be used for something like a cron backup. Do you just want the data put into a file?

[MacDknife]

Yes, I would like to programatically do this as a query in PHP - export a table to a formatted text file on the server. I have done this locally on my WAMP server with:
$sql = “SELECT * FROM inloggning INTO OUTFILE ‘C:/Temp/sample.csv’ CHARACTER SET ‘Latin1’ FIELDS ENCLOSED BY '”’ TERMINATED BY ‘;’ ESCAPED BY ‘"’ LINES TERMINATED BY ‘\r\n’"; and it works perfectly, but if I try this on the remote server (with server adjusted pathways), I receive an error message:
File output failed!(sql) - MySQL error: (1045) Access denied for user ‘skglimma_se’@’%’ (using password: YES).

: P
/MacD

[astonecipher]

So what you are going to want to do it, run the query and them loop through the results and append a file.
https://www.w3schools.com/php/func_filesystem_fputcsv.asp

In your example, you would need to shell_exec the command, which is a possibility, but not if you are on shared hosting.

[MacDknife]

$sql = “SELECT * FROM inloggning INTO OUTFILE ‘C:/Temp/sample.csv’ CHARACTER SET ‘Latin1’ FIELDS ENCLOSED BY '”’ TERMINATED BY ‘;’ ESCAPED BY ‘"’ LINES TERMINATED BY ‘\r\n’";

As you can see above, the output to the file is already formatted as csv text, including adaptation to Swedish characters. There is no appending in the statement, in fact, an error message is generated if the file already exists. I would just like to take the contents of a table and save in a text file.

I need to do this because I am going to drop the target table afterwards and replace it with another. I need to recover the current data if anything goes wrong. This is a function in a website that will be administrated by others in the future, so it has to be done programmatically.

: P
/MacD

[astonecipher]

That is because the statement is a sql statement and not php doing it.

[MacDknife]

Yes, I know that it is, but one can still execute SQL statements as a query from PHP! That statement above is the proof that it works. What I need is the correct syntax and conditions on the remote server for the query to function there. If you you do not know this astonecipher, then I appreciate your input so far, but I need to look elsewhere for further help.
Thanks,
/MacD

[astonecipher]

So make it variable

$path = "/home/backups/" + date('Ymd');

$sql = "SELECT * FROM inloggning INTO OUTFILE '{$path}' CHARACTER SET 'Latin1' FIELDS ENCLOSED BY '\"' TERMINATED BY ';' ESCAPED BY '\"' LINES TERMINATED BY '\r\n'";

[MacDknife]

I had tried that before and I got the same error:
File output failed!(sql) - MySQL error: (1045) Access denied for user ‘skglimma_se’@’%’ (using password: YES)

I do not think my user has enough privileges on that particular server to execute the statement. On my own local WAMP server I had the same sort of problem until I canceled the –secure-file-priv"error, by entering secure-file-priv = “” under the [mysqld] header of my.ini.

As support has tested their statement from a command line input and it functioned properly, they apparently expect me to be able to do the same from PHP, which it doesn’t.
I just think it is crazy that no-one else has had this problem before now and has not left a solution on the net. Sure one can easily do this with various tools, but still…
: P
/MacD

[astonecipher]

$path = "/home/backups/" + date('Ymd') + '.csv'; 
$sql = "SELECT * FROM inloggning";

$pdo = new PDO(/***/);
$result = $pdo->query($sql);

$file = fopen($path,"w");
foreach($result as $row)
{
   fputcsv($file,$row);
}

fclose($file);

Something similar should work.

[MacDknife]

Yes, thank you. I have thought of using the f file statements in PHP. That is next on my list of trials, but I would like to try and find an SQL statement solution in PHP first, if there is one available. I am a pensioner and I have been doing this for a week now, so a couple more days is not a train smash. I just thought it would be so easy when I started out. Silly me…
: P
/MacD

[MacDknife]

SUCCESS!!!
There is another statement in PHP called exec(), that executes an external command. So I used it like this with the information I had received from website support (the website is on a Linux platform):

exec("/usr/bin/mysqldump -u User -pPassword -h Host Database Table > /var/www/domain name/pathway to folder/FileName.sql");

The output is in my case an SQL formatted file - perfect for backup!

Wow! It took a long time, but I got there in the end. (NEVER give up!)
Thanks again for all your help and input, astonecipher.
regards,
/MacD