get mysql table structure with php

Question

I have a script which takes a database and generate export like query for the database as a backup. I need this script for a purpose. My php code is:

mysql_query("SHOW CREATE TABLE `myTable`");

there are 17 tables in the database. I am running a for loop for all 17 tables. I get the create table query with foreign key which I do not want. It returns like-

CREATE TABLE `customers` (
  `person_id` int(10) NOT NULL,
  `account_number` varchar(255) DEFAULT NULL,
  `taxable` int(1) NOT NULL DEFAULT '1',
  `deleted` int(1) NOT NULL DEFAULT '0',
  UNIQUE KEY `account_number` (`account_number`),
  KEY `person_id` (`person_id`),
  CONSTRAINT `customers_ibfk_1` FOREIGN KEY (`person_id`) REFERENCES `people` (`person_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

because of the foreign key, it gets an error while running queries for creating tables. is there any way to get the sql without foreign key? like-

CREATE TABLE IF NOT EXISTS `customers` (
  `person_id` int(10) NOT NULL,
  `account_number` varchar(255) DEFAULT NULL,
  `taxable` int(1) NOT NULL DEFAULT '1',
  `deleted` int(1) NOT NULL DEFAULT '0',
  UNIQUE KEY `account_number` (`account_number`),
  KEY `person_id` (`person_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Have you investigated any of the db backup tools that are out there already rather than trying to reinvent the wheel? You can use MySQL's native replication for backups along with programs like mysqldump or mysqlhotcopy. — liquorvicar
– liquorvicar, Commented Dec 29, 2011 at 13:30

Henrik · Accepted Answer · 2011-12-29 12:39:52Z

3

Disable foreign keys during the creation of your tables.

SET FOREIGN_KEY_CHECKS=0

CREATE TABLE a
CREATE TABLE b
...

Enable keys again

SET FOREIGN_KEY_CHECKS=1

answered Dec 29, 2011 at 12:39

Henrik

3,7041 gold badge21 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

James C · Accepted Answer · 2011-12-29 12:51:31Z

0

You could disable foreign keys checks as suggested elsewhere. If you really want to remove the constraints from the CREATE TABLE statements the following code could be used, it assumes that $createSql will contain the CREATE TABLE SQL statement:

$createSql = preg_replace('/CONSTRAINT[^\n]+/im', '', $createSql);
$createSql = preg_replace('/,\s+\)/im', "\n)", $createSql);

answered Dec 29, 2011 at 12:51

James C

14.2k1 gold badge37 silver badges45 bronze badges

Collectives™ on Stack Overflow

get mysql table structure with php

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related