CodeIgniter Forums
it's a foreign key? - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived General Discussion (https://forum.codeigniter.com/forum-21.html)
+--- Thread: it's a foreign key? (/thread-7674.html)



it's a foreign key? - El Forum - 04-18-2008

[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..


it's a foreign key? - El Forum - 04-18-2008

[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;
}



it's a foreign key? - El Forum - 04-18-2008

[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.


it's a foreign key? - El Forum - 04-20-2008

[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


it's a foreign key? - El Forum - 04-20-2008

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


it's a foreign key? - El Forum - 04-20-2008

[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;



it's a foreign key? - El Forum - 04-21-2008

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

Thank youuu :coolsmile: