export noahs database into a CSV file to import at Open Classifieds

export.php

<?
set_time_limit(0);
ini_set('memory_limit', '1024M');

include("initdirs.php");

include(NOAH_APP . "/config.php");
include(NOAH_APP . "/constants.php");


 // Try to guess installation URL
$url = 'http://'.$_SERVER['SERVER_NAME'];
if ($_SERVER['SERVER_PORT'] != '80') 
    $url = $url.':'.$_SERVER['SERVER_PORT'];

//getting the folder, erasing the export
$folder = str_replace('/export.php','', $_SERVER['SCRIPT_NAME']).'/';
$url .=$folder;

//conect to DB
$db = phpMyDB::GetInstance($dbUser,$dbUserPw,$dbName,$hostName);




//get categories, map them into an array.
$categories = $db->getRows('SELECT id,name,wholeName,description FROM '.$dbPrefix.'category','assoc','id');

//d($categories);

//get title columns per category
$title_columns = $db->getRows("SELECT cid, columnindex FROM  `".$dbPrefix."customfield` WHERE  `name` =  'title' AND cid !=0",'assoc','cid');

//d($title_columns);

//get description columns per category
$description_columns = $db->getRows("SELECT cid, columnindex FROM  `".$dbPrefix."customfield` WHERE  `name` =  'description' AND cid !=0",'assoc','cid');

//d($description_columns);

//get price columns per category
$price_columns = $db->getRows("SELECT cid, columnindex FROM  `".$dbPrefix."customfield` WHERE  `name` =  'price' AND cid !=0",'assoc','cid');

//d($price_columns);

//get picture columns per category
$picture_columns = $db->getRows("SELECT cid,  REPLACE( columnindex,  'col_',  '' ) columnindex, name FROM  `".$dbPrefix."customfield`  
                                    WHERE  `name` LIKE  'Pic%' AND cid !=0 ORDER BY cid,columnindex",'assoc');

//d($picture_columns);

//get all users as array
$users = $db->getRows('SELECT id,name,email FROM '.$dbPrefix.'user where active=1','assoc','id');
//d($users);

//get all items
$items = $db->getRows('SELECT * FROM '.$dbPrefix.'item where status=1','assoc','id');


/*

table item:
cid = id_category
ownerId = id_user
*/

//prepare array for CSV

//header that we are going to return
$csv_header = array('user_name','user_email','title','description','date','category','location',
                            'price','address','phone','website','image_1','image_2','image_3','image_4');

$ads = array();

$ads[] = $csv_header;

foreach ($items as $key => $item) 
{
    //get title
    if (isset($title_columns[$item['cid']]['columnindex']))
    {
        $title = $item[$title_columns[$item['cid']]['columnindex']];
    }
    //no title
    else 
        $title = '';

    //get description
    if (isset($description_columns[$item['cid']]['columnindex']))
    {
        $description = $item[$description_columns[$item['cid']]['columnindex']];
    }
    //no description
    else 
        $description = '';

    //get price
    if (isset($price_columns[$item['cid']]['columnindex']))
    {
        $price = $item[$price_columns[$item['cid']]['columnindex']];
    }
    //no price
    else 
        $price = NULL;

    $images = item_get_images($key,$item['cid'],$picture_columns);

    $pic1 = NULL;
    $pic2 = NULL;
    $pic3 = NULL;
    $pic4 = NULL;

    if (count($images)>0)
    {
        if (isset($images[0]))
            $pic1 = $url.$images[0];

        if (isset($images[1]))
            $pic2 = $url.$images[1];

        if (isset($images[2]))
            $pic3 = $url.$images[2];

        if (isset($images[3]))
            $pic4 = $url.$images[3];
    }

    $values = array(
                        $users[$item['ownerId']]['name'],
                        $users[$item['ownerId']]['email'],
                        $title,
                        $description,
                        $item['creationtime'],
                        $categories[$item['cid']]['name'],
                        NULL,
                        $price,
                        NULL,
                        NULL,
                        NULL,
                        $pic1,$pic2,$pic3,$pic4
                    );

    $ads[] = $values;
}

//d($ads);
array_to_csv_download($ads);



function item_get_images($id,$cid,$picture_columns)
{
    $columns = array();
    foreach ($picture_columns as $key => $value) 
    {
        if ($value['cid']==$cid)
        {
            $columns[] = $value['columnindex'];
        }
    }
    //print_r($columns);
    $images = array();
    
    foreach ($columns as $col)
    {
        $pic  =  '/'.$id.'_'.$col.'.jpg';
        $path = AD_PIC_DIR .$pic;
        //echo($path);
        if( file_exists($path) )
            $images[] = 'pictures/listings'.$pic;
    }
    
    return $images;
}


function array_to_csv_download($array, $filename = "export.csv", $delimiter=";") {
    // open raw memory as file so no temp files needed, you might run out of memory though
    $f = fopen('php://memory', 'w'); 
    // loop over the input array
    foreach ($array as $line) { 
        // generate csv lines from the inner arrays
        fputcsv($f, $line, $delimiter); 
    }
    // reset the file pointer to the start of the file
    fseek($f, 0);
    // tell the browser it's going to be a csv file
    header('Content-Type: application/csv');
    // tell the browser we want to save it instead of displaying it
    header('Content-Disposition: attachment; filename="'.$filename.'";');
    // make php send the generated csv lines to the browser
    fpassthru($f);
}


/*
 * Name:    phpMyDB
 * URL:     http://neo22s.com/phpmydb/
 * Version: v1.3
 * Date:    12/07/2012
 * Author:  Chema Garrido
 * License: GPL v3
 * Notes:   Mysql Object with cache integrated
 */

class phpMyDB {//requires wrapper cache class to use caching
    private $dbh;//data base handler
    public $query_counter=0;//count queries
    private $insert_last_id;//last insert ID for mysql_insert_id()
    private static $instance;//Instance of this class
    
        // Always returns only one instance
        public static function GetInstance($dbuser='', $dbpass='', $dbname='', $dbhost='',$dbcharset='utf8',$dbtimezone='',$dbconnectiontype='default'){
            if (!isset(self::$instance)){//doesn't exists the isntance
                 self::$instance = new self($dbuser, $dbpass, $dbname, $dbhost,$dbcharset,$dbtimezone,$dbconnectiontype);//goes to the constructor
            }
            return self::$instance;
        }
        
        // Prevent users to clone the instance
        public function __clone(){
           die('Clone is not allowed.');
        }
        
        // DB Constructor - connects to the server and selects a database
        private function __construct($dbuser, $dbpass, $dbname, $dbhost,$dbcharset,$dbtimezone,$dbconnectiontype){
            if ($dbconnectiontype=='persistent') $this->dbh = @mysql_pconnect($dbhost,$dbuser,$dbpass);
            else $this->dbh = @mysql_connect($dbhost,$dbuser,$dbpass);
            
            if (!$this->dbh){
                die('<ol><li><b>Error establishing a database connection!</b>
                                    <li>Are you sure you have the correct user/password?
                                    <li>Are you sure that you have typed the correct hostname?
                                    <li>Are you sure that the database server is running?</ol>');
            }
            $this->selectDB($dbname);
            $this->query('SET NAMES '.$dbcharset);
            if (!empty($dbtimezone))$this->query('SET time_zone =  \''.$dbtimezone.'\'');
        }
        
        public function __destruct() {
            $this->closeDB();
        }
        
        // Select a DB (if another one needs to be selected)
        public function selectDB($db){
            if ( !@mysql_select_db($db,$this->dbh)){
                die('<ol><li><b>Error selecting database <u>'.$db.'</u>!
                                    </b><li>Are you sure it exists?
                                    <li>Are you sure there is a valid database connection?</ol>');
            }
        }
        
        // Closes DB connection
        public function closeDB(){
            if (isset($this->dbh)){
                mysql_close();
                unset($this->dbh);
            }
        }
        
        // Normal query
        public function query($query) {
            $this->query_counter++;
            $return_val=@ mysql_query($query) or die('('.mysql_errno().') in line '.__LINE__.' error:'.mysql_error().' 
                                                                    <br/>Query: '. $query.' <br/>File: '. $_SERVER['PHP_SELF'] );
            return $return_val;
        }
        
        ///Select functions
        
        //normal select
        public function select($fields, $from, $where='') {  
            if (!empty($where)) $where = ' WHERE ' . $where;   
            $query = 'SELECT ' . $fields . ' FROM `' . $from . '`'. $where;  
            $result = $this->query($query);  
            return $result;  
        }  
        
        /**
         * From a given query returns an array,  uses cache if enabled
         * @param string $query
         * @param string $type
         * @param string $id for the index value
         * @return array results
         */
        public function getRows($query,$type='assoc',$id=NULL)
        {
            $result=$this->query($query);
            if (mysql_num_rows($result)>0)
            {
                $values=array();
                if ($type=='object')//@todo check this//if type is object and the cache is activated we use assoc since object can't be cached
                {
                    $type='assoc';
                }
                switch ($type)
                {
                    case 'assoc':
                        while($row = mysql_fetch_assoc($result)) 
                        {
                            if (isset($row[$id]))
                                $values[$row[$id]] = $row;
                            else
                                array_push($values, $row);  
                        }
                    break;
                    case  'row':
                        while($row = mysql_fetch_row($result))     array_push($values, $row);  
                    break;
                    case 'object':
                        while($row = mysql_fetch_object($result))  array_push($values, $row);  
                    break;
                    case 'value':
                        $row     = mysql_fetch_row($result);
                        $values  = $row[0];//return value
                    break;  
                    default:
                        $this->sql_error('Not recognized fetch mode: '.$type);
                    break;
                } 
            }
            else
            {
                $values=NULL;
            }
            mysql_free_result($result);//freeing memory

            return $values;
        }
        
        // return the 1st value from a field of a query
        public function getValue($query){
            return $this->getRows($query,'value');
        }
          
}

function d($v)
{
    die(print_r($v));
}