I guess many of you have already noticed a minor bug when you put in your $related_title_field parameter a field that accept NULL values: the resulting value may also be NULL, regardless of some other field is not null.
For example, let's assume you have built a simple customer CRUD.
The customer table has the ID, NAME, PHONE1 and PHONE2 fields.
ID is the primary key, NAME is not null, but PHONE1 and PHONE2 are nullables.
Suppose the following records in the customer table:
1, 'BOB', null, null
2, 'MARY', '555-4444', null
3, 'PETER', null, '555-3333'
Now let's also assume there's an order CRUD. For simplicity sake, the order table has only the following fields: ID, CUSTOMER_ID. ID is the primary key, CUSTOMER_ID is a foreign key, related to the customer table.
The records in the order table are:
1001, 3
1002, 1
1003, 2
If you want to display the customer name and its phone numbers in the order list, you would do something like this:
$crud = new grocery_CRUD();
...
$crud->set_relation('customer_id','customers','{name} {phone1} {phone2}');
$crud->set_table('orders');
...
$crud->render();
In that case, the customer_id column is always empty.
Grocery CRUD uses the CONCAT function to build up the final output value, and that's the cause of the problem.
We can easily fix that by using the COALESCE function. Check it out:
[b]FILE: application/models/grocery_model.php[/b]
function get_relation_array($field_name , $related_table , $related_field_title) {
...
if(strstr($related_field_title,'{'))
// OLD // $select .= "CONCAT('".str_replace(array('{','}'),array("',",",'"),mysql_escape_string($related_field_title))."') as $field_name_hash";
/* NEW */ $select .= "CONCAT('".str_replace(array('{','}'),array("',COALESCE(",", ''),'"),mysql_escape_string($related_field_title))."') as $field_name_hash";
else
$select .= "$related_table.$related_field_title as $field_name_hash";
...
}
function get_list() {
...
if(strstr($related_field_title,'{'))
// OLD // $select .= ", CONCAT('".str_replace(array('{','}'),array("',",",'"),mysql_escape_string($related_field_title))."') as $unique_field_name";
/* NEW */ $select .= ", CONCAT('".str_replace(array('{','}'),array("',COALESCE(",", ''),'"),mysql_escape_string($related_field_title))."') as $unique_field_name";
else
$select .= ", $unique_join_name.$related_field_title as $unique_field_name";
...
}
-Rafael
https://github.com/s...y-crud/issues/5