Printable Version of Topic

Click here to view this topic in its original format

HTMLHelp Forums _ Databases _ PHP Mysqli select to csv

Posted by: nuddernuby Aug 27 2015, 04:16 AM

Could someone save me from insanity and provide a working script for the following simple problem, please. (I've tried many and failed)

I have a table 'projects' with field 'projectname', currently populated with five records, say A,B,C,D & E.
I need to store an the array of these records in this format: $arr=("A","B","C","D","E") or output it to a csv string in csv file.

I can echo the results but fail to get this into the required format/file.

Basic query is:
$query=mysqli_query($conn,"SELECT projectname FROM projects");

What then?

Apologies for the simplicity of the problem.

Posted by: CharlesEF Aug 27 2015, 04:42 AM

You should post your attempts, even if they don't work. Someone might be able to spot the problem and help. Also, which do you want an array or output to csv file?

Posted by: nuddernuby Aug 27 2015, 06:33 AM

I need a variable to use in an options dropdown menu on a form. With a csv string I can get to it via str_getcsv(). A variable containing the array will be better.

Tried (inter alia) the following script to get csv string, but it only provides last record:
QUOTE
<?php // THIS QUERY EXTRACTS Project Names
include('db_conn_change.php');
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
//die("Database connection failed! " . $conn->connect_error);
trigger_error('Database connection failed: ' . $conn->connect_error, E_USER_ERROR);
}
$query=mysqli_query($conn,"SELECT projectname FROM projects");
function addRowToCsv(& $csvString, $cols) {
$csvString = implode(',', $cols) . PHP_EOL;
}
$csvString = '';
while ($row = mysqli_fetch_assoc($query)) {
addRowToCsv($csvString, $row);
}
file_put_contents('ChangeProjects.csv', $csvString);
mysqli_free_result($query);
mysqli_close($conn);
?>
UNQUOTE

Also tried this but received zero output:

QUOTE
$sql="SELECT projectname
INTO OUTFILE 'C:\xampp\htdocs\p2\dynform\projects.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY ' " '
LINES TERMINATED BY '\n'
FROM projects ";
UNQUOTE

Tried many others. This should be so simple....

Posted by: nuddernuby Aug 27 2015, 12:44 PM

Thank you, I finally managed to get it working using this simple script with $proj as the required output:

QUOTE

$query = "SELECT * FROM `projects` WHERE co='ABC' ";
$result = mysql_query($query);
$number = mysql_numrows($result);
$x = 0;
$proj = array();
while ($x < $number)
{
$row = mysql_fetch_array($result);
$id = $row['id']; $p = $row[ 'projectname' ];
$proj[ $id ] = $p;
$x++;
}
//print_r($proj);
$arr=implode(',',$proj);
//print_r('<br>' . $arr);
file_put_contents('NEWfilename.txt', print_r($proj, true));

So, all well. Thank you for your attention.