It's not that hard. First I'd wrap that recursive stuff by letting it behave like a RecursiveIterator
:
class RecursiveCategoryIterator implements RecursiveIterator {
const ID_FIELD = 'term_id';
const PARENT_FIELD = 'parent';
private $_data;
private $_root;
private $_position = 0;
public function __construct(array $data, $root_id = 0) {
$this->_data = $data;
$this->_root = $root_id;
}
public function valid() {
return isset($this->_data[$this->_root][$this->_position]);
}
public function hasChildren() {
$subid = $this->_data[$this->_root][$this->_position][self::ID_FIELD];
return isset($this->_data[$subid])
&& is_array($this->_data[$subid]);
}
public function next() {
$this->_position++;
}
public function current() {
return $this->_data[$this->_root][$this->_position];
}
public function getChildren() {
return new self($this->_data,
$this->_data[$this->_root][$this->_position][self::ID_FIELD]);
}
public function rewind() {
$this->_position = 0;
}
public function key() {
return $this->_position;
}
public static function createFromResult($result) {
$menu_array = array();
while($row = mysql_fetch_assoc($result)) {
$menu_array[$row[self::PARENT_FIELD]][] = $row;
}
return new self($menu_array);
}
}
Now why would I do that? First, because you can re-use id for displaying the tree, or do other stuff with it like import it in your own table. Second, if you have to test your code, you can just put in some other RecursiveIterator
as a mock (for example a RecursiveArrayIterator
).
Now the second part, the actual import of the word-press data:
// your original query
$sql="SELECT a.term_id,a.description,a.parent,a.count,b.name,b.slug
FROM wp_term_taxonomy a INNER JOIN wp_terms b WHERE a.term_id=b.term_id
AND a.taxonomy='category';
";
$result = mysql_query($sql, $dbh);
// always test for failure
if($result === false) {
die("query failed: ". mysql_error());
}
// create the iterator from the result set
$wpterms = RecursiveCategoryIterator::createFromResult($result);
// and import it.
insert_it($wpterms, 0);
// the function which does all the dirty work.
function insert_it($iterator, $parent_id = 0) {
foreach($iterator as $row) {
// insert the row, just edit the query, and don't forget
// to escape the values. if you have an insert function,
// use it by all means
$qry = 'INSERT INTO my_table (myparent, myname, ...)'
. ' VALUES (\'' . mysql_real_escape_string($parent_id)
. '\', \'' . mysql_real_escape_string($row['name']) . '\', ....)';
$status = mysql_query($qry);
if($status === false) {
// insert failed - rollback and abort
die("hard: " . mysql_error());
}
// you need to pass the id of the new row
// so the "child rows" have their respective parent
$cid = mysql_insert_id();
// insert the children too
if($iterator->hasChildren()) {
insert_it($iterator->getChildren(), $cid);
}
}
}