CODEWITHSUNDEEP
phpmysql
Imrul.H
Imrul.H

Reputation: 5870

get mysql table structure with php

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;

Upvotes: 0

Views: 798

Answers (2)

James C
James C

Reputation: 14149

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);

Upvotes: 0

Henrik
Henrik

Reputation: 3704

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

Upvotes: 3

Related Questions