Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

create statements from a mysql dump file

Posted on 2014-01-01
5
334 Views
Last Modified: 2014-01-03
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
;
0
Comment
Question by:rgb192
  • 2
  • 2
5 Comments
 
LVL 34

Expert Comment

by:gr8gonzo
ID: 39750567
<?php
$src = file_get_contents("yourdumpfile.sql");

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

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

?>
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 39751236
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

0
 

Author Comment

by:rgb192
ID: 39754220
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
0
 
LVL 34

Accepted Solution

by:
gr8gonzo earned 500 total points
ID: 39754232
Just do a foreach loop and dump the values:

foreach($matches[0] as $query)
{
  echo $query . "\n\n";
}
0
 

Author Closing Comment

by:rgb192
ID: 39755494
this code works to show all my table creates

thanks
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question