php mysql query outfile

Question

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?

Answer 1

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;'