⚠ In case you've missed it, we have migrated to our new website, with a brand new forum. For more details about the migration you can read our blog post for website migration. This is an archived forum. ⚠

  •     

profile picture

How to get default values from the database



davidoster
  • profile picture
  • Member

Posted 14 May 2013 - 04:44 AM

Recently I was trying to read the default values I had set on the fields of a table in my database.

Since it was a succesful way of doing it I thought to share it with you.

 

It invloves:

1. creating a model that extends the CI_Model with the following code

/* make a file under application/models/ called db_model.php */
<?php
class db_model extends CI_Model  {
public function get_default($table, $field)
{
	$query = "SHOW COLUMNS FROM `" .  $table . "` WHERE field = '" . $field . "'";
	$result = $this->db->query($query);
	return $result;
}


}

2. within your controller's function declaring a callback_field

public function my_controller()
{
...
$crud = new Grocery_CRUD();
$crud->set_table('my_table');
$crud->callback_field('a_field',array($this,'cb_a_field'));
...
}

3. coding the actual callback for each field

function cb_a_field($v, $pk = null)
{
	$this->load->model('db_model');
	$f = $this->db_model->get_default('my_table', 'a_field')->field_data();
        foreach($f as $field) { $v = $field->default; } 
        $return_value = '<div id="field-a_field" class="xxx">' . $v .'</div>'; 
        return $return_value; 
}

The $return_value can be any valid html component. Check here the ones that Grocery CRUD supports out of the box.


DrPaul
  • profile picture
  • Member

Posted 07 August 2014 - 17:19 PM

A useful bit of code, many thanks.

 

Certainly works for values held in a text field, though your example is a little misleading - $return_value won't contain a <div> but a form input element. In your example this would be something like this (using flexigrid):

 

     $return_value = "<input id='field-a_field' name='a_field' type='text' value=\"$v\">";
 

Now, how to get it to work for a SELECT statement?

 

One last observation - surely in the controller you should use $crud->callback_add_field() rather than $crud->callback_field()? Your code will overwrite the value from the db if you try to do an edit - default values should only be used when adding new records.


DrPaul
  • profile picture
  • Member

Posted 10 August 2014 - 12:20 PM

Hmm, used this approach for a couple of days but in the end I decided that I don't really see the point of having to write and invoke a callback function every time there's a default value to be found - you might as well hard-code the value in the callback and be done with it. Database default values are generally fixed by their very nature.

 

As I think others have said, GC should support default values on add screens "out of the box".

 

So I had a bit of a poke around in the core code, and re-factored some of your code, with the result that I've been able to mod GC to automatically display default values for all(?) data types. This requires a simple helper function and a mod to just one line of the core GC code:

 

First off, add a helper function to your system (see the GC documentation for details on implementing helpers):

if ( ! function_exists('get_input_default_value'))
{
	function get_input_default_value($table, $field)
	{
		$crud = & get_instance();
		$query = "SHOW COLUMNS FROM `" .  $table . "` WHERE field = '" . $field . "'";
		$result = $crud->db->query($query)->field_data();
		foreach($result as $field) { $v = $field->default; }
		return isset($v) ? $v : null;
	}
	
}

Then make one change to the get_add_input_fields() function in Grocery_CRUD.php, which starts around line 2650 of the latest version of GC. 11 lines into the function definition, remove or comment out this line:

$field_value = !empty($field_values) && isset($field_values->{$field->field_name}) ? $field_values->{$field->field_name} : null;

and replace it with this line:

$field_value = get_input_default_value($this->basic_db_table, $field->field_name);

This is working for me with text fields and SELECT statements, it may need some tweaking to deal with date formats - I will do some testing and report back (this post is "hot off the presses")

 

Update: No tweaking required, works fine for dates as well.