note 96521 deleted from function.mysql-affected-rows by danbrown
| From: | danbrown@php.net | Date: | Wed, 03 Mar 2010 14:46:15 +0000 |
| Subject: | note 96521 deleted from function.mysql-affected-rows by danbrown | ||
| References: | 1 | Groups: | php.notes |
| Request: | Send a blank email to php-notes+get-166574@lists.php.net to get a copy of this message | ||
Note Submitter: eugene at ultimatecms dot co dot za
----
This is a useful way to get foreign key variables from a specific sql table.
This function can be used to include all relevant data from all relating tables:
<?php
function get_string_between($string, $start, $end){
$string = " ".$string;
$ini = strpos($string,$start);
if($ini == 0)
return $tbl;
$ini += strlen($start);
$len = strpos($string,$end,$ini) - $ini;
return substr($string,$ini,$len);
}
function get_foreign_keys($tbl) {
$query = query_getrow("SHOW CREATE TABLE ".mysql_escape_string($tbl));
$dat = explode('CONSTRAINT',$query[1]);
foreach($dat as $d => $a) {
if(strpos($a,"FOREIGN KEY"))
$data['keys'][] = array($tbl,get_string_between($a,"
FOREIGN
KEY (","`) REFERENCES"));
}
foreach($dat as $d => $a) {
if(strpos($a,"REFERENCE"))
$data['references'][] = explode('
(',get_string_between($a,"REFERENCES ",") ON"));
}
return $data;
}
//Example code:
$data = get_foreign_keys('task_table');
echo '<pre>';
print_r($data);
echo '</pre>';
?>
// $query[1] outputs:
CREATE TABLE task_table (
task_id int(64) NOT NULL auto_increment,
ticket_id int(64) NOT NULL,
task_type varchar(64) NOT NULL,
comment text,
assigned_to int(11) default NULL,
dependant int(64) default NULL,
resolved int(1) default NULL,
PRIMARY KEY (task_id),
KEY ticket_id (ticket_id,dependant),
KEY assigned_to (assigned_to),
KEY task_dependant (dependant),
CONSTRAINT task_table_ibfk_1 FOREIGN KEY (ticket_id) REFERENCES
tickets_table (ticket_id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT task_table_ibfk_2 FOREIGN KEY (assigned_to) REFERENCES
contact_table (contact_id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT task_table_ibfk_3 FOREIGN KEY (dependant) REFERENCES
task_table (task_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
// $data outputs:
Array (
[keys] => Array
(
[0] => Array
(
[0] => task_table
[1] => ticket_id
)
[1] => Array
(
[0] => task_table
[1] => assigned_to
)
[2] => Array
(
[0] => task_table
[1] => dependant
)
)
[references] => Array
(
[0] => Array
(
[0] => tickets_table
[1] => ticket_id
)
[1] => Array
(
[0] => contact_table
[1] => contact_id
)
[2] => Array
(
[0] => task_table
[1] => task_id
)
)
)