SQL query to export field using phpMyadmin

danio_d
Topic Author
Offline
Senior Member

12 years 3 months ago #34670 by danio_d

SQL query to export field using phpMyadmin was created by danio_d

Again, I need your help.
I need to export from the database (using phpMyAdmin) all e-mail fields (in my case it's number 15) where items are in a parent category number 8 (inside is aboute 90 subcategories)

I found in the FAQ section or forum counter entries, but unfortunately does not know how to convert.
Can I ask for your help?

Code:

global $globalcats;

$top_cat = 8; // = false;  // use false for all items

if ($top_cat) {
 $cats_list = implode(',',$globalcats[$top_cat]->descendantsarray);
 $join_cats =  ' JOIN #__flexicontent_cats_item_relations AS rel'."\n";
 $join_cats .= '  ON rel.itemid=i.id '."\n";
 $where_cats = ' AND rel.catid IN('.$cats_list.') '."\n";

 //print_r( count($globalcats[$top_cat]->descendantsarray) ); exit;
} else {
 $join_cats = '';
 $where_cats = '';
}

$sql = 'SELECT COUNT(DISTINCT i.id) AS items_total,'."\n"
 .'  t.name AS type_name, t.id AS type_id'."\n"
 .' FROM #__content AS i'."\n"
 .' JOIN #__flexicontent_items_ext AS ext'."\n"
 .'   ON i.id=ext.item_id'."\n"
 .' JOIN #__flexicontent_types AS t'."\n"
 .'   ON t.id=ext.type_id'."\n"
 .$join_cats
 .' WHERE 1'
 .$where_cats
 .' GROUP BY t.id ';

$db = JFactory::getDBO();
$db->setQuery($sql);

$type_stats = $db->loadObjectList('type_id');

if ($db->getErrorNum()) {
  echo "Error in query:".$sql;
  echo $db->getErrorMsg();
}

Please Log in or Create an account to join the conversation.

ggppdk
Offline
Administrator

12 years 3 months ago #34708 by ggppdk

Replied by ggppdk on topic SQL query to export field using phpMyadmin

Code:

global $globalcats;

    $top_cat = 8; // = false;  // use false for all items
    $field_id = 15;

    if ($top_cat) {
     $cats_list = implode(',',$globalcats[$top_cat]->descendantsarray);
     $join_cats =  ' JOIN #__flexicontent_cats_item_relations AS rel'."\n";
     $join_cats .= '  ON rel.itemid=v.item_id '."\n";
     $where_cats = ' AND rel.catid IN('.$cats_list.') '."\n";

     //print_r( count($globalcats[$top_cat]->descendantsarray) ); exit;
    } else {
     $join_cats = '';
     $where_cats = '';
    }

    $sql = 'SELECT v.*, i.title '."\n"
     .' FROM #__flexicontent_fields_item_relations AS v '."\n"
     .' JOIN #__content AS i '."\n"
     .'  ON i.id=v.item_id '."\n"
     .$join_cats
     .' WHERE 1'
     .$where_cats
     .' AND v.field_id='.$field_id
     ;

    $db = JFactory::getDBO();
    $db->setQuery($sql);

    $results = $db->loadObjectList();

    if ($db->getErrorNum()) {
      echo "Error in query:".$sql;
      echo $db->getErrorMsg();
    }
     
    $data = array();
    if ($results) foreach ($results as $r) {
    	// Every item may have many field values, add these to an array indexed by item id
    	// (and unserialize each value since it is multi-property)
    	$item_id = $r->id;
    	$fieldvals[$item_id][] = @ unserialize($r->value);
    	
    	// $r contains some data about the item (e.g. title), add them to an array indexed by item id
    	if ( !isset($items[$r->id]) ) {
    		$items[$r->id] = $r;
    	}
    }
    
    if ($data) foreach ($fieldvals as $itemid => $vals ) {
    	echo "Item Id:".$itemid." Title: ".$items[$itemid]->title ." ";
    	foreach ($vals as $v) echo " , ".$v['addr'];
    	echo "<br/>\n";
    }

This is the code that you need please note it may have syntax errors , because i did not test it, after you have test this (and maybe fixed it, you can post back here)

-- Flexicontent is Free but involves a big effort on our part.
Like the our support? (for a bug-free FC, despite having a long list of functions) Like the features? Like the ongoing development and future commitment to FLEXIcontent?
-- Add your voice to the FLEXIcontent JED listing with a 5-star...

Please Log in or Create an account to join the conversation.

danio_d
Topic Author
Offline
Senior Member

12 years 1 week ago #37506 by danio_d

Replied by danio_d on topic SQL query to export field using phpMyadmin

@ggppdk I need Your help.
This code work fine. There was some erros.

Code:

global $globalcats;

    $top_cat = 8; // = false;  // use false for all items
    $field_id = 15;

    if ($top_cat) {
     $cats_list = implode(',',$globalcats[$top_cat]->descendantsarray);
     $join_cats =  ' JOIN #__flexicontent_cats_item_relations AS rel'."\n";
     $join_cats .= '  ON rel.itemid=v.item_id '."\n";
     $where_cats = ' AND rel.catid IN('.$cats_list.') '."\n";

     //print_r( count($globalcats[$top_cat]->descendantsarray) ); exit;
    } else {
     $join_cats = '';
     $where_cats = '';
    }

    $sql = 'SELECT v.*, i.title '."\n"
     .' FROM #__flexicontent_fields_item_relations AS v '."\n"
     .' JOIN #__content AS i '."\n"
     .'  ON i.id=v.item_id '."\n"
     .$join_cats
     .' WHERE 1'
     .$where_cats
     .' AND v.field_id='.$field_id
	  .' ORDER BY i.id DESC'
     ;

    $db = JFactory::getDBO();
    $db->setQuery($sql);

    $results = $db->loadObjectList();

    if ($db->getErrorNum()) {
      echo "Error in query:".$sql;
      echo $db->getErrorMsg();
    }

    //$data = array();

    if ($results) foreach ($results as $r) {
       // Every item may have many field values, add these to an array indexed by item id
       // (and unserialize each value since it is multi-property)
       $item_id = $r->item_id;
       $fieldvals[$item_id][] = @ unserialize($r->value);

       // $r contains some data about the item (e.g. title), add them to an array indexed by item id
       if ( !isset($items[$r->item_id]) ) {
          $items[$r->item_id] = $r;
       }
	 }

   foreach ($fieldvals as $itemid => $vals ) {
       echo "Item Id:".$itemid." Title: ";
		 echo $items[$itemid]->title;
		 foreach ($vals as $v) echo ", ".$v['addr'];
		 echo "<br/>\n";

    }

I try to add to this code another 'if'...
if from county 24 then show

select value if field_id 21 = 24 but i don't no how to ask sql? :oops:

Please Log in or Create an account to join the conversation.

ggppdk
Offline
Administrator

12 years 1 week ago #37516 by ggppdk

Replied by ggppdk on topic SQL query to export field using phpMyadmin

danio_d wrote: @ggppdk I need Your help.
This code work fine. There was some erros.

errors?
i mean ... i said i did not test it ... so how could i fix the "errors" ?

and i also said that the above code is mostly correct but it , but may have syntax errors (and might as well have logical errors)

danio_d wrote: I try to add to this code another 'if'...
if from county 24 then show

select value if field_id 21 = 24 but i don't no how to ask sql? :oops:

Did you change variable:

$field_id = 15;

to e.g

$field_id = 24;

if you mean something else, i do not know what you mean, if you are not a developer, then consider using a flexiIMPORT tool from netassopro , if it suits your needs or seeking the help of freelancer

Regards

Please Log in or Create an account to join the conversation.