CodeIgniter Forums

Full Version: it's a foreign key?
You're currently viewing a stripped down version of our content. View the full version with proper formatting.

El Forum

[eluser]zizzo[/eluser]
hi everyonee,

i've a new question for youu, i've a method for knowing if a key it's a foreign key, but it's so looong...i would want know if there is a faster method to know if a key in a database's table it's a foreign key...and maybe also know which table refer..

perhaps my request it's not so clear..

El Forum

[eluser]xwero[/eluser]
if you name your fieldnames well it's easy to know if a field is foreign key or not.
Code:
is_foreign_key($fieldname)
{
   $tables = $this->db->list_tables();

   foreach ($tables as $table)
   {
      $length = strlen($table);
      if(strncasecmp($table,$fieldname,$length) == 0)
      {
          return TRUE;
      }
   }

   return FALSE;
}

El Forum

[eluser]gtech[/eluser]
if you are using mysql you can do something like
Code:
select column_key from information_schema.columns where
table_schema='<your database name>' and
table_name='<your table name>' and
column_name='<your column name>';
if MUL is returned its a foreign key, if PRI its a primary if UNI its unique etc.

El Forum

[eluser]zizzo[/eluser]
if i try your query with myphpadmin i obtain a correct result (MUL or PRI), but when i use, in my controller, this:
Code:
$query="SELECT column_key FROM information_schema.columns
WHERE table_schema='sea' AND table_name= ? AND column_name= ?";

$foreign=$this->ci->db->query($query, array($this->table, $field_name));

in $foreign i have "Object id #15"

mmm why?

thanks Smile

El Forum

[eluser]zizzo[/eluser]
no idea? Sad

El Forum

[eluser]gtech[/eluser]
try
Code:
$query="SELECT column_key FROM information_schema.columns
WHERE table_schema='custandusers' AND table_name='users' AND column_name='customerid'";

$foreign=$this->db->query($query);
// add this line below to your code.
print_r($foreign->result_array());

I get
Code:
Array ( [0] => Array ( [column_key] => MUL ) )


or

Code:
...
$foreign=$this->db->query($query);
$foreign=$foreign->row_array();
$type=$foreign['column_key'];
echo $type;

El Forum

[eluser]zizzo[/eluser]
Perfect solution!! With your hints i've solved!

Thank youuu :coolsmile: