How to output mysql query to csv changing column value using php function
Get the solution ↓↓↓Solution:
You can't mix PHP and SQL like that. Either you first download the file with SQL, then open it and modify it with php, or your move the conversion logic to the SQL query itself.
Oviously the second solution is more efficient.
If the conversion list is small, aCASE
(or a CTE) should do it :
SELECT
column,
CASE
WHEN column2 = 'green' THEN 1
WHEN column2 = 'yellow' THEN 2
WHEN column2 = 'red' THEN 3
END column2
column3
INTO
OUTFILE '/path/to/file/result.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM yourtable
WHERE columnx = 'çondition';
If you are dealing with a large conversion list, you could consider storing it in a database table. Say your conversion table is calledyourconversiontable
and has columnsold
andnew
, then :
SELECT
t.column,
c.new
t.column3
INTO
OUTFILE '/path/to/file/result.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM yourtable t
INNER JOIN yourconversiontable c ON t.column2 = c.old
WHERE t.columnx = 'çondition';
Answer
Solution:
A working example :
<?php
$con=mysqli_connect("localhost","user","password","db_name");
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
// Your array
$conversionServiceId = [
'green'=>'column1',
'yellow'=>'column2',
'red'=>'column3'
];
$qstring = "
SELECT id," . $conversionServiceId['green'] . "
INTO OUTFILE 'e:/result.csv'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\\n'
FROM nodejs;";
// uncomment next line if you want to see query string in browser
//echo $qstring;
mysqli_query($con, $qstring);
mysqli_close($con);
?>
You can fill in your user and password, db_name, put this file in your root folder and test it from a browser.
Share solution ↓
Additional Information:
Link To Answer People are also looking for solutions of the problem: http failure during parsing for
Didn't find the answer?
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Similar questions
Find the answer in similar questions on our website.
Write quick answer
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.