DB -> Excel file

[oduth]

Hi everyone,

I got a code importing the first column data of database to a csv file named systems.csv. But somehow, i couldn't figure it out how can i do that for the other columns of database.

Any help would be greatly appreciated!

column names : hostname, ip_address, primary_admin, secondary_admin, join_date

Below writes only the hostname column to csv file.

Regards,

<?php

class DBToExcel
{
private $db_handle;
private $user_name;
private $password;
private $data_base;
private $host_name;
private $sql;
private $results;

function __construct($host="localhost",$user,$passwd)
{
$this->db_handle = mysql_connect($host,$user,$passwd);
}

function dbSelect($db)
{
$this->data_base = $db;
}

function executeSql($sql_stmt)
{
$this->sql = $sql_stmt;
$this->result = mysql_query($this->sql);
}
function returnResults()
{
return $this->result;
}
}

$user = "root";
$passwd = "";
$db = "inventory";
$sql = "select * from systems where status='active'";

$dbObject = new database($host="localhost",$user,$passwd);
$dbObject->dbSelect($db);
$dbObject->executeSql($sql);

$res = $dbObject->returnResults();

$file_name = "systems.csv";

$fpWrite = fopen("C:\$file_name", "w");

$nameStr = "";

while($record = mysql_fetch_object($res))
{
$name = $record->hostname;

$nameArray = explode("\n",$name);

if(count($nameArray) > 1)
{
$nameTemp = "";
for($i=0;$i < count($nameArray); $i++)
{
$nameTemp = $nameTemp . $nameArray[$i];

if($i != (count($nameArray) - 1))
$nameTemp = $nameTemp . "&sbquo;";
}
$name = $nameTemp;
}

$nameStr = $nameStr.$name."\n";
}

fwrite($fpWrite,$nameStr);
?>

[sneakyimp]

Please indent your code and use the code formatting tags like this:

<?php

class DBToExcel
{
    private $db_handle;
    private $user_name;
    private $password;
    private $data_base;
    private $host_name;
    private $sql;
    private $results;

function __construct($host="localhost",$user,$passwd)
{
    $this->db_handle = mysql_connect($host,$user,$passwd);
}

function dbSelect($db)
   {
    $this->data_base = $db;
}

function executeSql($sql_stmt)
{
    $this->sql = $sql_stmt;
    $this->result = mysql_query($this->sql);
}

function returnResults()
{
    return $this->result;
}
}

$user = "root";
$passwd = "";
$db = "inventory";
$sql = "select * from systems where status='active'";

$dbObject = new database($host="localhost",$user,$passwd);
$dbObject->dbSelect($db);
$dbObject->executeSql($sql);

$res = $dbObject->returnResults();

$file_name = "systems.csv";

$fpWrite = fopen("C:\\$file_name", "w");

$nameStr = "";

while($record = mysql_fetch_object($res))
{
    $name = $record->hostname;

$nameArray = explode("\n",$name);

if(count($nameArray) > 1)
{
    $nameTemp = "";
    for($i=0;$i < count($nameArray); $i++)
    {
        $nameTemp = $nameTemp . $nameArray[$i];

        if($i != (count($nameArray) - 1))
            $nameTemp = $nameTemp . "&sbquo;";
    }
    $name = $nameTemp;
}

$nameStr = $nameStr.$name."\n";
}

fwrite($fpWrite,$nameStr);
?>

It will make your code easier to read.

[sneakyimp]

I don't really understand what you are trying to do when you explode the hostname field and then perform all those operations on it.

[oduth]

Actually, this also works.
I removed some portion of it.
How can i export the other column's data to csv file?

class DBToExcel
{
    private $db_handle;
    private $user_name;
    private $password;
    private $data_base;
    private $host_name;
    private $sql;
    private $results;

function __construct($host="localhost",$user,$passwd)
{
    $this->db_handle = mysql_connect($host,$user,$passwd);
}

function dbSelect($db)
   {
    $this->data_base = $db;
}

function executeSql($sql_stmt)
{
    $this->sql = $sql_stmt;
    $this->result = mysql_query($this->sql);
}

function returnResults()
{
    return $this->result;
}
}

$user = "root";
$passwd = "";
$db = "inventory";
$sql = "select * from systems where status='active'";

$dbObject = new database($host="localhost",$user,$passwd);
$dbObject->dbSelect($db);
$dbObject->executeSql($sql);

$res = $dbObject->returnResults();

$file_name = "systems.csv";

$fpWrite = fopen("C:\\$file_name", "w");

$nameStr = "";

while($record = mysql_fetch_object($res))
{
    $name = $record->hostname;

$nameArray = explode("\n",$name);

$nameStr = $nameStr.$name."\n";
}

fwrite($fpWrite,$nameStr);
?>

[sneakyimp]

Your while loop is very confused. I don't really have any idea what you are trying to do. Maybe try this?

$output = '';
while($record = mysql_fetch_assoc($res)) {
  foreach($record as $key => $value) {
    $record[$key] = addslashes($value);
  }
  $output .= implode(",", $record) . "\n";
}

[oduth]

Thanks for your help sneakyimp,

This works too, just for the first column B [/B]of database. However, i also need to do that operation for the other columns of database.
Whole column info -> hostname, ip_address, primary_admin, secondary_admin, join_date

Regards,

[Weedpacket]

Um, that code that sneakyimp would do every column selected.

I'd also suggest using [man]fputcsv[/man].