create statements from a mysql dump file

CREATE TABLE `a_images` (
  `a_images_id` int(11) NOT NULL auto_increment,
  `profile_id` varchar(20) default NULL,
  `image_name` varchar(100) default NULL,
  `image_url` varchar(200) default NULL,
  PRIMARY KEY  (`a_images_id`)
) ENGINE=MyISAM AUTO_INCREMENT=88 DEFAULT CHARSET=utf8;


insert into table
...

...


CREATE TABLE `a_images2` (
  `a_images_id` int(11) NOT NULL auto_increment,
  `profile_id` varchar(20) default NULL,
  `image_name` varchar(100) default NULL,
  `image_url` varchar(200) default NULL,
  PRIMARY KEY  (`a_images_id`)
) ENGINE=MyISAM AUTO_INCREMENT=88 DEFAULT CHARSET=utf8;


insert
...
...
...

CREATE TABLE `a_images3` (
  `a_images_id` int(11) NOT NULL auto_increment,
  `profile_id` varchar(20) default NULL,
  `image_name` varchar(100) default NULL,
  `image_url` varchar(200) default NULL,
  PRIMARY KEY  (`a_images_id`)
) ENGINE=MyISAM AUTO_INCREMENT=88 DEFAULT CHARSET=utf8;

Open in new window

this is a mysql dump file

using php please echo
only the create statements
beginning with
CREATE TABLE
and ending with
;

PHP

Last Comment

rgb192

8/22/2022 - Mon

gr8gonzo

1/1/2014

<?php
$src = file_get_contents("yourdumpfile.sql");

preg_match_all("/CREATE TABLE [^;]+;/",$src,$matches);

print_r($matches[0]); // $matches[0] will have the statements.

?>

Ray Paseur

1/2/2014

I'm pretty sure @gr8gonzo has a good REGEX there, but I also wonder about the usefulness of the programmatic solution. It took me less than 30 seconds to do it all by hand in my text editor. See number 1205.

CREATE TABLE `a_images` (
  `a_images_id` int(11) NOT NULL auto_increment,
  `profile_id` varchar(20) default NULL,
  `image_name` varchar(100) default NULL,
  `image_url` varchar(200) default NULL,
  PRIMARY KEY  (`a_images_id`)
) ENGINE=MyISAM AUTO_INCREMENT=88 DEFAULT CHARSET=utf8;
CREATE TABLE `a_images2` (
  `a_images_id` int(11) NOT NULL auto_increment,
  `profile_id` varchar(20) default NULL,
  `image_name` varchar(100) default NULL,
  `image_url` varchar(200) default NULL,
  PRIMARY KEY  (`a_images_id`)
) ENGINE=MyISAM AUTO_INCREMENT=88 DEFAULT CHARSET=utf8;
CREATE TABLE `a_images3` (
  `a_images_id` int(11) NOT NULL auto_increment,
  `profile_id` varchar(20) default NULL,
  `image_name` varchar(100) default NULL,
  `image_url` varchar(200) default NULL,
  PRIMARY KEY  (`a_images_id`)
) ENGINE=MyISAM AUTO_INCREMENT=88 DEFAULT CHARSET=utf8;

Open in new window

rgb192

1/3/2014

ASKER

I understand Ray's link, but gr8gonzo code appears to work in every mysql dump file so I can reuse many times

gr8gonzo:
how to echo
without
[0]=>
[1]=>
[2]=>
[3]=>

so i can copy paste into my mysql query editor

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst

William Peck

ASKER CERTIFIED SOLUTION

gr8gonzo

1/3/2014

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts