note 96521 added to function.mysql-affected-rows
| From: | eugene at ultimatecms dot co dot za | Date: | Wed, 03 Mar 2010 14:39:55 +0000 |
| Subject: | note 96521 added to function.mysql-affected-rows | ||
| Groups: | php.notes | ||
| Request: | Send a blank email to php-notes+get-166573@lists.php.net to get a copy of this message | ||
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
)
)
)
----
Server IP: 69.147.83.197
Probable Submitter: 196.25.255.214
----
Manual Page -- http://www.php.net/manual/en/function.mysql-affected-rows.php
Edit -- https://master.php.net/note/edit/96521
Del: integrated -- https://master.php.net/note/delete/96521/integrated
Del: useless -- https://master.php.net/note/delete/96521/useless
Del: bad code -- https://master.php.net/note/delete/96521/bad+code
Del: spam -- https://master.php.net/note/delete/96521/spam
Del: non-english -- https://master.php.net/note/delete/96521/non-english
Del: in docs -- https://master.php.net/note/delete/96521/in+docs
Del: other reasons-- https://master.php.net/note/delete/96521
Reject -- https://master.php.net/note/reject/96521
Search -- https://master.php.net/manage/user-notes.php