PHP Sql语句从MySQL导出表数据

Question

In my site when Admin is logged in I want him to be able to export a table in phpmyAdmin to a Excel sheet. I was able to do it manually by export button in phpMyAdmin but I want to be able to do this using a SQL statement. This is the first time I am trying it and after looking at exporting-table-structure-to-excel-files-with-phpmyadmin and phpmyadmin-exporting-to-csv-for-excel

I wrote the following code.

if($level==1){
    echo "<br>";
    $q9=" SELECT * INTO OUTFILE 'C:\xampp\htdocs\excelsheet1.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY ''
LINES  TERMINATED BY '
'
FROM   INFORMATION_SCHEMA.COLUMNS
WHERE  TABLE_SCHEMA = DATABASE('db1') AND TABLE_NAME ='tb1'";
$s9=mysql_query($q9,$connect) or die("couldn't export values");
    }  

I have created a blank .csv fie in C:\xampp\htdocs named as excelsheet1.csv. But when I run this code it gives the error couldn't export values and there's a blank excel sheet. In the PHP statement how can I write OPTIONALLY ENCLOSED BY '"' inside SELECT because if I use " inside SELECT I get a syntax error. What can I do to export values in tb1 to excelsheet1.csv

Answer 1

The code you're writing is PHP only, not for phpMyAdmin (which is an administrative GUI interface for managing a MySQL database), so you might be searching based on some misleading keywords.

I suggest you first try running that SQL statement from the command line client -- does it work as expected there? If you run the query without the CSV export part does it show the output you expect?

Your query looks odd to me, I'm not sure about the DATABASE('db1') part -- you're calling the MySQL function DATABASE but I don't think it takes any arguments, so the 'db1' part is either ignored or throwing a warning or error. I don't see that you set a default database, so the function is probably returning NULL anyway.

In the TABLE_NAME ='tb1' part, you should use backticks like ` instead of single quotes like '

Back to the PHP side of things, in your error detection code, you could print the error message from MySQL to better see what the problem is.

Finally, unrelated to your problems, the mysql functions have been depreciated and it's recommended that you switch to pdo or mysqli instead.