it's a foreign key?

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

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

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

#4
[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

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

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

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

Thank youuu :coolsmile:


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.