note 54941 modified in ref.mysql by zak
| From: | zak@php.net | Date: | Sun, 07 Aug 2005 01:33:55 +0000 |
| Subject: | note 54941 modified in ref.mysql by zak | ||
| References: | 1 | Groups: | php.notes |
| Request: | Send a blank email to php-notes+get-93182@lists.php.net to get a copy of this message | ||
[Editor's Note: In MySQL v5.0+, you can use the INFORMATION_SCHEMA tables to retrieve
information on tables, views, databases and so on. --zak@php.net]
Here is a small function to parse a mysql creation table DDL. The function takes a string with the
SQL code to create a table and returns the table name, table fields, table key fields and fields
type, all in arrays (except by the name, obviously). The function requires that the primary key be
named "id" and the foreign keys named "id...". All foreign key types are suposed
to be int (or its variations, bigint, etc...). All those restrictions are easily modified to others
needs.
Here is a example of a DDL code.
CREATE TABLE
telefones (
id int(11) NOT NULL auto_increment,
id_tipo_telefone int(11) NOT NULL default '0',
numero varchar(15) NOT NULL default '',
id_pessoa int(11) NOT NULL default '0',
PRIMARY KEY (id),
KEY id_tipo_telefone (id_tipo_telefone),
KEY id_pessoa (id_pessoa),
CONSTRAINT 0_775 FOREIGN KEY (id_tipo_telefone) REFERENCES
tipos_telefone (id),
CONSTRAINT 0_776 FOREIGN KEY (id_pessoa) REFERENCES pessoas
(id)
) TYPE=InnoDB
that returns
$tbname = "telefones"
$fields = array("numero");
$kfields = array("id_tipo_telefone","id_pessoa");
$tipos = array("varchar");
Hope it helps...
<?php
function parseQuery($Q, &$tbname, &$fields, &$kfields, &$tipos) {
/** rules to a corect parse:
*
* 1 - primary key must be named "id"
* 2 - foreign key must be named "id..." eg.: id_field
* 3 - lowercase is recomended
*/
$Q = str_replace(array(chr(10),chr(13))," ",$Q);
$Q = str_replace(array("'","`")," ",$Q);
preg_match("/([^(]*)\((.*)\)(.*)/",$Q,$A);
$part1 = $A[1];
$part2 = $A[2];
$part3 = $A[3];
preg_match("/(.*) ([a-zA-Z_]+)/",$part1,$A);
$tbname = strtolower($A[2]);
$temp = split(",",$part2);
foreach ($temp as $t) {
preg_match("/ *([a-zA-Z_]+) +([a-zA-Z_]+)(.*)/",$t,$A);
$pcampo = strtolower($A[1]);
$ptipo = strtolower($A[2]);
if (!preg_match("/$pcampo/","primary key constraint id unique foreign")
) {
if ( ($pcampo[0] == "i") && ($pcampo[1] == "d") )
$kfields[] = $pcampo;
else {
$fields[] = $pcampo;
$tipos[] = $ptipo;
}
}
}
}
?>
--was--
Here is a small function to parse a mysql creation table DDL. The function takes a string with the
SQL code to create a table and returns the table name, table fields, table key fields and fields
type, all in arrays (except by the name, obviously). The function requires that the primary key be
named "id" and the foreign keys named "id...". All foreign key types are suposed
to be int (or its variations, bigint, etc...). All those restrictions are easily modified to others
needs.
Here is a example of a DDL code.
CREATE TABLE telefones (
id int(11) NOT NULL auto_increment,
id_tipo_telefone int(11) NOT NULL default '0',
numero varchar(15) NOT NULL default '',
id_pessoa int(11) NOT NULL default '0',
PRIMARY KEY (id),
KEY id_tipo_telefone (id_tipo_telefone),
KEY id_pessoa (id_pessoa),
CONSTRAINT 0_775 FOREIGN KEY (id_tipo_telefone) REFERENCES
tipos_telefone (id),
CONSTRAINT 0_776 FOREIGN KEY (id_pessoa) REFERENCES pessoas
(id)
) TYPE=InnoDB
that returns
$tbname = "telefones"
$fields = array("numero");
$kfields = array("id_tipo_telefone","id_pessoa");
$tipos = array("varchar");
Hope it helps...
<?php
function parseQuery($Q, &$tbname, &$fields, &$kfields, &$tipos) {
/** rules to a corect parse:
*
* 1 - primary key must be named "id"
* 2 - foreign key must be named "id..." eg.: id_field
* 3 - lowercase is recomended
*/
$Q = str_replace(array(chr(10),chr(13))," ",$Q);
$Q = str_replace(array("'","`")," ",$Q);
preg_match("/([^(]*)\((.*)\)(.*)/",$Q,$A);
$part1 = $A[1];
$part2 = $A[2];
$part3 = $A[3];
preg_match("/(.*) ([a-zA-Z_]+)/",$part1,$A);
$tbname = strtolower($A[2]);
$temp = split(",",$part2);
foreach ($temp as $t) {
preg_match("/ *([a-zA-Z_]+) +([a-zA-Z_]+)(.*)/",$t,$A);
$pcampo = strtolower($A[1]);
$ptipo = strtolower($A[2]);
if (!preg_match("/$pcampo/","primary key constraint id unique foreign")
) {
if ( ($pcampo[0] == "i") && ($pcampo[1] == "d") )
$kfields[] = $pcampo;
else {
$fields[] = $pcampo;
$tipos[] = $ptipo;
}
}
}
}
?>
http://php.net/manual/en/ref.mysql.php