Grocery relation sql databases

Question

I use grocery-crud for a simple SQL select

$crud->set_table('lista_ab');
$crud->set_relation('id_ab','lista_ab_term','Expire');

The problem is that it does not make the relation for 'id_ab'

My database looks

CREATE TABLE `lista_ab` (
  `id_ab` int(10) NOT NULL,
  `Subname` varchar(255) DEFAULT NULL,
  `Name` varchar(255) DEFAULT NULL,
  `Inregistrat` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `lista_ab_term` (
  `ID` int(10) NOT NULL,
  `id_ab` int(10) DEFAULT NULL,
  `Expire` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

In final I want to extract Subname and Expire.

Answer 1

You cannot create dropdown list and show field name of the first table : Subname, but you can have as many fields you like to call from the other table and the syntax is really simple. Just at the 3rd field you will have the symbol { and } . So it will be for example:

$crud->set_relation('id_ab','lista_ab_term','{Expire} - {ID}');

Answer 2

You can use join query like this for your expected results:

SELECT t1.Subname, t2.Expire FROM lista_ab t1 LEFT JOIN lista_ab_term t2 ON t1.id_ab = t2.id_ab

Or In Codeigniter

$this->db->select('lista_ab.Subname, 
                   lista_ab_term.Expire');
$this->db->from('lista_ab');
$this->db->join('lista_ab_term', 'lista_ab.id_ab= lista_ab_term.id_ab');
$q = $this->db->get();