dongsi2317
2016-07-19 09:20 阅读 45
已采纳

php mysql查询输出文件

I am trying to create a script that runs on my database. The query itself works fine in phpmyadmin but need to be able to run it elsewhere. So my idea was, put it into a php script.

My query looks like this:

SET @sql_text =
CONCAT ("SELECT
`table1`.`field1`,
`table2`.`field2`

FROM `table1`,`table2`

WHERE
`table1`.`field1`=`table2`.`field2`

 into outfile '/pathtofolder/myresult-"
   , DATE_FORMAT( NOW(), '%Y%m%d-%H:%i')
   , ".csv'
FIELDS TERMINATED BY '|'
    "    );

PREPARE s1 FROM @sql_text;
EXECUTE s1;

DROP PREPARE s1;

Now I seem to run into trouble trying to get this into php. My attempt:

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "databasename";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql = 'SET @sql_text =
CONCAT ("SELECT
`table1`.`field1`,
`table2`.`field2`

FROM `table1`,`table2`

WHERE
`table1`.`field1`=`table2`.`field2`

 into outfile '/pathtofolder/myresult-"
   , DATE_FORMAT( NOW(), '%Y%m%d-%H:%i')
   , ".csv'
FIELDS TERMINATED BY '|'
    "    );

PREPARE s1 FROM @sql_text;
EXECUTE s1;

DROP PREPARE s1;'

if ($conn->query($sql) === TRUE) {
    echo "Record updated successfully";
} else {
    echo "Error updating record: " . $conn->error;
}

$conn->close();
?> 

Unfortunatly this does not seem to work. What am I overlooking?

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

1条回答 默认 最新

  • 已采纳
    dongmiyu8979 dongmiyu8979 2016-07-19 09:32

    As the sql is within single quotes but contains single quotes you would need to escape them using the backslash.

    $sql = 'SET @sql_text =
    CONCAT ("SELECT
    `table1`.`field1`,
    `table2`.`field2`
    
    FROM `table1`,`table2`
    
    WHERE
    `table1`.`field1`=`table2`.`field2`
    
     into outfile \'/pathtofolder/myresult-"
       , DATE_FORMAT( NOW(), \'%Y%m%d-%H:%i\')
       , ".csv\'
    FIELDS TERMINATED BY \'|\'
        "    );
    
    PREPARE s1 FROM @sql_text;
    EXECUTE s1;
    
    DROP PREPARE s1;'
    
    点赞 评论 复制链接分享