Topic: [How to] SOBI2 and importing data

[neo]

The SOBI2 tables are in the Joomla database/
phpMyAdmin is already installed with XAMPP

[anadantra]

Hi Steve,
Neo and  Trinity have been very clear, but at risk of saying too much, I will add my 2 cents.

Joomla is a content manager "database driven". What does it mean? that instead of having all the information in files or other "physical" form, it takes it on the fly from a database. Now what is a database? it is just an array of information with certain protocols of access.

What you probably have is a list of something... that is called a database too, and they are related, BUT the only way you are going to be able to upload the info to the existing database successfully is respecting the structure. The info of sobi alone has different tables, even if you manage to add your info to one table the "relation" between that info and some otehr parameter might be missing....

I will make it sour and short for you... try not to edit the tables in the database unless you know exactly what you are doing (and I am sorry but it looks you don't), you can mess up big time. The best way to add the info you have (I know how painful it can be, believe me) is one by one through the normal channels.

Cheers,
S.   

[tiki16]

Hi Anadantra,
I've converted an excel spread sheet with 4500 entries into a DB with phpmyadmin.  I want to use this db to update or replace the content in sobi2. There are only a few entries in the sob12 Db at the moment. This is why i was wondering if  i can view the db because i am unsure as to how it resides on the server. Do my fieldnames have to match up exactly with the fields created in my sobi2Db.
thanx

[anadantra]

Ok,
so what you have is a table...

I did a lil trip to the database and found these tables for sobi:

jos_sobi2_categories
jos_sobi2_cats_relations
jos_sobi2_cat_items_relations
jos_sobi2_config
jos_sobi2_fields
jos_sobi2_fields_data
jos_sobi2_item
jos_sobi2_language
jos_sobi2_plugins
jos_sobi2_plugins_tables

Those with glow are the ones that seem to be affected whenever you add a new item to the database. I saw a question regarding the upload of info, and Trinity said that teh component didn't support it but it was addressed in the forum before. I did a quick search and couldn't find it, maybe someone else will be able to help you with that...

Another option is to use some other component like database query to upload the info. In any case, you will have to study carefully the structure to make it work.

S.

[diro]

Hi Lufi,

if you use search-function you get an amount of entries discussing these.

Please use search first next time to avoid double entries concerning to the same facts an problems.

Thanks.

diro

[Lufi]

Ok i´ve already done that, but is a litle dificult. I told that would be great to get a module or addon to import and export just to map the fields into sobi.

My goal with it is to make a directory to use with GPS having fields, like latitude and longitude.

If anyone has this please tell me, would be great to import, export and to get a possibility of having a file for download , like google earth link.

[tiki16]

Hello all,
Looking at the fields within the table can the table fields be changed so that  they have customised names i.e. phone, address, city. I have looked at jos_sobi2_fields_data and the field column data_txt contains all entries including city, phone, name etc in the same column. can i create a field name that will contain all unique ID entries so that data will reside in the respective field name. This would be helpful for importing from another DB. Is this the default format sobi2 creates when you setup the field names in the configuration menu?
Any help appreciated

[soby]

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...

Hi,

I tried out the script. Created a db table named "private_schools" with fields "adress1, zipcode, ...etc." in the database and an "import.php" File with the mentioned code, uploaded it to the server and got a connection, so long it functions. But then I get an error:
"Connected successfully
Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in ..../import.php on line 16
Done!"

Line 16 in the script is:
while ($row = mysql_fetch_assoc($schooldata)) {

HAs anybody an idea whats going wrong there? Also in the next line (17) there is a row called "$row['school_name']".
This row "school_name" first has to be created in the transitional table "private_schools" (beside the other fields "adress1, zipcode, etc..") or on what does it refer?

If anybody tried this out, would be very useful to post his experience.

Thanks!

[dmoore]

I am having the exact same issue.  It has a probelm with this query that is refernced in line16:
$schooldata = mysql_query("SELECT * FROM private_schools", $link);

I am using php version 5.1.2 and MySQL version 5.  Has anyone been able to shed any light on this issue?

Derek

[dmoore]

Finally got it to work

Here is the my script:

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

// Get the records from transition table
$mydata = mysql_query("SELECT * FROM excel.Table", $link);

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

//Add record jos_sobi2_item table
while ($row = mysql_fetch_assoc($mydata)) {
   //insert a record into the item table
   $results = mysql_query("INSERT INTO jos_sobi2_item (title) VALUES ('".$row['COMPANY 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['STREET ADDRESS']."', '".$row_itemid[0]."')");
      $result2 = mysql_query("INSERT INTO jos_sobi2_fields_data (fieldid, data_txt, itemid) VALUES ('3', '".$row['CITY']."', '".$row_itemid[0]."')");
      $result3 = mysql_query("INSERT INTO jos_sobi2_fields_data (fieldid, data_txt, itemid) VALUES ('5', '".$row['STATE']."', '".$row_itemid[0]."')");
      $result4 = mysql_query("INSERT INTO jos_sobi2_fields_data (fieldid, data_txt, itemid) VALUES ('2', '".$row['ZIP']."', '".$row_itemid[0]."')");
      $result5 = mysql_query("INSERT INTO jos_sobi2_fields_data (fieldid, data_txt, itemid) VALUES ('10', '".$row['PHONE NUMBER']."', '".$row_itemid[0]."')");
      $result6 = mysql_query("INSERT INTO jos_sobi2_fields_data (fieldid, data_txt, itemid) VALUES ('11', '".$row['FAX NUMBER']."', '".$row_itemid[0]."')");
      $result7 = mysql_query("INSERT INTO jos_sobi2_fields_data (fieldid, data_txt, itemid) VALUES ('8', '".$row['WEB ADDRESS']."', '".$row_itemid[0]."')");
      $result8 = mysql_query("INSERT INTO jos_sobi2_fields_data (fieldid, data_txt, itemid) VALUES ('14', '".$row['LAST NAME']."', '".$row_itemid[0]."')");
      $result9 = mysql_query("INSERT INTO jos_sobi2_fields_data (fieldid, data_txt, itemid) VALUES ('9', '".$row['FIRST NAME']."', '".$row_itemid[0]."')");
      $result10 = mysql_query("INSERT INTO jos_sobi2_fields_data (fieldid, data_txt, itemid) VALUES ('15', '".$row['CONTACT TITLE']."', '".$row_itemid[0]."')");
      $result11 = mysql_query("INSERT INTO jos_sobi2_fields_data (fieldid, data_txt, itemid) VALUES ('13', '".$row['PRIMARY BUSINESS DESCRIPTION']."', '".$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='2050-12-12 00:00:00', checked_out='0', owner='62', ip='127.0.0.1'");

     
}

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

Note that I specified the database and table that I was importing from:
$mydata = mysql_query("SELECT * FROM excel.Table", $link);

Also, make sure you specify the target db here:
mysql_select_db("dbinfo", $link);

Lastly, pay attention to the values you import you data to.  But these can be changed within the config menu.

Derek