CSV to PHP to JSON into MySQL

Question

I am developing a bespoke pricing matrix for a client, they have codes to distinguish products with different options, there are 6 different variations for one code, as there are 6 different types of material that incur different costs, other bolt ons also change the code and the costs based on the material... and so on.

I'll start by showing you my prices database structure (MySQL)

|------
|Field|Type|Null|Default
|------
|//**id**//|mediumint(9)|No|
|jp_code|varchar(7)|No|
|brand_rate|text|No|
|price_25|text|No|
|price_50|text|No|
|price_100|text|No|
|price_250|text|No|
|price_500|text|No|
|price_1000|text|No|

I store all 6 prices for each code as JSON in the prices and brand rate field, this is processed by JS later on.

So a typical entry for that database looks like this:

|1|JP6000|["F","F","n\/a","F","F","F"]|["2.92","2.92","n\/a","4.86","6.35","7.62"]|["2.77","2.77","n\/a","4.62","6.03","7.24"]|["2.55","2.55","4.21","4.25","5.55","6.66"]|["2.45","2.45","3.83","4.08","5.33","6.40"]|["2.38","2.38","3.64","3.96","5.17","6.20"]|["2.50","2.33","3.47","3.89","5.08","6.10"]

The client needs to be able to upload a CSV, which can seamlessly update these prices.

The CSV file will look like this:

If you take a look at the MySQL row entry you should be able to marry up the data, so onto the problem!

I am using this to work with CSV files: parseCSV v0.4.3 beta http://code.google.com/p/parsecsv-for-php/

I have got this to group all the results by the JP Code like this:

array(2) {
  ["JP6000"]=>
  array(6) {
    [0]=>
    array(9) {
      ["Code"]=>
      string(6) "JP6000"
      ["Brand Rate"]=>
      string(1) "F"
      ["Price_25"]=>
      string(4) "2.92"
      ["Price_50"]=>
      string(4) "2.77"
      ["Price_100"]=>
      string(4) "2.55"
      ["Price_250"]=>
      string(4) "2.45"
      ["Price_500"]=>
      string(4) "2.38"
      ["Price_1000"]=>
      string(4) "2.33"
      ["Material"]=>
      string(10) "Belluno PU"
    }
    [1]=>
    array(9) {
      ["Code"]=>
      string(6) "JP6000"
      ["Brand Rate"]=>
      string(1) "F"
      ["Price_25"]=>
      string(4) "2.92"
      ["Price_50"]=>
      string(4) "2.77"
      ["Price_100"]=>
      string(4) "2.55"
      ["Price_250"]=>
      string(4) "2.45"
      ["Price_500"]=>
      string(4) "2.38"
      ["Price_1000"]=>
      string(4) "2.33"
      ["Material"]=>
      string(9) "Torino PU"
    }
    [2]=>
    array(9) {
      ["Code"]=>
      string(6) "JP6000"
      ["Brand Rate"]=>
      string(3) "n/a"
      ["Price_25"]=>
      string(3) "n/a"
      ["Price_50"]=>
      string(3) "n/a"
      ["Price_100"]=>
      string(4) "4.21"
      ["Price_250"]=>
      string(4) "3.83"
      ["Price_500"]=>
      string(4) "3.64"
      ["Price_1000"]=>
      string(4) "3.47"
      ["Material"]=>
      string(11) "Full Colour"
    }
    [3]=>
    array(9) {
      ["Code"]=>
      string(6) "JP6000"
      ["Brand Rate"]=>
      string(1) "F"
      ["Price_25"]=>
      string(4) "4.86"
      ["Price_50"]=>
      string(4) "4.62"
      ["Price_100"]=>
      string(4) "4.25"
      ["Price_250"]=>
      string(4) "4.08"
      ["Price_500"]=>
      string(4) "3.96"
      ["Price_1000"]=>
      string(4) "3.89"
      ["Material"]=>
      string(8) "Finecell"
    }
    [4]=>
    array(9) {
      ["Code"]=>
      string(6) "JP6000"
      ["Brand Rate"]=>
      string(1) "F"
      ["Price_25"]=>
      string(4) "6.35"
      ["Price_50"]=>
      string(4) "6.03"
      ["Price_100"]=>
      string(4) "5.55"
      ["Price_250"]=>
      string(4) "5.33"
      ["Price_500"]=>
      string(4) "5.17"
      ["Price_1000"]=>
      string(4) "5.08"
      ["Material"]=>
      string(5) "Nappa"
    }
    [5]=>
    array(9) {
      ["Code"]=>
      string(6) "JP6000"
      ["Brand Rate"]=>
      string(1) "F"
      ["Price_25"]=>
      string(4) "7.62"
      ["Price_50"]=>
      string(4) "7.24"
      ["Price_100"]=>
      string(4) "6.66"
      ["Price_250"]=>
      string(3) "6.4"
      ["Price_500"]=>
      string(3) "6.2"
      ["Price_1000"]=>
      string(3) "6.1"
      ["Material"]=>
      string(8) "Richmond"
    }
  }
  ["JP6010"]=>
  array(6) {
    [0]=>
    array(9) {
      ["Code"]=>
      string(6) "JP6010"
      ["Brand Rate"]=>
      string(1) "F"
      ["Price_25"]=>
      string(4) "3.41"
      ["Price_50"]=>
      string(4) "3.24"
      ["Price_100"]=>
      string(4) "2.98"
      ["Price_250"]=>
      string(4) "2.86"
      ["Price_500"]=>
      string(4) "2.78"
      ["Price_1000"]=>
      string(4) "2.73"
      ["Material"]=>
      string(10) "Belluno PU"
    }
    [1]=>
    array(9) {
      ["Code"]=>
      string(6) "JP6010"
      ["Brand Rate"]=>
      string(1) "F"
      ["Price_25"]=>
      string(4) "3.41"
      ["Price_50"]=>
      string(4) "3.24"
      ["Price_100"]=>
      string(4) "2.98"
      ["Price_250"]=>
      string(4) "2.86"
      ["Price_500"]=>
      string(4) "2.78"
      ["Price_1000"]=>
      string(4) "2.73"
      ["Material"]=>
      string(9) "Torino PU"
    }
    [2]=>
    array(9) {
      ["Code"]=>
      string(6) "JP6010"
      ["Brand Rate"]=>
      string(3) "n/a"
      ["Price_25"]=>
      string(3) "n/a"
      ["Price_50"]=>
      string(3) "n/a"
      ["Price_100"]=>
      string(4) "4.77"
      ["Price_250"]=>
      string(4) "4.33"
      ["Price_500"]=>
      string(4) "4.13"
      ["Price_1000"]=>
      string(4) "3.93"
      ["Material"]=>
      string(11) "Full Colour"
    }
    [3]=>
    array(9) {
      ["Code"]=>
      string(6) "JP6010"
      ["Brand Rate"]=>
      string(1) "F"
      ["Price_25"]=>
      string(4) "5.41"
      ["Price_50"]=>
      string(4) "5.14"
      ["Price_100"]=>
      string(4) "4.74"
      ["Price_250"]=>
      string(4) "4.55"
      ["Price_500"]=>
      string(4) "4.41"
      ["Price_1000"]=>
      string(4) "4.33"
      ["Material"]=>
      string(8) "Finecell"
    }
    [4]=>
    array(9) {
      ["Code"]=>
      string(6) "JP6010"
      ["Brand Rate"]=>
      string(1) "F"
      ["Price_25"]=>
      string(3) "6.9"
      ["Price_50"]=>
      string(4) "6.56"
      ["Price_100"]=>
      string(4) "6.04"
      ["Price_250"]=>
      string(3) "5.8"
      ["Price_500"]=>
      string(4) "5.63"
      ["Price_1000"]=>
      string(4) "5.52"
      ["Material"]=>
      string(5) "Nappa"
    }
    [5]=>
    array(9) {
      ["Code"]=>
      string(6) "JP6010"
      ["Brand Rate"]=>
      string(1) "F"
      ["Price_25"]=>
      string(4) "8.28"
      ["Price_50"]=>
      string(4) "7.87"
      ["Price_100"]=>
      string(4) "7.25"
      ["Price_250"]=>
      string(4) "6.96"
      ["Price_500"]=>
      string(4) "6.76"
      ["Price_1000"]=>
      string(4) "6.62"
      ["Material"]=>
      string(8) "Richmond"
    }
  }
}
array(2) {
  ["JP6000"]=>
  array(6) {
    [0]=>
    array(9) {
      ["Code"]=>
      string(6) "JP6000"
      ["Brand Rate"]=>
      string(1) "F"
      ["Price_25"]=>
      string(4) "2.92"
      ["Price_50"]=>
      string(4) "2.77"
      ["Price_100"]=>
      string(4) "2.55"
      ["Price_250"]=>
      string(4) "2.45"
      ["Price_500"]=>
      string(4) "2.38"
      ["Price_1000"]=>
      string(4) "2.33"
      ["Material"]=>
      string(10) "Belluno PU"
    }
    [1]=>
    array(9) {
      ["Code"]=>
      string(6) "JP6000"
      ["Brand Rate"]=>
      string(1) "F"
      ["Price_25"]=>
      string(4) "2.92"
      ["Price_50"]=>
      string(4) "2.77"
      ["Price_100"]=>
      string(4) "2.55"
      ["Price_250"]=>
      string(4) "2.45"
      ["Price_500"]=>
      string(4) "2.38"
      ["Price_1000"]=>
      string(4) "2.33"
      ["Material"]=>
      string(9) "Torino PU"
    }
    [2]=>
    array(9) {
      ["Code"]=>
      string(6) "JP6000"
      ["Brand Rate"]=>
      string(3) "n/a"
      ["Price_25"]=>
      string(3) "n/a"
      ["Price_50"]=>
      string(3) "n/a"
      ["Price_100"]=>
      string(4) "4.21"
      ["Price_250"]=>
      string(4) "3.83"
      ["Price_500"]=>
      string(4) "3.64"
      ["Price_1000"]=>
      string(4) "3.47"
      ["Material"]=>
      string(11) "Full Colour"
    }
    [3]=>
    array(9) {
      ["Code"]=>
      string(6) "JP6000"
      ["Brand Rate"]=>
      string(1) "F"
      ["Price_25"]=>
      string(4) "4.86"
      ["Price_50"]=>
      string(4) "4.62"
      ["Price_100"]=>
      string(4) "4.25"
      ["Price_250"]=>
      string(4) "4.08"
      ["Price_500"]=>
      string(4) "3.96"
      ["Price_1000"]=>
      string(4) "3.89"
      ["Material"]=>
      string(8) "Finecell"
    }
    [4]=>
    array(9) {
      ["Code"]=>
      string(6) "JP6000"
      ["Brand Rate"]=>
      string(1) "F"
      ["Price_25"]=>
      string(4) "6.35"
      ["Price_50"]=>
      string(4) "6.03"
      ["Price_100"]=>
      string(4) "5.55"
      ["Price_250"]=>
      string(4) "5.33"
      ["Price_500"]=>
      string(4) "5.17"
      ["Price_1000"]=>
      string(4) "5.08"
      ["Material"]=>
      string(5) "Nappa"
    }
    [5]=>
    array(9) {
      ["Code"]=>
      string(6) "JP6000"
      ["Brand Rate"]=>
      string(1) "F"
      ["Price_25"]=>
      string(4) "7.62"
      ["Price_50"]=>
      string(4) "7.24"
      ["Price_100"]=>
      string(4) "6.66"
      ["Price_250"]=>
      string(3) "6.4"
      ["Price_500"]=>
      string(3) "6.2"
      ["Price_1000"]=>
      string(3) "6.1"
      ["Material"]=>
      string(8) "Richmond"
    }
  }
  ["JP6010"]=>
  array(6) {
    [0]=>
    array(9) {
      ["Code"]=>
      string(6) "JP6010"
      ["Brand Rate"]=>
      string(1) "F"
      ["Price_25"]=>
      string(4) "3.41"
      ["Price_50"]=>
      string(4) "3.24"
      ["Price_100"]=>
      string(4) "2.98"
      ["Price_250"]=>
      string(4) "2.86"
      ["Price_500"]=>
      string(4) "2.78"
      ["Price_1000"]=>
      string(4) "2.73"
      ["Material"]=>
      string(10) "Belluno PU"
    }
    [1]=>
    array(9) {
      ["Code"]=>
      string(6) "JP6010"
      ["Brand Rate"]=>
      string(1) "F"
      ["Price_25"]=>
      string(4) "3.41"
      ["Price_50"]=>
      string(4) "3.24"
      ["Price_100"]=>
      string(4) "2.98"
      ["Price_250"]=>
      string(4) "2.86"
      ["Price_500"]=>
      string(4) "2.78"
      ["Price_1000"]=>
      string(4) "2.73"
      ["Material"]=>
      string(9) "Torino PU"
    }
    [2]=>
    array(9) {
      ["Code"]=>
      string(6) "JP6010"
      ["Brand Rate"]=>
      string(3) "n/a"
      ["Price_25"]=>
      string(3) "n/a"
      ["Price_50"]=>
      string(3) "n/a"
      ["Price_100"]=>
      string(4) "4.77"
      ["Price_250"]=>
      string(4) "4.33"
      ["Price_500"]=>
      string(4) "4.13"
      ["Price_1000"]=>
      string(4) "3.93"
      ["Material"]=>
      string(11) "Full Colour"
    }
    [3]=>
    array(9) {
      ["Code"]=>
      string(6) "JP6010"
      ["Brand Rate"]=>
      string(1) "F"
      ["Price_25"]=>
      string(4) "5.41"
      ["Price_50"]=>
      string(4) "5.14"
      ["Price_100"]=>
      string(4) "4.74"
      ["Price_250"]=>
      string(4) "4.55"
      ["Price_500"]=>
      string(4) "4.41"
      ["Price_1000"]=>
      string(4) "4.33"
      ["Material"]=>
      string(8) "Finecell"
    }
    [4]=>
    array(9) {
      ["Code"]=>
      string(6) "JP6010"
      ["Brand Rate"]=>
      string(1) "F"
      ["Price_25"]=>
      string(3) "6.9"
      ["Price_50"]=>
      string(4) "6.56"
      ["Price_100"]=>
      string(4) "6.04"
      ["Price_250"]=>
      string(3) "5.8"
      ["Price_500"]=>
      string(4) "5.63"
      ["Price_1000"]=>
      string(4) "5.52"
      ["Material"]=>
      string(5) "Nappa"
    }
    [5]=>
    array(9) {
      ["Code"]=>
      string(6) "JP6010"
      ["Brand Rate"]=>
      string(1) "F"
      ["Price_25"]=>
      string(4) "8.28"
      ["Price_50"]=>
      string(4) "7.87"
      ["Price_100"]=>
      string(4) "7.25"
      ["Price_250"]=>
      string(4) "6.96"
      ["Price_500"]=>
      string(4) "6.76"
      ["Price_1000"]=>
      string(4) "6.62"
      ["Material"]=>
      string(8) "Richmond"
    }
  }
}

So what I need to do is further group this data and convert it into a JSON string like you see in the MySQL records using the information for each code.

The first entry for price_25 json array for JP6000 would be 2.92 and the second 2.92 - third being n/a

This also needs to loop through and update each mysql row accordingly based on the JP Code being the primary key.

I hope this all makes sense? very tricky!

Standard answer, do not store data in JSON format. It is meant for presentation. — ajreal, Sep 5 '11 at 14:06
Thanks for that helpful comment... unfortunately not everything works like magic so we have to find ways around to avoid saying 'NO' to the client, this is the only way we can deliver this solution to them. — Nathan Fitzgerald, Sep 5 '11 at 14:12

DaveRandom · Accepted Answer · 2011-09-05 14:41:37Z

 $rows = array();
 $format = array('brand_rate'=>'Brand Rate','price_25'=>'Price_25','price_50'=>'Price_50','price_100'=>'Price_100','price_250'=>'Price_250','price_500'=>'Price_500','price_1000'=>'Price_1000');

 // Build $rows array
 foreach ($groupedData as $jpCode => $jpData) {
   foreach ($jpData as $rowIndex => $rowData) {
     foreach ($format as $csvCol) {
       $rows[$jpCode][$csvCol][$rowIndex] = $rowData[$csvCol];
     }
   }
 }

 // Loop through $rows and INSERT them
 foreach ($rows as $jpCode => $row) {
   $cols = $vals = $duplicates = array();
   foreach ($format as $sqlCol => $csvCol) {
     $val = mysql_real_escape_string(json_encode($row[$csvCol]));
     $duplicates[] = "`$sqlCol` = '$val'";
     $cols[] = $sqlCol;
     $vals[] = $val;
   }
   $query = "INSERT INTO `dbname`.`tablename` (`jp_code`,`".implode('`,`',$cols)."`) VALUES ('$jpCode','".implode("','",$vals)."') ON DUPLICATE KEY UPDATE ".implode(", ",$duplicates);
   mysql_query($query);
 }

This makes a lot of assumptions, like you are using MySQL, you are using the old-style MySQL extension and not PDO, that your parsed data is stored in a variable called $groupedData, etc etc.

There will be considerable optimisation that can be performed on it, I hate to use that many foreach loops but this is just a quick example. You will probably want to add a unique index on jp_code (if there isn't one already) and add an ON DUPLICATE KEY UPDATE to the query.

I have not tested it so it may not 100% correct but I'm fairly sure it will give you a prod in the right direction at the very least.

EDIT

Note that this will put your fields into the database as JSON objects, not arrays, but they should (hopefully) still work in the same way as you expect them to at the JS side...

Also note I haven't done anything with the Material CSV field as it is missing from your DB schema.

I have also just added an ON DUPLICATE KEY UPDATE clause to the query code.

That has worked beautifully, thank you very much! – Nathan Fitzgerald Sep 5 '11 at 14:47 — Nathan Fitzgerald, Sep 5 '11 at 14:47

JSK NS · Answer 2 · 2012-10-11 12:02:28Z

You might want toconsider my class. It converts your CSV to MySQL Insert QUERY, Array, to JSON and to HTML table.

    /**
 * CSV_parser
 * 
 * @package   
 * @author Dave's Simple Project
 * @copyright MESMERiZE
 * @version 2012
 * @access public
 */
class CSV_parser
{
    private $source;
    private $array;
    private $length;
    private $delimiter;
    private $class;
    private $padding;
    private $border;
    private $id;
    private $width;

    /**
     * CSV_parser::__construct()
     * 
     * @param mixed $source
     * @param integer $length
     * @param string $delimiter
     * @return
     */
    public function __construct($source, $length = 8000, $delimiter = ',')
    {
        try
        {
            // Lets try to open the file and check if its readable else throw an error.
            if (!isset($source) || !is_readable($source))
            {
                throw new Exception('File Not Found!');
                return false;
            } else
            {
                //set the source file
                $this->source = $source;
                $this->length = $length;
                $this->delimiter = $delimiter;
                return true;
            }
        }
        catch (exception $e)
        {
            // Send an error message :)
            echo $e->getMessage();
        }
        parent::__construct();
    }
    /**
     * CSV_parser::toArray()
     * 
     * @return
     */
    public function toArray()
    {
        // 1. First open the source file
        $handler = fopen($this->source, 'r');
        // 2. Turn the CSV to array
        while (($data = fgetcsv($handler, $this->length, $this->delimiter)) !== false)
        {
            $a[] = $data;
        }
        // 3. Get the first index to be used as the key
        $h = $a[0];
        // 4. Lets remove the $h variable's empty values
        foreach ($h as $k => $v)
        {
            if ($v != '')
            {
                $headers[$k] = $v;
            }
        }
        // 4. Remove the first index and leave the others to be used as the value
        $a = array_slice($a, 1);
        // 5. Make an empty array
        $array = array();
        // 6. Lets loop the values
        foreach ($a as $k => $v)
        {
            $i = 0;
            // then loop the headers then for each headers lets get the values
            // from variable $a based on how many the headers are. So we increment.
            foreach ($headers as $key => $value)
            {
                $array[$k][$value] = $v[$i];
                $i++;
            }
        }
        return $array;
        fclose($handler);
    }
    /**
     * CSV_parser::toTable()
     * 
     * @param string $width
     * @param integer $border
     * @param integer $spacing
     * @param integer $padding
     * @param string $class
     * @param mixed $id
     * @return
     */
    public function toTable($width = '100%', $border = 1, $spacing = 0, $padding = 5,
        $class = 'mytable', $id = null)
    {
        $this->width = $width;
        $this->class = $class;
        $this->spacing = $spacing;
        $this->padding = $padding;
        $this->border = $border;
        $this->id = $id;

        $table = '<table width="' . $this->width . '" class="' . $this->class .
            '" cellspacing="' . $this->spacing . '" cellpadding="' . $this->padding .
            '" id="' . $this->id . '" border="' . $this->border . '">';
        // 1. Lets create some table headers
        $table .= '<thead><tr>';
        foreach ($this->toArray() as $key => $value)
        {
            $headers = $value;
        }
        $headers = array_keys($headers);
        foreach ($headers as $th)
        {
            $table .= '<th>' . $th . '</th>';
        }
        $table .= '</tr></thead>';
        // Lets create the table body
        $table .= '<tbody>';
        foreach ($this->toArray() as $key => $value)
        {
            $table .= '<tr>';
            foreach ($value as $val)
            {
                $table .= '<td>' . $val . '</td>';
            }
            $table .= '<tr>';

        }
        $table .= '</tbody>';
        $table .= '</table>';
        return $table;

    }
    /**
     * CSV_parser::toJSON()
     * 
     * @return
     */
    public function toJSON()
    {
        return json_encode($this->toArray());
    }
    public function toMYSQL($table_name = 'table_name')
    {
        $str = '';
        $the_array = $this->toArray();
        foreach($the_array as $array)
        {
            $k = implode(',',array_keys($array));
            $v = "'".implode("','",array_values($array))."'";
            $str .= "INSERT INTO $table_name($k) VALUES($v)\n";
        }
        return $str;
    }

}

Usage:

$data = new CSV_parser('C:\wamp\www/sample.csv');
echo '<code>';
echo $data->toJSON();
echo '</code>';
echo $data->toTable();
echo '<pre>';
    echo $data->toMYSQL('',TRUE);
echo '</pre>';
echo '<pre>';
    print_r($data->toArray());
echo '</pre>';

asked	2 years ago
viewed	523 times
active	1 year ago

current community

your communities

more stack exchange communities

CSV to PHP to JSON into MySQL

2 Answers 2

Your Answer

Not the answer you're looking for? Browse other questions tagged php mysql json csv or ask your own question.

Hot Network Questions

current community

your communities

more stack exchange communities

CSV to PHP to JSON into MySQL

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged php mysql json csv or ask your own question.

Related

Hot Network Questions