I have a Excel file with content like the showed in the image:
As you may see there is basically three columns (could be four or more) which represents categories and subcategories. I need to read this Excel file for then write some SQL sentences like for example:
INSERT INTO `category` (name, parent, status, modified) VALUES('Electronica', NULL, 1, NOW());
INSERT INTO `category` (name, parent, status, modified) VALUES('Celulares y Telefonos', 1, 1, NOW());
INSERT INTO `category` (name, parent, status, modified) VALUES('Accesorios para celulares', 2, 1, NOW());
I know how to read the file using PHPExcel_IO but don't know how to get the parent > childrens relationship in order to build the proper SQL sentences, any help or advice?
UPDATE
This is what I have done so far. First here is the complete source if yours have any problems please feel free to contact me in order to send the file (I do this because the Array is to large and services like pastie.org doesn't allow to publish it and here will ugly the post).
I created this function to read the array data:
function print_r_reverse($in) {
$lines = explode("\n", trim($in));
if (trim($lines[0]) != 'Array') {
return $in;
} else {
if (preg_match("/(\s{5,})\(/", $lines[1], $match)) {
$spaces = $match[1];
$spaces_length = strlen($spaces);
$lines_total = count($lines);
for ($i = 0; $i < $lines_total; $i++) {
if (substr($lines[$i], 0, $spaces_length) == $spaces) {
$lines[$i] = substr($lines[$i], $spaces_length);
}
}
}
array_shift($lines); // Array
array_shift($lines); // (
array_pop($lines); // )
$in = implode("\n", $lines);
preg_match_all("/^\s{4}\[(.+?)\] \=\> /m", $in, $matches, PREG_OFFSET_CAPTURE | PREG_SET_ORDER);
$pos = array();
$previous_key = '';
$in_length = strlen($in);
foreach ($matches as $match) {
$key = $match[1][0];
$start = $match[0][1] + strlen($match[0][0]);
$pos[$key] = array($start, $in_length);
if ($previous_key != '')
$pos[$previous_key][1] = $match[0][1] - 1;
$previous_key = $key;
}
$ret = array();
foreach ($pos as $key => $where) {
$ret[$key] = print_r_reverse(substr($in, $where[0], $where[1] - $where[0]));
}
return $ret;
}
}
And this is the code I have created to read the array values and write the SQL sentences:
$result = print_r_reverse($arrayData);
class RPMQGen {
var $array_to = "";
var $count = 1;
var $last_count_parent = -1;
var $t_name = "";
function RPMQGen($tableName, $array) {
$this->t_name = $tableName;
$this->array_to = $array;
}
function walk_children($value) {
$query = "";
$key_result = 0;
$index_count = 0;
foreach ($value as $key => $val)
if (strlen($val) > 1) {
$index_count++;
$key_result = $key;
};
if ($index_count > 1) {
foreach ($value as $key => $val) {
if (strlen($val) > 1) {
$query = $query . "INSERT INTO `" . $this->t_name . "` (`id`, `name`, `parent`, `deletedAt`) VALUES (" . $this->count . ",`" . $val . "`, " . ($key + 1) . ", 1, NULL); <br>";
$this->count++;
}
}
$this->last_count_parent = $this->count;
} else
if ($index_count == 1) {
$query = $query . "INSERT INTO `" . $this->t_name . "` (`id`, `name`, `parent`, `deletedAt`) VALUES (" . $this->count . ",`" . ($value[$key_result]) . "`, " . ($this->last_count_parent - 1) . ", 1, NULL); <br>";
$this->count++;
}
return $query;
}
function get_queries() {
$this->count = 2;
$query = "INSERT INTO `" . $this->t_name . "` (`id`, `name`, `parent`, `deletedAt`) VALUES (1,`root`, NULL, NULL); <br>";
foreach ($this->array_to as $key => $value) {
$query = $query . "INSERT INTO `" . $this->t_name . "` (`id`, `name`, `parent`, `deletedAt`) VALUES (" . $this->count . ",`" . $key . "`, 0, NULL); <br>";
$this->count++;
foreach ($value as $key => $value2) {
$query = $query . $this->walk_children($value2);
}
}
print($query);
}
}
$qgen = new RPMQGen('category', $result);
$qgen->get_queries();
But parent > children relations aren't right and I can't find the cause
