Print

Please login or register.
1 Hour 1 Day 1 Week 1 Month Forever
Login with username, password and session length

[WebJIVE]

I know this is a question that I had and once I rumaged around the site, I realized I wasn't alone.  After messing around with other components, I soon realized that I wasn't going to find component to meet the business objective for my customers directory needs (great component by the way).  So, I hacked up a script to import records from a temp DB for my bulk importing.  I'll share this with one caveat, I'm in the middle of a project and don't have time to answer questions about it.  This script is for people that know PHP and SQL.  If your like me and just a PHP/SQL hack, then you can make something of it.

One thing I have noticed, is when you get around 20k item entries (which is 20k x 14 records in the fieds data = 280k records), things get a bit slow.  If this customer wants to do more, then I may have to do a component from scratch that has all the records in a single table that can be called with a couple of lines of SQL.

Happy computing

Eric

Import Script
=========

<?php
$link = mysql_connect('server', 'db', 'password');
if (!$link) {
   die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully<br>';
mysql_select_db("db", $link);

// Get the records from transition table
$schooldata = mysql_query("SELECT * FROM private_schools", $link);

//set the counter for the last query
$i=0;

//Add record jos_sobi2_item table
while ($row = mysql_fetch_assoc($schooldata)) {
   //insert a record into the item table
   $results = mysql_query("INSERT INTO jos_sobi2_item (title) VALUES ('".$row['school_name']."')", $link);
   
   //get the last item itemid
   $itemid = mysql_query("SELECT MAX(itemid) FROM jos_sobi2_item", $link);
   $row_itemid = mysql_fetch_row($itemid);

   //now build a record for the fields data

      $result1 = mysql_query("INSERT INTO jos_sobi2_fields_data (fieldid, data_txt, itemid) VALUES ('1', '".$row['address1']."', '".$row_itemid[0]."')");
      $result2 = mysql_query("INSERT INTO jos_sobi2_fields_data (fieldid, data_txt, itemid) VALUES ('2', '".$row['zipcode']."', '".$row_itemid[0]."')");
      $result3 = mysql_query("INSERT INTO jos_sobi2_fields_data (fieldid, data_txt, itemid) VALUES ('3', '".$row['city']."', '".$row_itemid[0]."')");
      $result4 = mysql_query("INSERT INTO jos_sobi2_fields_data (fieldid, data_txt, itemid) VALUES ('4', '".$row['county']."', '".$row_itemid[0]."')");
      $result5 = mysql_query("INSERT INTO jos_sobi2_fields_data (fieldid, data_txt, itemid) VALUES ('5', '".$row['state']."', '".$row_itemid[0]."')");
      $result6 = mysql_query("INSERT INTO jos_sobi2_fields_data (fieldid, data_txt, itemid) VALUES ('7', '".$row['school_email']."', '".$row_itemid[0]."')");
      $result7 = mysql_query("INSERT INTO jos_sobi2_fields_data (fieldid, data_txt, itemid) VALUES ('8', '".$row['school_url']."', '".$row_itemid[0]."')");
      $result8 = mysql_query("INSERT INTO jos_sobi2_fields_data (fieldid, data_txt, itemid) VALUES ('9', '".$row['contact_fn']."', '".$row_itemid[0]."')");
      $result9 = mysql_query("INSERT INTO jos_sobi2_fields_data (fieldid, data_txt, itemid) VALUES ('10', '".$row['phone']."', '".$row_itemid[0]."')");
      $result10 = mysql_query("INSERT INTO jos_sobi2_fields_data (fieldid, data_txt, itemid) VALUES ('11', '".$row['fax']."', '".$row_itemid[0]."')");
      $result11 = mysql_query("INSERT INTO jos_sobi2_fields_data (fieldid, data_txt, itemid) VALUES ('14', '".$row['address2']."', '".$row_itemid[0]."')");
      $result12 = mysql_query("INSERT INTO jos_sobi2_fields_data (fieldid, data_txt, itemid) VALUES ('15', '".$row['principal_email']."', '".$row_itemid[0]."')");
      $result13 = mysql_query("INSERT INTO jos_sobi2_fields_data (fieldid, data_txt, itemid) VALUES ('16', '".$row['contact_ln']."', '".$row_itemid[0]."')");
      
      // update the cat_items_relations table
      $result14 = mysql_query("INSERT INTO jos_sobi2_cat_items_relations (catid, itemid, ordering) VALUES ('3', '".$row_itemid[0]."', '".$i."')");
      $i++;

      //now update so the items are published and data is good      
      $result15 = mysql_query("UPDATE jos_sobi2_item SET published='1', confirm='0', approved='1', archived='0', publish_up=NOW(), publish_down='3000-12-12 00:00:00', checked_out='0', owner='62', ip='127.0.0.1'");

      
}

echo "Done!";
mysql_close($link);
?>

[3dmaximus]

Hello,

Would like to importing EXCEL data into SOBI2
as there are many names we want to put in.

is there a way to do this ?  does SOBI2 have an import populate option ?

Best Regards,
3dm

[inspired]

Thanks for this script
Has anyone tried this script since it was posted here? What was your result?

Regards,

Jonathan

[tschesch]

Hi

Import it direct with phpMyAdmin in your database.
Add txt-or csv-file.

c y

[lenamtl]

Hi tschesch,

could you give us an example.

thanks

[Wicked]

An interesting analogy indeed and very useful.

does the new Sobi2 Rc 2.5 use multiple tables or just one as prescripbed by the author of this post?

seems to be some good advice concerning large Directories.

I would be interested to find out what the full capabilities of Sobi2 is before becomming bloated as is described above 280,000 records seems like a descent sized database but as is also mentioned this number divided by the number of fields decreases the number of actual listings considerably.

I also ponder the description txt and what effect this must have on the directory after a considerable amount of time.

Regards

Wicks
 

[inspired]

Hi

Import it direct with phpMyAdmin in your database.
Add txt-or csv-file.

c y

How do you propose doing that?
I've taken a look at it doesn't seem like a very easy option.

[tibi]

I'm not up to speed on script - Where would you enter the above script and how would you link to import the database file? Anybody have a suggestion.

[bill.galloway]

I am very interested in research in this direction.  I will post if I make any progress; I hope anyone with any success at this task will post here too!

Bill Galloway
www.mybiosphere.ca

[Wicked]

Import Script
=========

<?php
$link = mysql_connect('server', 'db', 'password');
if (!$link) {
   die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully<br>';
mysql_select_db("db", $link);

// Get the records from transition table
$schooldata = mysql_query("SELECT * FROM private_schools", $link);

//set the counter for the last query
$i=0;

//Add record jos_sobi2_item table
while ($row = mysql_fetch_assoc($schooldata)) {
   //insert a record into the item table
   $results = mysql_query("INSERT INTO jos_sobi2_item (title) VALUES ('".$row['school_name']."')", $link);
   
   //get the last item itemid
   $itemid = mysql_query("SELECT MAX(itemid) FROM jos_sobi2_item", $link);
   $row_itemid = mysql_fetch_row($itemid);

   //now build a record for the fields data

      $result1 = mysql_query("INSERT INTO jos_sobi2_fields_data (fieldid, data_txt, itemid) VALUES ('1', '".$row['address1']."', '".$row_itemid[0]."')");
      $result2 = mysql_query("INSERT INTO jos_sobi2_fields_data (fieldid, data_txt, itemid) VALUES ('2', '".$row['zipcode']."', '".$row_itemid[0]."')");
      $result3 = mysql_query("INSERT INTO jos_sobi2_fields_data (fieldid, data_txt, itemid) VALUES ('3', '".$row['city']."', '".$row_itemid[0]."')");
      $result4 = mysql_query("INSERT INTO jos_sobi2_fields_data (fieldid, data_txt, itemid) VALUES ('4', '".$row['county']."', '".$row_itemid[0]."')");
      $result5 = mysql_query("INSERT INTO jos_sobi2_fields_data (fieldid, data_txt, itemid) VALUES ('5', '".$row['state']."', '".$row_itemid[0]."')");
      $result6 = mysql_query("INSERT INTO jos_sobi2_fields_data (fieldid, data_txt, itemid) VALUES ('7', '".$row['school_email']."', '".$row_itemid[0]."')");
      $result7 = mysql_query("INSERT INTO jos_sobi2_fields_data (fieldid, data_txt, itemid) VALUES ('8', '".$row['school_url']."', '".$row_itemid[0]."')");
      $result8 = mysql_query("INSERT INTO jos_sobi2_fields_data (fieldid, data_txt, itemid) VALUES ('9', '".$row['contact_fn']."', '".$row_itemid[0]."')");
      $result9 = mysql_query("INSERT INTO jos_sobi2_fields_data (fieldid, data_txt, itemid) VALUES ('10', '".$row['phone']."', '".$row_itemid[0]."')");
      $result10 = mysql_query("INSERT INTO jos_sobi2_fields_data (fieldid, data_txt, itemid) VALUES ('11', '".$row['fax']."', '".$row_itemid[0]."')");
      $result11 = mysql_query("INSERT INTO jos_sobi2_fields_data (fieldid, data_txt, itemid) VALUES ('14', '".$row['address2']."', '".$row_itemid[0]."')");
      $result12 = mysql_query("INSERT INTO jos_sobi2_fields_data (fieldid, data_txt, itemid) VALUES ('15', '".$row['principal_email']."', '".$row_itemid[0]."')");
      $result13 = mysql_query("INSERT INTO jos_sobi2_fields_data (fieldid, data_txt, itemid) VALUES ('16', '".$row['contact_ln']."', '".$row_itemid[0]."')");
     
      // update the cat_items_relations table
      $result14 = mysql_query("INSERT INTO jos_sobi2_cat_items_relations (catid, itemid, ordering) VALUES ('3', '".$row_itemid[0]."', '".$i."')");
      $i++;

      //now update so the items are published and data is good     
      $result15 = mysql_query("UPDATE jos_sobi2_item SET published='1', confirm='0', approved='1', archived='0', publish_up=NOW(), publish_down='3000-12-12 00:00:00', checked_out='0', owner='62', ip='127.0.0.1'");

     
}

echo "Done!";
mysql_close($link);
?>

Quick Analysis of Script

Database Name: private_schools

fields:

address1
zipcode
city
country
state
school_email
school_url
contact_fn
phone
fax
address2
principal_email
contact_ln

I think that Basically you would set up a seperate Mysql DB called 'Private_schools' with the above Data Fields
then this script will import the data into the sobi DB.

Nice work..

might want to change publish_down='3000-12-12 00:00:00' to something a little closer to the present 

still a bit worried about the 280k caper.

Wks...