Thread

I’ve just imported all my book data from a database on my PC into an EE weblog.  On my PC all the book were assigned to one or more genre and I’ve imported this into a custom field (called “genre”!) and the data is held in the following format :

Crime; Fiction
Bigraphy; History, Non-Fiction

I’ve created a new group of Categories for the weblog and used the same names as those held in the genre field.  The million dollar question is there an easy/quick way to use the genre data to assign the books to categories?

Hm- I’ve done it looping through some php.  Let’s see if I can remember- you’re custom field that current holds what will be the categories is just a comma delimited list?

It’s delimited by semi-colons “;”  Some books have only one genre, some have several.

CSVGrab allows you to specify a category_id during the import process. However, if you already have your content in there, there is no automagic way of assigning those entries to a particular category.

You could do a custom query to return content based on the value of the genre field. Not as easy as categories, but it would work.

The other option is to write code to iterate through the new entries, read the value of genre, find it’s match and write out a new row to exp_category_posts.

Damn, I wish I’d realised that before.  It’s not very clear on what documentation there is for it.  If it will work with multiple categories it might be worth me deleting the data I have now and importing it again.

CSVGrab will work with single categories. I haven’t tried with multiples. The thing is, you’d have to import your items for each category, instead of doing them all at once.

Either method is going to take time.

Hm- I’d try this real quick.  It won’t insert any data, but if it works, think it would be pretty easy to do so.  Go create a new template, call it ‘test’- in preferences make sure php parsing is turned on.  Paste this, save- let’s see if it gives us any output.  NOTE- where I have field_id_x?  You need to change x to the id number of the field that holds your genre list.

<?php

global $DB;

$cat_array = array("Crime" => 2, "Fiction" => 7, "Bigraphy" => 9, "History" => 14, "Non-Fiction" => 15);

$sql = "SELECT field_id_x, entry_id FROM exp_weblog_data WHERE field_id_x != ''";
$query = $DB->query($sql);

foreach($query->result as $row)
{
    $barray = explode(';', $row['field_id_x']);
    foreach ($barray as $val)
    {
        $entry_data[$val] = $row['entry_id'];  
    }

}

echo '<pre>';
print_r($entry_data);

?>

x-fingers, it’s untested.  But it won’t bork anything.

This is the output from the template :

Array
(
  [Genre] => 813
  [Fantasy] => 1335
  [ Fiction] => 1335
  [Fiction] => 1329
  [ Thriller] => 865
  [Autobiography] => 1331
  [ Non-Fiction] => 1331
  [Non-Fiction] => 1298
  [ Politics] => 1297
  [Biography] => 1309
  [Crime] => 1333
  [ Biography] => 955
  [ Scientific] => 1230
  [ Science Fiction] => 1275
  [Technical] => 1067
  [History] => 1328
  [ History] => 1295
  [ Travel] => 1019
  [Food] => 908
  [ Health] => 908
  [Language] => 1316
  [ War] => 1188
  [ Historical] => 1260
  [ Reference] => 1298
  [Health] => 1245
  [ Religion] => 1174
)

Not really a ton of data there, but I think we’re getting close.  OK- try it with the below- edit the field_id_x bit.  We still aren’t actually inserting anything into the database yet- but if it works, this should show us the exact inserts that would happen once we add that last bit in.  Take a look- see if they look right to you.

<?php

global $DB;

$csql = "SELECT cat_name, cat_id FROM exp_categories";
$cquery = $DB->query($csql);
foreach($cquery->result as $row)
{
$cat_array[$row['cat_name']] = $row['cat_id'];
}



$sql = "SELECT field_id_x, entry_id FROM exp_weblog_data WHERE field_id_x != ''";
$query = $DB->query($sql);

foreach($query->result as $row)
{
    $barray = explode(';', $row['field_id_x']);
    foreach ($barray as $val)
    {
        $entry_data[trim($val)] = $row['entry_id'];  
    }

}

foreach($entry_data as $key => $val)
{
$data = array('cat_id' => $cat_array[$key], 'entry_id' => $val);
$sql = $DB->insert_string('exp_category_posts', $data);
echo $sql.'
';

}

?>

This is the output :

Notice: Undefined index: Genre in /home/tripewri/public_html/smg/core/core.functions.php(635) : eval()‘d code on line 29
INSERT INTO `exp_category_posts` (`cat_id`, `entry_id`) VALUES (’‘, ‘813’)
INSERT INTO `exp_category_posts` (`cat_id`, `entry_id`) VALUES (‘17’, ‘1335’)
INSERT INTO `exp_category_posts` (`cat_id`, `entry_id`) VALUES (‘14’, ‘1335’)

Notice: Undefined index: Thriller in /home/tripewri/public_html/smg/core/core.functions.php(635) : eval()‘d code on line 29
INSERT INTO `exp_category_posts` (`cat_id`, `entry_id`) VALUES (’‘, ‘865’)
INSERT INTO `exp_category_posts` (`cat_id`, `entry_id`) VALUES (‘21’, ‘1331’)
INSERT INTO `exp_category_posts` (`cat_id`, `entry_id`) VALUES (‘20’, ‘1331’)
INSERT INTO `exp_category_posts` (`cat_id`, `entry_id`) VALUES (‘28’, ‘1297’)
INSERT INTO `exp_category_posts` (`cat_id`, `entry_id`) VALUES (‘22’, ‘1309’)
INSERT INTO `exp_category_posts` (`cat_id`, `entry_id`) VALUES (‘23’, ‘1333’)

Notice: Undefined index: Scientific in /home/tripewri/public_html/smg/core/core.functions.php(635) : eval()‘d code on line 29
INSERT INTO `exp_category_posts` (`cat_id`, `entry_id`) VALUES (’‘, ‘1230’)
INSERT INTO `exp_category_posts` (`cat_id`, `entry_id`) VALUES (‘18’, ‘1275’)
INSERT INTO `exp_category_posts` (`cat_id`, `entry_id`) VALUES (‘31’, ‘1067’)
INSERT INTO `exp_category_posts` (`cat_id`, `entry_id`) VALUES (‘26’, ‘1328’)
INSERT INTO `exp_category_posts` (`cat_id`, `entry_id`) VALUES (‘32’, ‘1019’)
INSERT INTO `exp_category_posts` (`cat_id`, `entry_id`) VALUES (‘24’, ‘908’)
INSERT INTO `exp_category_posts` (`cat_id`, `entry_id`) VALUES (‘25’, ‘1245’)
INSERT INTO `exp_category_posts` (`cat_id`, `entry_id`) VALUES (‘27’, ‘1316’)
INSERT INTO `exp_category_posts` (`cat_id`, `entry_id`) VALUES (‘19’, ‘1188’)
INSERT INTO `exp_category_posts` (`cat_id`, `entry_id`) VALUES (‘16’, ‘1260’)
INSERT INTO `exp_category_posts` (`cat_id`, `entry_id`) VALUES (‘29’, ‘1298’)
INSERT INTO `exp_category_posts` (`cat_id`, `entry_id`) VALUES (‘36’, ‘1174’)

OK- double check your categories.  Do you have cats named Genre, Thriller and Scientific?  We could get rid of the errors, but from my understanding, each name in the custom field should have a corresponding category.  Those errors are happening because there’s a name in the custom field that doesn’t match up to one of the existing categories.  (Could be a spelling isse, er, maybe even a case issue.  I’m not dead sure.)  Before we go forward, we should figure out why there’s no match.  (Or I can just have it skip those.)

But yea- clear that up and we should be ready to import.  Still- for 500 books, that’s not really a whole lot that have been categorized.  Least, not based on the output.  Does it look ok to you, or does it seem like there should be a lot more books being categorized?

There is no cat called Genre.  I’d forgotten to make one for Thriller and had put Science instead of Scientific o_O I’ll run it again.  One thing that just occurred to me is that the delimiter is a ; followed by a space “; “, do I need to reflect this in the script?

This is what I get :

Notice: Undefined index: Genre in /home/tripewri/public_html/smg/core/core.functions.php(635) : eval()‘d code on line 29
INSERT INTO `exp_category_posts` (`cat_id`, `entry_id`) VALUES (’‘, ‘813’)
INSERT INTO `exp_category_posts` (`cat_id`, `entry_id`) VALUES (‘17’, ‘1335’)
INSERT INTO `exp_category_posts` (`cat_id`, `entry_id`) VALUES (‘14’, ‘1335’)
INSERT INTO `exp_category_posts` (`cat_id`, `entry_id`) VALUES (‘38’, ‘865’)
INSERT INTO `exp_category_posts` (`cat_id`, `entry_id`) VALUES (‘21’, ‘1331’)
INSERT INTO `exp_category_posts` (`cat_id`, `entry_id`) VALUES (‘20’, ‘1331’)
INSERT INTO `exp_category_posts` (`cat_id`, `entry_id`) VALUES (‘28’, ‘1297’)
INSERT INTO `exp_category_posts` (`cat_id`, `entry_id`) VALUES (‘22’, ‘1309’)
INSERT INTO `exp_category_posts` (`cat_id`, `entry_id`) VALUES (‘23’, ‘1333’)
INSERT INTO `exp_category_posts` (`cat_id`, `entry_id`) VALUES (‘30’, ‘1230’)
INSERT INTO `exp_category_posts` (`cat_id`, `entry_id`) VALUES (‘18’, ‘1275’)
INSERT INTO `exp_category_posts` (`cat_id`, `entry_id`) VALUES (‘31’, ‘1067’)
INSERT INTO `exp_category_posts` (`cat_id`, `entry_id`) VALUES (‘26’, ‘1328’)
INSERT INTO `exp_category_posts` (`cat_id`, `entry_id`) VALUES (‘32’, ‘1019’)
INSERT INTO `exp_category_posts` (`cat_id`, `entry_id`) VALUES (‘24’, ‘908’)
INSERT INTO `exp_category_posts` (`cat_id`, `entry_id`) VALUES (‘25’, ‘1245’)
INSERT INTO `exp_category_posts` (`cat_id`, `entry_id`) VALUES (‘27’, ‘1316’)
INSERT INTO `exp_category_posts` (`cat_id`, `entry_id`) VALUES (‘19’, ‘1188’)
INSERT INTO `exp_category_posts` (`cat_id`, `entry_id`) VALUES (‘16’, ‘1260’)
INSERT INTO `exp_category_posts` (`cat_id`, `entry_id`) VALUES (‘29’, ‘1298’)
INSERT INTO `exp_category_posts` (`cat_id`, `entry_id`) VALUES (‘36’, ‘1174’)

I don’t understand why this is returning so few rows, I’ve knocked up a basic template at http://www.tripewriting.net/index.php/bookblog/ and as you can see, every book has at least one category in the “Genre” field.

Yea- I was being an idiot.  Let’s see what this gets- edit the field_id_x bit again:

<?php

global $DB;

$csql = "SELECT cat_name, cat_id FROM exp_categories";
$cquery = $DB->query($csql);
foreach($cquery->result as $row)
{
$cat_array[$row['cat_name']] = $row['cat_id'];
}



$sql = "SELECT field_id_x, entry_id FROM exp_weblog_data WHERE field_id_x != ''";
$query = $DB->query($sql);

foreach($query->result as $row)
{

        $entry_data[$row['entry_id']] = $row['field_id_x'];  

}



foreach($entry_data as $key => $val)
{
$cats = explode(';', $val);
    foreach($cats as $cat_val)
    {
        $data = array('cat_id' => $cat_array[trim($cat_val)], 'entry_id' => $key);
        $sql = $DB->insert_string('exp_category_posts', $data);
        echo $sql.'
';        
    }



}

?>