What's the best way to populate an associative MYSQL table?

zwelldesigns · January 2016

I hate to keep asking new questions, but I've run into another issue -- hopefully the last one for a while. (I found these posts helpful in researching it: http://forum.getfuelcms.com/discussion/1587/add-multiple-entries-in-another-table-from-a-module-template-form/p1 and http://forum.getfuelcms.com/discussion/584/save-related-data-to-another-table/p1)

Basically, I want to know what the best way is to make a link between an SQL table and a related table.

I've made a profile module on the back-end. The form allows you to input some specific details like:
-name
-slug
-title
-location
-bio
-expertise
-phone number

This is all stored in the "main" table, called attorneys, and this unique info is associated with an ID unique to each attorney (nothing special).

The same module has several repeatable form fields. Because I want to keep my SQL tables such that each column has only ONE piece of data, I am storing these repeatable fields in another table called attorneys_biodata:
-education
-accolades
-faculty positions
-admissions

The attorneys_biodata table has 4 columns:
-id (unique to the entry)
-attorney_id (= to the attorney's unique ID from the attorneys table)
-biodata (=the name of the form field)
-biodata_value (=the user's input in the form field)

I actually managed to make this work, I just have one issue. Here is the relevant code for attorneys_model.php. Of interest is the on_after_save() function:

 function on_after_save($values){ 
		parent::on_after_save($values); 
		if(!empty($_POST['biodata']) && !empty($_POST['id'])){
			$CI =& get_instance();
			$CI->load->model('attorneys_biodata_model');
			$biodata = array();
			foreach(array_filter($_POST['biodata']) as $k=>$bd){
				if(!empty($bd)){
					if(is_array($bd)){
						foreach(array_filter($bd) as $bd_inner){
							if(!empty(array_values($bd_inner)[0])){ 
								$biodata[] = array(
												'attorney_id' => $_POST['id'], 
												'biodata' => $k,
												'biodata_value' => array_values($bd_inner)[0]
											);
								$this->db->where(array('attorney_id' => $values['id'], 'biodata' => $k));
								$this->db->delete('attorneys_biodata'); 
							}
						}
					}
				//	$b->biodata = $k; 
				//	$b->biodata_value = $bd['practice_areas']; 
				//	$b->save();
				}
			}
			
			$this->db->insert_batch('attorneys_biodata', $biodata); 
			
		} 
		return $values; 
	}

The issue is with my DELETE statement. For instance, let's say I'm inserting two rows with biodata = EDUCATION. These will be inserted fine. If I then insert 1 row with biodata = EDUCATION, the previous two rows will be deleted.

I know this is because in my WHERE statement, I need to add an additional condition specifying the unique ID of the biodata row, and then check if the $_POST'ed ID = the existing biodata's ID.

I don't know how to access the ID column of attorneys_biodata and then pass that value through the form so it is accessible in on_after_save(). I tried to use the $foreign_keys variable to do this, but I don't know if that's right...

If the issue doesn't make sense, I can re-explain or clarify. Here's a diagram of my form and the data routes:

Also -- is this the right way I should be approaching what I want to do? Or am I unnecessarily complicating things?

admin · January 2016

Do you want to just delete all the bio data first for the attorney before you begin the loop? Also you could add a hidden id field to your repeatable 'biodata' form field. What does your form_fields look like?

zwelldesigns · January 2016

[see below]

zwelldesigns · January 2016

No, ideally I would just update existing info and insert if not there.
In my form_fields() function, how do I access the id of the currently active record? I tried uri_segment, but it didn't work. I also tried this:

$foreign_id = $this->fuel->model('attorneys_biodata_model', array('find' => 'all', 'where' => 'attorney_id = '.$values['id']));

I think $values is not working because that data isn't available until after the form is submitted?

Here's form_fields():

function form_fields($values = array()){ 	
		$foreign_id = $this->fuel->model('attorneys_biodata_model', array('find' => 'all', 'where' => 'attorney_id = '.uri_segment(2)));
		
		$fields = parent::form_fields($values);
		$fields['name_f'] = array('label' => 'First Name', 'required' => TRUE); 
		$fields['name_m'] = array('label' => 'Middle Name/Initial', 'required' => FALSE); 
		$fields['name_l'] = array('label' => 'Last Name', 'required' => TRUE);  
		$fields['slug'] = array('label' => 'Slug', 'required' => FALSE, 'description' => 'If you do not provide one, one will be automatically generated.', 'type' => 'slug', 'linked_to' => 'none');  
		$fields['title'] = array('label' => 'Title of Position', 'required' => TRUE, 'description' => 'The title of their position, such as Esquire or Senior Partner.');
		$fields['title_abbrev'] = array('label' => 'Abbreviation of Title', 'required' => FALSE, 'description' => 'An abbreviation of the title: Esquire > Esq.'); 
		$fields['image']['class'] = 'asset_select images/attorneys'; 
		$fields['biodata_heading'] = array('type' => 'section', 'value' => 'Attorney\'s Biographical Data'); 
		$fields['biodata[practice_areas]'] = array(
			'display_label' => FALSE, 
			'required' => FALSE, 
			'repeatable' => TRUE, 
			'type' => 'template', 
			'fields' => array(
				'practice' => array(
					'label' => 'Practice Areas'
				)
			)
		); 
		$fields['biodata[education]'] = array(
			'display_label' => FALSE, 
			'required' => FALSE, 
			'repeatable' => TRUE, 
			'type' => 'template', 
			'fields' => array(
				'university' => array(
					'label' => 'University'
				), 
				'degree' => array(
					'label' => 'Degree'
				),
				'major' => array(
					'label' => 'Major(s)'
				)
				
			)
		); 
		
		$fields['biodata[bar_admissions]'] = array(
			'display_label' => FALSE, 
			'required' => FALSE, 
			'repeatable' => TRUE, 
			'type' => 'template', 
			'fields' => array(
				'bar' => array(
					'label' => 'Bar Admissions'
				)
			)
		); 
		
		$fields['biodata[court_admissions]'] = array(
			'display_label' => FALSE, 
			'required' => FALSE, 
			'repeatable' => TRUE, 
			'type' => 'template', 
			'fields' => array(
				'court' => array(
					'label' => 'Court Admissions'
				)
			)
		); 
		
		$fields['biodata[faculty_memberships]'] = array(
			'display_label' => FALSE, 
			'required' => FALSE, 
			'repeatable' => TRUE, 
			'type' => 'template', 
			'fields' => array(
				'faculty' => array(
					'label' => 'Faculty Memberships'
				)
			)
		); 
		
		$fields['biodata[industry_recognition]'] = array(
			'display_label' => FALSE, 
			'required' => FALSE, 
			'repeatable' => TRUE, 
			'type' => 'template', 
			'fields' => array(
				'industry' => array(
					'label' => 'Industry Recognition', 
					'display_preview' => TRUE, 
					'type' => 'asset',
					'upload' => TRUE, 
					'overwrite' => TRUE, 
					'class' => 'asset_select images/attorneys/{name_l}'
				)
			)
		); 
		
		$fields['biodata[fellowships]'] = array('type' => 'list_items', 'list_type' => 'ul', 'description'=>'Separate each fellowship one by hitting ENTER/RETURN.', 'label' => 'Fellowships'); 
		
		//$fields['image_upload']['upload_path'] = assets_server_path('attorneys', 'images'); 
		if(!empty($values['image'])){ 
			//$fields['image_upload']['before_html'] = '<div class="img_display"><img src="'.img_path('attorneys/'.$values['image']).'" style="float: right;" /></div>'; 

		} 
		return $fields; 
	}

In repeatable template fields, I was thinking it would be useful to make the name of the field = to the id which corresponds to it in attorneys_biodata so the info is easily accessible. Would that work? Like this:

$fields['biodata[practice_areas]'] = array(
			'display_label' => FALSE, 
			'required' => FALSE, 
			'repeatable' => TRUE, 
			'type' => 'template', 
			'fields' => array(
				$foreign_id->practice_areas => array(
					'label' => 'Practice Areas'
				)
			)
		);

admin · January 2016

You may run into issues with not deleting it all before looping through to save in cases where you delete a repeatable row.

You are correct in that the values information is empty until saved so you have to check if it exists yet. You would need to do a query in your form_fields method using the $values['id'] to get all the values for the biodata:


function form_fields($values = array(), $related = array())
{
     $this->CI->load->model('biodata_model');
     if (!empty($values['id']))
     {
          $biodata = $this->CI->biodata_model->find_all_array(array('attorney_id', $values['id']);
          // Next, add a hidden ID field for each repeatable area
     }
     ...
}

Regarding your last idea there, I don't think it will work.

zwelldesigns · January 2016

Okay, that's good to know. I have done as you said and I have been able to pass the ID through like I wanted.

Now I have some new questions:

1. What's the best way to populate template fields with existing values? Right now, I have a so-so workaround (not great, but it's good for now), where a foreach loops through all existing data and creates a field for each value found:

$biodata = $this->CI->attorneys_biodata_model->find('all', 'attorney_id = '.$values['id'], 'id ASC');
			if(!empty($biodata)){ 
				foreach($biodata as $bd){
					$fields['biodata[_'.$bd['biodata'].']'] = array(
						'display_label' => FALSE, 
						'required' => FALSE, 
						'repeatable' => TRUE, 
						'type' => 'template', 
						'fields' => array(
							$bd['biodata'] => array( 
								'value' => $bd['biodata_value']
							),
							'_'.$bd['biodata'].'_id' => array(
								'type'=>'hidden', 
								'value'=> $bd['id']
							)
						)
					);
				}
			}
		}

I would like not to have to do that for a few reasons. Is there a better way to keep my underlying form fields ordering and just populate if a value exists?

2. In my current foreach loop, I tried to create the $fields keys dynamically, setting them like this:
$fields['biodata['.$bd['biodata'].']'] = array(

This doesn't work UNLESS I add a character before $bd['biodata']. I have been using an underscore, but any valid array key character makes it work. Why is this? Is there a way to generate $fields keys without a leading character?

This works:
$fields['biodata[_'.$bd['biodata'].']'] = array(

(Notice the leading underscore.)

3. If I cannot populate repeatable template fields differently....
Right now, if, for instance, there are multiple "education" values in the database, then multiple $fields['biodata[education]'] are created. How can I make it so that only ONE $fields['biodata[education]'] is created, and then have the multiple values populated within the (repeatable) fields array of $fields['biodata[education]']?

zwelldesigns · January 2016

Nevermind about this nonsense, I decided my way was unnecessarily complex. I have made it so all the biographical info is stored along with the rest of the attorney's profile in serialized fields. Made it so much easier!

Howdy, Stranger!

Categories

What's the best way to populate an associative MYSQL table?

Comments